Developers* seem to think of a transaction as a block of code. A try/catch block seems analogous at first glance — try to do a series of actions, and if one fails, roll them all back, else commit. So many people trip up and try to write code within a transaction to avoid deadlock or race conditions.
This is the source of the problems with the commands that perform an implicit
COMMIT (that links to the 4.1 and under documentation, be sure to check manual for the specific version you are using). Implicit
COMMITs can throw a lot of unexpected bugs in, so try to use those commands wisely. Transactions are session-specific. So a
COMMIT, implicit or otherwise, affects any existing transaction within that session — as START TRANSACTION performs an implicit commit, you can only work on one transaction at a time in a session. The SQL Standard provides the
However, a transaction really it should be treated as a command. The point of a transaction is to be atomic. It is designed put a few tasks in a natural group. The beauty of a declarative language like SQL is that I tell it what I want, not how to do it. The optimizer figures out how to do it. In procedural and object-oriented programming, I specify how to do something.
Trying to fix a race condition or deadlock by using locking within a transaction defeats the point of a transaction. Locking a table, changing the schema, or starting another transaction, is not a natural grouping. I keep using “natural” but that is my made-up term for it.
Think of a joint bank account — if one person uses their bank card to pay at a restaurant, the waiter will go to the terminal and authorize an amount — larger than the bill, probably 30% larger, to ensure that when the gratuity is added, there is enough money in the account to cover it. After the tip is added and the bill signed, the receipt gets put into a pile, and once a day a batch is run, which is when the money actually gets taken out of your account.
Now imagine the other person fills their tank with gasoline. When the bank card is swiped at the gas pump, an amount is authorized**, and the driver is allowed to start filling the tank. At the end of the fill, the exact amount is determined and sent off to the bank for processing (or done in a batch, as above).
What banks and credit cards do is authorize the amount and “hold” it — this is why the “available” bank balance may be different than the “actual” bank balance. That is the “natural” way to avoid a race condition. What developers try to do by adding “unnatural” statements such as locking tables, or transactions within transactions, is analogous to a bank skipping authorization and not allowing any transactions to occur until a payment clears.
This is obviously primitive to folks who want to purchase more than one item per day with their card. And yet, developers foam at the mouth, cursing the database engine (all have implicit commits, MySQL is not alone) for not doing what they tell it to. Of course the database engine does not do what you tell it to — it is not designed to. It is designed to give you the answer you want, not follow the path you specify.
So the moral of the story is that care should be taken that transactions actually encompass the very smallest atomic operation, not “things to group in a block”. Stored procedures actually help in the matter of getting around the issue folks have with implicit commits; in fact, while researching this article I found that the MySQL Reference Manual for Version 3.23.10 alpha states:
For the moment, we are much more for implementing the SQL server language (something like stored procedures). With this you would very seldom really need COMMIT-ROLLBACK. This would also give much better performance.
* When I say developers, I mean “people who think like developers” meaning they do not grok the declarative nature of SQL.
** I found this out the hard way driving from Boston to Chicago and back in a 24′ moving truck. I was filling the tank and the pump stopped filling at $50, and I had to re-swipe my debit card.