Re: [sqlite] Re: Re: Re: SQLite3 Concurrency
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: BEGINsets 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] -
Re: [sqlite] Re: Re: Re: SQLite3 Concurrency
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: > BEGINsets 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] -
Re: [sqlite] Re: Re: Re: SQLite3 Concurrency
Based on the description quoted by Igor-2 the situation below would not occur because the reserved lock is not set until the INSERT is intercepted. Since there can be only one reserved lock current, a busy can occur at that point. As I recall that logic counteracts write starvation by stopping shared locks being set when a write is pending. John Stanton wrote: How about the case of: BEGINsets reserved lock on thread one SELECT promotes lock to shared on thread one BEGIN sets reserved lock from thread two SELECT promotes reserved lock in thread two to shared ...at this point two threads are simultaneously processing SELECTs INSERT tries to promote shared lock to exclusive on thread one but fails because second thread holds a shared lock 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. The strategy which increases concurrency does have consequences. Igor Tandetnik wrote: John Stanton <[EMAIL PROTECTED]> wrote: Igor, I confused the issue with names. The correct name is a "reserved" lock, created when a simple transaction is launched in "deferred" mode. Here is the Sqlite explanation. A BEGIN IMMEDIATE or EXCLUSIVE will acquire a write lock immediately. Yes. And that may fail with SQLITE_BUSY. But once it succeeds, subsequent SELECT statements will always succeed, too. How does your observation help prove the point that every single sqlite3_step call may return SQLITE_BUSY? This is the only claim of yours I'm arguing against. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Re: SQLite3 Concurrency
How about the case of: BEGINsets reserved lock on thread one SELECT promotes lock to shared on thread one BEGIN sets reserved lock from thread two SELECT promotes reserved lock in thread two to shared ...at this point two threads are simultaneously processing SELECTs INSERT tries to promote shared lock to exclusive on thread one but fails because second thread holds a shared lock 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. The strategy which increases concurrency does have consequences. Igor Tandetnik wrote: John Stanton <[EMAIL PROTECTED]> wrote: Igor, I confused the issue with names. The correct name is a "reserved" lock, created when a simple transaction is launched in "deferred" mode. Here is the Sqlite explanation. A BEGIN IMMEDIATE or EXCLUSIVE will acquire a write lock immediately. Yes. And that may fail with SQLITE_BUSY. But once it succeeds, subsequent SELECT statements will always succeed, too. How does your observation help prove the point that every single sqlite3_step call may return SQLITE_BUSY? This is the only claim of yours I'm arguing against. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -