Richard Klein <[EMAIL PROTECTED]>
wrote:
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.
Note that "releases SHARED lock" here means "rolls back transaction".
That's the only way for A to release the lock.
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 bug in the banking application, then. It relies on the balance
obtained in a now-rolled-back transaction to still be valid, which of
course is very much not guaranteed. Process A must rerun the original
sequence from the beginning - start transcation, read balance, update
balance, commit transaction. Only when both read and update operations
are run within a single transaction is consistency guaranteed.
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.
Process A would be wise to clear its cache when it rolls back its
transaction.
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.
Yes.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
