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 = <balance - withdrawal> WHERE accountId = '123-45-6789'; COMMIT; Process B --------- BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = <balance + deposit> 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] -----------------------------------------------------------------------------