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@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
