Locking Strategies
Learn how to distinguish the differences between different types of locking strategies.
We'll cover the following
A lock on a database item ensures that only one transaction updates that item in the presence of concurrent transactions. A lock ensures that the database remains consistent before and after the transaction. We’ll discuss three different types of locking strategies in this lesson:
Two-phase locking
Predicate locking
Index range locking
Two-phase locking
Two-phase locking (2PL) is a type of concurrency control that provides a serializability isolation level. In two-phase locking, multiple transactions are allowed to read concurrently if no transaction wants to write the data. When a transaction wants to write the data, it takes an exclusive lock and blocks all readers and writers.
In a two-phase locking:
Writers block other writers and readers.
Readers don't block other readers.
Two-phase locking blocks readers and writers by taking a lock in the database. There are two types of locks:
Shared lock: A lock acquired by readers that allows concurrent transactions to read data independently without blocking each other.
Exclusive lock: A lock acquired by a writer that blocks all other readers and writers.
These are different scenarios where lock acquisition is used in two-phase locking:
When a transaction wants to read the data, it acquires a shared lock. Multiple transactions can acquire shared locks concurrently if no transaction has acquired an exclusive lock.
When a transaction writes data, it acquires an exclusive lock and ensures no concurrent transaction has ever acquired a shared or exclusive lock. The incoming transaction will wait for the lock release if a lock is already acquired.
A transaction that acquired a shared lock wanting to write data upgrades the lock to an exclusive lock. But the upgrade process should ensure no concurrent transaction ever acquired a shared or exclusive lock.
Once the transaction acquires an exclusive lock, it holds the lock until it commits or aborts.
Get hands-on with 1400+ tech skills courses.