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