InnoDB Deadlock Count Patch

InnoDB Deadlock Count Patch

Deadlocks are a common occurrence in relational databases. They usually aren’t a problem until they start happening too frequently. Innodb can provide you with information about the latest deadlock in SHOW ENGINE INNODB STATUS. This can be useful for debugging but it’s almost impossible to get the rate at which deadlocks are occurring. This patch applies against MySQL 5.0.72sp1 and probably quite a few other versions. It adds a counter to show table status that tracks the number of deadlocks. In this example mysql-1> is connection 1 and mysql-2> is connection 2.

mysql-2> show global status like ‘innodb_deadlocks’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| Innodb_deadlocks | 0 |
+——————+——-+
1 row in set (0.00 sec)

mysql-1> begin; select * from t where t=1 for update;
Query OK, 0 rows affected (0.00 sec)

+—+
| t |
+—+
| 1 |
+—+
1 row in set (0.00 sec)

mysql-2> begin; select * from t where t=2 for update;
Query OK, 0 rows affected (0.00 sec)

+—+
| t |
+—+
| 2 |
+—+
1 row in set (0.00 sec)

mysql-1> select * from t where t=2 for update;
+—+
| t |
+—+
| 2 |
+—+
1 row in set (1.71 sec)

mysql-2> select * from t where t=1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql> show global status like ‘innodb_deadlocks’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| Innodb_deadlocks | 1 |
+——————+——-+
1 row in set (0.00 sec)

Enjoy!

9 Comments

  1. ryan says:

    That is so simple, and yet so brilliant at the same time. Having a value like that monitored would have provided valuable info in the very recent past.

    Have you applied to get this patch applied to mainline tree?

  2. I’ve found that it is a very good idea to log deadlocks in your application, because knowing how many deadlocks there are is only half of the picture. The database abstraction layer (everyone should use one) should be extended to log deadlocks and the SQL which created them.

    Newer versions of InnoDB won’t roll back the entire transaction on deadlock. Instead it will only roll back the rows for the statement which failed, which makes dealing with the deadlocks a lot easier.

  3. domas says:

    <3 <3

    though I’d like to have ‘RETRY TRANSACTION’ statement within SQL (it is all buffered in binlog cache anyway, right? :-)

  4. [...] Bergen offers his InnoDB deadlock count patch, which he introduces thus: “[Deadlocks] usually aren’t a problem until they start happening [...]

  5. [...] Bergen offers his InnoDB deadlock count patch, which he introduces thus: “[Deadlocks] usually aren’t a problem until they start happening [...]

  6. VadimTk says:

    Eric,

    Can you provide BSD license on your patch ?

  7. Eric Bergen says:

    Yes. Is there some official way that I should declare the license of the patch so you can use it?

  8. VadimTk says:

    Eric,

    Yes, the best way would be if we can get the patch along with file COPYING filled by template from:
    http://www.opensource.org/licenses/bsd-license.php

    Thanks,
    Vadim

  9. Eric Bergen says:

    I’ve added the COPYING file to the patches directory containing the patch.

Leave a Reply