Locking in a database management system is used to control transactions. We have two types of locks:
In Shared lock, we can only read the data, we cannot write the data.
In Exclusive lock, we can both read and write the data.
Now, let’s understand two-phase locking.
Two-phase locking is a protocol that ensures serializable conflict schedules.
In two-phase locking, we have two phases:
In the growing phase, the transaction obtains locks, but the transaction may not release the locks.
The transaction may not release locks in the shrinking step, and the transaction may not obtain locks.
Two-phase locking does not ensure freedom from deadlocks.
Cascading roll-back is possible under two-phase locking. To avoid this, follow a modified protocol called strict two-phase locking. Here, a transaction must hold all its exclusive locks until it commits/aborts.
Rigorous two-phase locking is even stricter. Here, all locks are held till commit/abort. In this protocol, transactions can be serialized in the order they commit. Two-phase locking with lock conversions:
First phase:
Second phase:
can release a lock-S
can release a lock-X
can convert a lock-X to a lock-S (downgrade)
This protocol assures serializability but still relies on the programmer to insert the various locking instructions.
S.no | T1 | T2 | T3 |
1 | LOCK-X(R1) | ||
2 | |||
3 | |||
4 | LOCK-S(R2) | ||
5 | UNLOCK(R1) | LOCK-S(R1) | |
6 | UNLOCK(R2) | ||
7 | LOCK-X(R2) | ||
8 | LOCK-S(R1) | ||
9 | UNLOCK(R1) | ||
10 | UNLOCK(R2) | ||
11 | UNLOCK(R1) |
In the first transaction, the growing phase is from steps 1 to 4, and the shrinking stage is from steps 5 to 6. We lock the process at step 4.
In the second transaction, the growing phase is from steps 7 to 8, and the shrinking stage is from steps 9 to 10. We lock the process at step 8.
In the third transaction, the growing phase is from steps 5 to 10, and the shrinking stage is at step 11. We lock the process at step 5.