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