Pairs with Lesson 2: Isolation Levels and Check-Then-Act Races and Lesson 22: What COMMIT Actually Does.
| 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.
| Term | Meaning |
|---|---|
| WAL | Write-ahead log: a sequential stream of redo records (every change + commit records) in pg_wal. The log is the truth; table files are a cache of it. |
| Commit record | The one WAL record that makes a transaction durable and visible. "COMMITTED" = this record is in the WAL on this machine's storage. |
| fsync | The call that waits until the storage device confirms persistence (past the OS page cache). The whole meaning of "durable". |
| Durability window | The gap between write() (data in OS cache/RAM) and fsync() (on device). Everything inside it is lost on power failure. |
| Group commit | Concurrent commits share one WAL flush, so throughput isn't one fsync per transaction; latency still is per-transaction. |
| synchronous_commit | on (default): ack after WAL fsync. off: ack before fsync — recent commits can be lost on crash, even on failover. remote_write/remote_flush/remote_apply: wait for standby at that level (sync replication). |
| full_page_writes | First change to a page after checkpoint writes a whole 8KB page image to WAL, so recovery can survive torn pages. Why big UPDATEs amplify WAL. |
| Checkpoint / crash recovery | Recovery replays WAL forward from the last checkpoint's redo point — never rolls back. Data pages are flushed by background writer/checkpoints, not at commit. |
| LSN | Position of a record in the WAL. Replication lag, standby ack granularity, and "what's durable" are all positions on this one log. |
1. Append commit record to WAL (shared memory)
2. fsync the WAL tail (synchronous_commit = on)
3. Reply "COMMITTED"
-- data pages still sit in shared_buffers;
flushed later by background writer/checkpoint --Read the ack precisely: it promises a position in the log on a specified device, nothing more. Every durability incident is a scope mismatch between what the ack meant and what the caller assumed.