The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › Storage and recovery

Undo and redo logs

Why does the data survive even if the power goes out right after a commit?

When a database changes data, it does not modify the data file right away. It changes the data in memory first and leaves two records: one needed to undo the change (undo) and one needed to apply it again (redo).

Checked against documentation and standards · Checked against the MySQL documentation

Concepts in this lab

Undo log and redo log

An undo log records data as it was before a change. It is used to roll back a transaction, and in InnoDB it is also used to show past versions to other transactions (MVCC). A redo log records the changes themselves. If a failure occurs before committed changes are fully written to the data files, the changes are applied again at restart to recover them.

ACID

The four properties that a transaction must guarantee. Atomicity means everything succeeds or everything fails. Consistency means the data satisfies the defined rules before and after the transaction. Isolation means transactions that run at the same time do not interfere with each other. Durability means committed results are kept even if a failure occurs.

MVCC (multiversion concurrency control)

A method that keeps earlier versions of data when the data changes, so that a reading transaction can see the version that matches the time of its snapshot. The snapshot time depends on the isolation level: in MySQL, REPEATABLE READ takes it at the first read of the transaction, and READ COMMITTED takes a new one each time a statement runs. Reads do not wait for writes and writes do not wait for reads, so concurrent performance is high. MySQL InnoDB, PostgreSQL, and Oracle all use this method.

Transaction

A way to group several data changes into a single unit so that either all of them are applied (commit) or all of them are canceled (rollback). In a bank transfer, it prevents an intermediate state where only one of the withdrawal and the deposit is applied. It is the most basic means a database has for keeping data consistent.

Try these

• Default settings — A failure right after the commit. The data file holds the old value, but after a restart the value is the new one.
• A failure before the commit — After a restart, the change is as if it never happened.
• Rollback — The data is returned to the earlier value that was kept in the undo log.
• A normal commit — At commit time, only the log is written to disk.

Running with the default settings

1. The balance of account 1 is 1000. This value is in the data file on disk, and the same value is also loaded in memory (the buffer pool).
2. The UPDATE starts. First, the value before the change, 1000, is recorded in the undo log. It is used when the change has to be undone, and when another transaction has to read the value from before the commit (MVCC).
3. The data held in memory is changed to 500. The data file on disk is still 1000. Changing the data file right away every time would require writes to many places on the disk, which is slow, so the changes are written later in a batch.
4. What was changed, and how, is recorded in the redo log. A log only needs to be appended to the end of a file, so this is much faster than changing the data file.
5. COMMIT: The redo log is written to disk for certain (fsync). Only after this is done does the server reply "commit succeeded" to the user. Even at this point the data file is still 1000. What is written to disk at commit time is only the log.
6. The server went down right after the commit reply, before the changes were written to the data file. The 500 in memory is lost, and the data file still holds 1000. The user has already received "success".
7. The server starts again. Recovery reads the redo log on disk from the last checkpoint.
8. The redo log has "account 1 set to 500" and the record that it committed. This change is applied again. The balance is recovered as 500. No committed data was lost.

Result of this run

• A committed change is recovered from the redo log even if it was not yet written to the data file. The redo log supports "what is committed remains even after a failure" (durability).
• Writing the log to disk before the data in this way is called write-ahead logging (WAL). Undo is in charge of "rolling back" and "showing earlier versions", and redo is in charge of "reapplying".

Code

START TRANSACTION;
UPDATE account SET balance = 500 WHERE id = 1;   -- it was 1000
COMMIT;
-- (the server goes down right after the commit reply)

Assumptions of this model

This is a simplified version of the structure of MySQL InnoDB. Details are left out, such as the fact that changes to the undo log are themselves protected by the redo log, and the log buffer and the several stages of writing to disk. Writing the redo log to disk at every commit is the behavior of the default setting (innodb_flush_log_at_trx_commit=1). These results were not confirmed by causing failures in a real database; the lab restates what the MySQL documentation explains.

Previous lab: Deadlocks in the database · Next lab: Buffer pool and pages