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, jonathandly...@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 <p...@mollysrevenge.com>
Sender: use-livecode-boun...@lists.runrev.com
Date: Sat, 27 Nov 2010 13:09:33
To: How to use LiveCode<use-livecode@lists.runrev.com>
Reply-To: How to use LiveCode <use-livecode@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@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@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@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
