Transactions, isolation and locking
What actually stops two people buying the last seat, inside one database.
What problem does a transaction solve?
Two requests read "1 seat left" at the same moment. Both decide it is fine. Both write. You have sold one seat twice, and no amount of scaling fixed it, because the bug is not about speed.
This is the oldest problem in databases, and it has a precise set of answers. Most "contested resource" interview questions (seats, stock, balances, unique names) are this question in disguise.
What a transaction costs you
Every concept in here states a cost. A technique with no price is a technique you have not understood yet — and naming the cost out loud is most of what separates a senior answer from a junior one.
When to reach for a transaction, and when not to
Use it when
- Anything where two people can want the same thing: seats, stock, balances, usernames.
- Any change that spans several rows and must land together, or not at all.
- Any read-check-then-write sequence. That gap is the bug.
Do not, when
- Independent writes that cannot conflict. Wrapping them in a strict transaction just adds waiting.
- Long-running work. Never hold a transaction open while calling an external API or waiting on a human.
- Counting things nobody competes for, like views. That is a counter problem, not a transaction problem.
How a transaction actually works
- —SELECT ... FOR UPDATE. The row is locked until you commit.
- —The second request waits, then sees the true value. It cannot go wrong.
- —Writers queue, so throughput on that row is one at a time.
- —Deadlocks are possible if two transactions take locks in different orders.
- —Right when conflicts are common: a flash sale on one item.
- —Read the row and its version. Write with WHERE version = the one you read.
- —Zero rows updated means someone beat you, so you re-read and retry.
- —No locks held while you think, so readers and writers do not block.
- —A retry storm if conflicts are frequent: you do the work repeatedly.
- —Right when conflicts are rare: editing your own profile, most CRUD.
Start with the thing people say and rarely explain: ACID. Atomic means all of the changes or none. Consistent means the database's own rules, such as constraints and foreign keys, are never broken. Isolated means concurrent transactions do not see each other's half-finished work. Durable means once it is committed, a crash cannot lose it. The one worth understanding properly is isolation, because it is the one you get to choose.
Isolation levels, and the specific bug each one removes. *Read uncommitted* lets you see another transaction's uncommitted changes, which nobody wants. *Read committed*, the common default, means you only see committed data, but two reads inside your transaction can return different values, because someone committed in between. *Repeatable read* fixes that: rows you have read stay the same for the length of your transaction. *Serializable* is the strongest: the result is guaranteed to be the same as if the transactions had run one after another, in some order. It is also the slowest, and under load it aborts transactions that would have conflicted, so your code must be ready to retry.
The trap here is assuming your database is stricter than it is. Postgres and MySQL default to read committed and repeatable read respectively, not serializable. So the classic read-then-write race is fully available to you by default, and the database will not save you. You have to ask for the protection.
How to actually make the seat safe, in order of preference. Best: let the database decide in one statement, with no read-then-write at all: UPDATE seats SET taken = true WHERE id = ? AND taken = false, then check how many rows changed. One row means you got it. Zero means someone else did. There is no gap for a race to live in. Next best: a unique constraint, so the second insert fails. That is how you stop duplicate usernames, and it works no matter how many application servers you run. After that: SELECT ... FOR UPDATE to lock the row, when you do need to read and think before writing. Worst, and the one to name as wrong: read the value in application code, decide, then write.
MVCC is why readers usually do not block writers. Instead of overwriting a row, the database keeps multiple versions of it, and each transaction sees the version that was current when it started. That is what makes a long report query possible without freezing everyone out. The costs: old versions have to be cleaned up later, and a very long-running transaction blocks that cleanup. That is how one reporting query bloats a production database without any error.
Deadlocks happen when two transactions hold what the other wants. The database notices and kills one, and your code sees an error it must retry. The prevention is boring and effective: always take locks in the same order (for example, update the lower account id first when transferring money), and keep transactions short.
One rule to carry everywhere: a transaction is not a place to wait. Do not call another service, charge a card, or send an email inside one. You are holding a database connection and a set of locks while depending on something you do not control, and that is how one slow third party takes down a database.
- Assuming the default isolation level protects you. It usually does not.
- Read, decide in application code, then write. That gap is the bug.
- Holding a transaction open across a network call to another service.
- Using serializable everywhere and being surprised by aborted transactions under load.
The a transaction follow-up an interviewer will ask
"Two people click Buy on the last ticket at the same millisecond. Walk me through what your database does."
It depends on how I wrote it, so here is the version I would build. The reservation is a single conditional statement: update the seat row setting it to held with this booking id, where the seat id matches and the current state is free. The database serialises that row internally, so one of the two statements reports one row changed and the other reports zero. The one that changed a row proceeds to payment. The other gets an immediate "just sold" with alternatives. There is no window between checking and writing, because there was no separate check. The wrong version, which I want to name explicitly, is selecting the seat, seeing it free in application code, and then updating. Both requests can pass that check before either writes, and both proceed. More servers or a faster database do not fix that. Moving the decision into the write does. I would also add that the hold is not the end: it needs an expiry, so an abandoned checkout releases the seat, and the payment step needs an idempotency key so a retried payment does not charge twice for one hold. Cost: that seat row is a serialisation point, so a single very popular event has a throughput ceiling on it, which I would accept, because there are only so many seats anyway.
Your turn
Your code does SELECT balance, checks it is enough, then UPDATE balance. What is the bug, and give two different fixes.
Learn it from the source
This page gets you interview-ready. These take you to where the idea is actually specified or measured — read them when you want the real depth.
- PostgreSQL — Transaction Isolation ↗Read committed, repeatable read and serializable, with the anomalies each one still allows.
- Jepsen — Consistency models ↗The map showing how isolation levels and distributed consistency relate.