SQLite Locking

[Peter Haworth]

The recent discussion on the pluses/minuses of using SQLIte and  
associated locking mechanisms got my attention since I'm using SQLIte  
and I expect my app to be used by a small number of users with an  
occasional coincidence of more than 1 person trying to change the  
database at the same time.

The SQLite documentation is quite clear that only one user can be  
updating the database at any one time but any number of users can be  
reading from the database while it's locked for updating, although  
there are three different types of lock that can be requested.

I did a few experiments using the FireFox SQLIte manager plugin to  
issue a BEGIN command, and my application to try to write to the  
database, both processes running on the same computer.  In my  
application, I coded a repeat loop to try to get a lock and if not  
successful, wait 1 second then try again for a total of 5 attempts.   
LiveCode reported an error when the database was locked by Firefox and  
if I got into Firefox quickly enough to release the lock, my repeat  
loop acquired the lock.

The Firefox lock was always an IMMEDIATE lock.  When my app's lock was  
IMMEDIATE, the attempt to get a lock failed.  When my apps lock was  
DEFERRED, the attempt to get a lock was successful but the INSERT  
command failed with a lock error, unless I went back into Firefox and  
released the lock after my app got it's lock but before it executed  
the INSERT.  My app normally only does IMMEDIATE locks but if anyone  
wanted to to DEFERRED locks, there'd need to be some mechanism to  
release the lock when a subsequent db access failed.

For my application, this will work fine, although I do need to do  
further testing to make sure this all still works with users on  
different computers accessing the db on one of them.

My only concern is what might happen if someone got to the point where  
they had acquired a lock then went to lunch before the rest of the  
transaction completed.  I'm pretty sure all my transactions flow  
through without any user interaction after the lock is acquired but I  
need to check and put some sort of timeout in the code to detect that  
situation if necessary, release the lock and end the transaction.

In view of this, I plan to stick with SQLite for this app at least.   
As always, it's horses for courses!

Pete Haworth