Igor Tandetnik wrote:
John Stanton <[EMAIL PROTECTED]> wrote:
More correctly:
BEGIN Thread one
BEGIN Thread two
INSERT Thread one sets reserved lock
INSERT Thread two, fails to set reserved lock
SELECT Thread two, set shared lock
COMMIT on thread one promotes reserved lock to pending
SELECT Thread two fails to set shared lock
Thread two already has its shared lock, left over from the first SELECT
statement. While a transaction is in progress, locks it holds can only
be promoted (that is, ever more and stricter locks acquired), never
demoted.
That would mean thread one could not COMMIT until thread two COMMITs,
and raises a possible deadlock, requiring that one transaction be
interrupted to break the deadly embrace.
COMMIT on thread one continues, promoting pending lock to
exclusive and commits journal, then releases lock
INSERT Thread two, retries and gets reserved lock
...
I am not clear on the mechanism of promoting a pending lock to
exclusive. Does it block until all shared locks are cleared or does
it return?
I'm not sure either.
Does an attampt to set a shared lock when a pending lock is
set return a BUSY or block?
Returns BUSY.
Breaks a deadlock situation.
In a threaded environment what we do is equivalent to a BEGIN
IMMEDIATE except that it offers greater concurrency by permitting
multiple
concurrent read-only transactions:
I seem to remember there was a discussion of introducing something like
BEGIN SHARED to SQLite: a statement that attempts to start a transaction
and immediately acquire a SHARED lock, somewhat similar to BEGIN
IMMEDIATE. You can fake it programmatically, by issuing BEGIN and then a
dummy SELECT statement (e.g. select 1 from sqlite_master limit 1; ).
a. Read-Only Transaction -
set pthread_rwlock to read
BEGIN
SQL....
COMMIT
reset pthread-rwlock
b. Transaction which modifies DB -
set pthread_rwlock to write
BEGIN
SQL...
COMMIT
reset pthread_rwlock
It looks like you can achieve the same effect by using BEGIN SHARED (or
its programmatic equivalent) for readers, and BEGIN EXCLUSIVE for writers.
Igor Tandetnik
Another advantage is that there can be no BUSYs and no polling; instead
contentions are resolved by more efficient blocking. Of course this
only works in a single process threaded application. In a multi-process
situation the effect could be achieved by using semaphores co-operatively.
Currently I bypass file locking by simply linking in a dummy fcntl, but
a better solution would be a compile option to strip out the Sqlite
locking logic. It could be defined as a threading option and include
adding the thread synchronization functions. The proviso is that users
need to be able to define the equivalent of BEGIN EXCLUSIVE and BEGIN
SHARED and not leave it to Sqlite.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
