Bug #94775 Innodb_row_lock_current_waits status variable incorrect values on idle server
Submitted: 26 Mar 4:37 Modified: 2 May 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

[26 Mar 4:37] Uday Sitaram
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.
[26 Mar 4:38] Uday Sitaram
data collected

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

[26 Mar 14:58] Sinisa Milivojevic
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.
[27 Apr 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".