Richard Klein <[EMAIL PROTECTED]> wrote:
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.
Process B _cannot_ commit, since process A holds a shared lock for as long as its transaction is open. Your scenario describes a deadlock: the only way out of it is for one process or the other to roll back its transaction.
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.
Quite. In other words, process A needs to do all its reading and writing within a single transaction if it wants its data to be consistent. Well, duh.
Igor Tandetnik
----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
