The main purpose of locking is to control interleaving transactions. This is obvious when there are concurrent transactions in the system. There are two types of Locks called available Shared S(a) to read data items and Exclusive X(a) to both read and write data items. The philosophy behind 2PL is to avoid isolation violation before it happens.
The locking itself is not enough due to the early release of locks which might lead to some inconsistency in the database upon aborting transactions. The below figure illustrates the issue of the early release of locks. If T1 aborts, the T2 will commit a dirty read from T1 and will lead to inconsistent results in the database.
To guarantee serializability in a distributed manner, we normally use a protocol called two-phase locking(2PL). 2PL has two phases to control the execution of the transactions. It ensures that the transactions are serializable conflict. The serializable conflict means when there is no cycle between the read and write operations of different transactions.
The first phase is a growing phase where a transaction is only acquiring all locks needed and does not release any lock. The second phase is the shrinking phase where the transaction releases the locks and does not request any new locks. 2PL guarantees conflict serializability, but the problems are cascading aborts, deadlock, and unnecessary or early acquiring of locks.
The locks in 2PL can be upgraded and downgraded for both reading and writing.
To overcome this issue, a strong strict 2PL is proposed. Strick 2PL does not allow the acquisition of locks after the growing phase finishes and does not allow the shrinking phase to release locks until the transactions commit or abort.
- Deadlock detection: It keeps track of which transactions waiting for. The database creates a wait-for graph to keep track of what locks each transaction is waiting for to acquire.
- Deadlock prevention: transactions need to know all locks in advance which is done by the application. For instance, two transactions try to acquire a lock. If one of them wants to acquire the lock, then there are two algorithms:
1) Wait-die: old waits for young.
2) Wound-wait: young waits for old.