Two requests read a balance of 100 and each add 10. The final value is 110. What happened, and how do you fix it?

This is a lost update: both read the old value and the second write overwrote the first. Use one atomic statement such as `SET balance = balance + 10`, or lock the row, or check a version when writing. The trade-off is simplicity against waiting or retries.

When would you choose optimistic locking over pessimistic locking?

Optimistic locking suits rare conflicts: reads do not block, and only the loser retries. Pessimistic locking suits frequent conflicts or costly mistakes, because retries would be wasteful. The trade-off is retries and wasted work against waiting and the risk of deadlocks.

How do you stop two people booking the same seat?

Make the claim a single conditional update (`SET holder = X WHERE seat = 7 AND holder IS NULL`) or insert a row under a unique constraint, so the database lets only one win and the other is told it failed. Add a hold with an expiry for the payment step. The trade-off is that a held seat is unavailable until it expires.

What is a deadlock and how do you prevent it?

Two transactions each hold a lock the other needs, so neither can continue. Prevent it by taking locks in a fixed order, keeping transactions short, and handling the abort the database raises by retrying. The trade-off is discipline in code order, plus a retry path.

Two doctors each check that at least two are on call and both go off call. What went wrong?

This is write skew: each transaction read a rule's data, saw it hold, and wrote a different row, so snapshot isolation did not detect a conflict. Fix it with serialisable isolation, a lock on the rows the rule reads, or a constraint that covers the rule. The trade-off is lower concurrency or retries.

Your service runs on MySQL and PostgreSQL. Does repeatable read stop a lost update on both?

No. PostgreSQL repeatable read aborts the second writer with a serialisation error, so the application must retry. MySQL InnoDB repeatable read lets a plain read, compute, write sequence overwrite the first update. Use an atomic statement, `SELECT ... FOR UPDATE`, or a version check to be safe on both. The trade-off is waiting or retries against portability.

A worker holds a lock with a lease, pauses for a long time, then wakes up. How do you stop it damaging data?

Issue a fencing token, a number that grows with each grant, and make the protected resource reject any write with an older token. The lease alone is not enough, because the paused worker does not know it expired. The trade-off is that the resource must support the check.

How do several workers take jobs from a table without two taking the same job?

Each worker claims a job with `SELECT ... FOR UPDATE SKIP LOCKED` inside a transaction, so rows locked by another worker are skipped rather than waited for. If a worker crashes, its transaction aborts and the job becomes available again. The trade-off is that ordering is not strict.

Concurrency Control | TheLearningLoop

Why you need concurrency control, and when to use it

When two requests change the same data at the same time, the result can be wrong even though each request is correct on its own. Both read a balance of 100, both add 10, both write 110, and one update is lost. Concurrency control is the set of rules that makes simultaneous changes behave as if they ran one after another.

It gives you two things interviewers always ask about:

Correctness: no lost updates, no double spending, no two people booking the same seat.
Throughput: as many requests as possible run at the same time without breaking correctness.

Use it when:

Several requests can change the same row or object: balances, stock, seats, counters.
A decision depends on data that another request might change at the same moment (check, then act).
You enforce a rule such as "at most one" or "never negative".

Skip it, or use something simpler, when:

Requests touch different data, so they never collide.
Changes can be written as one atomic statement (UPDATE ... SET balance = balance + 10), which the database makes safe by itself.
Data is append-only, so nothing is ever overwritten.

Words used in this lesson

Transaction: a group of reads and writes that the database treats as one unit, which either all take effect (commit) or none do (abort).
Lock: a mark on a row that makes other transactions wait until the owner commits or aborts.
Version number: a counter stored with the row that changes on every write, so a writer can tell if someone else got there first.
Commutative: the order does not matter. Adding 10 and then 5 gives the same result as adding 5 and then 10.
Lease: a lock that expires by itself after a set time, so a crashed owner cannot hold it for ever.
Fencing token: a number that grows each time a lock is granted. The resource being protected rejects any request carrying an older number.
Idempotent: doing it twice has the same effect as doing it once, which makes a retry safe.

