MySQL Bugs: #30331: Table_locks_waited shows inaccurate values

Bug #30331	Table_locks_waited shows inaccurate values
Submitted:	9 Aug 2007 16:17	Modified:	11 Feb 21:15
Reporter:	Victoria Reznichenko
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:	Davi Arnaut	Target Version:	5.1
Triage:	D4 (Minor)

Description:
Table_locks_waited is incremented not when query request for a lock and can not get it but
when query that held lock finishes and our query acquires a lock.

This cause Table_locks_waited shows inaccurate value because if query gets aborted while
it is waiting for lock Table_locks_waited is not incremented though it satisfies
Table_locks_waited description "The number of times that a table lock could not be
acquired immediately and a wait was needed."

This is also different from Innodb_row_lock_waits behaviour. When query gets locked both
Innodb_row_lock_waits and Innodb_row_lock_current_waits incremented.
If query acquires a lock or gets aborted Innodb_row_lock_current_waits decremented.

How to repeat:
1. Do FLUSH STATUS to clean status variables.
2. In one session do LOCK TABLE t1 WRITE;
3. Do SHOW GLOBAL STATUS LIKE "Table_locks%"; Table_locks_immediate should be incremented
as expected.
4. In another session run any query against t1. It will be locked. 
5. Do SHOW GLOBAL STATUS LIKE "Table_locks%" again. The second session is locked and value
Table_locks_waited remains the same.
6a. If you abort query, check Table_locks_waited - no changes
6b. If you unlock t1, Table_locks_waited will be incremented just after UNLOCK TABLES.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40336

ChangeSet@1.2583, 2007-12-21 10:49:21-02:00, davi@mysql.com +3 -0
  Bug#30331 Table_locks_waited shows inaccurate values
  
  The problem is that the Table_locks_waited was incremented only
  when the lock request succeed. If a thread waiting for the lock
  gets killed or the lock request is aborted, the variable would
  not be incremented, leading to inaccurate values in the variable.
  
  The solution is to increment the Table_locks_waited whenever the
  lock request is queued. This reflects better the intended behavior
  of the variable -- show how many times a lock was waited.

I think it is OK to push this patch into 5.1 after adjusting test case to use new and nice
facilities instead of wait_show_pattern.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41298

ChangeSet@1.2661, 2008-01-28 10:52:41-02:00, davi@mysql.com +3 -0
  Bug#30331 Table_locks_waited shows inaccurate values
  
  The problem is that the Table_locks_waited was incremented only
  when the lock request succeed. If a thread waiting for the lock
  gets killed or the lock request is aborted, the variable would
  not be incremented, leading to inaccurate values in the variable.
  
  The solution is to increment the Table_locks_waited whenever the
  lock request is queued. This reflects better the intended behavior
  of the variable -- show how many times a lock was waited.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Noted in 5.1.24, 6.0.5 changelogs.

The Table_locks_waited waited variable was not incremented in the 
cases that a lock had to be waited for but the waiting thread was 
killed or the request was aborted.

Also documented for 5.1.23-ndb-6.2.14.

Also documented for 5.1.23-ndb-6.3.11.