SQLite Locking

Peter Haworth pete at mollysrevenge.com
Sat Nov 27 18:23:20 EST 2010


Yep, the SQLite docs have some words about that, especially when using  
NFS.  Since I'm running on a Mac and so will the other users of may  
app) I was glad to see:

The versions of SQLite that come preinstalled on Apple Mac OS X  
computers contain a version of SQLite that has been extended to use  
alternative locking strategies that work on all network filesystems  
that Apple supports. These extensions used by Apple work great as long  
as all processes are accessing the database file in the same way.  
Unfortunately, the locking mechanisms do not exclude one another, so  
if one process is accessing a file using (for example) AFP locking and  
another process (perhaps on a different machine) is using dot-file  
locks, the two processes might collide because AFP locks do not  
exclude dot-file locks or vice versa.

However, that now begs the question of how I can use the preinstalled  
version of Sqlite since it's my understanding that the SQLite library  
is built in to LiveCode.  Maybe I'll just try it out first before  
worrying about that.

Pete Haworth

On Nov 27, 2010, at 3:12 PM, jonathandlynch at gmail.com wrote:

> You might want to check to see if the locking works as desired  
> across a network connection - not all networks do file locking  
> properly.
>
> Sent from my Verizon Wireless BlackBerry
>
> -----Original Message-----
> From: Peter Haworth <pete at mollysrevenge.com>
> Sender: use-livecode-bounces at lists.runrev.com
> Date: Sat, 27 Nov 2010 13:09:33
> To: How to use LiveCode<use-livecode at lists.runrev.com>
> Reply-To: How to use LiveCode <use-livecode at lists.runrev.com>
> Subject: SQLite Locking
>
> 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
>
>
> _______________________________________________
> 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
> _______________________________________________
> 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