[MDEV-14336] Deadlock at unlocking tables when local server and replication write to same table Created: 2017-11-09  Updated: 2019-03-22

Status: Open
Project: MariaDB Server
Component/s: Data Manipulation - Update, Locking, Platform RedHat, Replication, Storage Engine - InnoDB
Affects Version/s: 10.2.9, 10.2.10, 10.2.11
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Alex Assignee: Unassigned
Resolution: Unresolved Votes: 4
Labels: deadlock, innodb, replication, update
Environment:

CentOS 7

Attachments: File 20171208-deadlock.gdb     Text File 20171208-processlist.txt     File 20171213-deadlock-after-stop.gdb     File 20171213-deadlock.gdb     Text File 20171213-processlist.txt     PNG File screenshot-1.png    
Issue Links:
Relates
relates to MDEV-19018 Deadlock at unlocking tables in paral... Open

 Description   

We have a daisy-chain replication with 3 masters. Since the upgrade from 10.1 to 10.2.9 we get deadlocks 1-2 times a week on one specific table (varies from 0 to 500 entries) which is written by the local server and also by the replication. The replication stucks at the state "Unlocking tables" and stops the whole replication. It is not possible to kill the process within MariaDB, you have to kill the whole mysql-process with kill -9.

There are no errors in error-log. A recent upgrade from 10.2.9 to 10.2.10 did not solve the problem. Any ideas?

• Configuration

log-slave-updates
 
log_bin_trust_function_creators = 1
 
log-bin = /var/log/mysql/mysqld-bin
 
max_binlog_size = 10M
 
binlog_format = STATEMENT
 
relay-log = /var/log/mysql/mysqld-relay-bin
 
max_relay_log_size = 10M

• Stucked Process

      Id: 11
 
    User: system user
 
    Host: 
      db: database
 
 Command: Slave_SQL
 
    Time: 34937
 
   State: Unlocking tables
 
    Info: UPDATE `Table` SET `a` = 'b', `c` = 'd' ...
 
Progress: 0.000

• Workaround at Deadlock

systemctl stop mariadb
 
kill -9 `cat /var/lib/mysql/*.pid`
 
systemctl start mariadb



 Comments   
Comment by Elena Stepanova [ 2017-11-20 ]

Could you please get a stack trace from all threads on a still running (deadlocked) server? gdb --batch --eval-command="thread apply all bt" <path to mysqld> <pid> or alike.

Comment by Alex [ 2017-12-07 ]

For the record, we had 1-2 deadlocks per week with 10.2.9, we had 1 deadlock after upgraded to 10.2.10 and no further deadlocks after filing this bug-report.
We are now running 10.2.11.

We will make a stack trace if the deadlock occurs again.

Comment by Alex [ 2017-12-08 ]

Unfortunately it happend again with 10.2.11. The replication stucked at the state "Unlocking tables" and stopped the whole replication.
Attached now the stacktrace and also a part of the processlist.

Comment by Alex [ 2017-12-13 ]

Added a new stacktrace and processlist for today when the deadlock happened again. Additionally a stacktrace after "systemctl stop mariadb" where the mysql-process is still running stucked.

Generated at Thu Feb 08 08:12:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.