Two Phase Locking

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.

1. Two phase locking and Predicate locking

2. 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:

1. 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 ...