Bug #78761 Unexpected deadlock with innodb_autoinc_lock_mode=0
Submitted: 8 Oct 2015 15:36 Modified: 4 Apr 2016 13:47
Reporter: Yiftach Kaplan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.6/5.7 OS:CentOS
Assigned to: CPU Architecture:Any

[8 Oct 2015 15:36] Yiftach Kaplan
Description:
When I continuously run the same statement in 50 threads at the same time I'm getting a deadlock with this status: "
LATEST DETECTED DEADLOCK
------------------------
2015-10-08 13:24:21 7f0cb9ef0700TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION"

This works perfectly in 5.5 (all the versions) and failed in all the versions of 5.6 that I've tried.

How to repeat:
1. Create a table with auto increment and a primary key:
DROP TABLE IF EXISTS aaa; 
CREATE TABLE aaa 
  ( 
     mykey INT, 
     aid   BIGINT auto_increment, 
     count INT, 
     PRIMARY KEY( mykey ), 
     KEY( aid ) 
  ) 
engine = innodb; 

2. Run the statement:
  INSERT INTO aaa VALUES(300*RAND(), 0, 1) ON DUPLICATE KEY UPDATE count=count+1;
continuously in 50 parallel threads.

3. Look at the deadlock.

Suggested fix:
Remove the deadlock? Increase  LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK?
[11 Oct 2015 3:16] MySQL Verification Team
Thank you for the bug report. I couldn't repeat with 5.6.27 running on CentOS 6.5 and  source server on Windows. Please provide your my.cnf. Thanks.
[11 Oct 2015 9:36] Yiftach Kaplan
Hi,

If you are using virtual machine make sure it have more than one CPU.

my.cnf:

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
#innodb-log-file-size           = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 2G

# LOGGING #
log-error                      = /var/log/mysql/mysql-error.log
log-queries-not-using-indexes  = 0
slow-query-log-file            = /var/log/mysql/mysql-slow.log
slow-query-log                 = 0

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Settings user and group are ignored when systemd is used (fedora >= 15).
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
user=mysql
character-set-server=utf8
collation-server=utf8_general_ci
init-connect='SET NAMES utf8'
innodb_autoinc_lock_mode=0

# Semisynchronous Replication
# http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
# uncomment next line on MASTER
;plugin-load=rpl_semi_sync_master=semisync_master.so
# uncomment next line on SLAVE
;plugin-load=rpl_semi_sync_slave=semisync_slave.so

# Others options for Semisynchronous Replication
;rpl_semi_sync_master_enabled=1
;rpl_semi_sync_master_timeout=10
;rpl_semi_sync_slave_enabled=1

# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[client]
default-character-set=utf8

[mysql]
# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock
[11 Oct 2015 20:27] MySQL Verification Team
Thank you for the feedback. When running my multi-threaded client against servers 5.6 and 5.7 when enabled the below variable:

innodb_autoinc_lock_mode=0

I got the below errors:

Server Thread ID: 26
Server Thread ID: 27
Server Thread ID: 28
Server Thread ID: 29
Server Thread ID: 30
Server Thread ID: 31
Error (query): Deadlock found when trying to get lock; try restarting transaction
Error (query): Deadlock found when trying to get lock; try restarting transaction
Server Thread ID: 32
Error (query): Deadlock found when trying to get lock; try restarting transaction
Error (query): Deadlock found when trying to get lock; try restarting transaction
Error (query): Deadlock found when trying to get lock; try restarting transaction
Server Thread ID: 33
Server Thread ID: 34
Error (query): Deadlock found when trying to get lock; try restarting transaction
Error (query): Deadlock found when trying to get lock; try restarting transaction
Error (query): Deadlock found when trying to get lock; try restarting transaction
Server Thread ID: 35
Error (query): Deadlock found when trying to get lock; try restarting transaction
Server Thread ID: 36
Error (query): Deadlock found when trying to get lock; try restarting transaction
Error (query): Deadlock found when trying to get lock; try restarting transaction
Error (query): Deadlock found when trying to get lock; try restarting transaction
Error (query): Deadlock found when trying to get lock; try restarting transaction
Server Thread ID: 37
Error (query): Deadlock found when trying to get lock; try restarting transaction
Server Thread ID: 38
Server Thread ID: 39
Server Thread ID: 40
Server Thread ID: 41
Server Thread ID: 42
Server Thread ID: 43
Server Thread ID: 44
Server Thread ID: 45
Server Thread ID: 46
Server Thread ID: 47
Server Thread ID: 48
Server Thread ID: 49
Server Thread ID: 50

That no happens with server 5.5.

When I comment innodb_autoinc_lock_mode=0 the issue goes away. Please check on your side disabling it too. Thanks.
[12 Oct 2015 8:11] Yiftach Kaplan
Yes I get the same behavior (deadlock only when innodb_autoinc_lock_mode is 0). It's still a bug of course....
[12 Oct 2015 15:08] MySQL Verification Team
Version 5.5 not affected only 5.6 and 5.7 server versions.
[4 Apr 2016 13:47] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.6.31, 5.7.13, 5.8.0 release, and here's the changelog entry:

With autoinc_lock_mode=0, multiple threads waiting for a table-level lock
caused an unexpected deadlock. 

Thank you for the bug report.
[26 Sep 2016 21:56] Ed Dawley
The bug is reproducible on 5.6.30 with autoinc_lock_mode=1.  All you need is a long INSERT INTO ... SELECT query sprinkled in amongst many concurrent small inserts.  The fix for 5.6.31 appears to address this case as well.

Adding this comment for others who, like myself, did not think this bug would affect them under the default value of autoinc_lock_mode.  The release notes for 5.6.31 really should be updated to indicate that the default configuration is affected as well.