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;
