I recently upgraded to mysql 5.7 and observing frequent deadlock issue. Is there any change related to locking in 5.7?

Question

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 inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL 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 waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 39420432524, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL 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 table

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Kaitlin

Your title seem to imply that 1) you recently upgraded to 5.7, and 2) you did not experience these problems before. If those are true, please directly state them in your question, as well as what version you upgraded from. The actual query and the SHOW CREATE TABLE tablename output would not hurt, either, regardless of whether the other points are true. – Heathenize

Thank Willem Renzema and Ryan Vincent for the reply. I have added few more details related to question. Please Help. – Kaitlin

Answer 1

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 concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs.

Disabling it could improve your performance and reduce deadlock issue.

Mccarver