This article provides a brief explanation of deadlocks in MariaDB and the method to clear it. At Bobcares, with our Server Management Services, we can handle your server issues.

How do we clear deadlocks in MariaDB?

Deadlocks in MariaDB: Deadlocks are frequently caused by an app issue rather than a database issue. The database itself indicates the application problem by sending the following message to the application as an error:

If this error message appears in the application, we know exactly where the issue is. However, a deadlock always arises between two distinct connections. So we’ve to locate the other component of the issue. Let’s look into an entire deadlock situation:

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)
...

Here, we can only see the last Deadlock error. So we can use the below configuration option in the MariaDB configuration file (my.cnf) to record all deadlock errors in the error log for MariaDB.

innodb_print_all_deadlocks = ON

Clear/Fix deadlocks in MariaDB

Deadlocks occur when multiple (at least 2) transactions do some action including INSERT, UPDATE, REPLACE, or DELETE on the same rows of data, which then are locked.

In a transactional database system with fine-grained locking, deadlocks are always possible. As a result, the program needs to be aware of deadlocks, detect the deadlock issue, and attempt the transaction again.

try restarting transaction

Deadlock Prevention

Only no-concurrency conditions can promise Zero Deadlocks. But when we say “prevent,” we really mean “lower the likelihood of a deadlock to almost zero.” We can follow any of the below methods to prevent the deadlock:

1. We must consider changing the behavior of the app which causes this deadlock.
2. We must ensure that the locks are held for the shortest amount of time possible and that the conflicting transactions are processed as quickly as possible.
3. Also, ensure that the number of locks is decreased. A deadlock is less likely to occur if the conflicting transactions lock fewer rows.
4. We must reduce certain tasks. For e.g., refresh data less often or poll every 10 seconds instead of every second.
5. Consider the order of transaction execution. Is it necessary for them to start running simultaneously?
6. Find the deadlock error and retry the transaction again a few milliseconds later.
7. Modify the transaction isolation level to a level causing fewer locks.

[Need further help? We’re available 24/7.]

Conclusion

Some DB rows are prone to deadlocks. Here, we’ve included the steps we can follow to avoid the deadlock to a great extent. We also included the command provided by our Tech team to clear the deadlock in MariaDB.