Transactions and Concurrency Control

Row-level locking

BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- Only row id=1 is locked; other transactions can freely update rows 2, 3, 4...
COMMIT;

This is the default granularity for InnoDB (MySQL), PostgreSQL, and SQL Server's row-locking mode — it maximizes concurrency because unrelated rows in the same table remain fully accessible to other transactions. The cost is more bookkeeping: the engine must track potentially many individual row locks per transaction.

Table-level locking

LOCK TABLE accounts IN EXCLUSIVE MODE;   -- blocks ALL other access to the whole table

Locks the entire table regardless of which specific rows are touched — simple to implement and low per-operation overhead, but drastically reduces concurrency, since even transactions touching completely unrelated rows must wait. Common uses: DDL operations (ALTER TABLE) that must see a globally consistent view of the schema, or explicit bulk maintenance operations where you deliberately want exclusive access.

Page-level locking

Locks a whole disk page (which typically holds multiple rows) rather than a single row or the whole table — a middle ground some engines (older SQL Server versions, some MyISAM configurations) used for lower locking overhead than pure row-level, at the cost of unrelated rows on the same page blocking each other (a form of false contention sometimes called "false sharing" at the storage layer).

Lock escalation

Some engines (notably SQL Server) automatically escalate from many row-level locks to a single table-level lock once a transaction holds "too many" row locks (a threshold, often ~5,000), to reduce lock-management memory overhead — this can unexpectedly turn a seemingly-fine-grained update into a full-table lock if it touches a large number of rows in one transaction, a subtle gotcha worth knowing when debugging unexpected blocking on large batch updates.

Default to trusting your engine's row-level locking for normal OLTP transactions — it's what's designed for high-concurrency workloads. Reach for explicit table-level locks only for genuinely table-wide maintenance operations, and be aware that a very large single-transaction batch update might unintentionally trigger lock escalation (SQL Server) or simply hold a very large number of row locks for a long time (PostgreSQL/MySQL), either of which can significantly increase blocking for other transactions — consider batching large updates into smaller committed chunks instead.

Compatibility matrix

Shared lock — for reading

-- Explicit shared lock (SQL Server style)
SELECT * FROM accounts WITH (HOLDLOCK) WHERE id = 1;

Multiple transactions can hold a shared lock on the same row/table simultaneously — any number of concurrent readers is fine, since none of them are modifying the data. A shared lock only conflicts with an exclusive lock request: if any transaction holds a shared lock, no one else can acquire an exclusive lock on that resource until all the shared locks are released.

Exclusive lock — for writing

UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- Acquires an exclusive lock on row id=1 for the duration of the transaction

Only one transaction can hold an exclusive lock on a given resource at a time, and while held, no other transaction can acquire any lock (shared or exclusive) on that same resource — it must wait.

Why MVCC changes the practical picture

