Consider table bank_accounts = (account_id, balance). Assume two accounts with…

Two-Phase Locking (2PL) with lock upgrades (shared to exclusive) guarantees serializability among the transactions that commit, and it does this by converting any dangerous overlap into a blocking wait (or a deadlock) rather than letting it complete silently. Concretely: a transaction must hold a shared lock to read an item and must upgrade that to an exclusive lock before writing it; the upgrade is refused for as long as any other transaction still holds a lock on the same item. So the only reachable final states are ones equivalent to a serial execution (both transactions commit) or ones where a lock conflict forces exactly one transaction to abort, leaving only the survivor's effect visible.

Trace the two operation sequences against this rule -- T1: READ(A), READ(B), WRITE(A), WRITE(B); T2: READ(B), READ(A), WRITE(B), WRITE(A):

1. Serial-equivalent case: one transaction fully commits (and releases its locks) before the other begins its writes. Because one transfers Rs. 1,000 from A to B and the other transfers Rs. 1,000 from B to A, running them one after the other in either order cancels out, leaving A = Rs. 5,000 and B = Rs. 5,000.

2. Reversed-order case: T1 reads A first and T2 reads B first; since shared (read) locks don't conflict with each other, each transaction can then also acquire a shared lock on the OTHER account before either attempts to write. At that point both transactions hold shared locks on both A and B, and each one's attempt to upgrade to an exclusive lock (needed to write) is blocked by the other's shared lock -- a genuine circular wait, i.e. a deadlock, which the DBMS must detect and resolve by rolling one transaction back.

3. If the DBMS resolves that circular wait by rolling T2 back FIRST, T2's shared locks are released, T1's blocked upgrade then succeeds, and only T1 goes on to write and commit -- leaving A = Rs. 4,000 and B = Rs. 6,000 (and, symmetrically, rolling T1 back first would let T2 alone finish, leaving A = Rs. 6,000 and B = Rs. 4,000).

4. The one outcome this locking rule structurally rules out is A = Rs. 4,000 and B = Rs. 4,000 (both accounts net LOWER). That number can only come from an uncontrolled lost update: both transactions reading the original Rs. 5,000 balances before either writes, then each independently overwriting the other's result so the Rs. 1,000 'in transit' is destroyed for both accounts. But that requires both transactions to hold read locks on both items and then write without ever waiting for the other -- exactly the situation step 2 shows is blocked (converted into a wait/deadlock) rather than allowed to complete.

Cross-check with the conservation invariant: A + B must equal Rs. 10,000 in every state reachable from a fully committed-or-rolled-back execution, since each transaction only moves Rs. 1,000 between the two accounts. 5,000 + 5,000 = 10,000 (both commit); 4,000 + 6,000 = 10,000 and 6,000 + 4,000 = 10,000 (only one transaction's transfer survives) -- all conserve the total. But 4,000 + 4,000 = 8,000 does not conserve the total, confirming it cannot be a legitimate outcome of two atomic Rs. 1,000 transfers; it is reachable only through an uncontrolled lost update, which the required lock upgrade here prevents.

So the account state that will never occur under this locking scheme is both balances showing Rs. 4,000.