HardSQL#1658 min readJul 11, 2026
Transaction Logs
practice
Hook: A transaction log is the database’s receipt book: if the server crashes, it is how the engine remembers what was safely done and what still needs to be undone.
Question: What are transaction logs in SQL databases, and why do they matter?
Answer: Transaction logs are append-only records of database changes that help the system recover after a crash and keep committed work durable. They are a core part of ACID: Atomicity means all-or-nothing, and Durability means a committed change survives failure. In many engines this is implemented with write-ahead logging (WAL), which means the log is written before the data pages are finally flushed.
Interview-Ready Answer: “A transaction log is the database’s durable change record. The key idea is write-ahead logging: before the engine says a transaction is committed, it flushes the log to stable storage. That lets the database redo committed work and undo incomplete work after a crash, which is why transactions stay atomic and durable.”
%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%20Logs%3C%2Ftext%3E%3C%2Fsvg%3E)
Detailed Explanation: Think of the transaction log as the database’s time-ordered notebook. Every change is appended in the order it happened, so the engine can later replay the notebook from a safe point. The most important mental model is simple: log first, data later. That single rule is what keeps committed changes safe after a power loss.
What it is
A transaction log is usually an append-only file or set of files that records enough information to reconstruct changes. Different engines use different names: WAL in PostgreSQL-style systems, redo log in some others, and transaction log in SQL Server-style terminology. The exact format is engine-specific, but the goal is the same: protect committed work.
How it works under the hood
- The application starts a transaction with
BEGIN. - The database records each change in memory and creates log records that describe the old and/or new values.
- Before commit, the log records are flushed to durable storage.
Flushedmeans written all the way to stable storage, not just sitting in RAM. - The engine writes a commit record. At that moment the transaction is considered committed.
- Later, data pages are written to the table files in the background. These pages can lag behind the log.
- If the server crashes, recovery reads the log: it
redoscommitted work that had not reached the table files yet andundosincomplete work that never committed.
Dirty pages are pages in memory that were modified but not yet written back to disk. The log makes it safe for those pages to trail behind, because recovery can rebuild the truth from the log.
Why databases use it
Without a transaction log, a crash could leave half a payment saved, half an inventory update missing, or a row updated in one table but not another. The log gives the database a reliable “before/after” trail so it can choose the correct outcome after failure. It also lets the engine group many commits together, which is much faster than forcing each row update directly to disk.
Transaction log vs audit log vs table data
| Item | Stores | Goal | Read pattern |
|---|---|---|---|
| Transaction log | Changes and commits | Recovery | Sequential append |
| Table data | Current rows | Queries | Random read/write |
| Audit log | Business events | Traceability | Query/report |
The big gotcha is that the engine’s transaction log is not the same as an application audit table. An audit table is something you design for humans and reporting. The internal transaction log is something the database uses to recover correctly, and you usually do not query it directly in normal SQL.
When and why to care
- Crash recovery: after a power loss, the log decides what must be redone or undone.
- Consistency: multi-step updates stay all-or-nothing.
- Performance: sequential log writes are cheaper than forcing random writes to every table page immediately.
- Replication: many systems ship log records to replicas so they can replay the same changes.
Performance note: log appends are usually amortized O(1) per change, because appending is sequential. Recovery time is roughly proportional to the amount of log replay since the last checkpoint. A checkpoint is a safe point where enough dirty pages have been flushed so recovery does not need to start from the very beginning. On fast SSDs, a single flush may still cost around 1–10 ms; group commit lets many transactions share one flush, which is why throughput can jump dramatically under load.
Edge cases and gotchas: long-running transactions can keep log records alive and make the log grow. If checkpointing falls behind, the log file can expand a lot. Also, “committed” does not always mean “the table file already changed”; it means the log is durable enough that the change can be recovered. Some databases offer delayed durability or relaxed flush settings, but that trades safety for speed and can lose the last few transactions in a crash.
Memory check: if you remember only one thing, remember this: the log is the source of truth during recovery, not the table file on disk.
Real-World Story: Imagine a checkout service for an e-commerce app. One transaction must place the order, charge the card, and decrease inventory. The team uses a transaction so those steps succeed together or fail together. If the machine crashes after the card is charged but before inventory is updated, the transaction log lets the database recover to a consistent state instead of leaving a ghost order.
Here is what a misunderstanding looks like in production: an engineer assumes “the INSERT happened, so the order is safe,” but the transaction never committed. After a restart, the row is gone because the log showed the transaction was incomplete. Users see missing orders, support sees payment receipts, and logs show confusing messages like “charged OK” followed by no committed database record. That is the exact kind of bug transaction logs are built to prevent when used correctly.
Symptoms: duplicate charges, missing orders, inventory mismatches, and recovery logs that mention redo/undo activity. User impact: customers get charged without a visible order, or orders appear in the app but disappear after a crash because the app wrote only part of the workflow outside one transaction.
SQL
-- A small, runnable teaching example for transaction logs in practice.
-- This shows the database idea with a real SQL transaction plus an
-- application-level audit table, because the engine's internal WAL/redo
-- log is usually not queryable from plain SQL.
CREATE TABLE accounts (
account_id INTEGER PRIMARY KEY,
owner VARCHAR(50) NOT NULL,
balance INTEGER NOT NULL CHECK (balance >= 0)
);
CREATE TABLE transaction_audit (
log_id INTEGER PRIMARY KEY,
event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
event_type VARCHAR(20) NOT NULL,
details VARCHAR(200) NOT NULL
);
INSERT INTO accounts (account_id, owner, balance) VALUES
(1, 'Ava', 100),
(2, 'Ben', 50);
INSERT INTO transaction_audit (log_id, event_type, details) VALUES
(1, 'SEED', 'Created two demo accounts');
-- Successful transaction: both updates must happen together.
BEGIN TRANSACTION;
INSERT INTO transaction_audit (log_id, event_type, details)
VALUES (2, 'BEGIN', 'Transfer 30 from Ava to Ben');
UPDATE accounts
SET balance = balance - 30
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 30
WHERE account_id = 2;
INSERT INTO transaction_audit (log_id, event_type, details)
VALUES (3, 'COMMIT', 'Transfer 30 completed');
COMMIT;
-- Failure path: we detect a risky transfer and roll back before it becomes real.
BEGIN TRANSACTION;
SAVEPOINT risky_transfer;
INSERT INTO transaction_audit (log_id, event_type, details)
VALUES (4, 'BEGIN', 'Attempt transfer 999 from Ava to Ben');
-- In a real app, the client would check the balance before continuing.
-- We read first to show the precondition that would make the change unsafe.
SELECT account_id, owner, balance
FROM accounts
WHERE account_id = 1;
-- Instead of making a bad change and letting it hit the CHECK constraint,
-- we abort cleanly. This is the idea behind undoing incomplete work.
ROLLBACK TO SAVEPOINT risky_transfer;
INSERT INTO transaction_audit (log_id, event_type, details)
VALUES (5, 'ROLLBACK', 'Transfer 999 aborted: insufficient funds');
COMMIT;
SELECT account_id, owner, balance
FROM accounts
ORDER BY account_id;
SELECT log_id, event_time, event_type, details
FROM transaction_audit
ORDER BY log_id;Follow-up & Tricky Questions:
- What is the difference between a transaction log and an audit log? The transaction log is an internal recovery structure used by the engine; an audit log is a business-level record you design for reporting, compliance, or debugging.
- Why do databases write the log before the data? Because if the data page reaches disk first and the server crashes, the database could not safely tell whether the change was committed. Logging first makes recovery deterministic.
- What happens during crash recovery? The engine scans the log after the last checkpoint, redoes committed changes that were not yet persisted in table files, and undoes incomplete transactions.
- Why can commit be fast even when tables are large? Commit only needs the durable log record, not a full rewrite of every affected table page. The actual data pages can be flushed later.
- Can a transaction log grow forever? Not if checkpoints, backups, and log truncation are working correctly. Long transactions or replication lag can delay cleanup and make the log grow temporarily.
- Is a transaction durable as soon as the app gets “success”? Only if the database has flushed the commit record to durable storage. A client-side success message without a committed transaction is not enough.
- Tricky: If the table row is on disk, does that mean the transaction committed? No. The row may be there because of an earlier flush, but the log is what proves the transaction was committed and recoverable.
- Tricky: Can I query the internal transaction log with normal SQL? Usually no. Most engines expose metadata, backups, or system views, but the raw recovery log is not a normal table you query.
- Tricky: Does rollback delete the log? No. Rollback itself is recorded, and the log is part of how the database proves the undo happened correctly.
Common Mistakes:
- Mixing up transaction logs and audit logs. Correction: the internal log is for recovery; an audit table is for business history.
- Thinking COMMIT means all table files are already updated. Correction: COMMIT means the durable log record exists; table pages may be written later.
- Ignoring long transactions. Correction: long-running transactions can hold back cleanup and make the log grow, which hurts recovery and storage.
- Assuming a successful app response guarantees durability. Correction: only a flushed commit record gives the database strong durability guarantees.
Memory Hook: “The database keeps the receipt before it moves the furniture.” The receipt is the transaction log; the furniture is the table data.
Cheat Sheet:
- Transaction logs are append-only change records.
- They power
redofor committed work andundofor incomplete work. Write-ahead loggingmeans log before data.- Commit durability depends on flushing the log to stable storage.
- Recovery time grows with log size since the last checkpoint.
- Internal logs are not the same as app audit tables.
Practice Tasks:
- Write a two-table money transfer wrapped in one transaction, then verify both balances after
COMMIT. - Add an audit table and record
BEGIN,COMMIT, andROLLBACKevents for a sample workflow. - Insert a few hundred rows, then explain which statements would need redo if the server crashed before the next checkpoint.