MySQL implicit commit

MySQL implicit commit might be one of those features that can get you by surprise if you don’t understand how it works. I’m not going to jump in and question why it was done like that and I believe there is a very reasonable reason behind it, but, nonetheless, if you don’t understand this feature of MySQL it will act against you.

The feature is simple: given certain situations MySQL, regardless of its configuration, will implicitly commit the preceding statements.

What this means is that if you have a bunch of inserts and then something that causes an implicit commit, all those inserts, regardless of the result of the implicit commit statement, will be committed leaving the database in an odd state.

Sometimes text doesn’t help so let’s move to some code or some form of a code.

START TRANSACTION
INSERT
INSERT
INSERT
INSERT
ALTER TABLE
...

Regardless of the result of the ALTER TABLE, based this feature of MySQL all the commits before it will be committed and, anything after the ALTER TABLE (if it doesn’t fail), will start a new transaction.

This same behavior will occur with:

• any structural changes
• another transaction start (note MySQL has the concept of SAVEPOINTS but not nested transactions)
• any BEGIN statement (there are 2 types)

So how can this affect you? Simply consider that you are restoring a SQL dump and a set of tables with it’s following data are correctly created and seeded, but one, is not. Everything up that moment will be committed and anything after will simply fail.

Another scenario is data migration scripts or scripts that attempt to reconstruct/alter data. Not understanding the implicit commit will leave your database in a bad state.

There is a lot more to talk about it but, just give the documentation a read. It is worth a million.

Ref: https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

This article was originally at: https://bit.ly/2Z5EzF1