db locking

AAAAGGGGHHHH! :-/

Frustrations with SQLite.!!

I post this in the hopes that it can prevent others from having the same problems I"ve had with SQLite.

SQLite has it's place for holding and looking up information.

I figured I could work around the fact that it locks the entire DB when it does a write. But that is not the case. The real problem is SQLite's inability to free them up.

At times, I've had to read the DB into memory so it would think it had 2 databases.. But for some reason that confuses SQLite and many times it still locks up the system.. You would think that when reading from the memory file and writing to the disk DB file that wouldn't be the case?

Here for example is a code snippit of a very simple routine.. It reads a trace table, and a couple other tables. It needs to INSERT records based on some aggregate informations it finds.
However no matter if you connect the DB immediately before the insert and disconnect immediately after -- it locks the DB. The only out of the situation is to kill the RB server and restart the Console.
This happens to be one of the files I tried to move to memory so it reads from one DB and writes to another. But it still locks..

Can't wait for mySQL..
Hope this helps someone...

sqliteconnect #sql, runINVdb$ ' Connect to the DB

for tn = 1 to numWpo
thisWpoNum = val(word$(wpoList,ドルtn,","))
prefStorageId$ = ""
pretStorageId$ = ""

sql$ = "
SELECT
workTrace.wpoNum as wpoNum,
workTrace.fstorageId as fstorageId,
workTrace.fPreWpoNum as fPreWpo,
workTrace.fPreFromTo as fPreFT,
workTrace.tstorageId as tstorageId,
workTrace.tPreWpoNum as fPreWpo,
workTrace.tPreFromTo as fPreFT,
workTrace.tendQty as endQty,
wpoVariety.varietyNum as varietyNum,
wpoVariety.areaId as areaId,
wpoVariety.year as year,
sum(wpoVariety.pcnt) as sumPcnt,
avg(wpoVariety.pcnt) as avgPcnt,
sum(wpoVariety.qty) as sumQty,
sum(wpoVariety.glQty) as sumGlQty,
sum(wpoVariety.yield) as sumYield,
sum(wpoVariety.yield * workTrace.fmovQty * (wpoVariety.storageId = workTrace.fstorageId)) as fyq,
sum(wpoVariety.yield * workTrace.tmovQty * (wpoVariety.storageId = workTrace.tstorageId)) as tyq

FROM workTrace
LEFT JOIN wpoVariety
ON wpoVariety.wpoNum = fPreWpoNum
AND wpoVariety.fromTo = fPreFromTo
AND wpoVariety.storageId = fstorageId

OR wpoVariety.wpoNum = tPreWpoNum
AND wpoVariety.fromTo = tPreFromTo
AND wpoVariety.storageId = tstorageId

WHERE workTrace.wpoNum = ";thisWpoNum;"
GROUP BY workTrace.wpoNum,workTrace.tstorageId,
wpoVariety.varietyNum,wpoVariety.areaId,wpoVariety.year"

#sql execute(sql$)
WHILE #sql hasanswer()
#row = #sql #nextrow()
wpoNum = #row wpoNum()
year = #row year()
sumPcnt = #row sumPcnt()
avgPcnt = #row avgPcnt()
sumQty = #row sumQty()
sumGlQty = #row sumGlQty()
sumYield = #row sumYield()
fyq = #row fyq()
tyq = #row tyq()

sql1$ = "INSERT INTO wpoVariety VALUES(";_
wpoNum;",'";_
year;",";_
qty;",";_
sumGlQty;",";_
gaugeGlQty;",";_
concQty;",";_
avgPcnt;",";_
sumYield;")"

sqliteconnect #sql1, runINVdb$ ' Connect to the DB
#sql1 execute(sql1$)
#sql1 disconnect()

WEND
next tn
#sql disconnect()
wait


Have a great day..
I'm going out in the back yard and eat worms..
Dan

Is there an effective way of checking to see if a database is locked... or if a query actually succeeds? is there a way to force an unlock of a locked database WITHOUT rebooting the server?

Being new to SQLite may be why I'm having problems. So I'm probably not the right person to answer this. But I'll give you my experience if it can help.

If there is another way to unlock, I haven't found it.. Maybe someone knows something here.

I like SQLite. It's small and stand alone and uses flat files.
If you have a simple application without update complications, I'd go with SQLite.

It's not the size or number of tables that causes the problem, It's the application. I've had small databases with only 10 or 20 tables cause more problems than larger databases.

If you have lots of people updating tables, or have the DB opened multiple times in a application it will cause problems.

