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
LucíaMateoServer 1Server 1Server 2Server 2tickets DBPaymentsproviderPaymentsseat 12 · free: 1Buy a seatn = SELECT free FROM seats WHERE id = 12if n > 0:  UPDATE seats SET free = n - 1  INSERT INTO bookings …n - 1 is computed in the app, not the DBseat 12 · free: 0 · 2 bookings!booked ✓booked ✓What happened1. Lucía readsfree = 12. Mateo readsfree = 13. Lucía writesfree = 04. Mateo writesfree = 0 againseat 12 · free: 0 · 2 bookings!read committedbooked ✓booked ✓Inside a transactionBEGIN  SELECT free … → 1 (both)  UPDATE … ; INSERT …COMMITall-or-nothing ≠ one at a timerow 12 locked by Lucíasold outbooked ✓waits for the lockseat 12 · free: 0sold outbooked ✓One atomic statementUPDATE seats SET free = free - 1  WHERE id = 12 AND free > 0Lucía1 row → bookedMateo0 rows → sold outseat 12 · free: 0 · version 5v4 stalev4 → v5 ✓Compare and writereadfree = 1, version = 4writefree = 0, version = 5only ifWHERE version = 4Mateo0 rows → re-read, retryserializablesold outbooked ✓Isolation levelsread committedno dirty readscheapestsnapshot / RR+ fixed picture on readssome abortsserializableas if one at a timemost abortssame name, different guarantee per enginebookings: seat 12 × 2booked ✓booked ✓Nothing to lockSELECT … FROM bookings  WHERE seat = 12 FOR UPDATE → 0 rowsINSERT INTO bookings (seat = 12)both see 0 rows → both insertLucía: A · waits BMateo: B · waits AabortedA → BA cycle of waitsLucíalocks A … waits for BMateolocks B … waits for Acyclethe DB aborts onefixlock the lowest id firstseat 12 · free: 0UNIQUE (concert, seat)duplicatebooked ✓locked 4 s · 35 waiting4 s per chargewaiting…paying 4 s…35 in lineheld: Lucía · 20:15takenpayingThree short steps1hold: held_until = 20:152charge, no transaction open3confirm: sold_to = Lucíajobreleases expired holdsnow: 20:05 · 10-minute holdseat 12 · free: 0sold outbooked ✓
  • 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.

You will learn

  • Why read-then-write oversells
  • Row locks and atomic UPDATEs
  • Optimistic concurrency and isolation levels
  • Phantoms and write skew
  • Deadlocks, and constraints as a safety net
  • Short transactions with holds that expire