When does Innodb Start Transaction ? The answer looks obvious – when you issue “BEGIN” command. This is however wrong answer from engine point of you. Run “SHOW INNODB STATUS” and you will see “not started” status in transaction list.
It is only when you read (or write) to INNODB table you can see transaction is started:
---TRANSACTION F56AED, ACTIVE 4 sec, process no 23727, OS thread id 139753656395520
MySQL thread id 112, query id 411 localhost root
show innodb status
Trx read view will not see trx with id >= F56AEE, sees < F56AEE
If you’re running in AUTOCOMMIT=0 mode the same logic will apply – setting this mode will NOT start transaction however reading or writing Innodb table will.
I would call it delayed transaction creation – having transaction open is costly, especially in repeatable-read mode as it will require innodb not to purge any old row versions from start of transaction until it is committed so delaying the start makes sense.
There is the side effect of this behavior though. When we say in repeatable read mode reads will see state of data at the time of transaction start we might refer to issuing BEGIN statement while it is actually first access of Innodb table. In most cases you would not care but if you really need all data be seen at the time of transaction start you can use “START TRANSACTION WITH CONSISTENT SNAPSHOT” command.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.