MySQL Bugs: #94775: Innodb_row_lock_current_waits status variable incorrect values on idle server

Bug #94775	Innodb_row_lock_current_waits status variable incorrect values on idle server
Submitted:	26 Mar 2019 4:37	Modified:	2 May 2019 13:18
Reporter:	Uday Sitaram	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.7.24, 8.0.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	global status counters, innodb, row locks

Description:
I have run a simple read-write sysbench test on my local Oracle 8.0 docker container. the test took a few seconds and completed without any issues. 

After the test finished, I still see positive values for Innodb_row_lock_current_waits which is expected to be zero as there are no active transactions on the server.

I can not reproduce this every time but often I can. required details are attached for your review.

How to repeat:
Below steps can be followed to reproduce this.  

1. Prepare the database for read-write test:
sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-password=root123 --mysql-db=DB1 --num-threads=20 prepare

2. Run the test.
sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-password=root123 --mysql-db=DB1 --num-threads=20 run

3. Check global status.
mysql> show global status like '%Innodb_row_lock_current_waits%';

Observations:
1. I have spinning disks rather SSD's
2. During the test, there were deadlocks which are expected.
3. If I restart the container, is Innodb_row_lock_current_waits reset to zero.

I have attached below details captured during the test.

SHOW ENGINE INNODB STATUS\G
SHOW FULL PROCESSLIST ;
SELECT * FROM sys.innodb_lock_waits;

Suggested fix:
Reset the value accordingly when a transaction waiting for a row lock has received the lock.

data collected

Attachment: Oracle8_innodb_current_row_lock_waits.txt (text/plain), 9.53 KiB.

Hi,

Thank you for your bug report.

We have analysed it and it seems this might not be a bug. 

Yes, you are correct that all threads use the same counter instance, which seems to match your hypothesis.

However, this counters seem to be designed as "fuzzy" so we do not think that this is really a bug if they show a wrong value. Hence, the behaviour is so by design.

There is no mutex protection for these counters as their exact value is not required.

I hope that you have understood this message fully.

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

In the ref:
Innodb_row_lock_current_waits :
The number of row locks currently being waited for by operations on InnoDB tables.

when Innodb_row_lock_current_waits  is some value not 0,the instance has not transaction or lock,How this design?  why not a bug?

As a workaround, since 8.0.19, if you don't expect threads waiting for table locks (which is rather rare), you can use the precise (eventually consistent) counter `lock_threads_waiting`, which is enabled by default, and can be queried with:

mysql> SELECT `COUNT`,`COMMENT` FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="lock_threads_waiting";
+-------+-----------------------------------------------------+
| COUNT | COMMENT                                             |
+-------+-----------------------------------------------------+
|     0 | Number of query threads sleeping waiting for a lock |
+-------+-----------------------------------------------------+
1 row in set (0.00 sec)

The drawback is that as the name suggests, it doesn't distinguish between locks on records and locks on tables. The positive thing is that its value can't drift over time, because it is refreshed each time deadlock detector scans all the waiting transactions to match the reality.

If you need to subtract the threads waiting for a table lock, you can query performance_schema.data_locks for that purpose:

select count(*) from performance_schema.data_locks WHERE LOCK_STATUS="WAITING" AND LOCK_TYPE="TABLE";

however this query will return 0 most of the time, even if your application uses LOCK TABLE t FOR SHARE etc., because most of the table-level locks are handled at the Server layer, which is above InnoDB, so queries do not even reach the InnoDB layer until the Server grants them permission, so InnoDB rarely gets a chance to observe a transaction waiting on a table lock.

I have monitors in my environment that track these counters, and I can confirm that these values *never* drop from their highest value once they increment.
This *does* seem like a bug, as what's the point of a "current" counter if it doesn't provide correct information over time?

I could understand if the values were slightly delayed, but until I restart the mysqld service, the counters. remain at their maximum value and never decrease.

e.g.

```
mysql> SELECT `COUNT`,`COMMENT` FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="lock_threads_waiting";
+-------+-----------------------------------------------------+
| COUNT | COMMENT                                             |
+-------+-----------------------------------------------------+
|     1 | Number of query threads sleeping waiting for a lock |
+-------+-----------------------------------------------------+
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 | 48    |
+-------------------------------+-------+
1 row in set (0.00 sec)
```

Hello Andrew Ernst,
Could you please clarify if you believe the result (1) of
SELECT `COUNT`,`COMMENT` FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="lock_threads_waiting"; 
to be correct?