Bug #77399 Deadlocks missed by INFORMATION_SCHEMA.INNODB_METRICS lock_deadlocks counter
Submitted: 18 Jun 2015 8:22 Modified: 13 Jan 14:59
Reporter: Laurynas Biveinis (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.25,5.7.7, 5.6.26, 5.7.8, 8.0.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution, deadlocks, innodb_metrics

[18 Jun 2015 8:22] Laurynas Biveinis
Description:
Some deadlocks do not bump the INFORMATION_SCHEMA.INNODB_METRICS lock_deadlocks counter. The MTR below will result in

SELECT NAME, COUNT, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="lock_deadlocks";
NAME	COUNT	COMMENT
lock_deadlocks	0	Number of deadlocks

But if you check ie. engine status, you will see

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-06-18 11:20:16 10d7a0000
*** (1) TRANSACTION:
TRANSACTION 1292, ACTIVE 0 sec starting index read
... info ...

How to repeat:
--source include/have_innodb.inc

connect (con1,localhost,root,,);
connect (con2,localhost,root,,);

create table t(a INT PRIMARY KEY, b INT) engine=InnoDB;
insert into t values(2,1);
insert into t values(1,2);

SET GLOBAL innodb_monitor_reset=lock_deadlocks;

connection con1;
BEGIN; SELECT b FROM t WHERE a=1 FOR UPDATE;

connection con2;
BEGIN; SELECT b FROM t WHERE a=2 FOR UPDATE;

connection con1;
SEND SELECT b FROM t WHERE a=2 FOR UPDATE;

connection con2;
SEND SELECT b FROM t WHERE a=1 FOR UPDATE;

connection con1;
--error 0,ER_LOCK_DEADLOCK
reap;
ROLLBACK;

connection con2;
--error 0,ER_LOCK_DEADLOCK
reap;
ROLLBACK;

connection default;

SELECT NAME, COUNT, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="lock_deadlocks";
[18 Jun 2015 8:55] Umesh Shastry
Hello Laurynas,

Thank you for the report and test case.

Thanks,
Umesh
[18 Jun 2015 8:57] Umesh Shastry
Also, confirmed with 5.7.8
[18 Jun 2015 9:47] Laurynas Biveinis
Bug 77399 patch

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug77399.patch (application/octet-stream, text), 678 bytes.

[14 Feb 2017 9:49] Laurynas Biveinis
When this is fixed, the next issue is that lock_deadlocks counter is not reset by SET GLOBAL innodb_monitor_reset = lock_deadlocks
[2 Jun 2017 2:02] Laurynas Biveinis
Bug 77399 fix for 8.0.1

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug77399-8.0.1.patch (application/octet-stream, text), 4.56 KiB.

[2 Jun 2017 2:03] Laurynas Biveinis
8.0 is also affected
[5 Aug 2017 7:18] Laurynas Biveinis
Bug 77399 fix for 8.0.2

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug77399-8.0.2.patch (application/octet-stream, text), 4.56 KiB.

[2 Feb 2018 5:29] Laurynas Biveinis
The latest contributed fix applies cleanly and works on 8.0.4 too.
[13 Jun 2018 12:27] Laurynas Biveinis
Bug 77399 fix for 8.0.11

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug77399-8.0.11.patch (application/octet-stream, text), 4.58 KiB.

[14 Jun 2018 5:06] Umesh Shastry
Thank you for the contributions!

Regards,
Umesh
[14 Jun 2018 7:08] Jakub Lopuszanski
Hi!
 
Thanks for the contribution! Indeed it looks like a bug and fix also seems sensible.

We plan to rewrite deadlock detection code, to make it run faster (linear instead of quadratic time) and in parallel to other lock_sys operations (instead of holding global latch). In particular the part of the code which your patch affects will be removed entirely in near future, so it doesn't seem worthwhile to go through the process of applying it. I'll make sure that the new code will pass your tests!
[14 Jun 2018 8:03] Laurynas Biveinis
Thanks Jakub, if the code is about to be replaced soon, then it makes sense to take only the testcase
[9 Jan 15:54] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.18 release, and here's the changelog entry:

The INFORMATION_SCHEMA.INNODB_METRICS lock_deadlocks counter did not
count all deadlocks. 

Thanks to Laurynas Biveinis for the contribution.
[10 Jan 8:12] Laurynas Biveinis
Thank you.

Is the version number in the above description a typo and this is actually fixed in 8.0.19 not .18?
[13 Jan 14:59] Laurynas Biveinis
I am sorry, this is indeed fixed in 8.0.18.