Pessimistic Locking
- sumitnagle
- Jun 26
- 12 min read
Updated: Jun 27
Pessimistic Locking is a concurrency control strategy where the database prevents other transactions from accessing or modifying data until the current transaction is complete. It assumes that conflicts are likely (pessimistic, because it assumes conflicts are likely and therefore blocks other operations preemptively) and proactively acquires locks to prevent these conflicts.
Pessimistic locks block other operations that could lead to inconsistency or data corruption.
Pessimistic locking ensures strong consistency but can lead to reduced concurrency (which result in reduced system throughput). As its locking data, it also need additional system resources to manage these locks.
Pessimistic locking is ideal for high-contention environments (many transactions modify the same data) where data conflicts are frequent. However, if using improper locking sequences, it can lead to deadlocks.
Locking is a straightforward, before a transaction accesses data (for reading or writing), it acquires a lock (base on the isolation level) on that data. Other transactions wanting to access the same data check if a lock is held,
If the lock is compatible (for example, multiple readers can share the data), the second transaction can also proceed.
If the lock is not compatible (for example, one transaction wants to write while another is reading), the second transaction must wait until the lock is released.
When the transaction completes its operation, it releases the lock, allowing other waiting transactions to proceed.
Although locking strategies can vary between systems, these two lock types are most common,
Read Lock (shared lock), is a type of lock used in database to manage read operations on a particular resource (such as a row, page, or table).
Multiple transactions can hold a shared lock on the same resource simultaneously. However, no transaction can write while a shared lock is in place, preventing any writes while reads are in progress.
Meaning all these transactions can safely read the same data simultaneously without blocking each other, ensuring data is not modified by another transaction while one transaction is reading it.
As any write operation is prohibited while a shared lock, it can lead to blocking for writers, if there is a long-running transaction which haven't released the lock.
At higher isolation levels (such as, Repeatable Read, Serialisable), shared locks may be held until the transaction completes to ensure consistent reads.
This shared lock can be at multiple levels such as locking a row of data, locking a whole page (containing multiple row of data) or even a table (containing all the pages).
For example, say we have transaction A runs,
SELECT * FROM employees;this will acquire a read lock on those rows/pages, transaction B also wants to read the same rows, so it can share the read lock with A. Now, if another transaction C tries to UPDATE employees on the same data. It must wait until both A and B finish reading and release their read locks before it can perform write (actually they do write lock).
Write Lock (exclusive lock), is a strict lock and only one transaction can hold an exclusive lock on a piece of data at a time. And this prevents both read and write locks from other transactions (no new shared locks or exclusive locks can be granted on that resource until current exclusive lock is released).
Shared locks and exclusive locks are not always used together and they serve different purposes, shared locks are for read operations and exclusive locks for write operation and both of them can be applied at multiple levels, row, page, table or even database.
Although different purpose, in many scenarios, they coexist as part of ensuring data consistency. For example, a transaction may first acquire a shared lock to read and verify data, and later upgrade it to an exclusive lock to modify the data. When multiple transactions are reading and one attempts to write, both locks will be present but the write will wait for the shared locks to release.
Ok!! So we understand different type of locks but how do they actually work with isolation levels,
There some other type of locks, which are used together with shared locks and exclusive locks, which acts like support to these two actual locks.
Intent Lock
Intent Lock, is a type of meta-lock used in database systems to indicate a transaction's intention (not actually locking, but only showing intent) to acquire a more restrictive lock (like shared lock or exclusive lock) at some lower level. The level we meant is the level at which transaction is acquiring an actual lock, is it at row level, page level or table level or even at database level.
Now the question is why! why do we even need to show intention? why can't we just directly lock? Think of a situation where transaction A was to update a row (exclusive lock on a row ) and transaction B want to update all the rows (exclusive lock on a table)! so in this case transaction B need to scan all the rows in that table and check if none of them have a actual lock (shared lock or exclusive lock). But now transaction A shows intent lock at table level and actual lock at row level, transaction B know it cannot update the whole table because A has intention on locking something at some lower level.
We know why we want intent! but how actually intent works! following above example, when transaction A want to acquire exclusive lock at a row "r" (present in page "p" in table "t"), it first shows intent lock on table "t", then shows intent lock on page "p" and then it actually acquires actual exclusive lock on row "r".
Now consider these below scenarios,
transaction B want to update whole table "t" (i.e. acquire and actual lock on table), here it sees that table "t" have intent lock, meaning something is getting changed within this table) so it cannot process this transaction as of now and it need to wait for lock to be released.
Note: Intent lock, are release implicitly when the transaction is committed or rolled back, handled by database engine.
transaction C want to update a page "p", here also this transaction start from table level, it sees the intent lock on table "t", however transaction C intention is to update something on page level and there could be chances there could be some other page getting locked! for this purpose transaction C need to check at page level as well! but here it sees that the same page "p" have intent lock, so it cannot process as of now and it also need to wait for lock to be release.
transaction D want to update row "r2" in page "p", following the same, transaction would check for lock at row level, but now it don't see any lock (neither intent lock or any actual lock) so its good to process hence it proceeds an acquires intent lock on table "t", intent lock of page "p" and then exclusive lock on row "row2".
So intent lock, help us save time it takes to scan data at each level to check for possible locks and ensures proper coordination and conflict detection across different levels.
Also, as intent lock, is only intention to lock something at lower level, multiple transaction can share intent lock. Intent locks are of different types,
Intent shared, indicates intention to acquire a shared lock at a lower level.
Intent exclusive, indicates intention to acquire an exclusive lock at a lower level.
Shared with intent exclusive, indicates a shared lock at the current level and intention to acquire exclusive locks at a lower level.
Before we discuss, the next lock, we need to know something,
Similar to any concurrent system, even here deadlocks can occur. For example, when two or more transactions are waiting for each other to release locks, but none can proceed because they are holding locks that the other transaction needs. Say transaction A locks row 1 and needs row 2 (locked by transaction B) and transaction B locks row 2 and needs row 1 (locked by transaction A), because of this, neither transaction can proceed. This deadlock is of circular wait kind.
-- Transaction A
BEGIN TRANSACTION;
UPDATE Employees SET salary = 5000 WHERE id = 1; -- Locks Row 1
WAITFOR DELAY '00:00:05'; -- Simulates processing time
UPDATE Employees SET salary = 7000 WHERE id = 2; -- Wants Row 2 (Blocked!)
COMMIT;-- Transaction B
BEGIN TRANSACTION;
UPDATE Employees SET salary = 6000 WHERE id = 2; -- Locks Row 2
WAITFOR DELAY '00:00:05'; -- Simulates processing time
UPDATE Employees SET salary = 8000 WHERE id = 1; -- Wants Row 1 (Blocked!)
COMMIT;A deadlock occurs when these four conditions hold simultaneously,
Mutual Exclusion. At least one resource (like a row or table) is held in a non-sharable (exclusive) mode, meaning only one transaction can use it at a time.
Hold and Wait. A transaction holds one or more resources and waits to acquire additional resources held by other transactions.
No Preemption. Resources cannot be forcibly taken away from a transaction and a transaction must release its resources voluntarily.
Circular Wait. A closed chain of two or more transactions exists, where each transaction is waiting for a resource held by the next transaction in the chain.
To prevent or mitigate deadlocks, databases employ a mix of techniques including but not limited to, update lock (we will discuss this now), intent lock (used to efficiently manage locking in hierarchical structures), optimistic locking, and transaction design patterns like consistent lock ordering and lock timeout policy.
Databases also automatically detect deadlocks and terminate one transaction to resolve the issue. For example, in SQL server, this below command tells the database to allow the other transaction to proceed.
SET DEADLOCK_PRIORITY LOW;
And we can configure, our application retry the failed transaction after a short delay.
Update Lock
Update lock, is a special type of lock used by databases to prevent escalation deadlocks where multiple transactions acquire shared locks and later try to upgrade to exclusive locks on the same resource.
This allows only update lock on a resource at a time which prevents the classic upgrade deadlock scenario.
When a transaction intends to modify a resource but hasn't modified it yet, it acquires a update lock. This ensures that only one transaction can prepare to modify a resource (prepare meaning the update is not done yet, but in future, only this specific transaction can update). Once the actual update is performed, the update lock is upgraded to an exclusive lock.
A update lock is compatible with shared lock (others can still read), but it is not compatible with other update lock or exclusive lock locks (only one transaction can prepare for an update).
When a transaction reads data, it acquires a shared lock and later, when it wants to update the data, it tries to convert the shared lock to an exclusive lock and if two transactions hold shared lock and both try to upgrade to exclusive lock, it can lead to a deadlock. So for this case, instead of acquiring a shared lock during the read, the transaction acquires an update lock. Since update lock are incompatible with other update lock or exclusive lock, only one transaction can hold a update lock on a resource at a time. This ensures that only one transaction can prepare for an update, preventing deadlocks.
If two transactions immediately acquire exclusive lock (without the read phase, i.e. no shared lock before exclusive lock), update lock won't help.
Key-Range Lock
Key-Range Lock is a special type of lock used by databases (like SQL Server and PostgreSQL) to prevent phantom reads by locking a range of index values. It is used primarily in the Serialisable isolation level to ensure transaction consistency when dealing with range queries. It ensures that no new rows can be inserted into, updated within, or deleted from the locked range during the transaction.
Key-range locks work only on indexed columns. If a range condition is queried on a non-indexed column, the database might resort to table-level locks, reducing concurrency. Also if the query range is too large, it can result in extensive locking and block other transactions unnecessarily.
Apart from intent locks, update locks, and key-range locks, databases also use other supportive locks to manage concurrency safely. Schema locks protect database structures when queries or schema changes happen. Bulk update locks help handle large data loads efficiently without blocking everything. Next-key locks in InnoDB combine a row lock with a gap lock to prevent phantom rows during reads. Gap locks only lock empty spaces between index records to stop new inserts in a range. Lastly, table locks can lock an entire table when needed, ensuring no other transaction can read or write until it's done. These locks work together behind the scenes to keep multi-user systems safe and consistent.
Ok! so we are done with all these locks and for TLDR; Shared locks and exclusive locks are the core mechanisms for controlling concurrency. Intent lock, update lock, and key-range locks complement shared locks and exclusive locks, especially in complex scenarios and higher isolation levels. Isolation levels determine how and when these locks are applied. Multiple locks can be used together to ensure both data consistency and high concurrency.
Finally we are done with these different type of locks and lets actually see the locking protocol, which determines the protocol on which these lock will take action!
Locking Protocol
To manage when locks (that we have discussed above) are acquired or released, databases use locking protocols. A locking protocol is a mechanism used to ensure that multiple transactions can safely access and modify a database without causing data in-consistency.
Two-Phase Locking
The transaction's lock management is divided into two distinct phases,
Growing Phase, transaction acquires all the locks (shared, exclusive, intent, so on) it needs. No locks can be released during this phase and this phase continues until the first lock is released.
Shrinking Phase, transaction releases locks but cannot acquire any new locks. Once the first lock is released, the transaction cannot acquire additional locks.
Two-Phase Locking, guarantees serialisability (i.e. Serialisable isolation level) but doesn’t guarantee deadlock prevention.
BEGIN TRANSACTION;
-- Growing Phase (Acquiring Locks)
SELECT * FROM Employees WITH (S); -- Acquires shared Lock
UPDATE Employees SET salary = 5000 WHERE id = 1; -- Upgrades to exclusive lock
-- Shrinking Phase (Releasing Locks)
COMMIT; -- All locks are released
There are variants of two-phase locking,
Transaction starts by entering the growing phase and it acquires locks as needed. Once it releases any lock, it enters the shrinking phase, during this phase, it cannot acquire new locks, only release them. Once all locks are released, the transaction commits or rolls back, this type is called basic two-phase locking. basic two-phase locking, the protocol ensures serialisability but does not concern itself with recoverability or avoiding dirty reads, this is because, since locks can be released before commit, other transactions can read uncommitted data, leading to dirty reads. For example, if a transaction rolls back after releasing locks, other transactions that read that data will also need to roll back, causing cascading failures.
In strict two-phase locking, all exclusive locks are held until the transaction commits or rolls back, which prevents dirty reads and ensures recoverability. Shared locks can still be released earlier (depending on the implementation). Databases like PostgreSQL, SQL Server, and Oracle use strict two-phase locking.
There are two more two-phase locking, In rigorous two-phase locking, where all locks (shared lock and exclusive lock) are held until the transaction commits, this provides strong consistency but reduces concurrency. In conservative two-phase locking, transaction acquires all required locks upfront (before it begins executing). If any lock isn’t available, the transaction waits, this completely avoids deadlocks but can be less efficient.
The question left is! How two-phase locking ensures serialisability! first, no conflicting operations (like write–write or read–write conflicts) can happen concurrently, second, the two phases (growing then shrinking) control when locks can be acquired and released, preventing anomalies like dirty reads, non-repeatable reads, or phantom reads.