MySQL Bugs: #77399: Deadlocks missed by INFORMATION_SCHEMA.INNODB_METRICS lock

Bug #77399	Deadlocks missed by INFORMATION_SCHEMA.INNODB_METRICS lock_deadlocks counter
Submitted:	18 Jun 2015 8:22	Modified:	13 Jan 2020 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

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";

Hello Laurynas,

Thank you for the report and test case.

Thanks,
Umesh

Also, confirmed with 5.7.8

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.

When this is fixed, the next issue is that lock_deadlocks counter is not reset by SET GLOBAL innodb_monitor_reset = lock_deadlocks

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.

8.0 is also affected

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.

The latest contributed fix applies cleanly and works on 8.0.4 too.

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.

Thank you for the contributions!

Regards,
Umesh

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!

Thanks Jakub, if the code is about to be replaced soon, then it makes sense to take only the testcase

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.

Thank you.

Is the version number in the above description a typo and this is actually fixed in 8.0.19 not .18?

I am sorry, this is indeed fixed in 8.0.18.