In MySQL, if the server crashes after an update and the transaction was not committed, the changes made by the transaction will be rolled back during the crash recovery process. This is because MySQL uses a technique called “write-ahead logging” (WAL), which ensures that all updates are written to the transaction log before they are applied to the database.
When the MySQL server restarts after a crash, it reads the transaction log to determine the state of the database at the time of the crash. If it finds any uncommitted transactions in the transaction log, it rolls back those transactions to ensure that the database is in a consistent state.
It’s important to note that this only applies to InnoDB tables in MySQL, which is the default storage engine in recent versions of MySQL. MyISAM tables do not support transactions and therefore do not have a transaction log. In the case of a crash with MyISAM tables, any uncommitted changes may be lost. Therefore, it’s generally recommended to use InnoDB tables when transactions are needed to ensure data consistency and recovery in case of a crash.
How redo and rollback are implemented in InnoDB?
InnoDB implements redo and rollback operations using a combination of write-ahead logging (WAL) and multi-version concurrency control (MVCC) techniques.
Redo operations are used to ensure that committed changes are persisted to disk and survive system crashes. When a transaction modifies data in an InnoDB table, the changes are first written to the transaction log, which is stored on disk. The transaction log contains a record of all the changes made to the database by committed transactions. After the changes have been logged, they are applied to the database tables.
If a system crash occurs, InnoDB uses the transaction log to “replay” the changes made by committed transactions that were not yet written to the database tables at the time of the crash. This ensures that the database is in a consistent state after recovery.
Rollback operations are used to undo changes made by aborted transactions. When a transaction is rolled back, the changes made by that transaction are removed from the database tables. InnoDB uses MVCC to keep track of multiple versions of data, which allows it to undo specific changes made by a transaction without affecting other changes made by other transactions. This is done by creating a new version of the data for each change made by a transaction. When a transaction is rolled back, InnoDB simply removes the most recent version of the data and reverts to the previous version.
In summary, InnoDB implements redo and rollback operations using WAL and MVCC techniques. Redo operations are used to persist changes to disk and survive system crashes, while rollback operations are used to undo changes made by aborted transactions.
