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