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.
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.
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
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
