7.Two Phase Locking Protocols and Deadlock
7.Two Phase Locking Protocols and Deadlock
UNIT IV - Transaction
Transaction Concepts – ACID Properties – Schedules – Serializability – Concurrency
Control – Need for Concurrency – Locking Protocols – Two Phase Locking – Deadlock –
Transaction Recovery – Save Points – Isolation Levels – SQL Facilities for Concurrency and
Recovery.
T1 T2
1 lock-S(A)
2 lock-S(A)
3 lock-X(B)
4 ………. ……….
5 Unlock(A)
6 Lock-X(C)
7 Unlock(B)
8 Unlock(A)
9 Unlock(C)
10 ………. ……….
This is a basic outline of a transaction that demonstrates how locking and unlocking work
in the Two-Phase Locking Protocol (2PL).
Transaction T1
The growing Phase is from steps 1-3
The shrinking Phase is from steps 5-7
Lock Point at 3
Transaction T2
The growing Phase is from steps 2-6
The shrinking Phase is from steps 8-9
Schedule
The image illustrates a transaction schedule using the Two-Phase Locking (2PL) protocol,
showing the sequence of actions for three transactions T1, T2 and T3.
Key Points:
1. Transaction T1:
T1 acquires an exclusive lock (X) on data item A, performs a write operation
on A and then acquires a shared lock (S) on B.
T1 reaches its lock point (LP) after acquiring all locks.
Eventually, T1 fails and a rollback is triggered, undoing its changes.
2. Transaction T2:
T2 reads A after T1 writes A. This is called a dirty read because T1’s write is not
committed yet.
When T1 rolls back, T2’s operations become invalid, and it is also forced to
rollback.