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

[9 Aug 2007 14:17] Victoria Reznichenko
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.
[21 Dec 2007 12:49] Bugs System
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.
[22 Jan 2008 14:58] Dmitry Lenev
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.
[28 Jan 2008 12:52] Bugs System
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.
[11 Feb 2008 16:23] Bugs System
Pushed into 5.1.24-rc
[11 Feb 2008 16:26] Bugs System
Pushed into 6.0.5-alpha
[11 Feb 2008 20:15] Paul DuBois
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.
[6 Mar 2008 9:03] Jon Stephens
Also documented for 5.1.23-ndb-6.2.14.
[30 Mar 2008 18:56] Jon Stephens
Also documented for 5.1.23-ndb-6.3.11.
[18 Dec 2009 20:33] Bugs System
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.
[15 Jan 2010 9:00] Bugs System
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)
[5 Feb 2010 11:48] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100204063540-9czpdmpixi3iw2yb) (version source revid:alik@sun.com-20091224075613-es9uswo4lidkm3tj) (pib:16)
[5 Feb 2010 11:53] Bugs System
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)
[5 Feb 2010 12:00] Bugs System
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)
[10 Feb 2010 19:44] Paul DuBois
Noted in 5.5.2 changelog.

Setting report to Need Merge pending push to Celosia.
[12 Mar 2010 14:07] Bugs System
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)
[12 Mar 2010 14:23] Bugs System
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)
[12 Mar 2010 14:37] Bugs System
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)
[12 Mar 2010 17:54] Paul DuBois
Fixed in earlier 5.1.x, 5.5.x.