Transactions and isolation
- Lesson 18 of 40
- 14 min
- Intermediate
- 13 steps
Two people want the same seat and press «buy» almost together. We will see why code that looks correct sells it twice, and which tools the database gives you to stop that.
- Seats free
- 0
- Tickets sold
- 1
- Oversold
- 0
- Mateo waits
- 2 ms
- request
- response
- waiting in queue
- slow request
Step 1 of 13
Concept
A single seat left
Lucía and Mateo try to buy the last ticket for a concert. Each request lands on a different app server, but both servers use the same database.
The purchase code looks sensible: check how many seats are free and, if there is one, store the booking. Timings and numbers in this lesson are example values.
Step 2 of 13
Problem
Both of them get the seat
Both servers read «1 left» before either has written. Both decide the seat is free, both write «0 left» and both send a confirmation.
One seat, two tickets: a race condition. More precisely, a lost update: Mateo's write overwrote Lucía's and nobody noticed.
What changed
- Both read «1 left»
- Both write «0 left»
- Seat 12 sold twice
Step 3 of 13
Concept
A transaction alone does not stop it
Wrap the purchase in BEGIN … COMMIT and it becomes all-or-nothing, yet it can still be sold twice. Under read committed, the default level of many engines, each read sees the latest committed value: 1, for both.
ACID is four guarantees: atomicity (all or nothing), consistency (the data's rules hold), isolation (what each transaction sees of the others) and durability (what was committed survives a crash). The default isolation is weak on purpose, for speed.
What changed
- BEGIN … COMMIT around the purchase
- Still sold twice
Step 4 of 13
Solution
Lock the row while you decide
Read with SELECT … FOR UPDATE. Lucía's transaction locks row 12; when Mateo's tries to read it, the database makes it wait until Lucía commits (see his request held next to the database).
Then Mateo reads the new value, 0, and gets a polite «sold out».
What changed
- Read with FOR UPDATE
- Mateo waits about 30 ms for Lucía's lock
- The seat is sold once
Step 5 of 13
Technique
Or do it in a single statement
Often you do not need to read first at all. Let the database check and change in one atomic step:
UPDATE seats SET free = free - 1 WHERE id = 12 AND free > 0, then look at how many rows it changed. 1 means the seat is yours; 0 means it was already gone.
What changed
- Check and change in one UPDATE
- Mateo's UPDATE changes 0 rows → sold out
Step 6 of 13
Technique
Optimistic: check a version number
Locks are pessimistic: they assume a conflict and make others wait. Optimistic concurrency assumes conflicts are rare: read the row with its version, then write … WHERE id = 12 AND version = 4, bumping it to 5.
If someone wrote first, the version no longer matches, the write changes 0 rows and you read again and retry. Nobody waits for anybody.
What changed
- version column
- Mateo's write rejected (version changed) → retries → sold out
Step 7 of 13
Trade-off
Isolation levels
Databases offer several isolation levels. The stronger the level, the more anomalies it prevents, but the more often a transaction has to wait or abort.
Read committed stops you reading uncommitted data, but lets through reads that change mid-transaction and lost updates. Snapshot / repeatable read gives each transaction a fixed picture of the data; depending on the engine, it does or does not abort the second writer of the same row. Serializable guarantees the same result as running the transactions one at a time.
Step 8 of 13
Problem
Case: locking a row that does not exist yet
A new flow drops the counter and checks the bookings table instead: «is there a booking for seat 12? No: insert one». Both transactions even use FOR UPDATE…
…but under read committed there is no row to lock. Both see zero rows, both insert, and seat 12 is booked twice. A new row that changes the result of your search is a phantom; deciding on what you read and writing somewhere else, so that together the writes break a rule, is called write skew.
What changed
- «Check, then insert» flow
- FOR UPDATE finds no row to lock
- Seat 12 booked twice
Step 9 of 13
Problem
Case: the deadlock
Locks bring their own trap. Lucía moves money from account A to B: she locks A, then asks for B. At the same instant Mateo moves money from B to A: he locks B, then asks for A.
Each waits for the other forever. The database spots the cycle (some engines at once, others after waiting about a second) and aborts one of the two transactions, here Mateo's, which must retry.
What changed
- Two transfers lock A and B in opposite order
- Each waits for the other
- The database aborts Mateo's transaction
Step 10 of 13
Solution
Constraints: the last line of defence
Code changes, and sooner or later someone will write a purchase path that forgets the lock. So let the database itself enforce the rule: a unique constraint on (concert, seat) in the bookings table.
Now a second booking for seat 12 fails with a duplicate-key error, whatever the application code does.
What changed
- UNIQUE (concert, seat) on bookings
- A duplicate booking is rejected
Step 11 of 13
Problem
Case: a lock held during the payment
To play it safe, someone moves the card charge inside the purchase transaction: lock seat 12, call the payment provider, then commit. The provider takes 4 seconds.
For those 4 seconds the seat stays locked and every other buyer waits, each one holding a database connection. On sale day the connection pool runs out and the whole site stalls.
What changed
- Charge inside the transaction
- Seat 12 locked for 4 s per attempt
- Connection pool exhausted
Step 12 of 13
Solution
Hold, charge, then confirm
Split it into short steps. Transaction 1 marks seat 12 as held for Lucía until 20:15 and commits right away. Then she pays, outside any transaction. Transaction 2 marks the seat as sold.
If she never pays, a periodic job releases expired holds. Mateo hears straight away that the seat is taken: no waiting, no locks held for seconds.
What changed
- Transaction 1: hold the seat (expires at 20:15)
- Charge outside the transaction
- Transaction 2: confirm
- Expired holds are released
Step 13 of 13
Summary
Recap
Read-then-write under concurrency causes races and lost updates.
Fix it with row locks, one atomic UPDATE or version checks.
Isolation levels trade correctness against waits and retries.
Always lock in the same order and back your rules with constraints.
Short transactions: while waiting for a payment, hold with an expiry, never with a lock.
← → step · R replay · Space pause
You will learn
- Why read-then-write oversells (covered)
- Row locks and atomic UPDATEs (covered)
- Optimistic concurrency and isolation levels (covered)
- Phantoms and write skew (covered)
- Deadlocks, and constraints as a safety net (covered)
- Short transactions with holds that expire (covered)