An introduction to InnoDB error handling
Posted in Databases on Sep 26, 2006
Do you know the fine details of MySQL’s transactional error handling with the InnoDB storage engine? If you’re writing transactional SQL, you need to be prepared to handle errors appropriately, and to do that, you need to know how MySQL handles them. This article introduces you to the topic.
When a MySQL query has an error in a non-transactional storage engine, such as MyISAM, the results are anybody’s guess. For example, if your
INSERT against a MyISAM table causes a duplicate index violation when it’s halfway done, it stops, leaving half the data inserted. Not so in InnoDB; even if you’re not in an explicit transaction, each individual statement is a mini-transaction unto itself. It either completely succeeds, or completely fails.
Many programmers are familiar with this distinction, but what happens when you’re in the middle of a multi-statement transaction?
There are three common types of errors you’ll encounter in these situations:
INSERT... SELECT. Your statement waits for some time, but if it doesn’t get the lock, it will eventually time out and abort with an error.
InnoDB handles each type of error slightly differently, and doesn’t always follow the SQL standard.
When InnoDB aborts because of a lock wait timeout, it rolls back only the statement that times out, unless your version of MySQL is older than 5.0.13. In these cases, it rolls back the entire transaction. That is, if you have issued five statements in the transaction, and the sixth times out, the first five are rolled back too. The behavior in 5.0.13 and newer is more logical and better to work with – you should be able to retry just the statement that timed out, and if it succeeds, continue with further statements in your transaction.
If you are having issues with lock wait timeouts, you may have some transactions holding locks too long. MySQL has limited tools to help you solve these problems, but if you browse through my past articles, you’ll see I have written extensively about how to determine what is blocking your statement. Unfortunately, there’s only so much you can do.
When your statement is aborted because of a deadlock, the entire transaction is rolled back. This means you cannot just retry the last statement and continue your work. However, if you started the transaction explicitly (i.e. it wasn’t an implicit one-statement transaction), the
START TRANSACTION statement is not rolled back. The effect is that you’re still in a transaction, but all your work has been erased.
Finally, integrity constraints can be handled at various places within MySQL, including higher-level layers, but when a violation occurs within InnoDB, just the offending statement is rolled back. Duplicate-key violations can be avoided with the
IGNORE option to the
INSERT statement so they’re silently ignored.
You can see there are two possibilities for what happens during an error: either the entire transaction is rolled back, or just the offending statement. What happens to locks held by the transaction?
As it turns out, locks are not released when a single statement is rolled back, but when the entire transaction is rolled back, all its locks are released, too. An easy way to remember this is that the entire-transaction rollback scenario basically wipes your transaction and leaves you with a fresh start.
I always recommend you read the MySQL manual. In this case, you should read at least section 14.2.15, InnoDB Error Handling, and you may want to read more of the sub-sections of chapter 14, too.