Two Phase Locking
Explore the two phase locking protocol used in databases to maintain data integrity and serializable isolation. Understand how shared and exclusive locks prevent race conditions, the handling of deadlocks, and the use of predicate and index-range locks to manage concurrent reads and writes safely.
We'll cover the following...
Databases can achieve serializable isolation using a technique called two phase locking (2PL) combined with either predicate or index-range locks to eliminate write skew and phantom reads.
Two phase locking and Predicate locking
Two phase locking and Index-range (a.k.a next-key) locking
When discussing the various race conditions that concurrent transactions can exhibit in the previous lessons, we saw that one of the solutions is to lock the rows when a transaction attempts to write a record/object. A more restrictive locking approach called two-phase locking eliminates race conditions that weaker forms of isolations suffer from.If a transaction T1 reads a record/object and hasn’t committed or aborted yet then another transaction T2 can’t write to the same record/object before T1 commits or aborts.
The two phase locking algorithm follows the below rules:
If a transaction T1 reads a record/object and hasn’t committed or aborted yet then another transaction T2 can’t write to the same record/object before T1 commits or aborts.
2. If a transaction T1 writes to a record/object and hasn’t committed or aborted yet then another transaction T2 can’t read the same record/objects before T2 commits or aborts.
3. Any number of transactions can read a record/object as long as that record/object isn’t being written to. In other words, readers don’t block other readers but they block writers. Read access is represented by a shared lock that multiple transactions attempting to read an object/record can acquire ...