In a pure lock-based engine, a long-running reader holding a shared lock can block a writer, and vice versa. Under MVCC (PostgreSQL, InnoDB, SQL Server's snapshot isolation), plain SELECTs generally don't take shared row locks at all — they read a consistent snapshot instead (see the MVCC question), so ordinary reads and writes don't block each other. Shared/exclusive locks in an MVCC engine mostly come into play for explicit locking reads (SELECT ... FOR SHARE / SELECT ... FOR UPDATE) and for the writes themselves, not for plain reads.

-- Explicit shared lock in PostgreSQL/MySQL: "I'm reading this, don't let anyone modify it until I'm done"
SELECT * FROM accounts WHERE id = 1 FOR SHARE;

-- Explicit exclusive lock: "I intend to modify this, block others from reading-for-update or writing it"
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

Use FOR UPDATE (exclusive) when you're about to modify a row based on its current value and must prevent another transaction from changing it in between your read and your write (classic "read, check, then write" race condition). Use FOR SHARE when you need to ensure a row doesn't change while you rely on its value, but you're not modifying it yourself and are fine with other readers also holding a shared lock concurrently.

A phantom read example

-- Transaction A                                -- Transaction B
BEGIN;
SELECT COUNT(*) FROM orders
  WHERE status = 'pending';  -- returns 5
                                                 BEGIN;
                                                 INSERT INTO orders (status, ...)
                                                   VALUES ('pending', ...);
                                                 COMMIT;
SELECT COUNT(*) FROM orders
  WHERE status = 'pending';  -- returns 6 !!
COMMIT;

Transaction A ran the identical query twice within the same transaction and got two different row counts, because Transaction B committed a new row matching the filter in between. The new row is the "phantom" — it wasn't part of the original result set, then suddenly was.

Why this differs from a non-repeatable read

A non-repeatable read is about a specific, already-fetched row changing value; a phantom read is about the set of rows matching a condition changing, specifically due to inserts or deletes (not updates to existing rows already in the result). This distinction is why some isolation levels can prevent one but not the other — locking a specific set of already-read rows (preventing non-repeatable reads) doesn't automatically prevent a new row from being inserted into that range.

Which levels prevent it

Per the ANSI SQL standard: only Serializable is required to fully prevent phantom reads — Repeatable Read is standard-permitted to still allow them, because traditional row-locking implementations lock the rows you've already read, not a "gap" covering rows that don't exist yet.

However, engine implementation varies: PostgreSQL implements Repeatable Read via snapshot isolation (MVCC), which takes a full snapshot at transaction start — since the "new" row in the example above wasn't part of that snapshot at all, PostgreSQL's Repeatable Read actually does prevent this specific phantom scenario, stricter than the standard's minimum requirement for that level. MySQL/InnoDB's Repeatable Read additionally uses gap locks and next-key locks specifically to prevent phantom inserts within a locked range for locking reads.

Don't assume "Repeatable Read" means the same guarantee across every database — verify your specific engine's actual behavior rather than relying on the SQL standard's minimum bar, since several major engines (PostgreSQL, MySQL/InnoDB) exceed it. If phantom reads matter to your business logic and you're not certain of your engine's exact behavior at Repeatable Read, Serializable is the only level the standard guarantees will prevent them everywhere.

The problem: atomicity across multiple systems

A normal BEGIN...COMMIT transaction is atomic within one database. But suppose you need to atomically update two separate databases (e.g., debit an account in DB-A and credit an account in DB-B, where A and B are physically separate database servers) — a plain commit on each individually can't guarantee both succeed or both fail together; one could commit while the other crashes or fails.

The two phases

Phase 1 — Prepare: The coordinator asks every participant to do everything needed to commit (validate constraints, write to its own durable log) and reply "yes, I can commit" or "no, I can't" — but without actually making the change visible/permanent yet.

Coordinator -> Participant A: PREPARE
Coordinator -> Participant B: PREPARE
Participant A -> Coordinator: READY (durably logged, could commit or abort from here)
Participant B -> Coordinator: READY

Phase 2 — Commit (or Abort): Only if every participant replied "yes" does the coordinator tell everyone to actually commit. If even one participant said "no" (or timed out), the coordinator tells everyone to abort instead.

Coordinator -> Participant A: COMMIT
Coordinator -> Participant B: COMMIT
Participant A -> Coordinator: COMMITTED
Participant B -> Coordinator: COMMITTED

Because every participant durably logged "I'm ready to commit" before replying yes in phase 1, even a crash between phase 1 and phase 2 is recoverable — on restart, a participant can check with the coordinator (or replay its own log) to find out whether the overall transaction ultimately committed or aborted, and finish accordingly.

When it's actually needed

• Genuine distributed transactions across separate database engines/instances — e.g., updating two different PostgreSQL clusters, or a database plus a JMS message queue, as a single atomic unit (XA transactions implement this pattern in many enterprise stacks).
• Rare in modern web-scale architectures, because 2PC has real costs: it's blocking (participants hold locks/resources while waiting through both phases), and the coordinator is a single point of failure/bottleneck during the protocol.

Why most modern systems avoid it

Distributed systems at scale generally prefer eventual consistency with compensating actions (the Saga pattern: a sequence of local transactions, each with a corresponding "undo" action if a later step fails) or idempotent, retryable operations with outbox patterns (write the "intent to do X" in the same local transaction as the primary change, then a background process reliably delivers it) rather than 2PC, because these avoid 2PC's blocking behavior and single coordinator bottleneck, at the cost of only eventual (not immediate) cross-system consistency.

2PC is worth knowing conceptually — it's the classical answer to "how do you get atomicity across two databases" — but a strong answer also mentions why modern distributed architectures usually avoid it in favor of sagas/outbox patterns, since that shows awareness of its real operational costs, not just the protocol's mechanics.

This is a very common system-design-flavored SQL interview question — it tests whether you understand that "check, then act" in application code is inherently racy against another concurrent request doing the same thing.

The bug, first

-- Application code, naive version:
-- Step 1: check availability
SELECT available FROM seats WHERE id = 42;   -- returns true

-- (time passes — two concurrent requests can both get 'true' here)

-- Step 2: book it
UPDATE seats SET available = false WHERE id = 42;

If two users' requests both execute Step 1 before either executes Step 2, both will see available = true and both will proceed to "book" the seat — a classic lost-update/double-booking race condition. The check and the write are two separate round-trips with a gap between them where another transaction can interleave.

Fix 1: pessimistic locking — make the check-then-act atomic via a lock

BEGIN;
SELECT available FROM seats WHERE id = 42 FOR UPDATE;  -- locks the row
-- Any other transaction's FOR UPDATE on row 42 now blocks here until we commit/rollback
IF available THEN
    UPDATE seats SET available = false WHERE id = 42;
    COMMIT;   -- lock released, second transaction now proceeds and sees available=false
ELSE
    ROLLBACK;
END IF;

The second transaction's SELECT ... FOR UPDATE blocks until the first commits, then correctly sees available = false and can reject the booking.

Fix 2: atomic conditional update — no explicit lock needed

Often simpler and doesn't require holding a transaction open across application logic:

UPDATE seats
SET available = false
WHERE id = 42 AND available = true;

-- Check rows affected (returned by most drivers/ORMs):
-- If 1 row affected: you got the seat.
-- If 0 rows affected: someone else already booked it (or it never existed) -- reject/retry.

This works because the check (available = true) and the write happen as a single atomic statement at the database level — the database itself guarantees no other transaction's update can interleave in the middle of one UPDATE statement's row evaluation, regardless of isolation level.

Which to prefer

The atomic conditional UPDATE (Fix 2) is usually the better default — it avoids holding open transactions/locks across application logic (which is risky if that logic is slow or fails unexpectedly), and it composes well with optimistic-concurrency patterns more broadly. Reach for explicit FOR UPDATE locking when the "check" involves more complex logic than a single column comparison that can't be expressed as a single atomic UPDATE ... WHERE clause.

The general principle

Never trust a "read, then act based on what I read" sequence to be safe under concurrency unless the read and the act are combined into a single atomic database operation, or the row is explicitly locked for the entire duration between them.