Post by pierre on Mar 26, 2020 5:57:39 GMT -5
Hello LB (and RB) users. I am new to this forum. Not really new to LB, for I bought it more than 10 years ago, but never did anything fascinating with it.
I am above all interested in databases so I studied a little the SQLite lock problems that haunt this forum and the RB forum since a very long time.
In my opinion, SQlite is not as bad as it seems to be. It all depends on your program. I cannot run meerkat's sample program, for I only have Runbasic's free version and it gives me a syntax error for the graphicbox command, but that is not essential. Meerkat's programs are very sophisticated and for others they are not easy to understand.
I have tried to simplify things by starting from the simple contact.bas program recently published on this forum (I mean, the SQlite version of it).
Beforehand, I apologize for the lenght of my comments. They are the result of all my findings so far.
1)Runbasic keeps the SQLite database locks.
Here is what the Runbasic help file tells us : « Opening and closing databases : in web programming it is important not to leave things like databases open, because if the user closes the browser, the server cannot know that this has happened....... ».
This doesn’t only apply to databases : Runbasic keeps the locks until the server itself is shut down, because Runbasic keeps the *.bas program running even when that program has been closed in the browser......
2) the SQLite database locks
Actually, there are two kinds of locks in SQLite :
a) Result code # 5 : The database file is locked.
b) Result code # 6 : A table in the database is locked.
The first lock is caused by two different database connections and it gives a SQLITE_BUSY error.
The second lock is caused by the database connection itself and gives a SQLITE_LOCKED error.
The problem is that all interfaces I have seen, only give one error « database locked », probably because of the fact that SQLite being one big file, it cannot lock just a table and leaves the locking process at file level to the Operating System.
a) the SQLITE_BUSY error --> the database is locked (by another connection)
According to the SQLite documentation, this only may last a couple of miliseconds, but you have to trap the error in order not to interrupt the program’s flow. So, if occasionally you stumble on a « write lock », then you have to capture it in a rollback and retry function, waiting for the other writer to commit and release the lock (see the SQLite.bas program on the Runbasic forum).
b) the SQLITE_LOCKED error (a table in the database is locked)
This the real problem : something in your application has gone the wrong way. It ist he most frequently occurring error and in many cases a coding issue and not a database problem. A rollback and retry function does not work, because it is up to your own program to release the lock, not to another user.
Here is a simple example (the « simple contact.bas » program, but now generated by RBGen.bas for Runbasic).
SQLite in the standard journal mode
Before running the program in Runbasic, modify the connectDb section.Remove all the referencies to the WAL mode and replace them by one line #sqlconnect etc. Verify in DB Browser if the database is actually in « delete » mode.
Errors found :
When you retrieve data from the database, you always have to use the « hasanswer » method,even when you retrieve only one item (i.e. aggregate functions like
Here is what the Runbasic help file tells us : « Opening and closing databases : in web programming it is important not to leave things like databases open, because if the user closes the browser, the server cannot know that this has happened....... ».
This doesn’t only apply to databases : Runbasic keeps the locks until the server itself is shut down, because Runbasic keeps the *.bas program running even when that program has been closed in the browser......
2) the SQLite database locks
Actually, there are two kinds of locks in SQLite :
a) Result code # 5 : The database file is locked.
b) Result code # 6 : A table in the database is locked.
The first lock is caused by two different database connections and it gives a SQLITE_BUSY error.
The second lock is caused by the database connection itself and gives a SQLITE_LOCKED error.
The problem is that all interfaces I have seen, only give one error « database locked », probably because of the fact that SQLite being one big file, it cannot lock just a table and leaves the locking process at file level to the Operating System.
a) the SQLITE_BUSY error --> the database is locked (by another connection)
According to the SQLite documentation, this only may last a couple of miliseconds, but you have to trap the error in order not to interrupt the program’s flow. So, if occasionally you stumble on a « write lock », then you have to capture it in a rollback and retry function, waiting for the other writer to commit and release the lock (see the SQLite.bas program on the Runbasic forum).
b) the SQLITE_LOCKED error (a table in the database is locked)
This the real problem : something in your application has gone the wrong way. It ist he most frequently occurring error and in many cases a coding issue and not a database problem. A rollback and retry function does not work, because it is up to your own program to release the lock, not to another user.
Here is a simple example (the « simple contact.bas » program, but now generated by RBGen.bas for Runbasic).
SQLite in the standard journal mode
Before running the program in Runbasic, modify the connectDb section.Remove all the referencies to the WAL mode and replace them by one line #sqlconnect etc. Verify in DB Browser if the database is actually in « delete » mode.
Errors found :
When you retrieve data from the database, you always have to use the « hasanswer » method,even when you retrieve only one item (i.e. aggregate functions like
« Select count(*) as numRecords from ....... » . You cannot simply assume that there will be an answer. See the Runbasic documentation. This is not done properly in RBGen and is not done properly in most of the programs I have seen on the Runbasic forum.
Wrap the result in a while - wend loop ) :
example :
sql$ = « select count(*) as numRecords from ..... »
#sql execute(sql$)
while #sql hasanswer()
#row = #sql #nextrow()
numRecords = #row numRecords()
..... /.......
wend
if you don’t use the hasanswer() method, you apparently do not release properly the read lock and you will leave a pointer in the database and that causes a table lock, stopping other users who want to write to the database And the next time Runbasic itself wants to write, the same errror occurs.....and then Runbasic keeps the lock....but that is a Runbasic problem.
In this case, other users can be :
- LB 4.5.1 --> doesn’t say anything, but does not commit.
- LB5-350 --> gives an arror message and locks.
- SQLite Expert --> gives an error message : database is locked
- DB Browser for SQLite --> gives something more : « database is locked. release restorepoint ».
Apparently the hasanswer () method, wrapped in a while-wend loop, releases that pointer.
N.B. Here, the « if......then.....end if » method does not give the expected result, I don’t know why ()
EDIT: The if ... then ... end if method must contain a for ... next loop: for i = 1 to #sql rowcount() .../...next i. Then it works.
Another solution is of course to program a disconnect/reconnect every time you have accessed the database, which also has the effect of releasing the pointer. In any case, you don’t need to do this always in order to write to the database. The #sql execute() method is sufficient.
In the simple RBGenerated program, there are only 2 places where this situation must be corrected.
With these changes applied, no locks whatsoever : If the RB app writes, you see immediately your changes, if another user writes, you only have to click « Next » in the Runbasic app in order to have your data refreshed.
We must also add « #sql disconnect() » after the doExit branch label .If we don’t do that, the database wil remain open until Runbasic itself is shut down.
Now RunBasic, LB5-350, LB 4.5.1, SQLite Expert and DB Browser all connected at the same time to the same database, can live happily together.
Writing to and reading from the WAL file (pragma journal_mode = WAL):
1) Update:
Use the 'journal_mode=WAL' pragma only 1 time to create the database. When this pragma is used every time the program starts, other users like SQLite Expert or DB Browser will be locked out. They cannot see the changes made by RB and when they eventually want to write, the database locks. If RB is working alone, we cannot see this problem. The WAL mode is persistent, so we don't have to repeat it all the time. The other anti-locking pragmas do not interfere, so they must stay as they are.
2) Apply the same changes as we did in the first example. That is : after every retrieve transaction, even for only 1 answer, use the hasanswer() method wrapping it in a while - wend loop.
3) Add an #sql disconnect() transaction to the [doExit] section. This assures the correct checkpointing of the WAL file to the main database file. If we don’t do this the database remains open until Runbasic itself is shut down.
No locks anymore. This apparently avoids overly repeated connect/disconnect transactions.
But perhaps the connect/disconnect method was the only way originally intended by the Runbasic interface ? Perhaps that is the way servers work : connect/disconnect .... connect/disconnect etc. all the time ?
N.B.
- the *.shm and *.wal files - visible during runtime - normally close when the last reader quits. They are not causing the database locks.
- SQLite has a number of functions to release locks under some very special circumstances. They only can be accessed via the C language interface. The « sqliteconnect » method in RB or LB5-350 apparently does not provide these facilities.
- RB nor LB5-350 do not provide neather the access to prepared statements with bind variables (available only through the SQLite C language interface). That is a pity, for many languages provide this possibility. By the way, the Runbasic program on the Rosetta Code site that is supposed to treat this problem, is incorrect : see the comments on the Rosetta site (I did not provide them).
Wrap the result in a while - wend loop ) :
example :
sql$ = « select count(*) as numRecords from ..... »
#sql execute(sql$)
while #sql hasanswer()
#row = #sql #nextrow()
numRecords = #row numRecords()
..... /.......
wend
if you don’t use the hasanswer() method, you apparently do not release properly the read lock and you will leave a pointer in the database and that causes a table lock, stopping other users who want to write to the database And the next time Runbasic itself wants to write, the same errror occurs.....and then Runbasic keeps the lock....but that is a Runbasic problem.
In this case, other users can be :
- LB 4.5.1 --> doesn’t say anything, but does not commit.
- LB5-350 --> gives an arror message and locks.
- SQLite Expert --> gives an error message : database is locked
- DB Browser for SQLite --> gives something more : « database is locked. release restorepoint ».
Apparently the hasanswer () method, wrapped in a while-wend loop, releases that pointer.
N.B. Here, the « if......then.....end if » method does not give the expected result, I don’t know why ()
EDIT: The if ... then ... end if method must contain a for ... next loop: for i = 1 to #sql rowcount() .../...next i. Then it works.
Another solution is of course to program a disconnect/reconnect every time you have accessed the database, which also has the effect of releasing the pointer. In any case, you don’t need to do this always in order to write to the database. The #sql execute() method is sufficient.
In the simple RBGenerated program, there are only 2 places where this situation must be corrected.
With these changes applied, no locks whatsoever : If the RB app writes, you see immediately your changes, if another user writes, you only have to click « Next » in the Runbasic app in order to have your data refreshed.
We must also add « #sql disconnect() » after the doExit branch label .If we don’t do that, the database wil remain open until Runbasic itself is shut down.
Now RunBasic, LB5-350, LB 4.5.1, SQLite Expert and DB Browser all connected at the same time to the same database, can live happily together.
Writing to and reading from the WAL file (pragma journal_mode = WAL):
1) Update:
Use the 'journal_mode=WAL' pragma only 1 time to create the database. When this pragma is used every time the program starts, other users like SQLite Expert or DB Browser will be locked out. They cannot see the changes made by RB and when they eventually want to write, the database locks. If RB is working alone, we cannot see this problem. The WAL mode is persistent, so we don't have to repeat it all the time. The other anti-locking pragmas do not interfere, so they must stay as they are.
2) Apply the same changes as we did in the first example. That is : after every retrieve transaction, even for only 1 answer, use the hasanswer() method wrapping it in a while - wend loop.
3) Add an #sql disconnect() transaction to the [doExit] section. This assures the correct checkpointing of the WAL file to the main database file. If we don’t do this the database remains open until Runbasic itself is shut down.
No locks anymore. This apparently avoids overly repeated connect/disconnect transactions.
But perhaps the connect/disconnect method was the only way originally intended by the Runbasic interface ? Perhaps that is the way servers work : connect/disconnect .... connect/disconnect etc. all the time ?
N.B.
- the *.shm and *.wal files - visible during runtime - normally close when the last reader quits. They are not causing the database locks.
- SQLite has a number of functions to release locks under some very special circumstances. They only can be accessed via the C language interface. The « sqliteconnect » method in RB or LB5-350 apparently does not provide these facilities.
- RB nor LB5-350 do not provide neather the access to prepared statements with bind variables (available only through the SQLite C language interface). That is a pity, for many languages provide this possibility. By the way, the Runbasic program on the Rosetta Code site that is supposed to treat this problem, is incorrect : see the comments on the Rosetta site (I did not provide them).
Perhaps this helps a little..
pierre