Pairs with Lesson 2: Isolation Levels and Check-Then-Act Races.
| Term | Meaning |
|---|---|
| READ COMMITTED | Postgres default. Each statement sees data committed as of when that statement starts. No row locks on plain reads. |
| REPEATABLE READ | Whole transaction sees a consistent snapshot from when the transaction started. Prevents non-repeatable reads, still allows some anomalies (e.g. write skew). |
| SERIALIZABLE | Strongest level — behaves as if transactions ran one-at-a-time. Detects conflicts and aborts one transaction with a serialization-failure error; caller must retry. |
| Check-then-act race | Reading a value, deciding in application code, then writing — with no guarantee nothing else changed the value in between. |
| Lost update | Two transactions read the same value, both compute a new value from it, second write silently overwrites the first's intent. |
| Write skew | Two transactions read overlapping data, each writes to a different row, but the combination violates an invariant neither transaction alone would violate. Not caught by REPEATABLE READ — needs SERIALIZABLE or explicit locking. |
| Row lock | Taken automatically by UPDATE/DELETE on the specific row touched, at the moment of the write — not on plain SELECT. |
If a decision depends on current data, put the condition in the UPDATE's WHERE clause — don't read, decide in app code, then write separately.
UPDATE t SET col = col - n WHERE id = ? AND col >= n;Check rows-affected: 0 means the condition failed against current data, not stale app-side data.
Write skew (invariant spans multiple rows, e.g. "at least one doctor on call") needs either SELECT ... FOR UPDATE to lock the rows read, or SERIALIZABLE isolation with retry logic on serialization failure.