Topic: Database Management Systems
Consider table bank_accounts = (account_id, balance). Assume two accounts with account_id as A and B. Also assume that initially both accounts have a balance Rs. 5,000 each. Two transactions T1 and T2 are executed concurrently, both under REPEATABLE READ isolation level. Database uses standard locking mechanism.
T1: transfers Rs. 1,000 from A to B.
DB operation sequence is as follows:
BEGIN; READ(A); READ(B); WRITE(A); WRITE(B); END
T2: transfers Rs. 1,000 from B to A.
DB operation sequence is as follows:
BEGIN; READ(B); READ(A); WRITE(B); WRITE(A); END
Both transactions read the corresponding account balances, deducts Rs. 1,000 from one account, then adds Rs. 1,000 to the other account and results are written back.
Assuming that all interleaving of execution of T1 and T2 are possible and that there are no extraneous factors, which of the following will never occur?
- A. A's account balance shows Rs. 5,000 and B's account balance shows Rs. 5,000
- B. Deadlock is detected and T1 will be rolled back to recover
- C. Deadlock is detected and T2 will be rolled back to recover
- D. A's account balance shows Rs. 4,000 and that of B will have Rs. 6,000
Answer: D. A's account balance shows Rs. 4,000 and that of B will have Rs. 6,000
Explanation: In a standard 2PL (Two-Phase Locking) system under REPEATABLE READ, reads acquire shared (S) locks and writes acquire exclusive (X) locks held until the end of the transaction. If T1 and T2 execute concurrently, they may both acquire S-locks on A and B during their READ phases. When they attempt to WRITE, they will both need X-locks, leading to a circular wait (Deadlock). The DBMS will detect the deadlock and roll back one of the transactions (either T1 or T2), allowing the other to complete. The aborted transaction will automatically be retried or handled by the application, ensuring that eventually both transactions complete successfully. Therefore, a permanent state where only one transaction has succeeded (A=4000, B=6000) will never occur as the system guarantees atomicity and eventual consistency (both complete, leaving balances at 5000).