SQLite Locking

Bob Sneidar bobs at twft.com
Mon Nov 29 19:00:06 EST 2010


I think the solution here is to wait until all user input is acquired, then do the lock/write/unlock in a single blocking call. The only thing that would hurt you here is a system lockup or disconnect during this very very brief moment of time. Unlikely, but possible. 

I read this in a writeup on sqLite: 

The "timeout" method

The "timeout" method is used to control how long the SQLite library will wait for locks to clear before giving up on a database transaction. The default timeout is 0 millisecond. (In other words, the default behavior is not to wait at all.)

The SQLite database allows multiple simultaneous readers or a single writer but not both. If any process is writing to the database no other process is allows to read or write. If any process is reading the database other processes are allowed to read but not write. The entire database shared a single lock.

When SQLite tries to open a database and finds that it is locked, it can optionally delay for a short while and try to open the file again. This process repeats until the query times out and SQLite returns a failure. The timeout is adjustable. It is set to 0 by default so that if the database is locked, the SQL statement fails immediately. But you can use the "timeout" method to change the timeout value to a positive number. For example:

db1 timeout 2000
The argument to the timeout method is the maximum number of milliseconds to wait for the lock to clear. So in the example above, the maximum delay would be 2 seconds.


The entire article can be found here: 

http://www.sqlite.org/tclsqlite.html

Bob


On Nov 27, 2010, at 1:09 PM, Peter Haworth wrote:

> 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





More information about the use-livecode mailing list