Understanding Database Locks: A Deep Dive into Concurrency Control
When multiple users or processes try to access the same data simultaneously, how does a database ensure they don’t step on each other’s toes? The answer lies in a sophisticated system of locks - the traffic signals that coordinate access to your data.
In this post, we’ll explore database locks from the ground up, building intuition through scenarios and examples. By the end, you’ll understand not just what locks are, but why they work the way they do.
Table of Contents
- Why Do We Need Locks?
- The Two Fundamental Lock Types
- The Lock Compatibility Matrix
- When Are Locks Acquired?
- Lock Duration: When Are Locks Released?
- Lock Granularity: What Gets Locked?
- Isolation Levels and Locking Behavior
- The Lost Update Problem
- Manual Locking: Taking Control
- Lock Escalation: When Locks Get Expensive
- What Happens When Locks Conflict?
- Advisory Locks: Beyond Data Rows
- Summary: Building Your Mental Model
Why Do We Need Locks?
Let’s start with first principles. Imagine two bank tellers working on the same account simultaneously:
Scenario: Concurrent Withdrawals
Account balance: $1000
Teller A: Customer wants to withdraw $600
1. Read balance: $1000 ✓
2. Check: $1000 >= $600 ✓
3. Calculate new balance: $1000 - $600 = $400
4. Write new balance: $400
Teller B: Customer wants to withdraw $600
1. Read balance: $1000 ✓
2. Check: $1000 >= $600 ✓
3. Calculate new balance: $1000 - $600 = $400
4. Write new balance: $400
Final balance: $400Wait, what just happened? The account had $1000, both withdrawals were approved, but the final balance is $400 instead of -$200 (or better yet, one withdrawal should have been rejected!). The bank just lost track of $600.
This is the fundamental problem that locks solve: coordinating access to shared data.
The Initial Intuition (And Why It’s Incomplete)
My first instinct was simple: “Locks sync processes - the process accessing data puts a lock on it so no other processes can change it until the current process finishes.”
This is directionally correct, but here’s the key question: Should ALL access require exclusive locks?
Let’s test this with different scenarios.
Testing Our Intuition: Three Critical Scenarios
Scenario 1: Two Readers
Transaction A: SELECT * FROM accounts WHERE id = 1;
Transaction B: SELECT * FROM accounts WHERE id = 1;Question: Should Transaction A block Transaction B from reading?
Analysis: Both are just reading. Neither is changing anything. There’s no danger of conflict here - multiple readers can safely see the same data at the same time.
Conclusion: Reads should NOT block other reads.
Scenario 2: One Reader, One Writer
Transaction A: SELECT * FROM accounts WHERE id = 1; -- Just reading
Transaction B: UPDATE accounts SET balance = 500 WHERE id = 1; -- WritingQuestion: Should these block each other?
Analysis: If B changes the value while A is reading, A might see inconsistent data (like reading half of the old value and half of the new value). Conversely, if A is reading while B is writing, B’s write might be based on stale information.
Conclusion: Reads and writes should block each other.
Scenario 3: Two Writers
Transaction A: UPDATE accounts SET balance = balance - 100 WHERE id = 1;
Transaction B: UPDATE accounts SET balance = balance + 50 WHERE id = 1;Question: What if both proceed simultaneously?
Analysis: This is the bank scenario from earlier. If both read the same initial value and write independently, one update will overwrite the other. Money disappears.
Conclusion: Writes MUST block other writes.
The Pattern Emerges
From these scenarios, we can derive a fundamental principle:
Reads can coexist peacefully, but writes need exclusivity.
This insight leads us to the two fundamental lock types.
The Two Fundamental Lock Types
Shared Lock (S Lock) - “I’m Reading, Others Can Read Too”
Purpose: Protects a read operation from being disrupted by writes, while allowing other reads to proceed.
Behavior:
- Multiple transactions can hold shared locks on the same data simultaneously
- Blocks exclusive locks (writes must wait)
- Compatible with other shared locks
Example:
-- Transaction A
BEGIN;
SELECT * FROM accounts WHERE id = 1;
-- Acquires S lock on row 1
-- Reads balance: $1000
-- Transaction B (can proceed immediately)
BEGIN;
SELECT * FROM accounts WHERE id = 1;
-- Also acquires S lock on row 1
-- Reads balance: $1000
-- Both transactions reading simultaneously ✓Exclusive Lock (X Lock) - “I’m Writing, Everyone Else Wait”
Purpose: Protects a write operation from ALL other access - both reads and writes.
Behavior:
- Only ONE transaction can hold an exclusive lock on data
- Blocks both shared locks AND other exclusive locks
- No other transaction can read or write while X lock is held
Example:
-- Transaction A
BEGIN;
UPDATE accounts SET balance = 500 WHERE id = 1;
-- Acquires X lock on row 1
-- All other access to row 1 must wait
-- Transaction B (blocked)
BEGIN;
SELECT * FROM accounts WHERE id = 1;
-- Tries to acquire S lock
-- WAITS for Transaction A to release X lockThe Lock Compatibility Matrix
Everything about lock behavior can be summarized in this simple table:
| Transaction B wants Shared Lock | Transaction B wants Exclusive Lock | |
|---|---|---|
| A holds Shared Lock | ✓ ALLOWED (Multiple readers) | ✗ BLOCKED (Write waits for read) |
| A holds Exclusive Lock | ✗ BLOCKED (Read waits for write) | ✗ BLOCKED (Write waits for write) |
In plain English:
- ✓ Shared + Shared = Compatible - Multiple transactions can read simultaneously
- ✗ Shared + Exclusive = Incompatible - Writes must wait for reads to finish, and vice versa
- ✗ Exclusive + Exclusive = Incompatible - Only one writer at a time
This matrix is the foundation of everything that follows. Keep it in mind as we explore more complex scenarios.
When Are Locks Acquired?
Now that we understand lock types, the next question is: When does the database actually acquire these locks?
The answer depends on two factors:
- Your isolation level (automatic locking)
- Your explicit locking commands (manual locking)
Let’s start with automatic locking.
Automatic Locking: Isolation Level Controlled
Your isolation level determines the database’s locking strategy. Let’s examine each level.
READ COMMITTED: Minimal Locking
-- Transaction A at READ COMMITTED
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM accounts WHERE id = 1;
-- 1. Acquires S lock on row 1
-- 2. Reads the data
-- 3. IMMEDIATELY releases S lock ⚡
-- Lock is not held!
UPDATE accounts SET balance = 500 WHERE id = 1;
-- 1. Acquires X lock on row 1
-- 2. Performs the update
-- 3. Holds X lock until COMMIT or ROLLBACKKey Insight: Shared locks are released immediately after the read completes. The lock doesn’t persist for the duration of the transaction.
Implication: Between two SELECT statements in the same transaction, another transaction can modify the data. This is why READ COMMITTED allows “non-repeatable reads.”
REPEATABLE READ: Hold Those Read Locks
-- Transaction A at REPEATABLE READ
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id = 1;
-- 1. Acquires S lock on row 1
-- 2. Reads the data
-- 3. HOLDS S lock until transaction ends 🔒
-- Lock persists!
-- Later in the same transaction...
SELECT * FROM accounts WHERE id = 1;
-- Still holds the S lock from before
-- Guaranteed to read the same value
UPDATE accounts SET balance = 500 WHERE id = 1;
-- 1. Acquires X lock on row 1
-- 2. Performs the update
-- 3. Holds X lock until COMMIT or ROLLBACKKey Insight: Shared locks are held for the entire transaction duration.
Implication: Once you read a row, no other transaction can modify it until you commit or rollback. This guarantees “repeatable reads” - reading the same row twice yields the same result.
SERIALIZABLE: Maximum Protection
-- Transaction A at SERIALIZABLE
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE balance > 1000;
-- 1. Acquires S locks on matching rows
-- 2. Acquires RANGE locks on index gaps 🔐
-- 3. Holds all locks until transaction ends
-- Prevents other transactions from:
-- - Modifying matched rows
-- - Inserting new rows that would match the queryKey Insight: SERIALIZABLE goes beyond row locks to lock ranges and gaps in indexes.
Implication: Prevents “phantom reads” - new rows appearing in subsequent queries. This is the most restrictive (and slowest) isolation level.
Lock Duration: When Are Locks Released?
Understanding when locks are released is crucial for predicting transaction behavior.
Shared Lock Duration
| Isolation Level | S Lock Duration |
|---|---|
| READ COMMITTED | Released immediately after read |
| REPEATABLE READ | Held until transaction ends (COMMIT/ROLLBACK) |
| SERIALIZABLE | Held until transaction ends (COMMIT/ROLLBACK) |
| SELECT FOR SHARE | Held until transaction ends (COMMIT/ROLLBACK) |
Exclusive Lock Duration
| Isolation Level | X Lock Duration |
|---|---|
| ALL LEVELS | Always held until transaction ends |
Critical Rule: Exclusive locks are NEVER released early, regardless of isolation level. This is fundamental to preventing write conflicts.
Lock Granularity: What Gets Locked?
Locks can be applied at different “levels” of granularity. The choice affects both concurrency and overhead.
Row-Level Locks (Most Common)
UPDATE accounts SET balance = 500 WHERE id = 1;
-- Locks only the specific row with id = 1
-- Other rows in the accounts table remain accessiblePros:
- Maximum concurrency - other transactions can access different rows
- Fine-grained control
Cons:
- More overhead - database must track many individual locks
- Can be expensive with thousands of locks
Page-Level Locks (Less Common)
A “page” is a fixed-size block of storage (typically 8KB) that can contain multiple rows.
UPDATE accounts SET balance = 500 WHERE id = 1;
-- In a page-locking system, locks the entire page containing row 1
-- Might lock rows 1-50 if they all fit in one pagePros:
- Less overhead than row-level
- Middle ground between row and table locks
Cons:
- Can lock more data than necessary
- Reduced concurrency
Table-Level Locks
LOCK TABLE accounts IN EXCLUSIVE MODE;
-- Locks the ENTIRE accounts table
-- No other transaction can read or write ANY rowPros:
- Minimal overhead - just one lock for the whole table
- Useful for bulk operations
Cons:
- Terrible concurrency - blocks everything
- Should be used sparingly
Lock Escalation: When Locks Get Expensive
Databases are smart about managing lock overhead. Here’s what can happen:
-- Transaction starts updating many rows
UPDATE accounts SET status = 'active' WHERE region = 'west';
-- Suppose this matches 10,000 rows
-- Database behavior:
-- 1. Starts acquiring row-level locks: Lock row 1, row 2, row 3...
-- 2. After ~5,000 locks: "This is getting expensive to track"
-- 3. Escalates to table-level lock automatically 🔄
-- 4. Now blocks the ENTIRE table instead of individual rowsWhy This Matters:
Lock escalation can cause unexpected blocking. A transaction you thought was only touching a few rows might suddenly block the entire table.
Configuration (PostgreSQL):
PostgreSQL generally does NOT escalate locks automatically. It will track millions of row locks if necessary. However, other databases (like SQL Server) do escalate, so be aware of this behavior if you’re working across different database systems.
Isolation Levels and Locking Behavior
Let’s tie everything together by seeing how isolation levels map to locking strategies:
| Isolation Level | Read Locks | Write Locks | Prevents |
|---|---|---|---|
| READ UNCOMMITTED | None | X locks held | Nothing (dirty reads allowed) |
| READ COMMITTED | S locks (released immediately) | X locks held | Dirty reads |
| REPEATABLE READ | S locks held | X locks held | Dirty reads, non-repeatable reads |
| SERIALIZABLE | S locks + range locks held | X locks held | Dirty reads, non-repeatable reads, phantoms |
The Pattern:
- Higher isolation = Locks held longer and more broadly = More safety but less concurrency
- Lower isolation = Locks released quickly = Less safety but more concurrency
The Lost Update Problem
Now let’s explore a tricky scenario that reveals the limits of automatic locking.
The Classic Lost Update
-- Both transactions at READ COMMITTED
-- Initial balance: $1000
-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Reads: $1000
-- S lock acquired and IMMEDIATELY released
-- Transaction B
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Reads: $1000 (A's lock is already gone)
-- S lock acquired and IMMEDIATELY released
-- Transaction A
UPDATE accounts SET balance = 900 WHERE id = 1; -- Withdraw $100
-- X lock acquired
COMMIT;
-- Balance is now $900
-- Transaction B
UPDATE accounts SET balance = 1050 WHERE id = 1; -- Deposit $50
-- Calculated from the $1000 it read earlier!
-- X lock acquired
COMMIT;
-- Balance is now $1050Result: Transaction A’s withdrawal of $100 just disappeared! The balance should be $950 ($1000 - $100 + $50), but it’s $1050.
This is called a lost update.
Does REPEATABLE READ Prevent Lost Updates?
You might think: “In REPEATABLE READ, we hold the S lock, so this won’t happen, right?”
Let’s trace through it:
-- Both transactions at REPEATABLE READ
-- Initial balance: $1000
-- Transaction A
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- Reads: $1000
-- Acquires S lock and HOLDS it
-- Transaction B
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- Reads: $1000
-- Acquires S lock and HOLDS it
-- ✓ Both S locks are compatible!
-- Transaction A
UPDATE accounts SET balance = 900 WHERE id = 1;
-- Tries to acquire X lock
-- Sees that Transaction B holds S lock
-- ⏱️ WAITS for B to release S lock
-- Transaction B
UPDATE accounts SET balance = 1050 WHERE id = 1;
-- Tries to acquire X lock
-- Sees that Transaction A holds S lock
-- ⏱️ WAITS for A to release S lockBoth transactions are waiting for each other!
This is a deadlock - we’ll explore this in detail in a future post. For now, the key insight is:
REPEATABLE READ holds S locks longer, but when both transactions try to upgrade from S lock to X lock simultaneously, they deadlock.
The database detects this cycle and kills one transaction:
ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678
Process 5678 waits for ShareLock on transaction 1234
HINT: See server log for query details.Manual Locking: Taking Control
Automatic locking (via isolation levels) doesn’t always give you the control you need. Sometimes you need to explicitly tell the database: “I’m going to read this now, but I’ll write it later - give me the right lock upfront.”
SELECT FOR UPDATE: “I’ll Write This Later”
-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Acquires X lock IMMEDIATELY (not S lock!)
-- Reads: $1000
-- X lock is held until commit
-- Transaction B
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Tries to acquire X lock
-- ⏱️ WAITS because A holds X lock (X + X = incompatible)When A commits:
-- Transaction A
UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;
-- X lock released
-- Transaction B (now unblocked)
-- Reads: $900 (the updated value!)
UPDATE accounts SET balance = 950 WHERE id = 1;
COMMIT;Result: No lost update! Transaction B waited and saw the correct value.
SELECT FOR SHARE: “Others Can Read, But Nobody Can Write”
-- Transaction A
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- Acquires S lock explicitly
-- Holds until transaction ends
-- Transaction B (another reader)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- Also acquires S lock
-- ✓ Proceeds immediately (S + S = compatible)
-- Transaction C (a writer)
BEGIN;
UPDATE accounts SET balance = 500 WHERE id = 1;
-- Tries to acquire X lock
-- ⏱️ WAITS (S + X = incompatible)Use Case: You want to read data and ensure nobody changes it while you’re performing related operations, but you’re okay with other readers accessing it too.
Comparison: Regular SELECT vs FOR UPDATE vs FOR SHARE
| Lock Type | Initial Lock | Duration | Blocks Reads? | Blocks Writes? |
|---|---|---|---|---|
| SELECT (READ COMMITTED) | S (released immediately) | Instant | No | Only during the brief read |
| SELECT (REPEATABLE READ) | S | Until commit | No | Yes |
| SELECT FOR UPDATE | X | Until commit | Yes | Yes |
| SELECT FOR SHARE | S | Until commit | No | Yes |
What Happens When Locks Conflict?
When a transaction tries to acquire a lock that conflicts with an existing lock, it enters a wait state.
-- Transaction A
BEGIN;
UPDATE accounts SET balance = 500 WHERE id = 1;
-- Holds X lock on row 1
-- Transaction B
BEGIN;
SELECT * FROM accounts WHERE id = 1;
-- Needs S lock on row 1
-- ⏱️ WAITS because X + S = incompatibleThe wait can end in three ways:
1. Lock Holder Commits or Rolls Back
-- Transaction A
COMMIT;
-- X lock released
-- Transaction B
-- Immediately proceeds and reads row 1 ✓2. Lock Timeout
-- Set a timeout (PostgreSQL)
SET lock_timeout = '5s';
-- Transaction B has been waiting...
-- After 5 seconds:
ERROR: canceling statement due to lock timeoutNote: PostgreSQL’s default is NO timeout (waits indefinitely). You must configure timeouts explicitly if you want them.
3. Deadlock Detection
When transactions wait for each other in a cycle, the database’s deadlock detector kicks in:
-- Transaction A locks row 1, waits for row 2
-- Transaction B locks row 2, waits for row 1
-- Database detects the cycle and kills one:
ERROR: deadlock detectedThe database chooses a “victim” (usually the transaction that has done less work) and aborts it, allowing the other to proceed.
Advisory Locks: Beyond Data Rows
All the locks we’ve discussed so far are automatic - the database acquires them based on your queries.
Advisory locks are different - they’re manual locks that you explicitly request, and they’re not tied to specific rows or tables.
Basic Usage
-- Transaction A
SELECT pg_advisory_lock(1234);
-- Acquires lock with ID 1234
-- No connection to any table or row
-- Just a number you choose
-- Do some application logic here...
SELECT pg_advisory_unlock(1234);
-- Release the lockReal-World Use Cases
1. Preventing Duplicate Job Processing
-- Job processor
BEGIN;
SELECT pg_try_advisory_lock(hash('send_daily_email'));
IF lock_acquired THEN
-- This instance got the lock
-- Process the job
-- Other instances will skip this job
ELSE
-- Another instance is already processing
-- Skip this job
END IF;
COMMIT;2. Application-Level Coordination
-- Ensure only one instance updates a cache at a time
SELECT pg_advisory_lock(hash('rebuild_product_cache'));
-- Rebuild cache...
-- (might take 30 seconds)
SELECT pg_advisory_unlock(hash('rebuild_product_cache'));3. Rate Limiting
-- Allow only one request per user per second
user_id = 12345
lock_id = hash(user_id, current_second)
IF pg_try_advisory_lock(lock_id) THEN
-- Process request
ELSE
-- Rate limit exceeded
RETURN "Too many requests"
END IFAdvisory Lock Types
| Function | Behavior | Use Case |
|---|---|---|
pg_advisory_lock(id) | Waits if lock is held | Guaranteed exclusive access |
pg_try_advisory_lock(id) | Returns false if lock is held | Non-blocking, check-and-skip pattern |
pg_advisory_unlock(id) | Releases lock | Manual cleanup |
pg_advisory_lock_shared(id) | Shared advisory lock | Multiple readers, single writer |
Summary: Building Your Mental Model
Let’s consolidate everything into a coherent mental model.
Think of Locks as a Traffic Control System
- Shared locks (S) = Multiple cars can use the same lane (all going the same direction, all reading)
- Exclusive locks (X) = Only one car on the entire road (construction zone, writing)
- Lock duration = How long the road stays closed
- Lock granularity = Closing one lane vs. closing the entire highway
- Isolation levels = The traffic rules (how aggressive or cautious)
- Manual locks = You explicitly putting up a roadblock
Core Principles
-
Reads can share, writes need exclusivity
- Multiple S locks are compatible
- X locks are incompatible with everything
-
Isolation level determines automatic locking behavior
- READ COMMITTED: Release S locks immediately
- REPEATABLE READ: Hold S locks until commit
- SERIALIZABLE: Hold S locks + range locks until commit
- X locks ALWAYS held until commit
-
Lock granularity affects concurrency
- Row-level: Maximum concurrency, more overhead
- Table-level: Minimum concurrency, less overhead
- Page-level: Middle ground
-
Conflicts cause waits
- Transaction waits for incompatible lock to be released
- Can timeout or deadlock
-
Manual locking gives you control
- SELECT FOR UPDATE: Read with write intent
- SELECT FOR SHARE: Read with no-write guarantee
- Advisory locks: Application-level coordination
The Fundamental Tradeoff
Every locking decision involves balancing:
- More locks / longer duration = More safety, less concurrency, slower performance
- Fewer locks / shorter duration = Less safety, more concurrency, faster performance
Your choice of isolation level, lock granularity, and manual locking strategies all navigate this tradeoff.
What’s Next?
We’ve built a solid foundation for understanding locks. But there’s one critical scenario we’ve only touched on: deadlocks.
When transactions hold locks and wait for each other in a cycle, the system grinds to a halt. In the next post, we’ll explore:
- How deadlocks form (beyond the simple two-transaction case)
- Different types of deadlocks (index deadlocks, foreign key deadlocks, etc.)
- How databases detect deadlocks
- Strategies to prevent deadlocks
- How to handle deadlocks in your application code
Key Takeaways
If you remember nothing else, remember this:
✓ Locks coordinate access - They prevent conflicts when multiple transactions access the same data
✓ Two fundamental types - Shared (read) and Exclusive (write)
✓ Compatibility matrix - S+S works, everything else blocks
✓ Isolation levels control automatic locking - Higher isolation = more locks held longer
✓ X locks always held until commit - Writes never release locks early
✓ Manual locking gives control - Use SELECT FOR UPDATE when you know you’ll write later
✓ Granularity matters - Row vs. table locks affect concurrency dramatically
✓ Conflicts cause waits - Timeouts and deadlocks are how waits end badly
Understanding locks is essential for building high-performance, correct database applications. With this foundation, you’re ready to tackle deadlocks, optimize query performance, and debug concurrency issues with confidence.
Happy locking! 🔒