The below picture is just a representation of the flow. I created this figure by referring to the picture in percona doc but I don’t follow this picture anymore (I didn’t mean It was wrong) and have created my own one now. You can refer to my video I posted link in next paragraph

I keep on updating this document with the changes or correctness. Last updated on 1st December 2021.

I spoke on InnoDB transaction flow in more detail. Please check here :

Hope you have read my earlier posts as well. If not, you can read those as well (mentioned last ). Also while working on these features, I have found many bugs which I have reported to Oracle as well. One of the bugs, I reported is (https://bugs.mysql.com/bug.php?id=89158). I will try to cover such limitations in my next blogs.

In this blog, I am covering transaction flow and how it can improve DB performance. This blog has been written after reading various MySQL documentation, related snippet code, and joining all the blocks which can make some sense.

At the time of writing, there is no proper document from Oracle which can define this.

So, before going further, it is good if we understand the following terminology :

1. Buffer pool – Cache area of holding cached data . It is main part of MySQL and we allocate usually according to our business requirement.
2. log buffer – buffer for redolog files which keeps the data coming from MTR buffer.
3. redo logs – iblogfile
4. undo logs – undo files
5. tablespaces – ibdata
6. data files – ibd
7. check points
8. fuzzy check point
9. sharp check point
10. flush
11. overflow
12. commit
13. binary logs
14. sync binlog
15. innodb adaptive flushing.
16. MTR transaction.
17. MTR buffer
18. Flush list
19. Flushing methods
20. double write buffers
21. Change buffers
22. list – double linked list

Let us try to understand this in the following way :

1. Select – When a user execute select query , data comes from table space and get loaded in the buffer pool  . So at this point of time , complete data from table space ( i mean ibd file) doesn’t get loaded in buffer pool and only  “data matching criteria” gets loaded. By matching criteria I meant, only required pages get loaded and root of the index is always in cache for open table. Here number of read io threads, type of read ( linear,random) and threshold value matters which I may try to cover in my future post as a part of read optimisation.

2.  DML statement – In such cases, suppose we received an “insert into table_name” , it goes to log buffer and buffer pool simultaneously . After log buffer, it goes to log file ( which is redo log files and knows as  ib_logfile0,1). Journey of this transaction from log  buffer to log file will depend on innodb_flush_log_at_trx_commit. If it is 1 then at each transaction commits , tx will write and get flush to redo log (log file) .With this value , we are ensuring that chances of loss for any transaction is minimal in case of any crash and thus this value is safest . But I/O will be high due to write at each tx commit . To flush this data into disk ,OS will take care when to flush . Also , to flush this data into disk , innodb_flush_log_at_time_out (which is in seconds ) plays important role . By default , it is 1 second and at every second data got “flushes to disk” . Please note by  “flushes to disk” I don’t mean data gets written to data file , rather i mean ib_logfile using different flushing method. I will try to cover these 2 parameter in my future post but you can refer mysql documentation on this. So , you can try using different values for these parameters and choose what suits your MySQL server.

In start of this , i mentioned that , data goes in buffer pool as well . Buffer pool load a page from tablespace and also uncommitted version of data (to ensure MVCC) which is stored in undo tablespace .By uncommitted version , i mean the historical data (in case of update,delete so that we can rollback from and ensuring high performance) .This undo tablespace gets loaded in buffer pool and ensure that MVCC practice must be followed. Once this transaction get committed , dirty pages will get flush to “data file” from buffer pool (which now host the commited version of data instead of undo) and thus marks the check point.By data files , i mean where your data resides i.e. *.ibd files . This process of flushing the data ( dirty pages ) ,is known as fuzzy check point.

In reality , writing to disk gets more complex as we proceed further because Check point, overflow and commit follows different threads.I will try to make you understand in short and descriptive manner :

1.  Check point : At various intervals , innodb marks check point in buffer pool for dirty pages to flush out , preventing buffers to get filled up.
2. Over flow – This is the rare case when buffers get fill and innodb needs to make space . If log buffer is filled , then it will over write the LRU data and if data buffer is full , then it compares the LSN of data buffer with log buffer and if it is greater then , innodb will write the log records.
3.  Commit : By just commit , it never mean that data will write dirty pages to disk.  We need to remember that at very first point of time , data goes write in redo log first instead of data files because even if we face crash , data resides in redo files and can be recovered from that and this process is knows as WAL ( Write ahead logging )

Earlier blogs.

Function based Indexing

Is mysql acid compliant ?

Making index creation faster in MySQL

Please feel free to correct me , if i have pointed out anything wrong as it is simply my understanding .

About Ankit Kapoor

Ankit Kapoor is a database geek and enjoy playing with open source databases like MySQL and PG.
Connect here on linkedin