Bug #71520 Constantly increasing Innodb_row_lock_current_waits value
Submitted: 30 Jan 2014 8:43 Modified: 30 Apr 2018 12:19
Reporter: Alex Ru Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.6.14 OS:Any (CentOS release 5.6)
Assigned to: CPU Architecture:Any

[30 Jan 2014 8:43] Alex Ru
Description:
The value of Innodb_row_lock_current_waits is constantly increasing since db start. Seems to me that lock release doesn't decrease this value in some cases.

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 755389160
Server version: 5.6.14-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show status where Variable_name = 'Innodb_row_lock_current_waits';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 54    |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> select count(*) from information_schema.innodb_lock_waits;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> 

How to repeat:
Just start monitoring locks with 'show status' command
[30 Jan 2014 8:45] Alex Ru
Innodb_row_lock_current_waits history

Attachment: mysql-innodb-locks.png (image/png, text), 32.68 KiB.

[30 Jan 2014 8:50] Alex Ru
Some details added
[30 Jan 2014 18:24] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior with current developemnt server.
[30 Jan 2014 18:25] Sveta Smirnova
I am sorry: clicked "Submit" too fast. Please try with current version 5.6.15 and if issue still exists indicate accurate package name you use (file name you downloaded).
[1 Mar 2014 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[6 Apr 2014 12:48] Daniel Rogers
I am seeing the same behaviour.  Here's my package information:

# rpm -qi Percona-Server-server-56
Name        : Percona-Server-server-56
Version     : 5.6.16
Release     : rel64.2.el6
Architecture: x86_64
Install Date: Thu 27 Mar 2014 10:09:24 AM PDT
Group       : Applications/Databases
Size        : 90044320
License     : Copyright (c) 2000, 2010, Oracle and/or its affiliates.  All rights reserved.  Use is subject to license terms.  Under GPL license as shown in the Description field.
Signature   : DSA/SHA1, Tue 25 Mar 2014 05:17:27 AM PDT, Key ID 1c4cbdcdcd2efd2a
Source RPM  : Percona-Server-56-5.6.16-rel64.2.el6.src.rpm
Build Date  : Mon 24 Mar 2014 09:17:46 AM PDT
Build Host  : jhc-new-2-centos6-64.novalocal
Relocations : (not relocatable)
Packager    : Percona MySQL Development Team <mysqldev@percona.com>
Vendor      : Percona, Inc
URL         : http://www.percona.com/
Summary     : Percona Server: a very fast and reliable SQL database server
Description :
The Percona Server software delivers a very fast, multi-threaded, multi-user,
and robust SQL (Structured Query Language) database server. Percona Server
is intended for mission-critical, heavy-load production systems.

Percona recommends that all production deployments be protected with a support
contract (http://www.percona.com/mysql-suppport/) to ensure the highest uptime,
be eligible for hot fixes, and boost your team's productivity.

This package includes the Percona Server with XtraDB binary
as well as related utilities to run and administer Percona Server.

If you want to access and work with the database, you have to install
package "Percona-Server-client-56" as well!
[4 Jun 2014 14:54] Daniel Rogers
Is anyone actually looking at this?  I updated this ticket two months ago!
[11 Jul 2014 17:42] Sveta Smirnova
Thank you for the feedback.

But we don't support Percona server. Please try with Oracle MySQL binaries, available from dev.mysql.com/downloads and inform us if the problem still exists.
[18 Jul 2014 15:41] Chris Calender
I've seen this occur in *MySQL*:

5.6.13-log MySQL Community Server (GPL)

I don't have a reproducible test case yet, but in my case, it occurred after a deadlock.

Innodb_row_lock_current_waits has remained at 26 even after the deadlock, and with no active transactions, etc.
[24 Jul 2014 19:07] Sveta Smirnova
test case for MTR

Attachment: bug71520.test (application/octet-stream, text), 1.46 KiB.

[24 Jul 2014 19:08] Sveta Smirnova
Thank you for the feedback.

I still cannot repeat described behavior with test case attached and after I close all transactions. Please try with current version 5.6.19 and if problem still repeatable for you ensure that you closed all connections, waiting for a lock (including those which participated in deadlock) and try to create repeatable test case.
[30 Jul 2014 22:03] Chris Calender
Hi Sveta,

Thank you for attempting to reproduce this.  I've been unable to reproduce this as well - spent quite a few hours/days, even on some older versions.

I mimic'ed the deadlock, down to the exact table (w/ partitions), exact statements, same order, same locks/lock states by the preceding statements in the transactions (i.e., the statements holding the locks leading to the deadlock), the number of innodb_current_row_lock_waits, etc.  No matter what I've tried, innodb_current_row_lock_waits remains the same.

It's so minor, I'm hanging up trying to reproduce this.

Thanks again. :)

Best wishes,
Chris
[1 Aug 2014 19:30] Sveta Smirnova
Thank you for the feedback.

So I will close the bug as "Can't repeat". If anyone would be able to repeat this with Oracle MySQL binaries feel free to leave a comment and we will reconsider.
[2 Mar 2015 18:01] Michael Messina
I was able to reproduce this issue using mysql Enterprise Server 5.6.12

Server version: 5.6.12-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show status where Variable_name = 'Innodb_row_lock_current_waits';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 14    |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> select count(*) from information_schema.innodb_lock_waits;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
[3 Mar 2015 19:17] Sveta Smirnova
Michael,

thank you for the feedback.

But version 5.6.12 is very old. Please try with current version 5.6.23 and, if problem still exists, try to create repeatable test case: identify which load lead to this situation.
[4 Apr 2015 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[25 Jun 2015 6:58] Zhenye Xie
in my environment, I got a strange value. (-1 as uint64)

mysql> show status where Variable_name = 'Innodb_row_lock_current_waits';
+-------------------------------+----------------------+
| Variable_name                 | Value                |
+-------------------------------+----------------------+
| Innodb_row_lock_current_waits | 18446744073709551615 |
+-------------------------------+----------------------+
1 row in set (0.06 sec)

in lock0wait.cc, srv_stats.n_lock_wait_current_count.inc() srv_stats.n_lock_wait_current_count.dec();
is not thread safe. srv_stats is a global variable, but it has just a lock on trx surrounds changing the value.
[9 Jul 2015 0:19] Scott Nemes
I am seeing the same thing on a handful of 5.6.16 servers. Noticed this today, so no test case yet, but found this bug report while researching it.

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.16-log |
+------------+
1 row in set (0.00 sec)

mysql> show global status like "Innodb_row_lock_current_waits";
+-------------------------------+----------------------+
| Variable_name                 | Value                |
+-------------------------------+----------------------+
| Innodb_row_lock_current_waits | 18446744073709551615 |
+-------------------------------+----------------------+
1 row in set (0.00 sec)

mysql> select count(*) from information_schema.innodb_lock_waits;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
[6 Jan 2016 18:15] Nasser Naini
I am seeing this issue in MySQL 5.6 enterprise edition.

>select count(*) from information_schema.innodb_lock_waits;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.02 sec)

>show global status like 'Innodb_row_lock_current_waits';
+-------------------------------+----------------------+
| Variable_name                 | Value                |
+-------------------------------+----------------------+
| Innodb_row_lock_current_waits | 18446744073709551615 |
+-------------------------------+----------------------+
1 row in set (0.01 sec)

>select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.20-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.01 sec)
[14 Jun 2016 9:55] Ángel OR
Good morning, 

