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 = <balance - withdrawal>
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 - <withdrawal>
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 = <balance - withdrawal>
WHERE accountId = '123-45-6789';
COMMIT;
