MySQL InnoDB – How Transactions Rollback After Crash Restart

innodbMySQL

I know when I execute dml sql before commit , innodb will write the result to the innodb page cache which can flush to data page and write to the undo log file ; but at the same time , mysql crash and restart, how mysql innodb which transaction should rollback ?

Best Answer

That's all about journaling. Write is performed in three stages. First all changes are written along with the old data, then all references are relinked to the new values and if that two stages are passed ok then the whole transaction is marked as commited, all old links are purged and journal entry is removed. If engine at start find some uncommited transactions in the journal then all related changes are rolled back to the initial state.