Databases series: 1. Tables and basic SQL · 2. ERD, joins, GROUP BY · 3. Normalization · 4. Library exercise · 5. Indexes and query performance · 6. Transactions and locking
Code that works with one user can still break when two requests touch the same row: it oversells stock, money vanishes when two wallet top-ups run at once, a job runs twice. After this post you’ll know how to keep data correct (atomic updates, locks, versions, unique constraints) and how to retry properly; examples were run on MySQL 8.4 (InnoDB), with notes where PostgreSQL differs. Read Indexes and query performance first: InnoDB locks index records, so you need indexes to see why a single UPDATE can lock a whole table.
1. Transactions and ACID
Key idea: a transaction is a group of operations that either all succeed or none happen. InnoDB keeps the four ACID properties with the undo log, the redo log, locks and MVCC.
START TRANSACTION;
UPDATE account SET balance = balance - 500000 WHERE id = 1;
UPDATE account SET balance = balance + 500000 WHERE id = 2;
INSERT INTO transfer_log (from_id, to_id, amount) VALUES (1, 2, 500000);
COMMIT; -- or ROLLBACK on error
| Property | Meaning | How InnoDB does it |
|---|---|---|
| Atomicity | All or nothing | The undo log keeps old values for rollback |
| Consistency | Always from one valid state to another | Constraints, FKs, plus correct application logic |
| Isolation | Concurrent transactions don’t see each other’s half-done state (per isolation level) | Locks + MVCC |
| Durability | Committed data survives a crash | The redo log (WAL: write the log first, data pages later) is fsynced at commit |
2. Isolation levels and anomalies
Key idea: the isolation level decides how much of other transactions’ changes you can see. The SQL standard table only covers three read anomalies; lost update and write skew are what actually lose money, and each database handles them differently.
An anomaly is a wrong result that appears when two transactions interleave:
| Anomaly | Description |
|---|---|
| Dirty read | You read data another transaction hasn’t committed (and then it rolls back). |
| Non-repeatable read | You read a row twice in one transaction and get two values because someone updated and committed in between. |
| Phantom read | You run the same condition twice and the second time a new row appears because someone inserted it. |
| Lost update | Both sides read, then write based on what they read; the later write overwrites the earlier one. |
| Write skew | Both sides read the same data, each writes a different row, and together they break a rule (two doctors go off call when “at least one must stay on call”). |
The four levels in the SQL standard:
| Level | Dirty read | Non-repeatable | Phantom |
|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible |
| READ COMMITTED | No | Possible | Possible |
| REPEATABLE READ | No | No | Possible (per the standard) |
| SERIALIZABLE | No | No | No |
Lost update: a real run
Key idea: reading a value into the application, computing, then writing it back loses updates, even on MySQL RR. Let the database do the read-modify-write, or lock, or check a version.
Run on MySQL 8.4 (RR): the wallet holds 100, T1 adds 10, T2 adds 50, each reads in the application and writes back.
sequenceDiagram
participant T1
participant DB as wallet.balance
participant T2
T1->>DB: SELECT balance (100)
T2->>DB: SELECT balance (100)
T2->>DB: UPDATE balance = 100 + 50
T2->>DB: COMMIT (150)
T1->>DB: UPDATE balance = 100 + 10
T1->>DB: COMMIT (110, T2's 50 is lost)
The result is 110 instead of 160. Four fixes:
-- 1. Atomic update: let the database read-modify-write the latest version
UPDATE wallet SET balance = balance + 10 WHERE id = 1;
-- 2. Business condition: only decrement stock if there is some left
UPDATE product SET stock = stock - 1 WHERE id = ? AND stock >= 1;
-- affected rows = 0 means sold out
-- 3. Pessimistic locking: lock the row as you read it
START TRANSACTION;
SELECT balance FROM wallet WHERE id = 1 FOR UPDATE;
UPDATE wallet SET balance = ? WHERE id = 1;
COMMIT;
-- 4. Optimistic locking: a version column
UPDATE wallet SET balance = ?, version = version + 1
WHERE id = 1 AND version = ?;
-- affected rows = 0 means someone changed it first: re-read and retry
Write skew: an example and how to stop it
Key idea: write skew slips through because the two sides modify different rows, so there’s no conflict to detect. The fix is to turn the “hidden” conflict into a conflict on the same row.
Shift 7 has two doctors on call, and both press “take leave”. Each transaction counts 2 people, allows it, then updates its own row. Both MySQL RR and Postgres RR let it through, and shift 7 has nobody left.
-- Option 1: lock exactly the rows you read to make the decision
START TRANSACTION;
SELECT doctor_id FROM doctor_shift
WHERE shift_id = 7 AND on_call = TRUE
FOR UPDATE; -- the second transaction waits here
-- the application counts: only allow leave if >= 2 are on call
UPDATE doctor_shift SET on_call = FALSE WHERE shift_id = 7 AND doctor_id = 42;
COMMIT;
-- Option 2: lock one row that stands for the whole group (materialize the conflict)
START TRANSACTION;
SELECT id FROM shift WHERE id = 7 FOR UPDATE;
-- ... read, check, write as above
COMMIT;
- Don’t write
SELECT COUNT(*) ... FOR UPDATE: MySQL accepts it, but PostgreSQL rejectsFOR UPDATEwith aggregate functions. - Option 2 is needed when the rows to lock may not exist yet (e.g. “no two overlapping bookings”), because you can’t lock a row that isn’t there.
- On Postgres you can also run SERIALIZABLE and retry on serialization failures.
Choosing an isolation level in practice
Key idea: many large MySQL shops move from RR to READ COMMITTED to cut gap locks and deadlocks. But neither level replaces atomic updates, explicit locks or versions.
READ COMMITTED vs REPEATABLE READ on MySQL in detail
3. Locking and MVCC
Key idea: writes take locks; plain reads take no lock and read an older version instead (MVCC). Most incidents come from locking more than you think, holding locks too long, or retrying in the wrong place.
3.1. Lock types in InnoDB
Key idea: InnoDB locks index records, not abstract “rows”; under RR it also locks the gaps between records.
- Shared (S) and Exclusive (X) row locks:
SELECT ... FOR SHAREtakes S;FOR UPDATE,UPDATE,DELETEtake X. - A gap lock locks the gap between index records. A next-key lock = record lock + the gap before it. They exist under RR to stop phantoms, are almost unused under READ COMMITTED, and cause “mysterious” deadlocks when many transactions insert into the same range.
- Metadata lock (MDL):
ALTER TABLEwaits for every transaction using the table to finish. One forgotten uncommitted transaction + oneALTER= every later query queues up, a classic production incident.
To see exactly what each statement locks (record, gap, next-key, insert intention, intention locks, AUTO-INC) under RR and RC, Locks in InnoDB reads performance_schema.data_locks case by case on MySQL 8.4.
3.2. SKIP LOCKED and NOWAIT
Key idea: FOR UPDATE SKIP LOCKED lets many workers take work from one table without fighting over it or waiting on each other. NOWAIT fails immediately instead of waiting for a lock.
The “job queue in the database” pattern (MySQL 8+, Postgres 9.5+):
START TRANSACTION;
SELECT id FROM job
WHERE status = 'READY' AND run_at <= NOW()
ORDER BY run_at
LIMIT 10
FOR UPDATE SKIP LOCKED; -- skip any row another worker has locked
UPDATE job SET status = 'RUNNING' WHERE id IN (...);
COMMIT;
The full version (a locked_until lease for workers that die mid-job, a sweeper that returns expired work to the queue, a partial index over waiting rows only) is in the “database as a queue” section of Message brokers and how to choose one.
Job queue in the database or a message broker
3.3. Deadlocks
Key idea: a deadlock is a wait cycle (T1 holds A and waits for B, T2 holds B and waits for A). InnoDB detects it and rolls one side back with error 1213; your job is to make it rare and retry when it happens.
Prevention:
- Lock in a consistent order, e.g. a transfer always locks the account with the smaller id first.
- Keep transactions short, no network calls inside.
- Index the
WHEREofUPDATE/DELETEso fewer rows get locked. - Retry a bounded number of times: deadlocks are normal in a concurrent system, not a bug you can fully eliminate. See the latest one with
SHOW ENGINE INNODB STATUS.
Airline booking: holding seats when many people book at once applies these ideas to a real problem: a deadlock from locking seats in the order the customer clicked (fixed with a fixed ORDER BY), the SKIP LOCKED trap that reports sold out while seats remain, and a conditional update for the seat counter with no prior lock.
3.4. MVCC: readers don’t block writers
Key idea: MVCC (Multi-Version Concurrency Control) means the database keeps several versions of a row, so readers read the version that fits their snapshot instead of waiting for writers. The cost: old versions can only be cleaned up once no snapshot needs them.
- InnoDB: the newest version lives in the clustered index; older versions are rebuilt from the undo log through a pointer chain. Each consistent read has a read view (the list of running transactions) that picks the visible version. RC makes a new read view per statement, RR makes one for the whole transaction.
- PostgreSQL: every
UPDATEwrites a new copy of the row into the heap (the table’s row storage), and the old one is marked withxmax.VACUUMcleans up old versions later.
MVCC internals (undo log, read view, purge, measured history list length) and the comparison with PostgreSQL are in Transactions from the inside; how to find and fix transactions left open too long is in Harmful transactions.
3.5. Check-then-insert and gap locks
Key idea: “create it if it doesn’t exist” with FOR UPDATE doesn’t stop duplicates, and under RR it deadlocks. Only a unique constraint stops them reliably.
START TRANSACTION;
SELECT * FROM wallet WHERE user_id = 42 FOR UPDATE; -- no row yet
-- the application sees nothing, so it inserts
INSERT INTO wallet (user_id, balance) VALUES (42, 0);
COMMIT;
Unique constraints are also the foundation of idempotency. Idempotency part 1 explains why a lock (even FOR UPDATE or a Redis lock) isn’t idempotency: a lock stops concurrent runs, not a retry that arrives after the first run finished. It also measures that on PostgreSQL a second INSERT ... ON CONFLICT DO NOTHING waits for the transaction holding the same key to commit or roll back before returning.
3.6. Retrying the right way
Key idea: deadlocks, lock wait timeouts and serialization failures are normal, so the application must retry. But it must retry the whole transaction: each attempt is a new transaction.
@Service
public class TransferService {
private final TransactionTemplate tx;
private final AccountRepository accounts;
public TransferService(TransactionTemplate tx, AccountRepository accounts) {
this.tx = tx;
this.accounts = accounts;
}
// NO @Transactional here: each attempt must be a fresh transaction
public void transfer(long from, long to, BigDecimal amount) {
for (int attempt = 1; ; attempt++) {
try {
tx.executeWithoutResult(status -> doTransfer(from, to, amount));
return;
} catch (ConcurrencyFailureException e) { // deadlock, lock timeout, serialization, optimistic
if (attempt == 3) throw e;
sleepQuietly(ThreadLocalRandom.current().nextLong(20, 50L * attempt));
}
}
}
private void doTransfer(long from, long to, BigDecimal amount) {
// lock in ascending id order, see exercise 3
}
private static void sleepQuietly(long millis) {
try { Thread.sleep(millis); } catch (InterruptedException ie) { Thread.currentThread().interrupt(); }
}
}
Spring translates MySQL deadlocks (1213) and Postgres serialization failures (SQLState 40001) into subclasses of ConcurrencyFailureException, so catching the parent is enough. @Retryable (Spring Retry) works too, as long as it sits outside the transaction proxy.
Code review: two worked reviews points these mistakes out in a real PR: a Spring Boot booking flow that sends emails and publishes events inside the transaction.
3.7. Advisory locks: locking by name
Key idea: when the thing to lock is a “task” rather than a row (only one instance runs the 2 a.m. reconciliation job, only one request builds customer 42’s report), the database has built-in advisory locks by name.
Advisory locks in MySQL and PostgreSQL
When the work to lock isn’t inside a DB transaction (calling an external API, a job across many pods), people reach for a Redis lock. The “When you don’t need a Redis lock” section of Reviewing a Redis distributed lock compares Redis locks with database mechanisms (conditional statements, optimistic locks, FOR UPDATE, advisory locks) and shows when Redis is really needed.
Exercises
The course has no exercises for this part; these are my own.
Exercise 1: A flash sale that never oversells
Task: 1,000 concurrent requests try to buy a product with 1 left. Current code: read stock, and if (stock > 0) run UPDATE stock = stock - 1. It sells more than 1. Fix it.
Solution to exercise 1
Drop the application-level check-then-act and push the condition into an atomic UPDATE:
UPDATE product SET stock = stock - 1 WHERE id = :id AND stock >= 1;
Tested: the first purchase gets affected rows = 1, the second gets 0. In Java, int updated = repo.decrementStock(id); if updated == 0, return “sold out”. The UPDATE takes an X lock on the row, so requests serialize right on that row with no separate SELECT ... FOR UPDATE. Add CHECK (stock >= 0) as a last safety net. At very large scale one “hot” row becomes a bottleneck; only then do you need Redis DECR or splitting stock into several rows (buckets).
The same conditional-update trick makes handlers idempotent: in the auto-cancel ride case study in Redis: choosing data structures, the cancel is UPDATE ride SET status = 'CANCELLED' WHERE id = ? AND status = 'WAITING', so a duplicate worker, or one racing a driver who just accepted, simply gets 0 rows.
Exercise 2: A job queue with many workers
Task: 5 instances process the job table and sometimes a job runs twice. Fix it.
Solution to exercise 2
Each worker claims jobs with SELECT ... FOR UPDATE SKIP LOCKED and switches them to RUNNING in the same transaction (section 3.2). It needs an index on (status, run_at). Add locked_until/a heartbeat so jobs from a worker that died mid-run get picked up again, and make handlers idempotent, because “exactly once” doesn’t exist once crashes are involved.
Exercise 3: Money transfers without deadlocks
Task: an API transfers money between two accounts. When A pays B and B pays A at the same time, the logs occasionally show deadlock error 1213. Fix it so it doesn’t deadlock and balances never go negative.
Solution to exercise 3
It deadlocks because request 1 locks A then waits for B, while request 2 locks B then waits for A. Always lock in ascending id order, whatever the direction:
START TRANSACTION;
SELECT id, balance FROM account WHERE id = LEAST(:from_id, :to_id) FOR UPDATE;
SELECT id, balance FROM account WHERE id = GREATEST(:from_id, :to_id) FOR UPDATE;
-- the application checks :from_id's balance >= :amount, otherwise ROLLBACK
UPDATE account SET balance = balance - :amount WHERE id = :from_id;
UPDATE account SET balance = balance + :amount WHERE id = :to_id;
INSERT INTO transfer_log (from_id, to_id, amount) VALUES (:from_id, :to_id, :amount);
COMMIT;
- Two opposite-direction requests now both ask for the smaller id’s lock first, so one waits until the other finishes: no cycle.
- Add
CHECK (balance >= 0)as a safety net. - Keep the retry loop from section 3.6, since deadlocks can still come from elsewhere (gap locks, secondary indexes).
- Reject
from_id = to_idin the application. The client should send an idempotency key so a double click doesn’t transfer twice.
Remember
- Explain why MySQL REPEATABLE READ still loses updates while PostgreSQL RR raises an error.
- List the four fixes for lost updates and know when to pick optimistic vs pessimistic.
- Explain write skew and the two ways to turn it into a conflict on one row.
- Know why
FOR UPDATEon a missing row deadlocks, and what replaces it. - Write a retry loop in the right place: outside the transaction, bounded, with backoff.
Interview questions
Junior
What does ACID stand for?
The four transaction properties: Atomicity, Consistency, Isolation, Durability.
Atomicity: all or nothing. Consistency: constraints always hold. Isolation: concurrent transactions don’t see each other’s half-done state (per the isolation level). Durability: committed data survives a crash, thanks to the redo log/WAL.
How do dirty reads, non-repeatable reads and phantom reads differ?
A dirty read sees uncommitted data; a non-repeatable read re-reads a row and gets a different value; a phantom read re-runs a condition and sees a new row.
Non-repeatable reads come from another transaction updating and committing; phantoms come from another transaction inserting. READ COMMITTED stops the first, REPEATABLE READ also stops the second, SERIALIZABLE stops all three.
Mid
Optimistic vs pessimistic locking: what’s the difference and when do you use each?
Optimistic doesn’t lock and checks a version on write; pessimistic locks the row on read with SELECT ... FOR UPDATE.
Optimistic retries on conflict; it fits rare conflicts and state that spans several requests. Pessimistic fits frequent conflicts or expensive retries, but lowers throughput and can deadlock.
What is a deadlock and how do you prevent it?
Two or more transactions wait for each other’s locks in a cycle; InnoDB detects it and rolls one back (error 1213).
For example, T1 holds A and waits for B, T2 holds B and waits for A. Prevent it by locking in a consistent order, keeping transactions short, indexing the WHERE of UPDATE/DELETE so fewer rows are locked, and always having a bounded retry around the transaction, because deadlocks can’t be eliminated entirely.
What is FOR UPDATE SKIP LOCKED for?
Letting many workers take work from one table without fighting over it or waiting on each other.
Each worker locks a batch of ready rows and skips rows another worker already locked. Used for job queues in the database and handing out tickets/voucher codes from a pool. It needs an index on the filter and order, and handlers must be idempotent because a worker can die mid-job.
Senior
Does MySQL’s REPEATABLE READ prevent lost updates? How does PostgreSQL compare?
No, not when the application reads and writes back a precomputed value; PostgreSQL RR detects it and raises a serialization error.
InnoDB lets the UPDATE overwrite the latest version without complaint. PostgreSQL RR sees the row was changed by another transaction after the snapshot and errors so the application retries. Database-neutral fixes: an atomic update (SET x = x + ?), a condition in the WHERE, FOR UPDATE, or an optimistic version.
What is write skew and which isolation level prevents it?
Two transactions read the same data, each changes a different row, and together they break a rule; only SERIALIZABLE (Postgres SSI) or explicit locking stops it.
For example, two doctors both go off call when only two are on call. Snapshot isolation (MySQL RR, Postgres RR) doesn’t stop it. Alternatives: FOR UPDATE on the relevant rows, locking one “representative” row, or turning the rule into a database constraint.
How does MVCC work in InnoDB, and why are long transactions dangerous?
Each row points to undo records holding old versions and a read view picks the visible one; a long transaction keeps an old read view, so purge can’t clean the undo.
Each row stores the id of the last transaction that changed it and a pointer into the undo log. A consistent read rebuilds the version from undo when needed. A long transaction makes history list length grow, and every read wades through longer version chains. It also holds locks and metadata locks, which can block ALTER TABLE.
An UPDATE causes wide locking and deadlocks. How do you investigate?
First check whether the WHERE uses an index, because InnoDB locks every index record it scans.
Look at SHOW ENGINE INNODB STATUS (the LATEST DETECTED DEADLOCK section) and performance_schema.data_locks to see which locks are held and awaited. Fix: add the index, lock in a consistent order, shorten the transaction, consider READ COMMITTED to cut gap locks if the business allows it, and retry on deadlock.
Why must the retry loop sit outside the transaction?
Because a deadlock or serialization failure rolls back the whole transaction, so each attempt must be a new transaction.
Spring also marks the transaction rollback-only; re-running one statement inside loses earlier changes or ends in UnexpectedRollbackException. Use TransactionTemplate in a loop, or @Retryable around the @Transactional proxy, bounded, with randomized backoff, and no external side effects inside the transaction.
Two requests both “create it if it doesn’t exist”. How do you avoid duplicates?
Put a unique constraint on the business key and let the database reject the duplicate; checking in the application always races.
SELECT ... FOR UPDATE on a missing row doesn’t help, and under RR it deadlocks, because both sides’ gap locks don’t conflict but block each other’s inserts. Use INSERT ... ON DUPLICATE KEY UPDATE/INSERT IGNORE (MySQL) or ON CONFLICT (Postgres), or insert and catch the duplicate error to read back the existing row.
Useful phrases for interviews
- “MySQL’s REPEATABLE READ doesn’t detect lost updates, so I’d use an atomic update or a version column.”
- “To avoid deadlocks we always acquire locks in the same order and keep transactions short.”
- “Atomicity comes from the undo log, and durability from the redo log, which is fsynced at commit.”
- “Snapshot isolation stops non-repeatable reads, but write skew still needs SERIALIZABLE or explicit locks.”
- “I’d push the check into the UPDATE’s WHERE clause, so the database does the read-modify-write atomically.”
- “For a job queue in the database, I’d claim rows with FOR UPDATE SKIP LOCKED so workers never wait on each other.”
- “When InnoDB picks a deadlock victim it rolls back the whole transaction, so the retry has to wrap the entire transaction, not a single statement.”
- “Optimistic locking works well when conflicts are rare, and the version can travel with the client between requests.”
- “A long-running transaction keeps an old snapshot alive, so purge can’t keep up and every query slows down.”
Notes based on the Fundamentals of Backend course (Ronin Engineer), rewritten in my own words.