Deadlock

Translated by deepl.com

Introduction

Two points in advance:

1. A deadlock is a state in which two different transactions are no longer able to continue working because each transaction holds a lock that the other transaction would need. Because both transactions are now waiting for the other transaction to release their locks, neither transaction will release their respective locks. And that would last forever. To avoid this, the MariaDB instance intervenes and kills the transaction that has done less work. …

We get ever and ever again customer requests concerning Deadlocks. First of all, Deadlocks are usually an application problem, not a database problem! The database itself manifests the application problem with the following message which is sent to the application as an error:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

If your application receives this error message you know where in your application you have a problem. But a deadlock is always a problem between …

Recently we had a new customer who has had from time to time massive database problems which he did not understand. When we reviewed the MySQL configuration file (my.cnf) we found, that this customer had disabled the InnoDB Deadlock detection (innodb_deadlock_detect).

Because we have advised against doing this so far, but I never stumbled upon this problem in practice, I have investigated a bit more about the MySQL variable innodb_deadlock_detect.

The MySQL documentation tells us the following
[1
]: …

You got an InnoDB Deadlock. Please talk to your development department to fix this problem. Deadlocks are an application problem!

We typically suggest our customers to use our MySQL/Galera Cluster my.cnf configuration template to avoid MySQL configuration and performance problems.

And we are paranoid as well. Thus we enable all useful logging:

wsrep_log_conflicts = 1

But this has also some consequences of more visibility…

If you monitor carefully your Galera Cluster for example with the FromDual Performance Monitor for MySQL and MariaDB, you might probably see some strange values increasing from time to time:

mysql< SHOW …

Recently a customer has shown up with some deadlocks occurring frequently. They were of the following type (I have shortened the output a bit):

*** (1) TRANSACTION:

TRANSACTION 22723019234, fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1216, 14 row lock(s)
update location set expires='2012-08-10 04:50:29' where username='12345678901' AND contact='sip:12345678901@192.168.0.191:5060' AND callid='945a20d4-8945dcb5-15511d3e@192.168.0.191'

*** (1) WAITING FOR THIS LOCK TO …