After an unexpected server crash, a relational database keeps the effects of some transactions and throws others away. How does it decide which is which, and what does that mean for an application that already received a successful commit acknowledgement?
answer
- log decides, not data files
- commit record durable equals committed
- winners redone, losers undone
- dirty pages of losers can already be on disk
- unknown-outcome commits need idempotent retry
basics
~20 sThe transaction log decides. A transaction whose commit record reached durable storage before the crash is kept, and reapplied if needed. Every transaction without a durable commit record is rolled back completely. So an acknowledged commit survives; in-flight work disappears atomically, never half-applied.
solid answer
~50 sRecovery is driven by the transaction log, not by whatever happens to be sitting in the data files. On restart the engine scans the log and classifies transactions as **winners** (a commit record is present and durable) and **losers** (no commit record: they were in flight, or already aborting, when the crash hit). - **Winners** are guaranteed durable, but some of their changes may not have reached the data files yet because the modified pages were still cached in memory. Recovery reapplies those changes from the log. - **Losers** must be erased, and some of their changes *may* already sit in the data files, because a dirty page can be written to disk before its transaction commits. Recovery actively undoes them. The application contract is exactly the ACID one: if COMMIT returned success, those changes are present after restart. If the client never got the acknowledgement, the outcome is unknown to the client but still atomic in the database: all or nothing, never partial.
code
text · 8 linesLSN 100 T1 UPDATE page 7 old=A new=B
LSN 110 T2 UPDATE page 9 old=X new=Y
LSN 120 T1 COMMIT
LSN 130 T2 UPDATE page 7 old=B new=C
<< crash >>
recovery: keep T1 (commit record present)
undo all of T2 (no commit record)go deeper
State the rule cleanly: committed transactions survive, uncommitted ones are rolled back entirely, and the transaction log is what decides.
Add why both directions are needed: committed changes may be missing from the data files, and uncommitted changes may already be in them, because page writes are lazy and independent of commit.
Bring in the client-facing consequence: the ambiguous commit, idempotent retry design, and the availability gap while recovery runs.
Frame it as the durability contract you are buying and its cost: what commit acknowledgement means to callers, how recovery time interacts with the availability objective, and where non-database side effects fall outside the guarantee.
## Why a crash is not a clean state A database keeps recently used data pages in a memory cache (the buffer pool) and writes them back to the data files lazily, in the background. That means at any instant the files on disk are an arbitrary mixture: some committed changes have not been written yet, and some uncommitted changes already have been. A crash freezes that mixture. Recovery's job is to turn it back into a state that satisfies atomicity (a transaction is all or nothing) and durability (an acknowledged commit is permanent). ## The log is the authority, not the data files Before any change is allowed to reach the data file, the log record describing that change must already be durable on disk. This ordering rule is what makes the log the authoritative history: whatever state the data files ended up in, the log knows every change that was made and by whom, and the log knows which transactions committed. Commit is defined by the log, not by the data files. A transaction is committed exactly when its commit record is safely on durable storage. Nothing else is required at commit time: the transaction's data pages may still be dirty in memory and are allowed to be lost by the crash, because they can be reconstructed from the log. ## Winners and losers During recovery the engine reads the log and sorts transactions into two buckets. **Winners** are transactions with a durable commit (or end) record. Their effects must exist after recovery. The engine reapplies any of their logged changes that are missing from the data files. This is what makes durability real: the commit acknowledgement was sent as soon as the commit record hit the disk, so recovery has to honour it. **Losers** are transactions with no commit record. Either they were still running, or they had begun rolling back, or the client had already issued a rollback. Whatever fraction of their work reached the data files must be removed. Recovery walks their log records backwards and reverses each change using the before-images recorded in the log. Crucially, a loser is not merely ignored. Ignoring it would be wrong, because a dirty page holding its changes may well have been flushed to the data file minutes before the crash by ordinary background writing or by cache pressure. ## What the application sees Three cases matter to a client. 1. **COMMIT returned success.** The changes are guaranteed present after restart. This is the promise the database sells, and it is the reason commits cost a durable write. 2. **The transaction was still open** when the crash happened (nothing committed). It is gone entirely, as if it never ran. No partial rows, no half-applied multi-statement work. 3. **The connection died at the moment of commit** and the client never saw the answer. The database's own state is still atomic, but the client cannot tell which way it went. This is why retry logic needs idempotency (a natural key, a client-supplied request identifier, or a follow-up read) rather than blind re-execution. A related consequence: any work the application did outside the database in the same logical operation, such as sending an email or calling a payment API, is not covered by recovery. Database recovery restores database state only, which is why outbox patterns exist. ## Recovery time and availability Recovery is not instantaneous. The engine must read a stretch of the log and reapply and reverse changes before it can safely accept queries, so the service is unavailable during that window. The size of that window depends mainly on how much work happened since the last checkpoint, that is, how far behind the data files had fallen. This is why a crash in a heavy write workload can take noticeably longer to come back than a crash in an idle system, and why operators care about bounding it when they have a recovery-time objective. ## The mental model to carry The data files after a crash are untrustworthy and incomplete. The log is trustworthy and complete. Recovery replays the log against the files until the files agree with the log's version of history, then removes everything the log says was never committed. Anything a client was told about a commit is preserved; anything it was not told about is undone as a unit.
- A client sent COMMIT, the server crashed, and the client saw a connection reset. Is the transaction committed?The database's answer is definite but the client cannot know it without checking. If the commit record reached durable storage before the crash, recovery keeps the transaction; otherwise it is undone entirely. The client must resolve the ambiguity by reading back a unique marker (an idempotency key, a natural unique constraint, or the row itself) rather than blindly retrying, because a blind retry can double-apply the work.
- Why does recovery have to undo uncommitted transactions at all, rather than just ignoring them?Because their changes can already be in the data files. Pages modified by a still-open transaction are dirty in the buffer pool and may be evicted or flushed by background writing at any time, well before commit. Once such a page is on disk, doing nothing would leave uncommitted data permanently visible, which breaks atomicity.
The data files are a half-finished whiteboard; the log is the meeting minutes. After the lights go out, you do not trust the whiteboard, you rewrite it from the minutes and erase anything the minutes never approved.
saying these in an interview costs you the question
- Saying a commit is durable only once the table's data pages have been written to disk
- Assuming uncommitted changes cannot possibly be in the data files, so undo is unnecessary
- Claiming the database can be queried immediately at process start, with recovery happening later in the background
- Treating a lost connection at commit time as a guaranteed rollback and retrying blindly