I've tried all kinds of ways to solve this. I even tried to write my own DB manager where all DB action is done with a call to a single program.

Basically, I've come to the conclusion it just isn't worth the effort. So I've been writing all my applications knowing I will have to move to mySQL.

I tried DB triggers, and it's just impossible to know who or what is locking the DB once you have DB triggers competing with your applications.. And as you know triggers can save you lots of code.

With the other problems and staying away from triggers, you write a lot more code for SQLite than of other DB's.

You can do on error traps. You get errors when it fails for many reason. For example duplicate keys. And depending on what you are doing you may want to begin and end a rollback.

Another BIG problem with SQLite is that you cannot do replication. I've already tested replication using mySQL so I'm ready when the day comes.

Hope this helps a little.
Dan

If you need to stay with sqlite... start passing tokens.

User "A" opens an sqlite database and a token is created as a file containing the date and time in a directory... User "A" has all rights to the database for as long as the token file exists. The token is deleted when user "A" is done with the database.

User "B" comes along and wants the same database, but has to check for any tokens first. If "B" finds a token he writes his own token into the token directory. Observe all tokens until it's your turn. If the oldest stored token get's a little too stale, allow "B" (or next younger user) to delete it and then access the database. Now "B" has all rights to the database.

This can all be coded into one function block.

It can even be moved from using token files to using an sqlite database...

Just some observaions.

As you know, SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business.

sqlite's "database is locked" error doesn't have multiple meanings. It means "I tried to obtain a lock on the database but failed because a separate process is holding conflicting lock". It's certainly expected in code creating multiple connections to the same DB.

My guess is that the database is locked error isn't a problem in sqlite, but somehow RB has set the lock state when reading so it thinks it is in a write lock.

To be more specific, sqlite's locking protocol allows for many readers and exactly one writer at any given time. ie. if you attempt to start two write transactions concurrently, the second will encounter SQLITE_BUSY. We should be ok here since everything is single threaded.

In rollback-journal mode, a read transaction will also encounter SQLITE_BUSY if it starts just as a write transaction enters its COMMIT phase - as stated before, sqlite locks the entire database for the duration of COMMIT. This doesn't happen in WAL mode, because COMMIT doesn't need to update the main database file (just the write ahead journal).

Anyway I think you came to the same conclusion, that SQLITE_BUSY is unavoidable sometimes when using multiple connections to the same database. In WAL mode, a writer and readers do not block each other, but there is still only one writer allowed. They suggest, in order to avoid deadlocks in SQLite, programmers who want to modify a SQLite database start the transaction with BEGIN IMMEDIATE. If the transaction cannot acquire the necessary locks, it will fail, returning SQLITE_BUSY. At that point, the transaction falls back to an unlocked state whereby it holds no locks against the database. So why does RB get locked forever sometimes? This means that any existing transactions in a RESERVED state can safely wait for the necessary EXCLUSIVE lock in order to finally write their modifications from the in-memory cache to the on-disk database. I have not tried this, but doubt it will work, since for some reason RB gets in a forever lock mode.

But even then, you can still easily get SQLITE_BUSY via a transaction along the lines of:

BEGIN;
SELECT MAX(x) FROM table1;
INSERT INTO table1 VALUES(x+1);
COMMIT;
This will by itself already have a write transaction in progress on table1 when this INSERT statement runs, sqlite will immediately return SQLITE_BUSY, without waiting for the busy timeout.


The good news.. Carl has said he may have found something that has fixed similar issues in LB and uses the same routine in RB. Not sure when these will be available.

Have a great day..
Good luck..

As I am just a hobbyist and do not have much experience, every detail deserves a warm welcome.

I keep thinking about the hasanswer() method, only following scrupulously the explanations found in the Run BASIC Help file.

Your simple contact database program published Jan 13 on the LB forum, but now generated for RB by the RBGen program, shows the following:

1) it uses the hasanswer() method to retrieve the content of the database.
2) it does not use the hasanswer() method to retrieve the NumRecord data.
3) it does not use the hasanswer() method to retrieve data with the WHERE clause.

Running the program, RB shows the main page of Contacts. It has not made a single write attempt to the database.
But when I connect another user, i.e. Database Browser for SQLite, the new user can read, but not write..... database locked...... Why?

When I modifiy the program and use the hasanswer() method also for the above mentioned points 2) and 3), it all works perfectly, no more locks.

So, isn't this the proof that the hasanswer() method is actually cleaning up the cursors, what was Carl's main concern ?

I am afraid Carl only found the disconnect() method we already know. We will see what happens....

