When a transaction commits, what must reach durable storage before the client is told it succeeded, and which process writes the modified data pages to the table files?
answer
- Commit flushes log, not data pages
- Log writer keeps buffers moving; group commit shares a flush
- Dirty pages written later by bgwriter / checkpointer
- Log before page — never a page ahead of its log
- Commit latency ≈ log device latency
basics
~20 sOnly the transaction log records for that transaction must be durable at commit. The modified data pages stay dirty in the buffer pool and are written later by the background writer or the checkpointer, so commit costs one sequential log flush rather than scattered page writes.
solid answer
~50 sAt commit the engine must guarantee it can reproduce the change after a crash. It achieves that by making the **transaction log** durable — the log records describing the change plus the commit record — with a single sequential write and flush. The log writer keeps the shared log buffers moving so most of that work is already done; the committing backend waits only for its own commit record, and many concurrent commits share one flush (group commit). The **data pages** modified in the buffer pool are not written at commit. They stay dirty and are written later by the background writer, which trickles pages out ahead of eviction demand, or by the checkpointer during its sweep. The page may be rewritten many times before it ever hits disk. That asymmetry is the whole point: one sequential flush per commit group instead of many random page writes per transaction, and if the server crashes, replaying the log rebuilds the pages that never got written.
code
text · 7 linesbackend: modify page in buffer pool -> page marked dirty
backend: append log records to shared log buffer
log writer: flush log buffer to disk (shared by concurrent commits)
backend: COMMIT returns once its commit record is durable
...later...
bgwriter: write some dirty pages out ahead of eviction
checkpointer: flush all pages dirty as of checkpoint; log before it recyclablego deeper
Say clearly that only the log is flushed at commit and that data pages are written later by background processes.
Add the roles of log writer, background writer and checkpointer, group commit, and the log-before-page ordering rule.
Reason about commit latency being log-device latency, recovery time as a function of log since the last checkpoint, and what relaxed-durability settings actually trade away.
Frame it as the core amortisation contract of the storage engine: sequential durable log on the critical path, batched random page writes off it, with checkpoint cadence as the dial between steady-state I/O and recovery time.
## The question behind the question Interviewers ask this to see whether you believe a commit writes your rows to the table file. It does not, and understanding why is the key to the entire background-process architecture. ## What durability actually requires Durability means: after the client is told "committed", a crash must not lose the change. There are two ways to achieve it. Write the modified pages themselves, or write a *description* of the change from which the pages can be rebuilt. The second is far cheaper, and it is what every mainstream engine does. The description lives in the transaction log. It is appended sequentially, so the storage device does one contiguous write instead of many scattered ones. At commit, the engine ensures every log record for that transaction, including the commit record, has been written *and* flushed past any volatile cache. Only then does the client hear success. ## Who does the log work Backends append log records into a shared in-memory log buffer as they modify data. A dedicated **log writer** process moves those buffers out to the log file continuously, so at commit time most of the transaction's log is often already durable and the committing backend waits only for the tail. The flush is also shared. If ten transactions commit within the same short window, one flush can cover all of them — *group commit*. This is why commit throughput scales far better than "one fsync per transaction" would suggest, and why the log device's latency, not its bandwidth, tends to be the limiting factor. ## Who writes the data pages The rows themselves live in pages in the shared **buffer pool**. Modifying a row modifies the in-memory page and marks it dirty. Nothing forces that page to disk at commit. Two background processes eventually write it: - The **background writer** trickles dirty pages out continuously, aiming to keep clean, reusable buffers available so a backend needing a free buffer does not have to write one itself. - The **checkpointer** performs a periodic sweep that guarantees everything dirty as of a point in time is on disk, which is what allows older log to be discarded and bounds recovery work. A hot page — a counter row, an index root — may be modified thousands of times and written once. That coalescing is pure profit, and it is only possible because the log already guarantees durability. ## The ordering rule that makes it safe There is one constraint: a data page may not reach disk before the log records describing its changes. If a half-updated page were on disk with no log to explain it, recovery could not repair it. So a page write is preceded by flushing the log up to that page's latest change. Background writer and checkpointer both respect this; it is the reason the log writer is not merely an optimisation. ## What recovery does with all this After a crash, the engine starts from the last checkpoint — the point where the data files are known consistent — and replays log forward, reapplying changes to pages that never got written, then undoing effects of transactions that never committed. Committed transactions survive because their log records are durable; uncommitted ones vanish. A longer interval between checkpoints means more log to replay and slower startup, which is exactly the tradeoff the checkpointer's schedule expresses. ## Practical consequences 1. **Commit latency is log-device latency.** Putting the log on fast, low-latency storage matters more than putting the data files there. Under concurrency, group commit amortises it further. 2. **Relaxing durability changes the answer.** If an engine is configured to acknowledge commits before the flush completes, throughput rises and a crash can lose the most recent committed transactions. That is a deliberate durability tradeoff, not a bug — but it must be a conscious one. 3. **Write amplification is not per-commit.** A workload updating the same rows repeatedly generates far less page I/O than its transaction count suggests. 4. **A crash loses nothing committed, but startup is not instant.** Recovery time is a function of log volume since the last checkpoint. ## The one-sentence version Commit makes the *story* durable; the background processes make the *state* durable, later, at their own pace, in the order the log allows.
- Why is it safe for a committed change to exist only in the log and in a dirty memory page?Because recovery can reconstruct the page from the log. The log record describes the change precisely, and it is durable before the client is told the commit succeeded, so replay after a crash reapplies it to the page read from the data file. The rule that a page is never written to disk ahead of the log records describing it guarantees the log always explains whatever state the data file is in.
- What limits commit throughput in this design, and how does the engine work around it?The latency of making the log durable — the flush to the log device — is the bottleneck, not the amount of data written. Engines amortise it with group commit, where many transactions committing in the same window are covered by a single flush, so throughput rises with concurrency. Beyond that, the remaining levers are faster low-latency log storage or deliberately relaxing durability so commits are acknowledged before the flush completes, which risks losing recent commits on a crash.
Commit is writing the order down in a ledger; cooking and plating happen afterwards. If the kitchen burns down, the ledger is enough to redo every order that was accepted.
saying these in an interview costs you the question
- Claiming commit writes the changed rows into the table files
- Thinking each transaction forces its own separate flush regardless of concurrency
- Believing a dirty page can be written before its log records
- Assuming a crash loses committed transactions because their pages were not written
- Treating the background writer as the process that guarantees durability