SQLite Locking

Peter Haworth pete at mollysrevenge.com
Mon Nov 29 19:46:59 EST 2010


I'm kinda doing that.  In some cases, the db update consists of  
several insert operations which would leave the db in an inconsistant  
state if any of them failed before they all completed.  So after   
getting all the input, I lock the db, do all the insertions, then  
unlock it.  If any problems occur during the insertions, then I roll  
back the db to the state it was in before any of the insertions  
happened and let the user know what went wrong.

I'm confused by what  the article says.  The description of the BEGIN  
TRANSACTION statement in SQlite clearly states that  other readers can  
continue reading but only one writer will be allowed, assuming the  
lock is of type IMMEDIATE.  Matter of fact, I verified that in my  
tests.  Maybe they are referring to an EXCLUSIVE lock, which locks out  
everyone until the lock is released.

I can't find any reference to a lock timeout interval anywhere in the  
SQLite documentation other than this article and another one that  
describes the C/C++ API.  I suspect the APIs implement the timeout  
stuff internally.

I think SQLite locking will work OK for me but it is pretty  
primitive.  There really needs to be the ability to locak a specific  
table of event specific rows within a table but I guess you gotta pay  
the big bucks to get that capability!

Pete Haworth

On Nov 29, 2010, at 4:00 PM, Bob Sneidar wrote:

> 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
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your  
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>





More information about the use-livecode mailing list