In InnoDB, the undo log and the redo log are two indispensable components that play a vital role in maintaining data integrity and ensuring transactional consistency. Both logs are crucial in the ACID (Atomicity, Consistency, Isolation, Durability) properties of database systems. Additionally, they are essential for the Multi-Version Concurrency Control (MVCC) mechanism. In this blog post, we will delve into the differences between the InnoDB undo log and redo log, exploring their significance and providing code examples to illustrate their usage.
InnoDB Undo Log
The undo log, also known as the rollback segment, is a crucial part of the InnoDB storage engine. Its primary purpose is to support transactional consistency and provide the ability to roll back changes made during a transaction. Here’s how it works:
Maintaining before-images: Whenever a transaction modifies data in InnoDB, the undo log records the before-image of the modified data. This before-image contains the original values of the modified rows, allowing for undoing or rolling back changes if needed.
Transaction isolation: The undo log plays a vital role in providing transaction isolation levels like READ COMMITTED or REPEATABLE READ. It ensures that within a transaction, other concurrent transactions can still read consistent data by using the before-images stored in the undo log.
Crash recovery: In the event of a system crash or restart, the undo log helps restore the database to a consistent state by applying the necessary undo operations based on the recorded before-images.
Code example – InnoDB Undo Log:
1 2 3 4 5 6 7 8 | -- Begin a transaction START TRANSACTION; -- Modify data UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales'; -- Rollback the changes ROLLBACK; |
Terminal 1:
In Terminal 1, we created a table called “employees” and inserted some records into it. Then, we started a transaction, updated employees’ salaries in the “Sales” department, and observed that ten rows were affected. Finally, we selected all records from the “employees” table to see the updated salaries.
Table:
1 2 3 4 5 6 7 | mysql> CREATE TABLE employees ( -> employee_id INT PRIMARY KEY, -> name VARCHAR(100), -> department VARCHAR(100), -> salary DECIMAL(10, 2) -> ); Query OK, 0 rows affected (0.03 sec) |
Inserts a few records into the employees’ table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> INSERT INTO employees (employee_id, name, department, salary) -> VALUES -> (1, 'John Smith', 'Sales', 50000.00), -> (2, 'Jane Doe', 'Sales', 55000.00), -> (3, 'Michael Johnson', 'Sales', 60000.00), -> (4, 'Emily Williams', 'Sales', 52000.00), -> (5, 'David Anderson', 'Sales', 58000.00), -> (6, 'Sarah Thompson', 'Sales', 51000.00), -> (7, 'Christopher Lee', 'Sales', 54000.00), -> (8, 'Jessica Brown', 'Sales', 57000.00), -> (9, 'Matthew Davis', 'Sales', 53000.00), -> (10, 'Olivia Taylor', 'Sales', 56000.00); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 |
1 2 3 4 5 6 | mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales'; Query OK, 10 rows affected (0.00 sec) Rows matched: 10 Changed: 10 Warnings: 0 |
Using the same terminal, let’s query the table and observe that the query returns the updated record.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | mysql> select * from mytest.employees; +-------------+-----------------+------------+----------+ | employee_id | name | department | salary | +-------------+-----------------+------------+----------+ | 1 | John Smith | Sales | 55000.00 | | 2 | Jane Doe | Sales | 60500.00 | | 3 | Michael Johnson | Sales | 66000.00 | | 4 | Emily Williams | Sales | 57200.00 | | 5 | David Anderson | Sales | 63800.00 | | 6 | Sarah Thompson | Sales | 56100.00 | | 7 | Christopher Lee | Sales | 59400.00 | | 8 | Jessica Brown | Sales | 62700.00 | | 9 | Matthew Davis | Sales | 58300.00 | | 10 | Olivia Taylor | Sales | 61600.00 | +-------------+-----------------+------------+----------+ 10 rows in set (0.00 sec) mysql> |
Terminal 2:
In Terminal 2, we queried the “employees” table and noticed that it returned the old records, which are the before-images stored in the undo log. This demonstrates how the undo log maintains transactional consistency, allowing concurrent transactions to read consistent data.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> select * from mytest.employees; +-------------+-----------------+------------+----------+ | employee_id | name | department | salary | +-------------+-----------------+------------+----------+ | 1 | John Smith | Sales | 50000.00 | | 2 | Jane Doe | Sales | 55000.00 | | 3 | Michael Johnson | Sales | 60000.00 | | 4 | Emily Williams | Sales | 52000.00 | | 5 | David Anderson | Sales | 58000.00 | | 6 | Sarah Thompson | Sales | 51000.00 | | 7 | Christopher Lee | Sales | 54000.00 | | 8 | Jessica Brown | Sales | 57000.00 | | 9 | Matthew Davis | Sales | 53000.00 | | 10 | Olivia Taylor | Sales | 56000.00 | +-------------+-----------------+------------+----------+ 10 rows in set (0.00 sec) |
Terminal 1:
In Terminal 1, we performed a rollback, which utilized the before-images to undo or roll back the changes made during the transaction. After the rollback, we selected all records from the “employees” table again and confirmed that the salaries reverted to their original values.
These actions showcase the role of the undo log in maintaining transactional consistency and providing the ability to revert changes when needed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> ROLLBACK; Query OK, 0 rows affected (0.04 sec) mysql> select * from mytest.employees; +-------------+-----------------+------------+----------+ | employee_id | name | department | salary | +-------------+-----------------+------------+----------+ | 1 | John Smith | Sales | 50000.00 | | 2 | Jane Doe | Sales | 55000.00 | | 3 | Michael Johnson | Sales | 60000.00 | | 4 | Emily Williams | Sales | 52000.00 | | 5 | David Anderson | Sales | 58000.00 | | 6 | Sarah Thompson | Sales | 51000.00 | | 7 | Christopher Lee | Sales | 54000.00 | | 8 | Jessica Brown | Sales | 57000.00 | | 9 | Matthew Davis | Sales | 53000.00 | | 10 | Olivia Taylor | Sales | 56000.00 | +-------------+-----------------+------------+----------+ 10 rows in set (0.00 sec) |
In the above code snippet, the transaction modifies the salary column for employees in the Sales department. If the transaction is rolled back, the undo log is utilized to revert the changes made to the original values.
InnoDB Redo Log
The redo log, also known as the transaction log or InnoDB log, serves a different purpose than the undo log. Its primary function is to ensure durability and aid in crash recovery. Let’s explore its characteristics:
Recording changes: Whenever a transaction modifies data in InnoDB, the redo log records the changes made to the database. It stores the actual modifications performed, such as inserting new rows, updating existing rows, or deleting rows.
Redo Log not only records the changes made to the database but also includes the modifications that are written into the rollback segments. In a database system, rollback segments are used to temporarily store undo data during transactions. So, in addition to tracking database changes, the Redo Log also captures the changes made to the rollback segments. This ensures that during recovery procedures, the system can properly restore the database to a consistent state by applying both the database changes and the modifications stored in the rollback segments.
Write-ahead logging: The redo log follows a write-ahead logging approach, meaning that changes are written to the redo log before the corresponding data pages are updated. This ensures that in the event of a crash, the changes recorded in the redo log can be replayed to restore the database to a consistent state.
Crash recovery: During crash recovery, the redo log is crucial for replaying the logged changes to bring the database to a consistent state. By reapplying the changes recorded in the redo log, InnoDB can recover transactions that were committed but not yet written to disk.
Redo Logs are also used in making consistent physical backups. Tools like Percona XtraBackup leveraging the Redo Logs during the prepare stage to make the backup consistent. This distinguishes physical backups, which include changes made during the backup process, from logical backups that represent the database at the beginning of the backup. Logical backups provide an image of the database at the beginning of the backup. Physical backups provide an image of the database at the end of the backup.
1 2 3 4 5 6 7 8 | -- Begin a transaction START TRANSACTION; -- Modify data UPDATE products SET stock = stock - 10 WHERE id = 123; -- Commit the transaction COMMIT; |
In the above example, the transaction updates the stock column of a product identified by its ID. The changes are recorded in the redo log, ensuring durability and enabling recovery in case of a crash.
Conclusion
Understanding the differences between the InnoDB undo log and redo log is crucial for ensuring data integrity and durability in database systems. The undo log serves as a critical component for maintaining transactional consistency, allowing for the rollback of changes and supporting transaction isolation levels. On the other hand, the redo log plays a vital role in ensuring durability and aiding in crash recovery by recording database changes and following a write-ahead logging approach.
It is worth noting that the process of flushing (checkpointing vs. redo log) and purging (vs. undo logs) can have a significant impact on system performance and overhead. Flushing refers to the periodic writing of dirty pages from memory to disk, either through checkpointing or by utilizing the redo log. This process helps ensure that changes are safely persisted to disk, contributing to durability. Purging, on the other hand, involves removing old or unnecessary data from the system, which can include purging undo logs. Properly managing flushing and purging operations is crucial for system performance and tuning.
By understanding the overhead, tuning considerations, and the role of flushing and purging in relation to the undo log and redo log, database administrators can optimize their systems to achieve the desired balance between data integrity, performance, and storage requirements.
In summary, the InnoDB undo log and redo log are indispensable components that work together to provide transactional consistency, durability, and crash recovery capabilities. Alongside flushing and purging operations, these logs are essential for managing data integrity and system performance in database systems.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
