MySQL Bugs: #112427: please provide a deadlock detection counter in MySQL

Bug #112427	please provide a deadlock detection counter in MySQL
Submitted:	22 Sep 2023 9:34	Modified:	25 Sep 2023 5:08
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	8.X	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL currently allows you to find deadlocks (mainly in InnoDB) via output such as that from : SHOW ENGINE INNODB STATUS. It's also possible to make deadlock detection log to the error log.

However, there appears to be no counter of the number of deadlocks seen so visibility is poor.

Deadlocks may be normal but probably having a lot of them is not ideal and may be an indication that application concurrent access may need to be modified to reduce  this happening so better visibility of this would be good.

A counter is simple to monitor and variations in the value would be a useful metric to catch code changes causing unexpected behaviour.

The current deadlock handling is able to find and report the deadlocks so all that is needed is to add a counter which is visible to the user.

How to repeat:
Look for a deadlock counter. I could not find one.

Suggested fix:
Provide a deadlock counter possibly via a global status value.

Related to: bug#108891

Printing out the deadlock information in the logs if the frequency of the deadlocks is high would not be a good idea.  The counter would be a good place to be aware of this and then further analysis by using SHOW ENGINE INNODB STATUS or setting innodb_print_all_deadlocks = 1 could follow.

Hello Simon,

Thank you for the feature request!

regards,
Umesh

Is information_schema.innodb_metric not enough?

https://dev.mysql.com/doc/refman/8.1/en/innodb-information-schema-metrics-table.html

mysql81 17> SELECT * FROM information_schema.innodb_metrics WHERE name = 'lock_deadlocks'\G
*************************** 1. row ***************************
           NAME: lock_deadlocks
      SUBSYSTEM: lock
          COUNT: 2
      MAX_COUNT: 2
      MIN_COUNT: NULL
      AVG_COUNT: 0.0000008572662779223354
    COUNT_RESET: 2
MAX_COUNT_RESET: 2
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
   TIME_ENABLED: 2023-08-26 21:19:07
  TIME_DISABLED: NULL
   TIME_ELAPSED: 2332997
     TIME_RESET: NULL
         STATUS: enabled
           TYPE: counter
        COMMENT: Number of deadlocks
1 row in set (0.00 sec)

Apologies. you are right. There are metrics everywhere in MySQL and sometimes you need to know where to look.

This is also in 8.0.

So apologies for requesting a feature that's already been implemented and thanks for pointing out where to find it.