How InnoDB work with transactions:
When any transaction will be completed with COMMIT, InnoDB will write those changes in InnoDB Buffer Pool. After that InnoDB will run some background operations like checkpoint. Checkpoint is the most important operation which will writes the changes on disk. Lets see how it will work.
During the checkpoint phase, InnoDB writes dirty pages to the double write buffer, and then writes pages from the doublewrite buffer to the actual tablespace. During checkpointing, as pages are flushed to the actual tablespace making the data changes persistent on disk, log_sequence_numbers (LSN) are also updated on the pages. The LSN info written to the page is what identifies whether a data page has current data or not, during the crash recovery phase.
How InnoDB does crash / auto …[Read more]