Monday, July 16, 2007

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: , ,

5 comments:

Partha Sarathy said...

Nice articles. Really useful. Keep up the good work.

Parvesh Garg said...

Hi Partha,

Thanks for the appreciation. Stay tuned for more articles. Also, If you have any suggestions or problems to discuss, please let me know.

Regards,
Parvesh

mysqlguy said...

Nice work, Parvesh.. I hope you have a good salary in Bangalore :)

Parvesh Garg said...

@mysqlguy
is sunnyvale any better ;)

rohini said...

good work,also start writing in oracle pl/sql