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:
|
1 2 3 4 |
---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.
