Chapter 9: Locking and Concurrency Control
“Locks are pessimism made manifest: assume the worst, prevent it.”
MVCC handles most read/write concurrency, but some scenarios require explicit coordination. When two transactions want to modify the same row, one must wait. Locking mechanisms manage this coordination.
9.1 Why Locking Still Matters
Even with MVCC, we need locks for:
- Write-write conflicts: Two transactions can’t modify the same row simultaneously
- Schema changes: ALTER TABLE must exclude all other access
- Explicit coordination: Application-level locks for critical sections
- Foreign key checks: Ensuring referenced rows aren’t deleted
- Serializable isolation: Preventing certain anomalies
Transaction A Transaction B
UPDATE row WHERE id = 1 UPDATE row WHERE id = 1
Both want to modify the same row!
Without locking: Lost update anomaly
With locking: One waits for the other
9.2 Lock Types
Shared vs Exclusive Locks
The fundamental lock types:
SHARED (S) Lock - For reading
- Multiple transactions can hold simultaneously
- "I'm reading this, don't change it"
EXCLUSIVE (X) Lock - For writing
- Only one transaction can hold
- "I'm modifying this, everyone else wait"
Compatibility Matrix:
| Existing S | Existing X |
──────────┼────────────┼────────────┤
Request S | OK | BLOCK │
Request X | BLOCK | BLOCK │
Lock Granularity
Locks can apply at different levels:
LOCK HIERARCHY
Database Lock ──────────────────────────────────────────
│
└── Schema Lock ─────────────────────────────────
│
└── Table Lock ──────────────────────
│
└── Page Lock ─────────────
│
└── Row Lock ───
Coarse granularity (table locks):
- Few locks to manage
- Less overhead
- More contention (blocking)
Fine granularity (row locks):
- Many locks to manage
- More overhead
- Less contention
9.3 Row-Level Locking
Most OLTP databases use row-level locking for data modifications:
-- Transaction A
BEGIN;
UPDATE accounts SET balance = 900 WHERE id = 1;
-- Holds exclusive lock on row id=1
-- Transaction B (blocked)
UPDATE accounts SET balance = 800 WHERE id = 1;
-- Waits for A's lock...
-- Transaction A
COMMIT;
-- Lock released
-- Transaction B
-- Now acquires lock, proceeds
Lock Escalation
When too many row locks are held, some databases escalate to table locks:
SQL Server Lock Escalation:
Transaction locks 5000 rows on table T
→ System says "too many locks"
→ Escalate to single table lock
→ Release all row locks
Trade-off: Reduces lock memory but increases contention
PostgreSQL doesn’t escalate; it manages many row locks efficiently.
9.4 Intent Locks
To lock a row, we need to ensure no one has locked the entire table. Intent locks solve this:
INTENT LOCKS
To lock a row:
1. Acquire Intent Shared (IS) or Intent Exclusive (IX) on table
2. Acquire actual S or X lock on row
Intent Lock Compatibility:
| IS | IX | S | X |
──────────┼─────┼─────┼─────┼─────┤
IS | OK | OK | OK |BLOCK│
IX | OK | OK |BLOCK|BLOCK│
S | OK |BLOCK| OK |BLOCK│
X |BLOCK|BLOCK|BLOCK|BLOCK│
Example:
Transaction A wants to UPDATE row
1. Acquire IX lock on table (intent to write somewhere)
2. Acquire X lock on row
Transaction B wants to ALTER TABLE
1. Try to acquire X lock on table
2. BLOCKED by A's IX lock
3. Wait until A commits
9.5 Deadlocks
When transactions wait for each other in a cycle:
DEADLOCK SCENARIO
T1: Lock row A
T2: Lock row B
T1: Try to lock row B → WAIT (T2 has it)
T2: Try to lock row A → WAIT (T1 has it)
┌─────┐ ┌─────┐
│ T1 │───────►│ B │ waits for
└──▲──┘ └──┬──┘
│ │
│ holds │ holds
│ │
┌──┴──┐ ┌──▼──┐
│ A │◄───────│ T2 │ waits for
└─────┘ └─────┘
CYCLE! Neither can proceed.
Deadlock Detection
Databases maintain a wait-for graph:
Wait-For Graph:
T1 → T2 (T1 waits for T2)
T2 → T1 (T2 waits for T1)
Cycle detected! Deadlock exists.
Deadlock Resolution
When detected, abort one transaction (the “victim”):
-- PostgreSQL error
ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678;
blocked by process 5678.
Process 5678 waits for ShareLock on transaction 1234;
blocked by process 1234.
HINT: See server log for query details.
Victim selection criteria:
- Transaction with least work done
- Transaction that’s youngest
- Transaction with fewest locks held
Deadlock Prevention
Approach 1: Lock ordering
Always lock resources in consistent order (e.g., by ID)
Correct:
T1: Lock A, Lock B
T2: Lock A, Lock B
Wrong (potential deadlock):
T1: Lock A, Lock B
T2: Lock B, Lock A
Approach 2: Lock timeout
-- PostgreSQL: Give up after 1 second
SET lock_timeout = '1s';
UPDATE accounts SET balance = 0 WHERE id = 1;
-- ERROR: canceling statement due to lock timeout
Approach 3: NOWAIT
-- Fail immediately if lock unavailable
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row
9.6 Explicit Locking in SQL
SELECT FOR UPDATE
Lock rows for later modification:
BEGIN;
SELECT * FROM inventory WHERE product_id = 42 FOR UPDATE;
-- Row is now locked exclusively
-- Check quantity, decide to decrement
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT;
Without FOR UPDATE, another transaction could modify between SELECT and UPDATE.
SELECT FOR SHARE
Lock rows to prevent modification (but allow other readers):
BEGIN;
SELECT * FROM parent_table WHERE id = 1 FOR SHARE;
-- Now inserting child row - parent can't be deleted
INSERT INTO child_table (parent_id, data) VALUES (1, 'something');
COMMIT;
SKIP LOCKED
For job queues—skip rows that are locked:
-- Worker 1
BEGIN;
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Gets job A (locks it)
-- Worker 2 (concurrent)
BEGIN;
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Skips job A (locked), gets job B
This enables efficient work distribution without blocking.
9.7 Table Locks
Sometimes you need to lock entire tables:
-- PostgreSQL table lock modes (partial list)
LOCK TABLE accounts IN ACCESS SHARE MODE; -- Blocks nothing
LOCK TABLE accounts IN ROW SHARE MODE; -- Blocks exclusive
LOCK TABLE accounts IN ROW EXCLUSIVE MODE; -- Blocks share & exclusive
LOCK TABLE accounts IN SHARE MODE; -- Blocks writers
LOCK TABLE accounts IN EXCLUSIVE MODE; -- Blocks almost all
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE; -- Blocks everything
Common uses:
- Schema changes (ACCESS EXCLUSIVE)
- Bulk loads (SHARE or EXCLUSIVE)
- Preventing concurrent DDL
9.8 Advisory Locks
Application-defined locks not tied to database objects:
-- PostgreSQL advisory locks
-- Acquire lock on resource "user:42:profile"
SELECT pg_advisory_lock(42);
-- Do something critical
UPDATE user_profiles SET ... WHERE user_id = 42;
-- Release lock
SELECT pg_advisory_unlock(42);
Use cases:
- Distributed coordination
- Rate limiting
- Singleton job execution
- Custom critical sections
-- Try to get lock without waiting
SELECT pg_try_advisory_lock(12345);
-- Returns true if acquired, false if already held
-- Session-level lock (released when connection closes)
SELECT pg_advisory_lock(12345);
-- Transaction-level lock (released on commit/rollback)
SELECT pg_advisory_xact_lock(12345);
9.9 Optimistic vs Pessimistic Concurrency
Pessimistic Locking
Lock first, then operate. Assumes conflicts are common.
BEGIN;
SELECT * FROM inventory WHERE id = 42 FOR UPDATE; -- Lock now!
-- Calculate new quantity
UPDATE inventory SET quantity = ? WHERE id = 42;
COMMIT;
Pros: Prevents conflicts Cons: Reduces concurrency, potential deadlocks
Optimistic Locking
Check for conflicts at commit time. Assumes conflicts are rare.
-- Include version column
SELECT id, quantity, version FROM inventory WHERE id = 42;
-- Returns: quantity=100, version=5
-- Later, try to update with version check
UPDATE inventory
SET quantity = 99, version = version + 1
WHERE id = 42 AND version = 5;
-- If rows_affected = 0, someone else changed it!
-- Application must retry
Pros: More concurrency, no deadlocks Cons: Must handle retries, wasted work on conflict
When to Use Which
Pessimistic:
- High contention expected
- Short transactions
- Can't afford to retry
Optimistic:
- Low contention expected
- Long transactions (avoid holding locks)
- Read-heavy with occasional writes
- Web forms (user think time)
9.10 Latches vs Locks
Databases distinguish between:
Locks (heavyweight):
- Protect logical data (rows, tables)
- Held for duration of transaction
- Involved in deadlock detection
- Visible to users
Latches (lightweight):
- Protect physical structures (pages, B-tree nodes)
- Held for very short duration
- No deadlock detection (ordered acquisition)
- Internal implementation detail
B-tree insertion:
1. Acquire latch on root page (read)
2. Navigate down, acquiring latches
3. Find leaf page
4. Acquire latch on leaf (write)
5. Release latches on parent pages
6. Modify leaf
7. Release leaf latch
Very fast - microseconds, not milliseconds
9.11 Lock Monitoring
PostgreSQL
-- View current locks
SELECT
pid,
locktype,
relation::regclass,
mode,
granted
FROM pg_locks
WHERE relation IS NOT NULL;
-- View blocking relationships
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.query AS blocked_query,
blocking_locks.pid AS blocking_pid,
blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
MySQL InnoDB
-- View current locks
SELECT * FROM performance_schema.data_locks;
-- View lock waits
SELECT * FROM performance_schema.data_lock_waits;
-- Traditional (deprecated)
SHOW ENGINE INNODB STATUS\G
-- Look for "TRANSACTIONS" and "LATEST DETECTED DEADLOCK"
9.12 Reducing Lock Contention
1. Shorter Transactions
-- Bad: Long transaction holding locks
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- ... application does slow processing ...
UPDATE accounts SET balance = ? WHERE id = 1;
COMMIT;
-- Better: Do processing outside transaction
-- Read data (no lock)
SELECT * FROM accounts WHERE id = 1;
-- Application processing...
-- Then quick update
BEGIN;
UPDATE accounts SET balance = ? WHERE id = 1 WHERE balance = original_balance;
COMMIT;
2. Lock Only What’s Needed
-- Bad: Lock entire table
LOCK TABLE accounts IN EXCLUSIVE MODE;
UPDATE accounts SET balance = 0 WHERE id = 1;
-- Better: Lock only the row
UPDATE accounts SET balance = 0 WHERE id = 1;
3. Access Rows in Consistent Order
-- Bad: Different orders → Deadlock risk
-- Transaction 1: UPDATE ... WHERE id = 1; UPDATE ... WHERE id = 2;
-- Transaction 2: UPDATE ... WHERE id = 2; UPDATE ... WHERE id = 1;
-- Better: Always order by ID
-- Transaction 1: UPDATE ... WHERE id = 1; UPDATE ... WHERE id = 2;
-- Transaction 2: UPDATE ... WHERE id = 1; UPDATE ... WHERE id = 2;
4. Use Appropriate Isolation Level
-- Don't use SERIALIZABLE if READ COMMITTED is sufficient
BEGIN ISOLATION LEVEL READ COMMITTED;
-- Less locking overhead
9.13 Two-Phase Locking (2PL)
Traditional concurrency control protocol:
Two-Phase Locking Rules:
1. Growing phase: Acquire locks, never release
2. Shrinking phase: Release locks, never acquire
Transaction Timeline:
┌────────────────────────────────────────────────────────┐
│ Lock A │ Lock B │ Lock C │ Unlock A │ Unlock B │ Unlock C │
└────────────────────────────────────────────────────────┘
|←── Growing Phase ──→|←────── Shrinking Phase ──────→|
Strict 2PL: Hold all locks until commit/abort (common practice).
Guarantees serializability but can cause:
- Blocking (transactions wait for locks)
- Deadlocks (must be detected and resolved)
Most databases combine 2PL with MVCC: reads use MVCC snapshots, writes use 2PL.
9.14 Summary
Locking mechanisms coordinate concurrent access:
- Shared/Exclusive locks control read/write access
- Row-level locking provides fine-grained concurrency
- Intent locks enable hierarchical locking
- Deadlocks are detected and resolved by aborting a transaction
- Explicit locks (
FOR UPDATE,LOCK TABLE) provide application control - Advisory locks enable application-level coordination
- Optimistic locking uses version checks instead of explicit locks
- Latches protect internal structures
Understanding locking helps you design high-concurrency systems and debug performance issues.
What’s Next
In Chapter 10, we’ll explore query parsing and planning—how databases transform SQL text into executable operations.
“The best lock is the one you don’t need. The second best is the one you hold briefly.”