BiteSizedChunks.comLearn one small thing at a time.
Course contentsShow
← Databases & SQL
Lesson 568 of 1,9536. Query Performance and IndexingFree lesson

The Role of the Transaction Log

Trace the lifecycle of a transaction from memory buffer to durable disk storage.

In this lesson, you will learn how the transaction log acts as the bridge between volatile memory changes and permanent disk storage.

The Problem of Volatility

When you issue an UPDATE or INSERT statement, the database engine does not immediately write that data to the permanent table file on your hard drive. Writing directly to the data file is slow because it requires jumping to specific locations on the disk (random I/O). Instead, the database performs the work in the Buffer Pool (memory).

But what happens if the power cuts out before those memory changes are saved to the disk? The data would be lost. This is where the Transaction Log (often called the Write-Ahead Log or WAL) saves the day.

The Lifecycle of a Transaction

To ensure durability, the database follows a "Write-Ahead" rule:

  1. Preparation: When you run a query like UPDATE Products SET Price = 10 WHERE ID = 5;, the database creates a record of this change in memory.
  2. The Log Write: Before the database acknowledges success to you, it appends this change to the transaction log on disk. Because the log is an "append-only" file, it is extremely fast—the disk head doesn't have to move around to find data; it just writes to the end of the file.
  3. Buffer Update: The database updates the actual data page inside the Buffer Pool. At this stage, the page in memory is "dirty" because it differs from the version on disk.
  4. Checkpoint: Periodically, the database performs a "checkpoint," where it flushes these dirty pages from the Buffer Pool to the actual data files.

If the system crashes before the checkpoint occurs, the database looks at the transaction log upon restarting. It "replays" the logs to ensure any committed transactions that weren't yet saved to the main data files are restored to their correct state. This process ensures your data is never lost, even if the primary data files are slightly behind the current state of the system.

Key Takeaway: The transaction log guarantees durability by recording changes in a fast, sequential format before they are applied to the slower, primary data files.