I recently upgraded to mysql 5.7 and observing frequent deadlock issue. Is there any change related to locking 5.7 because I didn't experienced this problem before.

I have a simple table called retry_records having auto incremented id as a primary key and a column unique_reference as an unique index and two more columns. This table is getting used in fully multi threaded environment where a section of code is selecting n records from this table, process the data and update this table in case of process failure and other section of code is deleting few processed records from the table and another section of code is inserting a new record in this table. There is no join in any query, no batch insert, batch update, batch delete except select. All the update/delete/insert transaction has single statement. Even then I am observing deadlock on insertion.Below are the logs:

------------------------LATEST DETECTED DEADLOCK------------------------2016-09-22 13:05:09 0x7f3f427e0700*** (1) TRANSACTION:TRANSACTION 39420432534, ACTIVE 0 sec insertingmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 37503, OS thread handle 139917976340224, query id 1695822465

Insert Query..

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 1334 page no 1726952 n bits 312 index id of table `database_name`.`table_name` trx id 39420432534 lock_mode X insert intention waitingRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;*** (2) TRANSACTION:TRANSACTION 39420432524, ACTIVE 0 sec insertingmysql tables in use 1, locked 16 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1MySQL thread id 37494, OS thread handle 139909675222784, query id 1695822438

Another insert query...

*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 1334 page no 1726952 n bits 312 index id of table*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 1334 page no 1726952 n bits 312 index id of tableRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;
1

Best Answer


I experienced similar issue. Since 5.7, there is a new parameter name innodb_deadlock_detect

This option is used to disable deadlock detection. On high concurrencysystems, deadlock detection can cause a slowdown when numerous threadswait for the same lock. At times, it may be more efficient to disabledeadlock detection and rely on the innodb_lock_wait_timeout settingfor transaction rollback when a deadlock occurs.

Disabling it could improve your performance and reduce deadlock issue.