Travor, absolutely correct. my apology for misleading everyone.
Trevor Talbot wrote:
You're confused about the locking; see http://sqlite.org/lockingv3.html
On 8/26/07, John Stanton <[EMAIL PROTECTED]> wrote:
How about the case of:
BEGIN sets reserved lock on thread one
No lock.
SELECT promotes lock to shared on thread one
Thread one acquires SHARED lock.
BEGIN sets reserved lock from thread two
No lock.
SELECT promotes reserved lock in thread two to shared
Thread two acquires SHARED lock.
...at this point two threads are simultaneously processing SELECTs
INSERT tries to promote shared lock to exclusive on thread one
Thread one acquires RESERVED lock (upgrade from SHARED).
but fails because second thread holds a shared lock
Does not fail, as this is merely an intent to write and changes are
currently buffered.
My understanding is that once a reserved lock has been promoted to
shared, a further reserved lock can be set. That opens the possibility
that the concurrent transactions can conflict when they try to each set
a write lock.
All active readers have SHARED (reading) locks. A writer acquires a
RESERVED (intent to write) lock, which blocks all future writers.
Readers are not blocked. The writer buffers changes in memory.
When a writer needs to commit (or spill) changes, it acquires a
PENDING (need to write ASAP) lock while other SHARED locks exist. A
PENDING lock blocks all future readers. When all SHARED locks are
gone, it acquires an EXCLUSIVE (writing now) lock long enough to
commit the changes.
For two reading transactions that decide to write at the same time,
one will fail with SQLITE_BUSY. The successful writer will not be
able to commit until the failed writer ends the transaction. The
failed writer will not be able to write until it ends the transaction
and starts a new one.
The successful writer may later encounter SQLITE_BUSY if it has to
spill or commit changes while readers still exist. It is safe to
retry under the assumption that the readers will eventually finish.
However, the _first_ writing failure must not be retried due to the
above, which will lead to deadlock.
BEGIN IMMEDIATE replaces the first writing failure: if BEGIN IMMEDIATE
succeeds, all writes that fail with SQLITE_BUSY (due to cache spill or
commit) are safe to retry without deadlock, since only readers are
present and they will eventually finish.
If BEGIN EXCLUSIVE succeeds, there will never be SQLITE_BUSY failures.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