A worked example of a lost update, step by step. The balance is 100 and both transactions run at the same time. Transaction 1 reads 100. Transaction 2 reads 100. Transaction 1 writes 110 and commits. Transaction 2 writes 110 and commits. The final balance is 110, not 120, because transaction 2 worked from a value that was already out of date. Locks make transaction 2 wait for transaction 1, and version checks make transaction 2 fail and read again.

Try it

Several users add 10 to the same balance. Change the control, then change the load.

Concurrency Control

Step 1 of 5 · Press Space to play, arrow keys to step

ControlLoad

Initial state. 2 users each want to add 10 to a balance of 100. The correct final balance is 120.

tick:: 0
final balance:: 100
lost updates:: 0
retries:: 0
waiting turns:: 0

1 tick = one step of the simulation.

Speed

Things to try:

Run None with 2 users and see an update lost. Then try 5 users.
Run Pessimistic and watch users queue behind a lock. The answer is right, but users wait.
Run Optimistic with 2 and then 5 users, and compare the retries. Finally try different rows, where no control is needed.

What to watch:

The counters are lost updates, retries and waiting turns. A waiting turn means a user sat idle behind a lock, and a retry means a user did the work again after a rejected write. They are different costs, even when the numbers match.
In the optimistic run every user starts at the same moment, so each round has exactly one winner and the rest retry. That is the worst case, so 5 users give 10 retries. With rare conflicts the real number is close to zero.
The simulation is a simplified model. It does not show a lock holder crashing, a deadlock or write skew. Those are covered in the failure section below.

Concurrency control approaches

Approach	How it works	Good when	Weakness
Atomic statement	Let the database do read and write as one step (`SET x = x + 1`)	Simple counters and updates	Only covers one statement
Pessimistic locking	Lock the row before reading it (`SELECT ... FOR UPDATE`) and release at commit	Conflicts are common, mistakes are costly	Waiting, and deadlocks when locks are taken in different orders
Optimistic locking	Read a version number, and write only if it has not changed	Conflicts are rare	Retries under contention, and wasted work
Isolation levels	The database hides or prevents anomalies between transactions	General correctness inside one database	Stronger levels reduce concurrency
Serialisable transactions	The database detects and aborts conflicting transactions	Complex rules across many rows	Aborts that the application must retry
Distributed locks or queues	One owner or one queue serialises access across services	Resources outside a single database	Failure handling is hard

Pessimistic vs optimistic

Pessimistic: assume conflict will happen, so take a lock first. Others wait. It is predictable under heavy contention, but it holds locks while waiting, which reduces throughput and risks deadlocks.
Optimistic: assume conflict is rare, so read freely and check at write time that nothing changed. A loser retries. It is cheap when conflicts are rare and wasteful when they are common.

A third idea avoids both: design so most updates are atomic and commutative (add to a counter), or route all changes to one row through a single owner or queue.

How isolation levels work

A database transaction can see other transactions' changes in different ways. The standard levels, from weakest to strongest:

Read committed: you see only committed data, but a repeated read can return a different value. This is the default in many databases.
Repeatable read (snapshot): you see a consistent snapshot for the whole transaction. Lost updates are prevented or detected, depending on the database.
Serialisable: the result is as if transactions ran one at a time. The database may abort a transaction, and your code must retry.

Higher levels give stronger guarantees with more waiting or aborts.

Databases differ, so check yours. PostgreSQL, Oracle and SQL Server default to read committed, but MySQL with InnoDB defaults to repeatable read. At repeatable read, PostgreSQL stops the second writer of a row that changed after your snapshot with a serialisation error, and your code must retry. MySQL InnoDB does not: if you read a balance with a plain query, compute in the application and write it back, the stale write succeeds and the first update is lost. In MySQL, use an atomic statement, SELECT ... FOR UPDATE, a version check or serialisable. Serialisable in PostgreSQL detects conflicts and aborts one transaction, while serialisable in MySQL InnoDB mostly uses locks, so you see waiting and deadlock aborts instead. This is a summary, so confirm the details in your database's documentation. Know which anomaly you are guarding against: lost update, non-repeatable read, write skew (two transactions each check a rule and both break it together) or phantom rows.

