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: | |
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
[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.