Concurrency control is the set of techniques a database uses so that many transactions can run at the same time without producing results that no serial ordering could produce. Interviewers probe it because it is where "works on my machine" turns into "double-spent balance in production": the code is correct for one request and wrong for two. For a Backend or AI engineer this shows up in wallet and inventory updates, job queues where two workers grab the same task, and idempotent ingestion where two retries race. The skill being tested is naming the specific anomaly, knowing which mechanism prevents it, and stating what that mechanism costs.
Account 1 has balance = 100. Session A withdraws 80. Session B withdraws 50.
Naive application code: SELECT balance; check in app; UPDATE balance = (new value).
time Session A Session B balance in DB
---- -------------------------------- ----------------------------- -------------
t1 SELECT balance -> 100 100
t2 SELECT balance -> 100 100
t3 100 >= 80 ok, compute 20 100
t4 100 >= 50 ok, compute 50 100
t5 UPDATE SET balance = 20; COMMIT 20
t6 UPDATE SET balance = 50; COMMIT 50 <- A's debit is gone
Serial orders would give: A then B -> 20, B rejected (insufficient funds)
B then A -> 50, A rejected (insufficient funds)
Observed: 50, with BOTH withdrawals reported as successful.This is a race condition: the outcome depends on how two operations interleave, and the interleaving is not under your control. The bug is a check-then-act sequence split across statements. The check ("is there enough money?") was evaluated against a value that stopped being true before the action ("write the new balance") happened. Wrapping the two statements in BEGIN … COMMIT does not by itself fix it under the default isolation level of most engines, because both transactions can legally read 100 before either writes. The fix is to make the check and the write one indivisible step in the database, or to make the second writer detect that the row changed underneath it. Both techniques appear below.
LOST UPDATE (two read-modify-writes overwrite each other)
A: read x=100 B: read x=100
A: write x=100-80 B: write x=100-50 -> A's write is silently overwritten
DIRTY READ (reading another txn's UNCOMMITTED write)
A: UPDATE x = 20 (not committed)
B: SELECT x -> 20 <- B saw data that may never exist
A: ROLLBACK B acted on a value that was never committed
NON-REPEATABLE READ (same row, read twice, different values)
A: SELECT x -> 100
B: UPDATE x = 20; COMMIT
A: SELECT x -> 20 <- same query, same txn, different answer
PHANTOM READ (same predicate, different SET of rows)
A: SELECT count(*) WHERE dept='ops' -> 3
B: INSERT (dept='ops'); COMMIT
A: SELECT count(*) WHERE dept='ops' -> 4 <- a new row "appeared"
WRITE SKEW (each txn reads overlapping rows, writes DIFFERENT rows)
Rule: at least one doctor must stay on call. Alice and Bob both on call.
A: SELECT count(*) WHERE on_call -> 2 B: SELECT count(*) WHERE on_call -> 2
A: UPDATE alice SET on_call=false B: UPDATE bob SET on_call=false
A: COMMIT B: COMMIT
-> zero doctors on call. No row was written by both, so no write-write conflict fired.Each anomaly is a different way an interleaving breaks the illusion that a transaction runs alone. Dirty read violates isolation about uncommitted data; non-repeatable read and phantom read are about committed changes appearing mid-transaction, the former for an existing row and the latter for the set of rows matching a predicate (row locks cannot protect rows that do not exist yet, which is why phantoms need range or predicate protection). Lost update is a write-write problem; write skew is subtler because the two transactions write different rows, so first-writer-wins rules based on row conflicts never trigger. Which of these can happen depends on the isolation level and the engine, covered on the isolation levels page; here the point is to recognise each shape.
Requested lock vs already held: S (shared) X (exclusive)
-------------------------------- ----------- -------------
S (shared) "I am reading" compatible conflicts
X (exclusive) "I am writing" conflicts conflicts
(Textbook two-mode model. Real engines add more modes, e.g. PostgreSQL has several
row-level and table-level modes; InnoDB adds intention locks and gap/next-key locks.
Whether plain SELECT takes a shared lock at all depends on engine and isolation level:
PostgreSQL and InnoDB plain SELECTs generally use MVCC snapshots and do not block on
row writers; SQL Server at its default Read Committed level uses shared locks unless
READ_COMMITTED_SNAPSHOT is enabled.)
Granularity:
row lock -> only the touched rows are protected; high concurrency, more lock bookkeeping
table lock -> whole table; cheap to track, blocks everyone (DDL, LOCK TABLE, bulk ops)
engines may escalate many row locks to a coarser lock (e.g. SQL Server)A shared lock says "I am reading this; do not change it", and many holders can coexist. An exclusive lock says "I am changing this; nobody else may read-with-lock or write it", and only one holder is allowed. The compatibility table is the whole idea: readers can share, a writer must be alone. Locking is how one transaction is made to wait for another, which is also exactly how deadlocks become possible (see the deadlocks page). Finer granularity buys concurrency at the cost of more lock state to manage, and coarser granularity is cheap but serialises unrelated work, which is why a long ALTER TABLE or LOCK TABLE in production can stall a whole service.
-- (a) Atomic conditional UPDATE: check and write in ONE statement
UPDATE accounts SET balance = balance - 80 WHERE id = 1 AND balance >= 80;
-- rows affected = 1 -> success; rows affected = 0 -> insufficient funds (or no such row)
-- (b) Pessimistic: lock the row first, then read-check-write -- PostgreSQL / MySQL
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- takes a row-level exclusive lock
-- other sessions doing FOR UPDATE / UPDATE on this row now WAIT here
-- app checks balance, then:
UPDATE accounts SET balance = balance - 80 WHERE id = 1;
COMMIT; -- lock released
-- (c) Optimistic: no lock while thinking; detect conflict at write time
SELECT balance, version FROM accounts WHERE id = 1; -- e.g. (100, 0)
UPDATE accounts SET balance = 20, version = version + 1
WHERE id = 1 AND version = 0; -- 0 rows updated -> someone else got there first
-- on 0 rows: re-read, re-validate, retry (or return a conflict error)These are three answers to the same problem. The atomic conditional update is usually the best choice for simple counters and balances because it needs no lock in application code, no extra round trip, and the engine takes the row lock for just the statement. SELECT … FOR UPDATE is pessimistic: assume conflict is likely, take the lock up front and make competitors wait; it is the right tool when the decision needs several reads or business logic that cannot be one SQL statement, but it holds the lock across the logic and every extra millisecond is queueing time for others. Optimistic concurrency assumes conflict is rare, holds no lock while the user or service thinks, and checks a version column (or timestamp or hash) at write time; it shines for long "read, edit form, save" flows, and it degrades when contention is high because retries pile up and wasted work grows.
UPDATE … SET balance = balance - 80 WHERE id = 1 AND balance >= 80 merges check and write; check the affected-row count to know whether it succeeded.SELECT … FOR UPDATE is pessimistic locking: it makes other writers wait and holds the lock until commit or rollback, so keep the transaction short.WHERE of the update; zero rows updated means a conflict, and the caller must retry or report it. It suits low-contention workloads.FOR UPDATE.# Python + sqlite3, deterministic interleaving (two "sessions" are just two steps in order).
# Output is real output from running this script.
import sqlite3, os, tempfile
path = os.path.join(tempfile.mkdtemp(), "shop.db")
def conn(): return sqlite3.connect(path, isolation_level=None)
db = conn()
db.execute("CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0), version INTEGER NOT NULL DEFAULT 0)")
db.execute("INSERT INTO accounts (id, balance) VALUES (1, 100)")
def bal(): return db.execute("SELECT balance, version FROM accounts WHERE id=1").fetchone()
# 1) Lost update: both read 100 before either writes
a_read = db.execute("SELECT balance FROM accounts WHERE id=1").fetchone()[0] # A reads 100
b_read = db.execute("SELECT balance FROM accounts WHERE id=1").fetchone()[0] # B reads 100
db.execute("UPDATE accounts SET balance=? WHERE id=1", (a_read - 80,)) # A writes 20
db.execute("UPDATE accounts SET balance=? WHERE id=1", (b_read - 50,)) # B writes 50, overwriting A
print("naive read-then-write, final balance:", bal()[0])
# naive read-then-write, final balance: 50 (A's withdrawal of 80 was lost)
# 2) Atomic conditional UPDATE
db.execute("UPDATE accounts SET balance=100, version=0 WHERE id=1")
def withdraw(amount):
cur = db.execute("UPDATE accounts SET balance = balance - ? WHERE id=1 AND balance >= ?", (amount, amount))
return "ok" if cur.rowcount == 1 else "rejected (insufficient funds)"
print("withdraw 80:", withdraw(80), "->", bal()[0]) # withdraw 80: ok -> 20
print("withdraw 50:", withdraw(50), "->", bal()[0]) # withdraw 50: rejected (insufficient funds) -> 20
# 3) Optimistic locking with a version column
db.execute("UPDATE accounts SET balance=100, version=0 WHERE id=1")
def optimistic_withdraw(amount, seen_balance, seen_version):
cur = db.execute(
"UPDATE accounts SET balance=?, version=version+1 WHERE id=1 AND version=?",
(seen_balance - amount, seen_version))
return cur.rowcount == 1
a_bal, a_ver = bal(); b_bal, b_ver = bal()
print("A saw", (a_bal, a_ver), "B saw", (b_bal, b_ver)) # A saw (100, 0) B saw (100, 0)
print("A writes:", optimistic_withdraw(80, a_bal, a_ver), "->", bal()) # A writes: True -> (20, 1)
print("B writes:", optimistic_withdraw(50, b_bal, b_ver), "-> stale, 0 rows updated, state:", bal())
# B writes: False -> stale, 0 rows updated, state: (20, 1)
b_bal, b_ver = bal() # B retries: re-read and re-validate
if b_bal >= 50:
print("B retry:", optimistic_withdraw(50, b_bal, b_ver), bal())
else:
print("B retry: insufficient funds, balance", b_bal) # B retry: insufficient funds, balance 20
-- PostgreSQL / MySQL (InnoDB): pessimistic locking timeline (not run here; expected behaviour in words)
-- Session A -- Session B
BEGIN; BEGIN;
SELECT balance FROM accounts
WHERE id = 1 FOR UPDATE; -- 100, lock held
SELECT balance FROM accounts
WHERE id = 1 FOR UPDATE; -- BLOCKS (waits for A)
UPDATE accounts SET balance = balance - 80
WHERE id = 1;
COMMIT; -- lock released
-- B unblocks; in PostgreSQL at READ COMMITTED it re-reads
-- the latest committed row (balance 20). B's app check
-- (20 >= 50) now fails, so B ROLLBACKs. No lost update.
Q: What is a race condition?
A: A situation where the result depends on the relative timing of concurrent operations. In a database it is typically a check-then-act sequence, for example reading a balance, checking it in application code, then writing, where another transaction can change the row between the read and the write. Prevention means collapsing check and act into one atomic statement, taking a lock before the read, or detecting the conflict at write time; each has a cost in blocking or retries.
Q: What is a lost update?
A: Two transactions read the same value, both compute a new value from it, and the later write overwrites the earlier one so one update vanishes. With balance 100, withdrawals of 80 and 50 both read 100 and both succeed, and the row ends at 50 or 20 depending on who writes last. Fixes are an atomic SET balance = balance - x WHERE … AND balance >= x, SELECT … FOR UPDATE, a version-checked update, or an isolation level that detects the write-write conflict (PostgreSQL Repeatable Read raises a serialization failure that the application must retry).
Q: What is a dirty read?
A: Reading data written by another transaction that has not committed, which may later be rolled back so the reader acted on a value that never officially existed. It is only permitted at Read Uncommitted in the SQL standard, and PostgreSQL never allows it (its Read Uncommitted behaves as Read Committed). The cost of preventing it is tracking which versions or locks are committed, and it is essentially always worth paying.
Q: What is a non-repeatable read?
A: Reading the same row twice in one transaction and getting different committed values because another transaction updated and committed in between. It breaks logic that reads a value, does work, then re-reads assuming it did not change. It is prevented by holding read locks until commit or by reading from a transaction-wide snapshot (Repeatable Read and above), at the cost of more locking or of retaining old row versions longer.
Q: What is a phantom read?
A: Running the same predicate query twice in one transaction and seeing a different set of rows because another transaction inserted or deleted a matching row and committed. Row locks do not help because the new row did not exist to be locked, so engines use range or predicate protection (InnoDB gap and next-key locks) or snapshots (PostgreSQL Repeatable Read does not show phantoms). Serializable is the standard's level that rules them out generally.
Q: Shared vs exclusive lock?
A: A shared lock allows many concurrent readers and blocks writers; an exclusive lock allows one writer and blocks both other writers and locking readers. In the textbook compatibility table only S-S is compatible. Real engines have more modes and MVCC engines let ordinary reads bypass row locks altogether, so the two-mode model is a starting point and the details should be attributed to the engine.
Q: How does locking work?
A: A lock manager tracks which transaction holds which lock on which resource and in what mode. A request that conflicts with a held lock waits in a queue; locks are typically held until commit or rollback (strict two-phase locking) so no one sees or overwrites uncommitted changes. The trade-offs are reduced concurrency, the possibility of deadlock, and lock-tracking overhead, which is why engines add row-level granularity, MVCC for readers, lock timeouts and deadlock detection.
Follow-up: Why not just make everything SELECT … FOR UPDATE?
A: Because it serialises all access to those rows: every transaction touching them queues behind the lock holder, throughput drops, and lock-wait chains increase deadlock risk. It also holds the lock across whatever application logic sits between the select and the commit. Use it where contention on the row is real and the logic cannot be expressed as one atomic statement; prefer the atomic conditional update for simple cases.
Follow-up: When does optimistic locking beat pessimistic, and when does it lose?
A: It wins when conflicts are rare and the read-to-write window is long or crosses user think-time or HTTP requests, since no lock is held and no connection is pinned. It loses under hot-row contention: most attempts fail, each retry redoes work, and the system spends its effort on aborted attempts, so pessimistic locking or a queue that serialises the hot key is better. It also needs every writer to honour the version column.
Follow-up: Does MVCC solve lost updates and write skew?
A: Not by itself. MVCC prevents readers and writers from blocking each other and gives consistent snapshots, but two transactions can still read the same snapshot and write conflicting results. Depending on the engine and level, lost update is caught (PostgreSQL Repeatable Read aborts the second writer; InnoDB's plain snapshot read followed by an update can still overwrite, so use a locking read or atomic update) and write skew requires Serializable or explicit locking of the rows the decision depends on.
In a system design discussion, concurrency control is what makes shared counters, wallets, seat or stock reservations and job claims safe once you scale to several application servers hitting one database, because application-level mutexes stop working across processes and the database becomes the arbiter. The common toolkit is: atomic conditional updates for counters and balances, SELECT … FOR UPDATE (or SKIP LOCKED for queue-like claiming) where logic is multi-step, a version column for user-edit flows and API If-Match style preconditions, and unique constraints plus idempotency keys to make retries safe. When a single row becomes a hot spot (a popular item's stock, a global counter), locking serialises everyone through one row, so designs split the counter into shards, use a queue to serialise the hot key, or move to a reservation model with expiry; for distributed stores without multi-row transactions the same problem reappears as compare-and-set or conditional writes.
Every concurrency control mechanism converts a correctness risk into a cost. Locks cost waiting and deadlock risk; optimistic checks cost wasted work and retries; MVCC costs storage for old versions and cleanup; stronger isolation costs aborts or blocking. The right mechanism is set by contention and by what an anomaly costs: for a bank balance, the atomic conditional update is cheap and safe; for a rarely edited profile, an optimistic version check is enough; for a hot inventory row, neither alone scales and you look at sharded counters or queues. Pessimistic locking is the wrong choice when the lock would be held across slow work such as an external API call or user input, and optimistic locking is the wrong choice when conflicts are frequent. Most importantly, adding a transaction or an isolation level is not a substitute for identifying which anomaly you are actually exposed to and testing that interleaving.