Locking Mechanisms

Locks are how databases prevent two transactions from stepping on each other’s toes. When Transaction A is modifying a row, locks ensure Transaction B either waits or works with a consistent version.

Think of it like a bathroom door lock. When someone’s inside, the lock prevents anyone else from barging in. Database locks work the same way — they control who can read or write data at the same time.

Shared Locks vs Exclusive Locks

Shared lock (read lock): Multiple transactions can hold a shared lock on the same row simultaneously. Used for reading. Think of it as “I’m reading this — feel free to read it too, but nobody change it.”

Exclusive lock (write lock): Only one transaction can hold an exclusive lock. No other transaction can read or write the locked row. Think of it as “I’m changing this — everyone else stay away.”

Row-Level vs Table-Level Locks

Row-level locks lock individual rows. This is the default for most operations and gives the best concurrency — other transactions can still access different rows in the same table.

Table-level locks lock the entire table. This is needed for operations like ALTER TABLE, TRUNCATE, or explicit LOCK TABLE commands.

-- Row-level lock: only row id=1 is locked
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Other transactions can still update rows 2, 3, 4, etc.
COMMIT;

-- Table-level lock (PostgreSQL)
BEGIN;
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;
-- Nobody can read or write this table until we commit
COMMIT;

SELECT … FOR UPDATE / FOR SHARE

When we want to read a row and then update it later in the same transaction, a plain SELECT doesn’t lock the row. Another transaction could modify it between our SELECT and our UPDATE.

-- Problem: no lock between read and write
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- reads $1000
-- Another transaction could change this right now!
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Solution: lock the row when we read it
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Row is now exclusively locked — nobody else can touch it
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

FOR SHARE is the shared-lock version — it prevents other transactions from updating or deleting the row, but allows them to also SELECT ... FOR SHARE:

-- Lock for reading (shared) — prevents writes, allows other reads
SELECT * FROM products WHERE id = 42 FOR SHARE;

-- Lock for writing (exclusive) — prevents everything
SELECT * FROM products WHERE id = 42 FOR UPDATE;

-- Don't wait if the row is locked — return an error immediately
SELECT * FROM products WHERE id = 42 FOR UPDATE NOWAIT;

-- Skip locked rows instead of waiting
SELECT * FROM products WHERE id = 42 FOR UPDATE SKIP LOCKED;

SKIP LOCKED is incredibly useful for job queues — multiple workers can each grab the next unlocked row without waiting.

Intent Locks

Intent locks are a signaling mechanism used by some databases (like MySQL InnoDB). Before acquiring a row-level lock, the transaction places an intent lock on the table.

This tells other transactions: “Hey, I’m about to lock some rows in this table.” It prevents conflicts between row-level and table-level operations without having to check every individual row.

• Intent Shared (IS): “I intend to read-lock some rows”
• Intent Exclusive (IX): “I intend to write-lock some rows”

We don’t set these manually — the database handles them automatically.

Advisory Locks

Advisory locks are application-level locks that the database manages for us. The database doesn’t enforce them on any particular table or row — they’re just named locks we can use for coordination.

-- PostgreSQL advisory locks

-- Acquire a lock (blocks if already held by another session)
SELECT pg_advisory_lock(12345);

-- Do our critical work
-- ... only one session can hold this lock at a time ...

-- Release the lock
SELECT pg_advisory_unlock(12345);

-- Try to acquire without blocking (returns true/false)
SELECT pg_try_advisory_lock(12345);

Use cases for advisory locks:

• Preventing duplicate cron jobs from running
• Ensuring only one instance processes a specific resource
• Application-level mutexes

Lock Escalation

Some databases (notably SQL Server) will escalate row-level locks to table-level locks if a transaction holds too many row locks. This is a performance optimization — tracking thousands of individual row locks is expensive.

PostgreSQL does NOT do lock escalation. Each row lock is maintained individually, no matter how many there are.

Performance Impact

Locks mean contention. Contention means waiting. Waiting means slower queries.

Tips to minimize lock contention:

• Keep transactions short — acquire locks late, release early
• Access rows in a consistent order across all transactions (prevents deadlocks)
• Use the least restrictive lock type that works
• Avoid locking rows we don’t need to modify
• Consider optimistic locking for low-contention scenarios

-- Bad: locks rows for a long time
BEGIN;
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;
-- ... slow application processing ...
UPDATE orders SET status = 'done' WHERE id IN (...);
COMMIT;

-- Better: process first, lock briefly
-- Application fetches and processes orders outside a transaction
-- Then:
BEGIN;
UPDATE orders SET status = 'done' WHERE id IN (1, 2, 3);
COMMIT;  -- lock held for milliseconds

In simple language, locks are the traffic lights of a database. They prevent collisions but can cause traffic jams if we’re not careful. Use FOR UPDATE when we need to read-then-write, keep transactions short, and always think about what other transactions might be waiting on us.