SQLite Locking
jonathandlynch at gmail.com
jonathandlynch at gmail.com
Sat Nov 27 18:12:48 EST 2010
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
More information about the use-livecode
mailing list