Consider the following sequences of actions executed in a DBMS: Sequence S1:…
Consider the following sequences of actions executed in a DBMS:
Sequence S1:
T1:R(X), T2:W(X), T2:W(Y), T3:W(Y), T1:W(Y), T1:Commit, T2:Commit, T3:Commit
Sequence S2:
T1:R(X), T2:W(Y), T2:W(X), T3:W(Y), T1:W(Y), T1:Commit, T2:Commit, T3:Commit
For each sequence, explain how the following concurrency control mechanisms handle execution:
Strict 2PL with timestamp-based deadlock prevention (Wait-Die)
Strict 2PL with deadlock detection
Conservative Strict 2PL
Attempted by 7 students.
Show answer & explanation
Strict 2pl with wait-die
In this approach, older transactions wait while younger ones are aborted.
In S1, T1 acquires shared lock on X. When T2 requests exclusive lock on X, it is aborted due to lower priority. T3 proceeds with Y. T1 later waits for Y until T3 commits. After T3 releases locks, T1 completes. T2 restarts.
In S2, T2 starts but gets aborted when requesting lock on X held by older T1.Strict 2pl with deadlock detection
Here, transactions are allowed to wait, and deadlocks are detected using a waits-for graph.
In S1, T2 waits for X and T1 waits for Y, but no circular wait occurs, so execution completes without deadlock.
In S2, T1 waits for T2 and T2 waits for T1, forming a cycle. Deadlock is detected and one transaction is aborted.Conservative strict 2pl
Transactions acquire all required locks before execution.
Both S1 and S2 execute serially (T1 → T2 → T3), avoiding deadlocks completely but reducing concurrency.
Wait-Die:
Older → Wait
Younger → Abort ❌
Deadlock Detection:
T1 → T2
↑ ↓
Cycle → Deadlock ❌
Conservative 2PL:
All Locks First → No Waiting → No Deadlock