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

4. An object/record can only be written to by a single transaction. Write access is represented by an exclusive lock that can be acquired by a single transaction at any given time. In other words, a writer blocks all other writers and readers.

If you contrast two phase locking with snapshot isolation, the difference is that in the latter (i.e. snapshot isolation) readers don’t block writers and writers don’t block readers. Two phase locking is robust enough to block ...