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