Re: [sqlite] Race condition -- fixed?
I wrote: > I would still perform rollbacks for any errors other than the above > expected SQLITE_BUSY cases, of course, since they indicate something > else went wrong (such as running out of disk space). I think it's > safe to say those are all unusual cases though. Hmm, http://sqlite.org/lang_transaction.html suggests even SQLITE_BUSY might result in automatic transaction rollback. sqlite3_step() suggests the same thing. Other docs suggest that SQLITE_IOERR_BLOCKED is actually returned in such cases (like a writing transaction failing a cache spill attempt). Which is correct? Silently rolling back a transaction automatically is bad enough, but doing that on SQLITE_BUSY would be just plain evil. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
On 10/29/07, Richard Klein <[EMAIL PROTECTED]> wrote: > Perhaps the best solution is to follow these rules: > > IF THE TRANSACTION IS A WRITER (i.e. will eventually write to the db): > -- > (1) Begin the transaction with 'BEGIN IMMEDIATE'. If you get SQLITE_ > BUSY, it means that another writer is already accessing the db. Just > sleep awhile and retry, as many times as necessary. Once you get > SQLITE_OK, you're the only writer accessing the db. > > (2) If you get SQLITE_BUSY later in the transaction when you want to > write your cache to disk, it can only be due to one or more readers > holding SHARED locks (because you're the only writer). They will > eventually exit without trying to acquire any stronger locks (again, > because you're the only writer). Therefore, no deadlock is possible. > Just sleep awhile and retry, as many times as necessary. > > IF THE TRANSACTION IS A READER (i.e. will only read from the db): > - > (1) Begin the transaction with a simple 'BEGIN'. This starts the > transaction in the UNLOCKED state, so you *cannot* get a SQLITE_ > BUSY at this point. > > (2) If you get SQLITE_BUSY later in the transaction, it can only be > due to your trying to acquire a SHARED lock while some other process > holds a PENDING or EXCLUSIVE lock. That process will eventually > finish its writing and exit. Therefore, no deadlock is possible. > Just sleep awhile and retry, as many times as necessary. > > > If all transactions follow the above rules, then explicit rollbacks > should never be necessary. For avoiding deadlocks, yes. It can also be abstracted so the rest of your code works on just "read-only" and "read/write" transactions, which is convenient. I would still perform rollbacks for any errors other than the above expected SQLITE_BUSY cases, of course, since they indicate something else went wrong (such as running out of disk space). I think it's safe to say those are all unusual cases though. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Trevor Talbot wrote: On 10/29/07, Richard Klein <[EMAIL PROTECTED]> wrote: But am I correct in assuming that one way that SQLite provides serializable transactions is by automatically rolling back transactions when necessary (and returning SQLITE_IOERR)? No. That will happen in some specific unavoidable cases, but you cannot assume it will happen all of the time. I assume you are referring to the case discussed in the article: http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError i.e. the case where a non-COMMIT command within an explicit trans- action fails when the executing process is unable to spill its cache to disk due to a SQLITE_BUSY error, resulting in an inconsistent cache. What will most likely happen instead is that the first INSERT or UPDATE in a transaction will return SQLITE_BUSY, and you will have to roll back the transaction yourself. It's also possible to simply keep retrying a failing statement until it succeeds, but in the case of INSERT or UPDATE that may lead to deadlock, as two connections that want to write cannot proceed until one of them yields. Perhaps the best solution is to follow these rules: IF THE TRANSACTION IS A WRITER (i.e. will eventually write to the db): -- (1) Begin the transaction with 'BEGIN IMMEDIATE'. If you get SQLITE_ BUSY, it means that another writer is already accessing the db. Just sleep awhile and retry, as many times as necessary. Once you get SQLITE_OK, you're the only writer accessing the db. (2) If you get SQLITE_BUSY later in the transaction when you want to write your cache to disk, it can only be due to one or more readers holding SHARED locks (because you're the only writer). They will eventually exit without trying to acquire any stronger locks (again, because you're the only writer). Therefore, no deadlock is possible. Just sleep awhile and retry, as many times as necessary. IF THE TRANSACTION IS A READER (i.e. will only read from the db): - (1) Begin the transaction with a simple 'BEGIN'. This starts the transaction in the UNLOCKED state, so you *cannot* get a SQLITE_ BUSY at this point. (2) If you get SQLITE_BUSY later in the transaction, it can only be due to your trying to acquire a SHARED lock while some other process holds a PENDING or EXCLUSIVE lock. That process will eventually finish its writing and exit. Therefore, no deadlock is possible. Just sleep awhile and retry, as many times as necessary. If all transactions follow the above rules, then explicit rollbacks should never be necessary. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
On 10/29/07, Richard Klein <[EMAIL PROTECTED]> wrote: > But am I correct in assuming that one way that SQLite provides > serializable transactions is by automatically rolling back > transactions when necessary (and returning SQLITE_IOERR)? No. That will happen in some specific unavoidable cases, but you cannot assume it will happen all of the time. What will most likely happen instead is that the first INSERT or UPDATE in a transaction will return SQLITE_BUSY, and you will have to roll back the transaction yourself. It's possible to continue within a transaction by ignoring an error from an individual statement (depending on the nature of the error), is why I make this distinction. It's also possible to simply keep retrying a failing statement until it succeeds, but in the case of INSERT or UPDATE that may lead to deadlock, as two connections that want to write cannot proceed until one of them yields. On the other hand, if you get SQLITE_BUSY on COMMIT, you probably want to retry it, as it's just waiting for other readers to go away first. Automatically rolling back then would be bad :) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Its up to you to rollback the transaction. It would return a SQLITE_BUSY, not an IOERR. Richard Klein <[EMAIL PROTECTED]> wrote: But am I correct in assuming that one way that SQLite provides serializable transactions is by automatically rolling back transactions when necessary (and returning SQLITE_IOERR)? Thanks, - Richard Klein [EMAIL PROTECTED] wrote: > Ken wrote: >> BEGIN TRANSACTION; >> SELECT balance FROM accounts WHERE accountId = '123-45-6789'; >> UPDATE accounts SET balance = >> WHERE accountId = '123-45-6789'; >> COMMIT; >> >> This is a comman and naive assumption that the balance selected >> will remain consistent. >> > > Actually, SQLite does provide this guarantee. Nothing in > the database will change during a transaction, except for > changes caused by INSERT, UPDATE, and DELETE statements > that occur within the transaction itself. It is not > possible for another process to modify the value of the > "balance" in between the SELECT and the UPDATE in the > SQL above. > > This is true of SQLite because isolation in SQLite > is "SERIALIZABLE". This is the highest level of isolate > provided by SQL. Most client/server database engines > by default implement "READ COMMITTED". The value of > "balance" might change between the SELECT and the > UPDATE in MySQL, for example. (I'm less clear about > what happens in PostgreSQL and Oracle. The point is > that your mileage may vary so be cautious.) > > But SQLite gets this right. Transactions are fully > serializable, which means they appear as if the entire > transaction happens instanteously with no chance for > outside processes to change values in the middle of a > transaction. > > -- > D. Richard Hipp > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
But am I correct in assuming that one way that SQLite provides serializable transactions is by automatically rolling back transactions when necessary (and returning SQLITE_IOERR)? Thanks, - Richard Klein [EMAIL PROTECTED] wrote: Ken <[EMAIL PROTECTED]> wrote: BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; This is a comman and naive assumption that the balance selected will remain consistent. Actually, SQLite does provide this guarantee. Nothing in the database will change during a transaction, except for changes caused by INSERT, UPDATE, and DELETE statements that occur within the transaction itself. It is not possible for another process to modify the value of the "balance" in between the SELECT and the UPDATE in the SQL above. This is true of SQLite because isolation in SQLite is "SERIALIZABLE". This is the highest level of isolate provided by SQL. Most client/server database engines by default implement "READ COMMITTED". The value of "balance" might change between the SELECT and the UPDATE in MySQL, for example. (I'm less clear about what happens in PostgreSQL and Oracle. The point is that your mileage may vary so be cautious.) But SQLite gets this right. Transactions are fully serializable, which means they appear as if the entire transaction happens instanteously with no chance for outside processes to change values in the middle of a transaction. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
> This is true of SQLite because isolation in SQLite > is "SERIALIZABLE". This is the highest level of isolate > provided by SQL. Most client/server database engines > by default implement "READ COMMITTED". The value of > "balance" might change between the SELECT and the > UPDATE in MySQL, for example. (I'm less clear about > what happens in PostgreSQL and Oracle. The point is > that your mileage may vary so be cautious.) PostgreSQL uses "READ COMMITTED" by default as well (each statement acquires a new snapshot). However, PostgreSQL does not implement true SERIALIZABLE semantics because of the phantom problem (transaction outcome depends on the absence of certain rows--but no lock can be acquired on them, so they might be added by a parallel transaction, resulting in a non-serializable history). - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
On 10/26/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > This is true of SQLite because isolation in SQLite > is "SERIALIZABLE". This is the highest level of isolate > provided by SQL. Most client/server database engines > by default implement "READ COMMITTED". The value of > "balance" might change between the SELECT and the > UPDATE in MySQL, for example. (I'm less clear about > what happens in PostgreSQL and Oracle. The point ism > that your mileage may vary so be cautious.) PostgreSQL supports both READ COMMITTED and SERIALIZABLE, but the out-of-box default is READ COMMITTED. It can be selected per-transaction, or changed as a default for all transactions. I believe Oracle is similar. MySQL is a little weird due to different storage engines. From what I can tell, MyISAM doesn't actually support transactions, so you essentially get READ UNCOMMITTED if you try; InnoDB supports all 4 levels and defaults to REPEATABLE READ. The differences between MVCC (Multi-Version Concurrency Control) and locking -based implementations can also show up in corner cases, so it's something you definitely want to look at whenever examing a database engine. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Richard: Actually No, process A will not acquire the reserved lock. It failes returning a sqlite_busy, and must perform a rollback. Even if Process B commits. Process A will get a sqlite_busy, forcing you to rollback. In order for the application to be correct the entire transaction must be restarted. The select would be run again, returning process B's correct value of (10,000 + 1000) = 11,000 ; A simpler/cleaner implementation might be: begin immediate --- Busy waiting would occur here. ie sqlite busy is returned. select update commit; Ken Ken <[EMAIL PROTECTED]> wrote: Richard Klein wrote: Dan Kennedy wrote: > On Wed, 2007-10-24 at 21:38 -0700, Richard Klein wrote: >> As I was thinking about the locking mechanism in SQLite 3, >> it occurred to me that the following race condition could >> occur. >> >> Imagine a joint bank account with a balance of $10,000. >> The wife makes a withdrawal of $1,000 at ATM 'A' (serviced >> by process A in the bank's mainframe), while at the same >> time the husband makes a deposit of $1,000 at ATM 'B' >> (serviced by process B). The steps performed by each >> process are as follows: >> >> Process A >> - >> BEGIN TRANSACTION; >> SELECT balance FROM accounts WHERE accountId = '123-45-6789'; >> UPDATE accounts SET balance = >> WHERE accountId = '123-45-6789'; >> COMMIT; >> >> Process B >> - >> BEGIN TRANSACTION; >> SELECT balance FROM accounts WHERE accountId = '123-45-6789'; >> UPDATE accounts SET balance = >> WHERE accountId = '123-45-6789'; >> COMMIT; >> >> Both processes open the accounts database, obtain SHARED >> locks, and proceed at about the same pace. Process A >> updates her local cache with a new balance of $900, while >> process B updates his local cache with a new balance of >> $11,000. > > Evaluating the UPDATE requires a RESERVED lock on the database > file. Since only one process can hold the RESERVED lock, this > particular scenario cannot occur. One or other of the updates > will fail with SQLITE_BUSY. > > Dan. I went over the documentation again, and it appears that you are correct: A process wanting to do an UPDATE must first acquire a RESERVED lock. However, I believe there is still a race condition. Assume that process B is the first to reach the UPDATE statement, and so process B gets the RESERVED lock (which he later escalates to PENDING and EXCLUSIVE). Then process A is stuck in a busy wait until process B commits and releases his locks. At this process A acquires the RESERVED lock and does *her* UPDATE, but she does it using the old, now *stale*, value of the account balance ($10,000). That is, she computes the new balance as ($10,000 - $1,000) = $9,000, which is *wrong*. She needs to *re-execute* her SELECT statement to pick up the new balance of $11,000. Or, in general: She needs to start her transaction over from the beginning. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] - I submit that there is no race condition present. Merely a progamming error. BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; This is a comman and naive assumption that the balance selected will remain consistent. Even in other DB's such as oracle the problem described would persist. Two my knowledge there are two solutions. 1: Don't programatically put the balance into a variable. BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = balance - WHERE accountId = '123-45-6789'; COMMIT; 2: Lock the row with the select statement, this requires additional syntax, which sqlite does not support. BEGIN TRANSACTION; SELECT balance FOR UPDATE FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT;
Re: [sqlite] Race condition -- fixed?
That makes sense given SERIALIZABLE transactions. The entire trasaction will require rollback since it gets a sqlite_busy, Not just the update. This also has the effect of invalidating the data selected within the transaction that performs the rollback. Oracle defaults to Read Committed. But you may set a transaction to serailazable as well. Sorry for my confusion and thanks for the clarification. Ken Ken wrote: > > BEGIN TRANSACTION; > SELECT balance FROM accounts WHERE accountId = '123-45-6789'; > UPDATE accounts SET balance = > WHERE accountId = '123-45-6789'; > COMMIT; > > This is a comman and naive assumption that the balance selected > will remain consistent. > Actually, SQLite does provide this guarantee. Nothing in the database will change during a transaction, except for changes caused by INSERT, UPDATE, and DELETE statements that occur within the transaction itself. It is not possible for another process to modify the value of the "balance" in between the SELECT and the UPDATE in the SQL above. This is true of SQLite because isolation in SQLite is "SERIALIZABLE". This is the highest level of isolate provided by SQL. Most client/server database engines by default implement "READ COMMITTED". The value of "balance" might change between the SELECT and the UPDATE in MySQL, for example. (I'm less clear about what happens in PostgreSQL and Oracle. The point is that your mileage may vary so be cautious.) But SQLite gets this right. Transactions are fully serializable, which means they appear as if the entire transaction happens instanteously with no chance for outside processes to change values in the middle of a transaction. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Ken <[EMAIL PROTECTED]> wrote: > > BEGIN TRANSACTION; > SELECT balance FROM accounts WHERE accountId = '123-45-6789'; > UPDATE accounts SET balance = > WHERE accountId = '123-45-6789'; > COMMIT; > > This is a comman and naive assumption that the balance selected > will remain consistent. > Actually, SQLite does provide this guarantee. Nothing in the database will change during a transaction, except for changes caused by INSERT, UPDATE, and DELETE statements that occur within the transaction itself. It is not possible for another process to modify the value of the "balance" in between the SELECT and the UPDATE in the SQL above. This is true of SQLite because isolation in SQLite is "SERIALIZABLE". This is the highest level of isolate provided by SQL. Most client/server database engines by default implement "READ COMMITTED". The value of "balance" might change between the SELECT and the UPDATE in MySQL, for example. (I'm less clear about what happens in PostgreSQL and Oracle. The point is that your mileage may vary so be cautious.) But SQLite gets this right. Transactions are fully serializable, which means they appear as if the entire transaction happens instanteously with no chance for outside processes to change values in the middle of a transaction. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Richard Klein <[EMAIL PROTECTED]> wrote: Dan Kennedy wrote: > On Wed, 2007-10-24 at 21:38 -0700, Richard Klein wrote: >> As I was thinking about the locking mechanism in SQLite 3, >> it occurred to me that the following race condition could >> occur. >> >> Imagine a joint bank account with a balance of $10,000. >> The wife makes a withdrawal of $1,000 at ATM 'A' (serviced >> by process A in the bank's mainframe), while at the same >> time the husband makes a deposit of $1,000 at ATM 'B' >> (serviced by process B). The steps performed by each >> process are as follows: >> >> Process A >> - >> BEGIN TRANSACTION; >> SELECT balance FROM accounts WHERE accountId = '123-45-6789'; >> UPDATE accounts SET balance = >> WHERE accountId = '123-45-6789'; >> COMMIT; >> >> Process B >> - >> BEGIN TRANSACTION; >> SELECT balance FROM accounts WHERE accountId = '123-45-6789'; >> UPDATE accounts SET balance = >> WHERE accountId = '123-45-6789'; >> COMMIT; >> >> Both processes open the accounts database, obtain SHARED >> locks, and proceed at about the same pace. Process A >> updates her local cache with a new balance of $900, while >> process B updates his local cache with a new balance of >> $11,000. > > Evaluating the UPDATE requires a RESERVED lock on the database > file. Since only one process can hold the RESERVED lock, this > particular scenario cannot occur. One or other of the updates > will fail with SQLITE_BUSY. > > Dan. I went over the documentation again, and it appears that you are correct: A process wanting to do an UPDATE must first acquire a RESERVED lock. However, I believe there is still a race condition. Assume that process B is the first to reach the UPDATE statement, and so process B gets the RESERVED lock (which he later escalates to PENDING and EXCLUSIVE). Then process A is stuck in a busy wait until process B commits and releases his locks. At this process A acquires the RESERVED lock and does *her* UPDATE, but she does it using the old, now *stale*, value of the account balance ($10,000). That is, she computes the new balance as ($10,000 - $1,000) = $9,000, which is *wrong*. She needs to *re-execute* her SELECT statement to pick up the new balance of $11,000. Or, in general: She needs to start her transaction over from the beginning. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] - I submit that there is no race condition present. Merely a progamming error. BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; This is a comman and naive assumption that the balance selected will remain consistent. Even in other DB's such as oracle the problem described would persist. Two my knowledge there are two solutions. 1: Don't programatically put the balance into a variable. BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = balance - WHERE accountId = '123-45-6789'; COMMIT; 2: Lock the row with the select statement, this requires additional syntax, which sqlite does not support. BEGIN TRANSACTION; SELECT balance FOR UPDATE FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT;
Re: [sqlite] Race condition -- fixed?
Trevor Talbot wrote: On 10/25/07, Richard Klein <[EMAIL PROTECTED]> wrote: Trevor Talbot wrote: The thing is, SQLite's synchronization mechanism is simpler than most full-featured SQL databases. In all cases, the point is that if you are attempting to do simultaneous writes from two connections, each connection must be prepared to receive an error, rollback the transaction, and try again. Personally, I don't see anything terribly > complicated about that. It's not complicated at all. It's just an aspect of database programming that I was unaware of, until now (I'm a database newbie). You're also implementing SQLite's locking system for your own platform, so you need to be concerned with these details anyway. You're not just blindly implementing it, you're also verifying your understanding of the goals and taking the time to question SQLite's behavior to make sure everything is correct. I think that's absolutely wonderful. Thank you for understanding that! :-) - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
On 10/25/07, Richard Klein <[EMAIL PROTECTED]> wrote: > Trevor Talbot wrote: > > The thing is, SQLite's synchronization mechanism is simpler than most > > full-featured SQL databases. In all cases, the point is that if you > > are attempting to do simultaneous writes from two connections, each > > connection must be prepared to receive an error, rollback the > > transaction, and try again. Personally, I don't see anything terribly > > complicated about that. > > It's not complicated at all. It's just an aspect of database programming > that I was unaware of, until now (I'm a database newbie). You're also implementing SQLite's locking system for your own platform, so you need to be concerned with these details anyway. You're not just blindly implementing it, you're also verifying your understanding of the goals and taking the time to question SQLite's behavior to make sure everything is correct. I think that's absolutely wonderful. I just wanted to point out to anyone seeing this thread and thinking "eek" that from the perspective of using SQLite one doesn't need to be concerned with these details, because the original implementors, and people like yourself, keep verifying those details do what they're supposed to do. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
I am sure that you are correct, that Sqlite's sync mechanism is not terribly complicated for you and for anyone else who understands the principles, however it does confuse many users as you see from the posts to this forum. Simple to use could become simpler to use. Synchronizing transactions is not intricate, particularly when it is achieved transparently to the application programmer by the Sqlite application support layer. Trevor Talbot wrote: The thing is, SQLite's synchronization mechanism is simpler than most full-featured SQL databases. In all cases, the point is that if you are attempting to do simultaneous writes from two connections, each connection must be prepared to receive an error, rollback the transaction, and try again. Personally, I don't see anything terribly complicated about that. Concerning yourself with the intricate details in order to get the best performance is complicated, sure, but the basic principle isn't. You have to do error checking anyway, the only difference is that it may affect your entire transaction rather than just one statement. That's true for working with SQL databases in general. For those applications don't want parallel access at all, SQLite provides BEGIN EXCLUSIVE. Adding your own mechanisms on top just means another thing for you to test independently, which seems like adding complexity. I do recognize that none of the above necessarily applies to specific environments, where you need to do your own thing anyway. I'm just commenting that in general, SQLite is already simple to use. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Dan Kennedy wrote: On Wed, 2007-10-24 at 21:38 -0700, Richard Klein wrote: As I was thinking about the locking mechanism in SQLite 3, it occurred to me that the following race condition could occur. Imagine a joint bank account with a balance of $10,000. The wife makes a withdrawal of $1,000 at ATM 'A' (serviced by process A in the bank's mainframe), while at the same time the husband makes a deposit of $1,000 at ATM 'B' (serviced by process B). The steps performed by each process are as follows: Process A - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Process B - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Both processes open the accounts database, obtain SHARED locks, and proceed at about the same pace. Process A updates her local cache with a new balance of $900, while process B updates his local cache with a new balance of $11,000. Evaluating the UPDATE requires a RESERVED lock on the database file. Since only one process can hold the RESERVED lock, this particular scenario cannot occur. One or other of the updates will fail with SQLITE_BUSY. Dan. I went over the documentation again, and it appears that you are correct: A process wanting to do an UPDATE must first acquire a RESERVED lock. However, I believe there is still a race condition. Assume that process B is the first to reach the UPDATE statement, and so process B gets the RESERVED lock (which he later escalates to PENDING and EXCLUSIVE). Then process A is stuck in a busy wait until process B commits and releases his locks. At this process A acquires the RESERVED lock and does *her* UPDATE, but she does it using the old, now *stale*, value of the account balance ($10,000). That is, she computes the new balance as ($10,000 - $1,000) = $9,000, which is *wrong*. She needs to *re-execute* her SELECT statement to pick up the new balance of $11,000. Or, in general: She needs to start her transaction over from the beginning. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Trevor Talbot wrote: The thing is, SQLite's synchronization mechanism is simpler than most full-featured SQL databases. In all cases, the point is that if you are attempting to do simultaneous writes from two connections, each connection must be prepared to receive an error, rollback the transaction, and try again. Personally, I don't see anything terribly > complicated about that. It's not complicated at all. It's just an aspect of database programming that I was unaware of, until now (I'm a database newbie). For those applications don't want parallel access at all, SQLite provides BEGIN EXCLUSIVE. That's true. If you begin your transaction with BEGIN EXCLUSIVE, then it cannot be interrupted by another transaction wishing to write to the database, so rollback won't be an issue. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Lee Crain wrote: I've wrapped all of my company's SQLite database accesses in my own API layer that encapsulates all of our applications' business rules and forces ALL transactions, no matter how lengthy or trivial, to be atomic by using a MUTEX to avoid the types of scenarios described below. This includes simple database accesses, even reads. I think that's a perfectly acceptable solution, if you can live with the performance hit (and it sounds like you can). - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
The thing is, SQLite's synchronization mechanism is simpler than most full-featured SQL databases. In all cases, the point is that if you are attempting to do simultaneous writes from two connections, each connection must be prepared to receive an error, rollback the transaction, and try again. Personally, I don't see anything terribly complicated about that. Concerning yourself with the intricate details in order to get the best performance is complicated, sure, but the basic principle isn't. You have to do error checking anyway, the only difference is that it may affect your entire transaction rather than just one statement. That's true for working with SQL databases in general. For those applications don't want parallel access at all, SQLite provides BEGIN EXCLUSIVE. Adding your own mechanisms on top just means another thing for you to test independently, which seems like adding complexity. I do recognize that none of the above necessarily applies to specific environments, where you need to do your own thing anyway. I'm just commenting that in general, SQLite is already simple to use. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
You make a sound point. From my perspective the Sqlite synchronization mechanisms are a flawed part of an otherwise elegantly simple design, as reading this forum indicates. Synchronization problems are the major item of confusion among users. A more robust and less intricate interface would be a productivity improver. Follow Einstein - "Make it as simple as possible, but no simpler". We use the mutex approach and are rewarded with simpler logic, no ugly busy waits or wasteful pauses, more confidence when deploying applications and no 3AM calls. A sophistication is to use read locks where appropriate. A read (shared) lock can be easily contrived from the Windows sync primitives and exists with pthreads. Threaded programs are here to stay, so the synchronization method would be better built from threads up instead of at process level with threading support overlaid later. Lee Crain wrote: Guys, I read this forum regularly and I've given a lot of thought to all of these conversations about low level logic and trying to squeeze the last ounce of performance out of SQLite. That's not for me. Simplicity equates to robustness and my company needs robustness. And my time is really expensive so I need to not spend too much time tweaking my SQLite based application to get everything to work reliably and efficiently. I've wrapped all of my company's SQLite database accesses in my own API layer that encapsulates all of our applications' business rules and forces ALL transactions, no matter how lengthy or trivial, to be atomic by using a MUTEX to avoid the types of scenarios described below. This includes simple database accesses, even reads. Yes, I know it makes things slower. But, it's solid. And I won't be getting calls at 3:00 a.m. about some mysterious database problem. If my company wants something faster, they need to buy SQL Server. At $5000, it's a bargain. At my salary, I'm not. My 2 cents, Lee Crain _ -Original Message- From: Richard Klein [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 24, 2007 10:39 PM To: sqlite-users@sqlite.org Subject: [sqlite] Race condition -- fixed? As I was thinking about the locking mechanism in SQLite 3, it occurred to me that the following race condition could occur. Imagine a joint bank account with a balance of $10,000. The wife makes a withdrawal of $1,000 at ATM 'A' (serviced by process A in the bank's mainframe), while at the same time the husband makes a deposit of $1,000 at ATM 'B' (serviced by process B). The steps performed by each process are as follows: Process A - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Process B - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Both processes open the accounts database, obtain SHARED locks, and proceed at about the same pace. Process A updates her local cache with a new balance of $900, while process B updates his local cache with a new balance of $11,000. Now suppose B gets to the COMMIT first. He tries to get a PENDING lock and succeeds. He then tries to promote his PENDING lock to EXCLUSIVE, but gets a SQLITE_BUSY instead, because process A holds a SHARED lock. So, he goes to sleep, hoping that when he awakens the SHARED lock will be gone. Meanwhile, process A reaches her COMMIT, tries to get a PENDING lock, but gets a SQLITE_BUSY instead, because process B already holds a PENDING lock. Process A then releases her SHARED lock (so that process B can be promoted from PENDING to EXCLUSIVE and do his commit), and goes to sleep, hoping that when she wakes up the PENDING lock will be gone. Process B then wakes up, finds the database UNLOCKED, obtains his EXCLUSIVE lock, commits his local cache's balance of $11,000 to the database, releases his lock, and exits. Process A then wakes up, finds the database UNLOCKED, obtains an EXCLUSIVE lock, commits her local cache's balance of $9,000 to the database, releases her lock, and exits. *The database now erroneously shows a balance of $9,000.* The problem is that the moment that process B commits his local cache's balance of $11,000 to the database, he causes process A's local cache to become *stale*, i.e. inconsistent with the database. After scouring the documentation, I came across the following article: http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError which seems to describe the exact scenario I described above. According to this article, SQLite has been fixed so that if a process encounters a SQLITE_BUSY during an explicit trans- action, then the transaction will *automaticall
Re: [sqlite] Race condition -- fixed?
A classic solution to that problem is not to perform updates but to insert transactions, The concept of log file systems to give concurrency is worth scrutiny. Richard Klein wrote: As I was thinking about the locking mechanism in SQLite 3, it occurred to me that the following race condition could occur. Imagine a joint bank account with a balance of $10,000. The wife makes a withdrawal of $1,000 at ATM 'A' (serviced by process A in the bank's mainframe), while at the same time the husband makes a deposit of $1,000 at ATM 'B' (serviced by process B). The steps performed by each process are as follows: Process A - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Process B - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Both processes open the accounts database, obtain SHARED locks, and proceed at about the same pace. Process A updates her local cache with a new balance of $900, while process B updates his local cache with a new balance of $11,000. Now suppose B gets to the COMMIT first. He tries to get a PENDING lock and succeeds. He then tries to promote his PENDING lock to EXCLUSIVE, but gets a SQLITE_BUSY instead, because process A holds a SHARED lock. So, he goes to sleep, hoping that when he awakens the SHARED lock will be gone. Meanwhile, process A reaches her COMMIT, tries to get a PENDING lock, but gets a SQLITE_BUSY instead, because process B already holds a PENDING lock. Process A then releases her SHARED lock (so that process B can be promoted from PENDING to EXCLUSIVE and do his commit), and goes to sleep, hoping that when she wakes up the PENDING lock will be gone. Process B then wakes up, finds the database UNLOCKED, obtains his EXCLUSIVE lock, commits his local cache's balance of $11,000 to the database, releases his lock, and exits. Process A then wakes up, finds the database UNLOCKED, obtains an EXCLUSIVE lock, commits her local cache's balance of $9,000 to the database, releases her lock, and exits. *The database now erroneously shows a balance of $9,000.* The problem is that the moment that process B commits his local cache's balance of $11,000 to the database, he causes process A's local cache to become *stale*, i.e. inconsistent with the database. After scouring the documentation, I came across the following article: http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError which seems to describe the exact scenario I described above. According to this article, SQLite has been fixed so that if a process encounters a SQLITE_BUSY during an explicit trans- action, then the transaction will *automatically* be rolled back, and the app will receive an error code of SQLITE_IOERR *instead of* SQLITE_BUSY. I understand this to mean that whenever coding an explicit transaction, the programmer must always be prepared to receive an SQLITE_IOERR when stepping through any SQL statement, and must deal with this error by going back to the start of the transaction and starting over. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Race condition -- fixed?
Guys, I read this forum regularly and I've given a lot of thought to all of these conversations about low level logic and trying to squeeze the last ounce of performance out of SQLite. That's not for me. Simplicity equates to robustness and my company needs robustness. And my time is really expensive so I need to not spend too much time tweaking my SQLite based application to get everything to work reliably and efficiently. I've wrapped all of my company's SQLite database accesses in my own API layer that encapsulates all of our applications' business rules and forces ALL transactions, no matter how lengthy or trivial, to be atomic by using a MUTEX to avoid the types of scenarios described below. This includes simple database accesses, even reads. Yes, I know it makes things slower. But, it's solid. And I won't be getting calls at 3:00 a.m. about some mysterious database problem. If my company wants something faster, they need to buy SQL Server. At $5000, it's a bargain. At my salary, I'm not. My 2 cents, Lee Crain _ -Original Message- From: Richard Klein [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 24, 2007 10:39 PM To: sqlite-users@sqlite.org Subject: [sqlite] Race condition -- fixed? As I was thinking about the locking mechanism in SQLite 3, it occurred to me that the following race condition could occur. Imagine a joint bank account with a balance of $10,000. The wife makes a withdrawal of $1,000 at ATM 'A' (serviced by process A in the bank's mainframe), while at the same time the husband makes a deposit of $1,000 at ATM 'B' (serviced by process B). The steps performed by each process are as follows: Process A - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Process B - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Both processes open the accounts database, obtain SHARED locks, and proceed at about the same pace. Process A updates her local cache with a new balance of $900, while process B updates his local cache with a new balance of $11,000. Now suppose B gets to the COMMIT first. He tries to get a PENDING lock and succeeds. He then tries to promote his PENDING lock to EXCLUSIVE, but gets a SQLITE_BUSY instead, because process A holds a SHARED lock. So, he goes to sleep, hoping that when he awakens the SHARED lock will be gone. Meanwhile, process A reaches her COMMIT, tries to get a PENDING lock, but gets a SQLITE_BUSY instead, because process B already holds a PENDING lock. Process A then releases her SHARED lock (so that process B can be promoted from PENDING to EXCLUSIVE and do his commit), and goes to sleep, hoping that when she wakes up the PENDING lock will be gone. Process B then wakes up, finds the database UNLOCKED, obtains his EXCLUSIVE lock, commits his local cache's balance of $11,000 to the database, releases his lock, and exits. Process A then wakes up, finds the database UNLOCKED, obtains an EXCLUSIVE lock, commits her local cache's balance of $9,000 to the database, releases her lock, and exits. *The database now erroneously shows a balance of $9,000.* The problem is that the moment that process B commits his local cache's balance of $11,000 to the database, he causes process A's local cache to become *stale*, i.e. inconsistent with the database. After scouring the documentation, I came across the following article: http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError which seems to describe the exact scenario I described above. According to this article, SQLite has been fixed so that if a process encounters a SQLITE_BUSY during an explicit trans- action, then the transaction will *automatically* be rolled back, and the app will receive an error code of SQLITE_IOERR *instead of* SQLITE_BUSY. I understand this to mean that whenever coding an explicit transaction, the programmer must always be prepared to receive an SQLITE_IOERR when stepping through any SQL statement, and must deal with this error by going back to the start of the transaction and starting over. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
On Wed, 2007-10-24 at 21:38 -0700, Richard Klein wrote: > As I was thinking about the locking mechanism in SQLite 3, > it occurred to me that the following race condition could > occur. > > Imagine a joint bank account with a balance of $10,000. > The wife makes a withdrawal of $1,000 at ATM 'A' (serviced > by process A in the bank's mainframe), while at the same > time the husband makes a deposit of $1,000 at ATM 'B' > (serviced by process B). The steps performed by each > process are as follows: > > Process A > - > BEGIN TRANSACTION; > SELECT balance FROM accounts WHERE accountId = '123-45-6789'; > UPDATE accounts SET balance = > WHERE accountId = '123-45-6789'; > COMMIT; > > Process B > - > BEGIN TRANSACTION; > SELECT balance FROM accounts WHERE accountId = '123-45-6789'; > UPDATE accounts SET balance = > WHERE accountId = '123-45-6789'; > COMMIT; > > Both processes open the accounts database, obtain SHARED > locks, and proceed at about the same pace. Process A > updates her local cache with a new balance of $900, while > process B updates his local cache with a new balance of > $11,000. Evaluating the UPDATE requires a RESERVED lock on the database file. Since only one process can hold the RESERVED lock, this particular scenario cannot occur. One or other of the updates will fail with SQLITE_BUSY. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Race condition -- fixed?
As I was thinking about the locking mechanism in SQLite 3, it occurred to me that the following race condition could occur. Imagine a joint bank account with a balance of $10,000. The wife makes a withdrawal of $1,000 at ATM 'A' (serviced by process A in the bank's mainframe), while at the same time the husband makes a deposit of $1,000 at ATM 'B' (serviced by process B). The steps performed by each process are as follows: Process A - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Process B - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Both processes open the accounts database, obtain SHARED locks, and proceed at about the same pace. Process A updates her local cache with a new balance of $900, while process B updates his local cache with a new balance of $11,000. Now suppose B gets to the COMMIT first. He tries to get a PENDING lock and succeeds. He then tries to promote his PENDING lock to EXCLUSIVE, but gets a SQLITE_BUSY instead, because process A holds a SHARED lock. So, he goes to sleep, hoping that when he awakens the SHARED lock will be gone. Meanwhile, process A reaches her COMMIT, tries to get a PENDING lock, but gets a SQLITE_BUSY instead, because process B already holds a PENDING lock. Process A then releases her SHARED lock (so that process B can be promoted from PENDING to EXCLUSIVE and do his commit), and goes to sleep, hoping that when she wakes up the PENDING lock will be gone. Process B then wakes up, finds the database UNLOCKED, obtains his EXCLUSIVE lock, commits his local cache's balance of $11,000 to the database, releases his lock, and exits. Process A then wakes up, finds the database UNLOCKED, obtains an EXCLUSIVE lock, commits her local cache's balance of $9,000 to the database, releases her lock, and exits. *The database now erroneously shows a balance of $9,000.* The problem is that the moment that process B commits his local cache's balance of $11,000 to the database, he causes process A's local cache to become *stale*, i.e. inconsistent with the database. After scouring the documentation, I came across the following article: http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError which seems to describe the exact scenario I described above. According to this article, SQLite has been fixed so that if a process encounters a SQLITE_BUSY during an explicit trans- action, then the transaction will *automatically* be rolled back, and the app will receive an error code of SQLITE_IOERR *instead of* SQLITE_BUSY. I understand this to mean that whenever coding an explicit transaction, the programmer must always be prepared to receive an SQLITE_IOERR when stepping through any SQL statement, and must deal with this error by going back to the start of the transaction and starting over. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