Concurrency control vs the alternatives

Concern	Locks or versions	No control	Single-threaded owner
Correct with concurrent writers	Yes	No	Yes
Throughput under contention	Medium	Highest, but wrong	Limited to one worker
Waiting or retries	Yes	None	Queueing
Complexity	Medium	None	Medium
Works across services	Hard	No	Yes, via a queue
Best for	Shared rows in a database	Independent data	Hot items such as a ticket counter

Use an atomic statement first, then locks or versions, and a single owner for very hot items.

Avoiding a new single point of failure

Lock holder crashes: locks must have timeouts or be tied to the transaction, so a crash releases them.
Deadlocks: take locks in a fixed order, keep transactions short, and let the database detect deadlocks and abort one.
Distributed locks: use a lease with a fencing token so a paused holder cannot do damage after losing the lock.
Hot rows: split a hot counter into several shards and add them up on read.

You accept waiting or retries, in return for correctness.

A few details that matter in practice:

Timeouts: set a lock timeout and a statement timeout, and watch for transactions left open and idle, because one forgotten transaction can block everyone behind it.
Fencing tokens: the lock service hands out an increasing number with each lock. The protected resource, such as the database or storage, must compare it and reject an older one. Without that check, a holder that paused (for example during a long garbage collection) and wakes up after its lease expired can still overwrite newer data.
Retries: retry a conflicted or deadlocked transaction a limited number of times, with a short random delay (backoff with jitter), and make the work idempotent so a retry after an unknown outcome does no harm.
Redis or in-memory locks: a lock with a lease in a single Redis node is fine for efficiency, such as avoiding duplicate work, but is not enough alone for correctness unless the resource also checks a fencing token.

Common mistakes

Read, change in the application, write back. This loses updates. Use one atomic statement or a lock.
Long transactions that hold locks. Keep them short and never wait on a user inside one.
No retry for aborted or conflicted transactions.
Different lock order in different code paths. It causes deadlocks.
Assuming the default isolation level is serialisable. Check it.
Using a lock in memory in a multi-server system. It protects only one server.

How to talk about concurrency control in an interview

Name the anomaly: lost update, double booking, write skew.
Offer the lightest fix first: an atomic update or a unique constraint, then optimistic locking when conflicts are rare, pessimistic locking when they are common.
Mention isolation levels, retries, short transactions and deadlocks.
Name the trade-off: strictness costs concurrency, either as waiting (locks) or as retries (versions).

Where you meet concurrency control in real systems

Ticket booking, payments, inventory and counters all depend on it. Here are three drawn out, the first with SQL you can run, then more examples.

Example 1: Atomic update, row lock and version check

Three ways to make "add 10 to a balance" safe in PostgreSQL and MySQL. Open the example below to see the picture and the SQL.

Config: three safe ways to update a balanceSetup 1 table, 3 techniquesCode no code changes

Read the row and its version. When writing, change the row only if the version is still the same. If another request got there first, no row matches and the caller retries.

CREATE TABLE accounts (
  id      int PRIMARY KEY,
  balance int NOT NULL CHECK (balance >= 0),     -- the database refuses a negative balance
  version int NOT NULL DEFAULT 0
);
INSERT INTO accounts (id, balance) VALUES (1, 100);

-- 1. Best when possible: one atomic statement. The database does the read and the write together.
UPDATE accounts SET balance = balance + 10 WHERE id = 1;

-- 2. Pessimistic: lock the row, so other transactions that want it wait until this one commits.
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = 120 WHERE id = 1;     -- computed by the application from the value read
COMMIT;

