Preventing lost updates

Housemates put cash in a tin; the sheet shows the total.

In the house	In the database
two housemates read "£40", add their £10 in their heads, both write "£50"	read-modify-write loses one (step 1)
hand the tenner to the treasurer and say "add ten"	atomic UPDATE on the newest total (step 3)
take the sheet off the fridge until you have written	FOR UPDATE: others wait (steps 5–6)
"replacing line 7": refused if the sheet has reached line 8	version check, UPDATE 0 (steps 7–8)
the treasurer voids an entry based on an older sheet; you start again	40001 and a whole retry (steps 9–10)

In real terms: a value your code computed from an earlier read is a guess; let the database apply the change, lock, compare a version, or retry.

Breaks down when: a housemate who sees "£50" where she wrote "£50" might notice the missing tenner. Wrong inference: "an overwrite shows up as an error." At Read Committed Postgres reports UPDATE 1 for the overwrite.

Contrasting case: a housemate who lives alone. No one else writes, so read, add and write is always right.

4.1 ExamHall: a check-in went missing

EXH-1220: two invigilator tablets checked candidates into room R2 at the same moment, and the room count was one short. The tablet code read the count, added 1 and wrote it back inside a transaction. MR !1420 moves the addition into the UPDATE. Same forced order:

4.1 two invigilator tablets check in one candidate each; room R2 has 40 checked in (Read Committed)
  before (read, +1 in the app, write, in a transaction)  both read 40/40 · checked_in 41 after 2 check-ins · B waited for A's lock, then wrote its own 41
  after MR (UPDATE SET checked_in = checked_in + 1)       checked_in 42 · B waited, then added 1 to A's committed row
  alternative (SELECT ... FOR UPDATE, +1 in the app)      checked_in 42 · B's SELECT waited for A's commit, then read 41

The transaction made B wait, yet B still wrote the value it had computed (step 2). The atomic statement and the locking read both worked from A's committed row (steps 3, 5).

4.2 Ticketing: two staff edit one price

Both forms were loaded at version 4; each save runs UPDATE events SET price = $1, version = version + 1 WHERE id = 12 AND version = $2.

4.2 two box-office staff edit one event's price; both forms were loaded at price 45, version 4
  one after the other   A sets 50: UPDATE 1 -> 200 saved · B sets 55: UPDATE 0 -> 409 changed by someone else · row price 50 v5
  overlapping saves     A: UPDATE 1 · B waited for A's lock, re-checked version = 4 on A's row: UPDATE 0 · row price 50 v5
  handler ignores the count  B is told "200 saved" · row price 50 v5 · B's 55 was never written

The version turned a silent overwrite into a 409 in both orders (steps 4, 7). Ignoring the count told B a save succeeded that never happened (step 8).

4.3 Stricter levels

The 4.1 addition at Repeatable Read, then a limit of 4 tickets per customer: the customer holds 2, and two orders of 2 each count, check and insert.

4.3 the same +1 at REPEATABLE READ, then a limit that spans rows
  RR, no retry          B: ERROR 40001 "could not serialize access due to concurrent update" · checked_in 41
  RR, whole-transaction retry   B: attempt 1 40001, attempt 2 committed · checked_in 42
  READ COMMITTED  order A +2 · order B +2 · customer holds 6 (limit 4)
  SERIALIZABLE    order A +2 · order B attempt 1 40001, attempt 2: limit reached · customer holds 4 (limit 4)
  SERIALIZABLE    order B attempt 1 failed at its INSERT: 40001 "could not serialize access due to read/write dependencies among transactions"

Which level aborts which conflict is db-isolation-anomalies' (linked). What this lesson adds is the retry: each second attempt read the other commit, so the addition landed and the order over the limit was refused (steps 9–10).