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. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
