MySQL Bugs: #30331: Table_locks_waited shows inaccurate values

Bug #30331	Table_locks_waited shows inaccurate values
Submitted:	9 Aug 2007 14:17	Modified:	12 Mar 2010 17:54
Reporter:	Victoria Reznichenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any

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.

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/95014

3287 Davi Arnaut	2009-12-18
      Bug#30331: Table_locks_waited shows inaccurate values
      
      Post-merge fix: wait for statement result before disconnecting.
      Otherwise, the statement might affect unrelated tests.
     @ mysql-test/t/lock_multi.test
        Reap statement status.

Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:davi.arnaut@sun.com-20091218203255-47sd3q50lrqm36hz) (merge vers: 5.1.42) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100204063540-9czpdmpixi3iw2yb) (version source revid:alik@sun.com-20091224075613-es9uswo4lidkm3tj) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100205113942-oqovjy0eoqbarn7i) (version source revid:alik@sun.com-20100204064210-ljwanqvrjs83s1gq) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.2-m2 (revid:alik@sun.com-20100203172258-1n5dsotny40yufxw) (version source revid:alexey.kopytov@sun.com-20091223134205-pk9yvgfvpn3hy7lh) (merge vers: 5.5.1-m2) (pib:16)

Noted in 5.5.2 changelog.

Setting report to Need Merge pending push to Celosia.

Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

Fixed in earlier 5.1.x, 5.5.x.