Introduction
When multiple transactions run at the same time against the same data, the database must control how they interact so results stay correct. Concurrency control mechanisms include locks, which physically block conflicting access, and isolation levels, which define how much one transaction is allowed to see of another's uncommitted or concurrently changing data. Locking strategies are generally classified as pessimistic (lock first, then work) or optimistic (work first, then check for conflicts before committing).
Cricket analogy: Two DRS reviews requested simultaneously must be resolved without contradicting each other, like concurrency control; a strict pessimistic approach locks the review process first, while an optimistic one lets both proceed and checks for conflict after.
Syntax
-- Setting an isolation level for a transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- Pessimistic locking: acquire an exclusive lock on selected rows
SELECT * FROM inventory WHERE product_id = 7 FOR UPDATE;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 7;
COMMIT;Explanation
A shared lock (S-lock) allows multiple transactions to read the same data concurrently but blocks any of them from writing to it, while an exclusive lock (X-lock) is held by a single transaction for writing and blocks all other reads and writes to that data until released. Pessimistic locking assumes conflicts are likely, so it acquires locks (e.g. via SELECT ... FOR UPDATE) before modifying data, forcing other transactions to wait. Optimistic locking assumes conflicts are rare, so it lets transactions proceed without locks and instead checks at commit time (often via a version number or timestamp column) whether the data changed since it was read, rejecting the commit if a conflict occurred. The SQL standard defines four isolation levels that trade off consistency guarantees for concurrency: Read Uncommitted allows dirty reads, non-repeatable reads, and phantom reads; Read Committed prevents dirty reads but still allows non-repeatable reads and phantom reads; Repeatable Read prevents dirty reads and non-repeatable reads but may still allow phantom reads; Serializable prevents dirty reads, non-repeatable reads, and phantom reads by making concurrent transactions behave as if executed one after another.
Cricket analogy: SELECT FOR UPDATE locking a scorecard row is like a scorer physically holding the official scorebook so no other scorer can edit the same over's entry until they release it, an exclusive lock in action.
Example
-- Transaction A
BEGIN TRANSACTION;
SELECT quantity FROM inventory WHERE product_id = 7 FOR UPDATE;
-- Transaction B now tries to UPDATE or SELECT ... FOR UPDATE
-- the same row and must wait until A commits or rolls back
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 7;
COMMIT; -- releases the lock, Transaction B can now proceedAnalysis
The FOR UPDATE clause places an exclusive lock on the selected row, so Transaction B is blocked from modifying or lock-reading the same row until Transaction A commits or rolls back. This prevents the classic lost-update problem, where two transactions read the same quantity, both decrement it independently, and one update overwrites the other. Choosing a stricter isolation level like Serializable reduces anomalies but increases lock contention and the chance of transactions blocking or being aborted due to serialization failures; a looser level like Read Committed improves throughput but permits more anomalies, so the right choice depends on the application's tolerance for stale or inconsistent reads versus its need for concurrency.
Cricket analogy: The lost-update problem is like two ticket counters both reading '1 seat left' and both selling it, overselling the match; FOR UPDATE prevents this by locking the seat count row until one sale completes.
Key Takeaways
- Shared locks allow concurrent reads; exclusive locks allow only one transaction to read or write.
- Pessimistic locking blocks conflicting transactions upfront; optimistic locking checks for conflicts only at commit time.
- Read Uncommitted allows dirty reads, non-repeatable reads, and phantom reads.
- Read Committed blocks dirty reads but still allows non-repeatable and phantom reads.
- Repeatable Read blocks dirty and non-repeatable reads but may still allow phantom reads.
- Serializable blocks all three anomalies by fully isolating concurrent transactions, at the cost of concurrency.
Practice what you learned
1. What is the key difference between a shared lock and an exclusive lock?
2. Which isolation level allows a transaction to read data that another transaction has modified but not yet committed?
3. Which isolation level prevents dirty reads and non-repeatable reads but may still allow phantom reads?
4. What best describes optimistic locking?
Was this page helpful?
You May Also Like
ACID Properties
The four guarantees—Atomicity, Consistency, Isolation, Durability—that make database transactions reliable.
Transactions and COMMIT/ROLLBACK
How to group SQL statements into transactions and use COMMIT and ROLLBACK to make or undo their effects.
Query Optimization Basics
Foundational techniques for writing queries the optimizer can execute efficiently.