MariaDB Deadlocks

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 2 different connections. So how you can find the other part of the problem?

The full Deadlock situation is shown with the following command:

SQL> SHOW ENGINE InnoDB STATUS\G
...
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-12-23 18:55:18 0x7f51045e3700
*** (1) TRANSACTION:
TRANSACTION 847, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 46, OS thread handle 139985942054656, query id 839 localhost root Updating
delete from t where id = 10
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 847 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000034e; asc      N;;
 2: len 7; hex 760000019c0495; asc v      ;;

*** (2) TRANSACTION:
TRANSACTION 846, ACTIVE 25 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 39, OS thread handle 139985942361856, query id 840 localhost root Updating
delete from t where id = 11
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 846 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000034e; asc      N;;
 2: len 7; hex 760000019c0495; asc v      ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 846 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 00000000034f; asc      O;;
 2: len 7; hex 770000019d031d; asc w      ;;

*** WE ROLL BACK TRANSACTION (2)
...

Unfortunately with the SHOW ENGINE INNODB STATUS command you can only show the last Deadlock error. If you want to log all the Deadlock errors in the MariaDB error log you can activate the Deadlock error reporting with the following configuration option in your MariaDB configuration file (my.cnf):

innodb_print_all_deadlocks = ON

How to solve Deadlocks?

Deadlocks always can occur in a transactional database system with fine-grained locking. So your application must be aware of Deadlocks and catch the Deadlock error and retry the transaction. The error message already indicates this. It is an advice for YOU as a developer:

try restarting transaction

How to avoid deadlocks?

Deadlocks are an hot spot on some rows. Hot spot means: Many (at least 2) connections do something (INSERT, UPDATE, REPLACE, DELETE, SELECT FOR UPDATE) on the same rows (which then are locked).

So how can you solve a Deadlock problem? Solving in the meaning of "reduce the probability of a Deadlock close to zero". Zero Deadlocks guarantee you can only achieve in no-concurrency scenarios.

• Look at the design of your application: Is it necessary that the 2 conflicting transactions are done in the way you are doing it right now? Can you change the behaviour of your application?
• Reduce locking time: As shorter the locks are hold as smaller the probability is that you run into a Deadlock situation. So make sure your conflicting transactions are running as fast as possible and the locks are hold as short as possible. Check that all the transactions are using perfect indexes.
• Reduce the amount of locks: The fewer rows your conflicting transactions lock the smaller is the chance for a Deadlock. For example: Several small INSERTs in several transactions instead of one huge INSERT in one big transaction should cause less locks and thus a smaller probability of Deadlocks. Only change (REPLACE, INSERT ON DUPLICATE KEY UPDATE) what is really needed to change. Check before you change, that rows need to be changed.
• Do your tasks less often. For example polling every 10 seconds instead of every second, or do refreshes of data less often.
• Timely defer concurrent transactions. For example 2 batch jobs: Do they really need to run at the exact same time?
• Reduce concurrency (parallelism), if possibly.
• Catch the Deadlock error and retry the transaction again a few milliseconds later. If this does not happen too often it is not a problem for your database.
• Change transaction isolation level to a level causing less locks. You can do that per session (SET SESSION ...) to reduce the impact on the whole system or globally (SET GLOBAL ...). But changing the isolation level globally potentially can have also an impact on other parts of your application or even completely different applications running on the same database.

And to be aware: You cannot completely avoid (100%) Deadlocks. They always can happen and you have to cope with it!

Do not mix up Deadlocks with Galera cluster conflicts which look similar at the first look but are not.

Literature

• Deadlocks, indexing and Primary Key's
• MariaDB Deadlocks
• MySQL Deadlocks in InnoDB