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] -
[sqlite] Re: Re: Re: SQLite3 Concurrency
Igor Mironchick <[EMAIL PROTECTED]> wrote: while( ret != SQLITE_OK ) { std::cerr << "There is some errors while executing SQL statement: " << errors << std::endl; sqlite3_free( errors ); ret = sqlite3_exec( db, sql, 0, 0, ); Why do you want to keep running the same statement over and over? Since I guarante that there is no errors in SQL syntax something when ret != SQLITE_OK mean that there is concurrency problem and I need to wait unloking database. But you will never get ret != SQLITE_OK, except perhaps when executing the satement for the very first time. Once the first SELECT succeeds, your transaction has acquired a SHARED lock and will hold on to it until it ends. This, in turn, prevents any prospective writers from being able to modify the database. More dangerous when "COMMIT" after "INSERT" return SQLITE_BUSY!!! I'm not sure I understand what your point is here. You have a reader that hogs the database forever in a long-running transaction. Of course writers are locked out. No. Reader will not hog database because if in this example ret != SQLITE_OK This condition will never become true, barring catastrophic I/O failure (e.g. bad disk sector). then writer have already lock database for write A writer cannot obtain its lock until all readers release theirs. SQLite doesn't preempt readers to let writers through. Your reader however never releases its lock once acquired. and we need to wait for unlocking. And when I wrote: "More dangerous when "COMMIT" after "INSERT" return SQLITE_BUSY" I mean that if this wouldn't be checked then probably we lost ours data. Yes, this is possible, and this is precisely what would happen in your scenario. The reader would never be preempted, and would continue to loop forever. The writer will get SQLITE_BUSY either on INSERT statement, or on COMMIT statement. As for losing data, I'm not sure how this situation is different from that occuring in any other DBMS. Your data is only guaranteed to be in the database once the transaction has been committed successfully. A transaction may fail at any point prior to that, and be rolled back, resulting in the data not making it into the database. For example, MS SQL Server supports table and row-level locking, so in many cases it would let multiple readers and writers into the database at the same time. But occasionally, this may result in a deadlock (two transactions each trying to modify rows the other has locked). When this happens, one of these transactions is chosen as a deadlock victim and is rolled back to allow the other to proceed. So you may have a transaction rolling back on you literally at any moment. P.S. There is no reasons for two threads lock the same database and will run while cycle at the same time. I don't understand this statement. Be aware that SQLite does allow multiple readers to lock the database at the same time, each holding a SHARED lock. What's not allowed is a reader and a writer, or two writers, at the same time. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: SQLite3 Concurrency
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] -