Implicit Commits and Transactions

We think that whatever statements are there in a transaction can be rolled-back. Right? No! If by any chance you have statements that cause an implicit commit, then no. These statements commit the transaction mid-air. And if you think that these statements are only DDL, beware. There are many statements in MySQL that implicitly commit like LOAD DATA INFILE was an implicit commit statement for all storage engines until 5.1.12. Even now, if you are using NDB, this is still a implicit commit statement.

Say, we have statements like this:

1. START TRANSACTION
2. SOME INSERT STATEMENT
3. ONE MORE INSERT STATEMENT
4. ONE IMPLICIT COMMIT STATEMENT
5. ONE MORE INSERT STATEMENT
6. SOME MORE STATEMENTS
7. COMMIT

The implicit commit statement (statement number 4) commits the statements above it and aborts the transaction, as already mentioned. So, what happens to the statements 5 and 6. They run as normal statements outside transaction, and beat the whole purpose of a transaction. As MySQL documentation points out that this behavior occurs because we cannot have nested transactions. The same link also provides the list of all the implicit commit statements.

There is an open bug (I saw lately) requesting a feature on this, as most of the users take transactions for granted.

Hope this blog was helpful to you. Keep posting your comments.

del.icio.us Tags: InnoDB, Performance, Transaction