Using hasanswer() to clean up the cursors, is actual proof of the problem. There are no locks in the first place. All documents in SQLite state that reads do not lock the DB, and you can have multiple processing reading the file. So no matter how I read the file, even using for - next and without disconnect(), should make no difference. It should never have been locked in the first place. Somehow RB thinks it's locked. It gets really complex when multiple users are connected and using the db. How many and what processes were really happening when it locked. We did find that setting some of the WAL statements really cut down the locks. Reading WAL is like reading a parable because combinations get complex.
Carl said he found something, so maybe it's fixed??
But releases here do not seem to exist. So we are rewriting into REBOL. Never ever got a lock with REBOL.
The system I have is kinda small. It's only about 100 tables, 150 programs, and about 350 users. Probably only about 150 users are active most of the time. Some of the tables have maybe 100M records. Some SQL statements are complex because of the nature of the business. Some over a page long. And some batch jobs run hours. However with some WAL statements we were able to cut that batch time from 20 hours to 6.

Have a great day..
Dan

Thanks, meerkat.
If I understand you well, I am cleaning up locks that - according to every logical reasoning - should never have been there. Kind of ghost locks..turning into real nightmares....
In that case it would seem that RB/LB5 could be somehow fooling the database ? That is a weird problem ......
I know the complexity of the systems you are dealing with, I saw most of your programs on this website. For me it is much simpler, personally I don't need web programming, so I will stick with LB 4.5.1 and LB 5 for the future...You are right, I won't bother you anymore. Let's wait for Carl's solution.

P.S. ionSQL works fine in LB 4.5.1.... with the SQ3_4_LB.dll.

I have spent a very frustrating couple of hours trying to amalgamate separate SQLite DB's into one with multiple tables.
using LB I created the tables and then inserted data into one table (customers)
RB would not recognise the table . I could query it from LB and read it with DB browser. all in tact.
So I decide to recreate the table using RB and then re-insert the data
I get 'Runtime error database is locked, delete the DB close the browser re start, copy in another copy of the DB
same error over and over.

sqliteconnect #records, "brewcalc.db"
query$ = "create table CUSTOMERS (Fname char(50), Surname char(50), Street char(50),Suburb char(30),Pcode char(15),Note char(50),Phone1 char(20),Mobile char(20),email char(50),custno char(20))"
#records execute(query$)
#records disconnect()

Am I missing something with the above code?

brewcalc.db (167 KB)

Interesting, after a few glasses of singing syrup I decided to close the RB console and try again.
St#f me the code above worked BUT.So can I conclude RB console locks the DB's or at least prevents closing?
not on the DB in the project folder that the code and the DB existed (Rbp101\projects\aaa_project) in
BUT a copy of the DB in Rbp101 main folder. ?????.

So note to self use DefaultDir$

I seem to remember in past not being unable to edit *.bas files within Rbp101 with an external editor as the files were locked.
I will now try again later to duplicate (one too many reds)

metro

Metro..
Your code looks good to me. I put your table in one of my existing databases - cnc, and then immediately dropped it.
And it worked ok..
I put my databases in a usbdirectory data. Just to keep it seperated from my code. I also do all the database dumps in that directory.
------------------------------------
dbName$ ="cnc"
sqliteconnect #db, "C:\rbp101\projects\cnc_project\data\cnc.db"
query$ = "create table CUSTOMERS (
Fname char(50),
Surname char(50),
Street char(50),
Suburb char(30),
Pcode char(15),
Note char(50),
Phone1 char(20),
Mobile char(20),
email char(50),
custno char(20)
)"

#db execute(query$)
query$ = "drop table CUSTOMERS"
#db execute(query$)
end
------------------------------

hope this helps. SQLite is very liberal in it's field definition. Used to be char() and varchar() were different. It saved space to use varchar() instead of char(). But they work the same today. But I can't get out of the habbit of not using varchar() for the fields.
You will also notice that SQLite doesn't care what you put in a field. If you have something defined as char(xx) you can put anything in it including integer, float, or images. So basically you could simply define everything as text and use it anyway you like.
But best to try to define it the right way just to avoid confusion. Also putting integer and characters in the same field gets a little confusing when you sort that field..

Good luck.

Thanks for the tips Dan

You might want to use the SQLite.bas program I posted for download. It lets you create fields, and test your queries to make sure they work befor you put the sql commands in you RB code..

Dan.

Yeah, i have used it multiple times, thanks Dan.
I find i get a better understanding having a go freehand.
But clearly need more practise
Thanks for the help


Metro