-- 3. Optimistic: write only if the version is unchanged. "UPDATE 0" means someone else changed the row,
--    so the application re-reads and retries.
UPDATE accounts SET balance = 130, version = version + 1 WHERE id = 1 AND version = 0;
UPDATE accounts SET balance = 999, version = version + 1 WHERE id = 1 AND version = 0;   -- stale version: changes nothing

-- A stricter isolation level makes the database detect conflicts itself. The application must retry on error.
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

Both scripts were run against real servers (PostgreSQL 16 and MySQL 8): the atomic update, the locked update, an optimistic update that changed one row, and a stale-version update that changed none. Names and numbers are examples.

A note on the SERIALIZABLE block: with only one transaction running, it cannot conflict, so it only shows the syntax. With two concurrent transactions that read and then write overlapping data, PostgreSQL would abort one with a serialisation failure, and the application must retry the whole transaction.

Example 2: Booking the last seat

Two people click "book" for the last seat. A unique constraint or a conditional update lets exactly one win.

An illustrative version: UPDATE seats SET holder = 'buyer-1', hold_expires = now() + interval '5 minutes' WHERE id = 7 AND (holder IS NULL OR hold_expires < now()). The database reports how many rows changed. One row means this buyer holds the seat, and zero rows means someone else got it first, so the service tells the buyer the seat is gone. The hold expires if the buyer never pays, so the seat is not stuck. Payment then confirms the hold. Table and column names are examples.

Both requests try to claim the seat. The database lets only one conditional update succeed, and the other is told the seat is gone.

Example 3: Lock order and deadlocks

Two transactions that take the same locks in opposite order can wait for each other for ever.

Transaction 1 locks row A then wants B. Transaction 2 locks B then wants A. Each waits for the other, so the database aborts one. Taking locks in the same order avoids this.

More real-world scenarios

These are illustrative patterns that engineers commonly use, not descriptions of one named company's system.

4. Ticket and seat booking

A seat is claimed with a conditional update or a unique constraint, so two buyers never get the same one.

5. Inventory

Stock is decremented with UPDATE ... SET stock = stock - 1 WHERE stock > 0, which cannot go below zero.

6. Bank transfers

Both accounts are locked in a fixed order (by account number) inside one transaction, avoiding deadlocks.

7. Collaborative editing

Edits carry a version. A stale edit is rejected or merged, so one user does not overwrite another's change.

8. Rate counters and likes

Hot counters are split into shards and added together on read, so a popular post does not block on one row.

9. Job queues

Workers claim jobs with FOR UPDATE SKIP LOCKED, so two workers never take the same job.

10. Distributed locks for cron jobs

A lock with a lease ensures only one machine runs a scheduled job, and a fencing token protects against a paused holder.

Tools and managed services

Kind	Name	Notes
Relational databases	PostgreSQL, MySQL (InnoDB)	Row locks, versions and isolation levels
Document stores	MongoDB, Amazon DynamoDB	Conditional writes and version attributes
Coordination	etcd, Apache ZooKeeper	Distributed locks with leases
Caches	Redis	Atomic increments and scripts
ORMs and libraries	Optimistic locking support in many ORMs	A version column handled for you

The takeaway

Whenever two requests can change the same data, prefer one atomic statement, then a version check when conflicts are rare or a lock when they are common, and always retry the loser.

Concurrency Control

Key takeaways

Why you need concurrency control, and when to use it

Words used in this lesson

Try it

Concurrency Control

Concurrency control approaches

Pessimistic vs optimistic

How isolation levels work

Concurrency control vs the alternatives

Avoiding a new single point of failure

Common mistakes

How to talk about concurrency control in an interview

Where you meet concurrency control in real systems

Example 1: Atomic update, row lock and version check

Example 2: Booking the last seat

Example 3: Lock order and deadlocks

More real-world scenarios

4. Ticket and seat booking

5. Inventory

6. Bank transfers

7. Collaborative editing

8. Rate counters and likes

9. Job queues

10. Distributed locks for cron jobs

Tools and managed services

The takeaway

Interview questions