HardSQL#1646 min readJul 11, 2026
Recovery
practice
Why interviewers love this: recovery is where ACID stops being a slogan and becomes a real safety net.
Question: What does recovery mean in SQL transactions?
Answer: Recovery is the database’s way of getting back to a correct state after a crash, error, or aborted transaction. It means the database can undo work that did not commit and redo work that did commit, so data stays consistent. In simple terms: unfinished changes disappear, finished changes survive.
Interview-Ready Answer: I think of recovery as the database’s repair system for transactions. It uses transaction logs, often with write-ahead logging, so it can undo incomplete work and redo committed work after a failure. That is what protects atomicity and durability: either the whole transaction happens, or none of it does.
%22%20stroke%3D%22%234f46e5%22%20stroke-width%3D%221.5%22%2F%3E%3Ctext%20x%3D%22500%22%20y%3D%22308%22%20text-anchor%3D%22middle%22%20font-size%3D%2215%22%20font-weight%3D%22bold%22%20fill%3D%22%23ffffff%22%20font-family%3D%22Segoe%20UI%2C%20Arial%2C%20sans-serif%22%3ETransaction%20Recovery%3C%2Ftext%3E%3C%2Fsvg%3E)
What recovery is
Recovery is not usually a SQL command. It is the database engine’s internal process for restoring correctness after a crash, power loss, process kill, or transaction abort. The goal is simple: the database should never look half-updated. A transaction is either fully visible after COMMIT, or fully removed after rollback/recovery.
How it works under the hood
- The database records each change in a log. A log is an append-only history of what a transaction did.
- With write-ahead logging or
WAL(meaning the log is written before the data pages), the engine flushes the log first. This is the key rule: if the power dies, the database still has enough history to repair itself. - When a transaction commits, the commit record is forced to durable storage. The database may batch several commits together in one flush; this is called group commit.
- A checkpoint is a marker saying, “everything before here is known and safe enough.” Checkpoints shorten restart time because the engine does not need to scan the entire history.
- After a crash, the engine reads the log from the last checkpoint forward.
- It redoes committed changes that may not have reached the data file yet.
- It undoes unfinished changes from transactions that never committed.
Why this matters
Recovery is what makes COMMIT mean more than “the app thinks it worked.” It gives you two guarantees: committed work survives, and incomplete work is not left behind. Without recovery, a crash could leave one table updated and another table stale, which is a classic corruption pattern.
Recovery vs common transaction actions
| Action | Meaning | Recovery impact |
|---|---|---|
| COMMIT | Make changes permanent | Redo if needed |
| ROLLBACK | Cancel the whole transaction | Undo changes now |
| SAVEPOINT | Mark a partial undo point | Undo only part of txn |
| CHECKPOINT | Record a safe restart point | Speeds crash recovery |
Performance and practical numbers
- Commit cost: usually one log flush, so latency is often tied to disk
fsync; on SSDs this is commonly a few milliseconds, but it can spike under load. - Crash recovery time: roughly proportional to how much log exists after the last checkpoint, not the total database size. Scanning 100 MB of log is much faster than scanning a 500 GB data file.
- Savepoint rollback: cost is proportional to the work after the savepoint, so it is usually cheap for small partial mistakes.
- Space cost: the log grows continuously until archived or truncated, so recovery trades disk space for safety.
Important edge cases
- A crash after the log is flushed but before the data pages are written is okay; recovery can redo.
- A transaction that never committed must not become visible after restart.
- A failed SQL statement does not always erase the whole transaction automatically; some databases leave the transaction in an aborted state until you roll it back.
- Recovery is about database state, not application state. If the app already sent an email or charged a card outside the transaction, the database cannot magically undo that.
Memory mental model: write the receipt first, then move the furniture. The receipt is the log; the furniture is the table data.
Real-World Example: Imagine an e-commerce checkout service that creates an order, subtracts inventory, and writes a payment record. A developer splits those writes across separate autocommit statements instead of one transaction. Then the service crashes after the payment row is written but before the order row is inserted. After restart, recovery does its job correctly for the database, but the application logic is already broken: the user may be charged, yet no order exists. Symptoms look like missing order IDs, mismatched totals in reconciliation jobs, and support tickets saying, “I paid, but my cart vanished.” The lesson is that recovery only protects what you put inside a real transaction; it cannot fix work that was never grouped together.
SQL
-- Transaction recovery demo using a savepoint.
-- This is standard SQL style and shows how a database can undo
-- only the risky part of a transaction while keeping earlier work.
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
account_id INTEGER PRIMARY KEY,
owner VARCHAR(50) NOT NULL,
balance DECIMAL(12,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts (account_id, owner, balance) VALUES
(1, 'Alice', 100.00),
(2, 'Bob', 50.00),
(3, 'Carol', 25.00);
-- Start one unit of work so the change is all-or-nothing.
BEGIN TRANSACTION;
-- Step 1: debit Alice.
UPDATE accounts
SET balance = balance - 30.00
WHERE account_id = 1;
-- Save a recovery point after the safe part.
SAVEPOINT after_debit;
-- Step 2: we accidentally credit the wrong person.
UPDATE accounts
SET balance = balance + 30.00
WHERE account_id = 2;
-- Edge case: we discover the mistake before committing.
-- We do not want to lose Alice's debit, only the bad credit.
ROLLBACK TO SAVEPOINT after_debit;
-- Fix the mistake correctly.
UPDATE accounts
SET balance = balance + 30.00
WHERE account_id = 3;
COMMIT;
-- Final state after recovery-style correction.
SELECT account_id, owner, balance
FROM accounts
ORDER BY account_id;
-- Failure-path note:
-- The CHECK constraint prevents negative balances. If you uncomment
-- the line below, many engines will reject it and the transaction
-- would need a rollback instead of a commit.
-- UPDATE accounts SET balance = balance - 1000.00 WHERE account_id = 1;Follow-up & Tricky Questions:
- How is recovery different from rollback? Rollback is the action of canceling a transaction now; recovery is the broader restart process that fixes the database after a failure, including undo and redo work.
- What is write-ahead logging? It means the log record is persisted before the actual data pages are written. That ordering is what makes crash recovery possible and reliable.
- What does a checkpoint do? It gives the engine a known restart point, so crash recovery only needs to scan the log after that point instead of replaying everything from the beginning of time.
- What happens if the server crashes after COMMIT but before the data file is updated? The transaction still counts as committed if the commit record is durable; recovery will redo the change from the log.
- Why does recovery care about isolation? It mostly does not enforce isolation by itself; isolation controls what other transactions can see while recovery mainly restores committed truth after failure.
- Tricky: Does COMMIT always mean the data is already on the table file? No. It means the commit is durable in the log or equivalent durable storage, and the engine may write the table pages later.
- Tricky: If one statement in a transaction fails, is the whole transaction always gone? Not always. Many databases mark the transaction as failed or aborted, but you usually still need an explicit rollback to cleanly end it.
- Tricky: Can recovery fix a bad email or payment sent by application code outside SQL? No. Recovery only repairs database state; side effects outside the database need application-level compensation.
Common Mistakes:
- Thinking COMMIT means all files are physically updated immediately. Correction: the log is what must be durable first; data pages can be written later.
- Confusing rollback with recovery. Correction: rollback is a user-visible cancel; recovery is the engine’s crash-repair process.
- Using autocommit for multi-step business work. Correction: wrap related statements in one transaction so recovery can treat them as a single unit.
- Forgetting that external side effects are not rolled back. Correction: emails, HTTP calls, and payment gateways need their own compensation plan.
Memory Hook: Receipt before furniture. The database writes the receipt first, then moves the data, and on a crash it uses the receipt to undo or redo safely.
Cheat Sheet:
- Recovery = restore correctness after crash or abort.
- Undo incomplete transactions; redo committed ones.
- WAL means log first, data later.
- Checkpoint = shorter restart time.
- Savepoint = partial undo inside one transaction.
- Recovery protects database state, not outside systems.
Practice Tasks:
- Create two tables and move money between them inside one transaction, then use a savepoint to undo only the second step.
- Insert a row, rollback the transaction, and verify that no row remains after the rollback.
- Write a small transfer script with a CHECK constraint for nonnegative balances, then reason about what would happen if the second update failed.