I am seeing this in MySQL 5.7.11-log:

mysql> show status like '%Innodb_row_lock_current%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 45    |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> select count(*) from information_schema.innodb_lock_waits;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.11-log |
+------------+
1 row in set (0.00 sec)
[8 Feb 2017 17:59] Chris Calender
Seeing this still in latest 5.6.

Began after a deadlock again, but of course, not easily reproducible.

The output of the following 2 commands:

SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_current_waits';
SELECT COUNT(*) FROM INFORMATION_SCHEMA.innodb_lock_waits;

Returned:

Variable_name	Value
Innodb_row_lock_current_waits	2823

COUNT(*)
0
[11 Apr 2017 23:19] ben smith
I am seeing this on:
| version                         | 5.7.11-log                           |
| version_comment                 | MySQL Community Server (GPL)         |

It does sometimes go down, although over the last 7 days of uptime it has reached > 100,000:
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 113349 |
+-------------------------------+--------+

We see it every few minutes, if there is interesting data I can collect about query types please let me know, would love to help!
[30 Apr 2018 12:19] MySQL Verification Team
Hi,

I have tried to repeat it by starting several transactions in parallel. After I closed them all, that value went to zero.

Hence, we need a fully reproducible test case in order to verify this. We can not verify it without a full and proper test case.

Thank you very much, in advance.
[19 Sep 2019 6:57] Ke Lu
Also observed in 5.7.25-log. Innodb_row_lock_current_waits remains to be an unchanged value and there is no active transactions.
[13 Sep 2022 12:24] Martin Vobruba
Same issue with 5.7.39.

I was having high load due to many row locks. I killed some threads to resolve the issue and since then:

mysql> show status where Variable_name = 'Innodb_row_lock_current_waits';
+-------------------------------+----------------------+
| Variable_name                 | Value                |
+-------------------------------+----------------------+
| Innodb_row_lock_current_waits | 18446744073709551579 |
+-------------------------------+----------------------+
[19 Sep 2022 12:21] MySQL Verification Team
Hi Mr. Vobruba,

We were never able to obtain the value that you reported.

Hence, we would require a fully repeatable test case that would always lead to the MAX_LONG value.
[25 Oct 2022 0:29] PLACE rock
This issue is reproduced in mysql Enterprise Server 8.0.23.

mysql> mysql> show status like 'Innodb_row_lock%';
+-------------------------------+----------------------+
| Variable_name | Value |
+-------------------------------+----------------------+
| Innodb_row_lock_current_waits | 18446744073709551615 |
| Innodb_row_lock_time | 1449292947 |
| Innodb_row_lock_time_avg | 149 |
| Innodb_row_lock_time_max | 412314 |
| Innodb_row_lock_waits | 9689355 |
+-------------------------------+----------------------+

It is the case like upthread..
[31 Oct 2022 13:19] MySQL Verification Team
Hi,

8.0.23 is a very old release. Please try to reproduce it with 8.0.31.

If you manage to reproduce it with 8.0.31, send us a repeatable test case.