MySQL Bugs: #39897: lock_multi fails in pushbuild: timeout waiting for processlist

Bug #39897	lock_multi fails in pushbuild: timeout waiting for processlist
Submitted:	7 Oct 2008 6:13	Modified:	6 Mar 2010 23:25
Reporter:	Sven Sandberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Tests: Server	Severity:	S7 (Test Cases)
Version:	5.1, 6.0	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	pushbuild, sporadic, test failure

Description:
main.lock_multi fails in two ways on pushbuild. These are similar but not the same as BUG#34311. These failures occurred after BUG#34311 was closed.

-------- FAILURE 1 --------
main.lock_multi                [ fail ]

--- /data0/pushbuild/pb2/pb/mysql-6.0-opt/205/mysql-6.0.6-alpha-pb205/mysql-test/r/lock_multi.result	2008-05-04 22:28:47.000000000 +0300
+++ /data0/pushbuild/pb2/pb/mysql-6.0-opt/205/mysql-6.0.6-alpha-pb205/mysql-test/r/lock_multi.reject	2008-05-04 23:05:48.246348823 +0300
@@ -102,6 +102,8 @@
 lock table t1 read;
 update t1 set i= 10;;
 select * from t1;;
+Timeout in wait_condition.inc for select count(*) = 1 from information_schema.processlist
+where state = "Table lock" and info = "select * from t1"
 kill query ID;
 i
 ERROR 70100: Query execution was interrupted

mysqltest: Result content mismatch

Stopping All Servers
Restoring snapshot of databases
-------- END FAILURE 1 --------

-------- FAILURE 2 --------
main.lock_multi                [ fail ]

--- /data0/pushbuild/pb1-3/pb/mysql-6.0-runtime/261/mysql-6.0.6-alpha-pb261/mysql-test/r/lock_multi.result	2008-04-21 00:39:45.000000000 +0300
+++ /data0/pushbuild/pb1-3/pb/mysql-6.0-runtime/261/mysql-6.0.6-alpha-pb261/mysql-test/r/lock_multi.reject	2008-04-21 01:06:40.000000000 +0300
@@ -162,5 +162,9 @@
 connection: flush
 flush tables with read lock;;
 connection: default
+Timeout in wait_condition.inc for select count(*) = 1 from information_schema.processlist
+where state = "Flushing tables"
 flush tables;
+Timeout in wait_condition.inc for select count(*) = 1 from information_schema.processlist
+where state = "Flushing tables"
 drop table t1;

mysqltest: Result length mismatch

Stopping All Servers
Restoring snapshot of databases
-------- END FAILURE 2 --------

How to repeat:
xref: http://tinyurl.com/4cne8s

Example of failure 1:
https://intranet.mysql.com/secure/pushbuild/showpush.pl?dir=bzr_mysql-5.1-rpl&order=59 vm-win2003-64-b/n_mix

Example of failure 2:
https://intranet.mysql.com/secure/pushbuild/showpush.pl?dir=mysql-6.0-build&order=172 sles10-ia64-a-1/n_stm

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

2809 Davi Arnaut	2008-12-16
      Bug#39897: lock_multi fails in pushbuild: timeout waiting for processlist
      
      The problem is that relying on the "Table lock" thread state to
      detect that a thread is waiting on a lock is race prone. The "Table
      lock" state change happens before the thread actually tries to grab
      a lock on a table.
      
      The solution is to introduce a new "Waiting for lock" state that is
      set only when a thread is actually going to wait for a lock. The state
      is change happens after the thread fails to grab the lock (because it
      is owned by other thread) and proceeds to wait on a condition.

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

2967 Davi Arnaut	2009-01-15
      Bug#39897: lock_multi fails in pushbuild: timeout waiting for processlist
      
      The problem is that relying on the "Table lock" thread state in
      its current position to detect that a thread is waiting on a lock
      is race prone. The "Table lock" state change happens before the
      thread actually tries to grab a lock on a table.
      
      The solution is to move the "Table lock" state so that its set
      only when a thread is actually going to wait for a lock. The state
      change happens after the thread fails to grab the lock (because it
      is owned by other thread) and proceeds to wait on a condition.

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

3039 Davi Arnaut	2009-01-15
      Bug#39897: lock_multi fails in pushbuild: timeout waiting for processlist
      
      The problem is that relying on the "Table lock" thread state in
      its current position to detect that a thread is waiting on a lock
      is race prone. The "Table lock" state change happens before the
      thread actually tries to grab a lock on a table.
      
      The solution is to move the "Table lock" state so that its set
      only when a thread is actually going to wait for a lock. The state
      change happens after the thread fails to grab the lock (because it
      is owned by other thread) and proceeds to wait on a condition.
      modified:
        mysys/thr_lock.c
        sql/lock.cc

Queued to 6.0-bugteam

Pushed into 6.0.10-alpha (revid:matthias.leich@sun.com-20090212211028-y72faag15q3z3szy) (version source revid:matthias.leich@sun.com-20090212211028-y72faag15q3z3szy) (merge vers: 6.0.10-alpha) (pib:6)

Am I correct in thinking that this adds a new SHOW PROCESSLIST state that should be listed (with a definition) in the manual?

The "Table lock" state already existed. The difference is that this state is now only set when the calling thread must wait for the lock to become available (and remains set for the duration of the wait).

Noted in 6.0.10 changelog.

Threads were set to the "Table lock" state in such a way that use of
this state by other threads to check for a lock wait was subject to a
race condition.

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

2985 Konstantin Osipov	2009-12-03
      Backport of:
      ------------------------------------------------------------
      revno: 3035.4.1
      committer: Davi Arnaut <Davi.Arnaut@Sun.COM>
      branch nick: 39897-6.0
      timestamp: Thu 2009-01-15 12:17:57 -0200
      message:
      Bug#39897: lock_multi fails in pushbuild: timeout waiting for processlist
      
      The problem is that relying on the "Table lock" thread state in
      its current position to detect that a thread is waiting on a lock
      is race prone. The "Table lock" state change happens before the
      thread actually tries to grab a lock on a table.
      
      The solution is to move the "Table lock" state so that its set
      only when a thread is actually going to wait for a lock. The state
      change happens after the thread fails to grab the lock (because it
      is owned by other thread) and proceeds to wait on a condition.
      
      This is considered part of work related to WL#4284 "Transactional
      DDL locking"
     @ mysys/thr_lock.c
        Update thread state while waiting for a table lock.

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

2985 Konstantin Osipov	2009-12-03
      Backport of:
      ------------------------------------------------------------
      revno: 3035.4.1
      committer: Davi Arnaut <Davi.Arnaut@Sun.COM>
      branch nick: 39897-6.0
      timestamp: Thu 2009-01-15 12:17:57 -0200
      message:
      Bug#39897: lock_multi fails in pushbuild: timeout waiting for processlist
      
      The problem is that relying on the "Table lock" thread state in
      its current position to detect that a thread is waiting on a lock
      is race prone. The "Table lock" state change happens before the
      thread actually tries to grab a lock on a table.
      
      The solution is to move the "Table lock" state so that its set
      only when a thread is actually going to wait for a lock. The state
      change happens after the thread fails to grab the lock (because it
      is owned by other thread) and proceeds to wait on a condition.
      
      This is considered part of work related to WL#4284 "Transactional
      DDL locking"
     @ mysql-test/r/lock_multi.result
        A style fix.
     @ mysql-test/r/sp-threads.result
        Changed output of SHOW PROCESSLIST (new wait state).
     @ mysql-test/t/lock_multi.test
        Use a more accurate state description when waiting inside thr_lock.c.
     @ mysql-test/t/lock_sync.test
        Use a more accurate state description when waiting inside thr_lock.c.
     @ mysql-test/t/multi_update.test
        Use a more accurate state description when waiting inside thr_lock.c.
     @ mysql-test/t/query_cache_28249.test
        Use a more accurate state description when waiting inside thr_lock.c.
     @ mysql-test/t/sp_notembedded.test
        Use a more accurate state description when waiting inside thr_lock.c.
     @ mysql-test/t/status.test
        Use a more accurate state description when waiting inside thr_lock.c.
     @ mysys/thr_lock.c
        Update thread state while waiting for a table lock.
     @ sql/lock.cc
        State change was moved inside thr_lock.c.

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

2985 Konstantin Osipov	2009-12-03
      Backport of:
      ------------------------------------------------------------
      revno: 3035.4.1
      committer: Davi Arnaut <Davi.Arnaut@Sun.COM>
      branch nick: 39897-6.0
      timestamp: Thu 2009-01-15 12:17:57 -0200
      message:
      Bug#39897: lock_multi fails in pushbuild: timeout waiting for processlist
      
      The problem is that relying on the "Table lock" thread state in
      its current position to detect that a thread is waiting on a lock
      is race prone. The "Table lock" state change happens before the
      thread actually tries to grab a lock on a table.
      
      The solution is to move the "Table lock" state so that its set
      only when a thread is actually going to wait for a lock. The state
      change happens after the thread fails to grab the lock (because it
      is owned by other thread) and proceeds to wait on a condition.
      
      This is considered part of work related to WL#4284 "Transactional
      DDL locking"
      Warning: this patch contains an incompatible change. 
      When waiting on a lock in thr_lock.c, the server used to display "Locked"
      processlist state. After this patch, the state is "Table lock".
      The new state was actually intended to be display since year 2002,
      when Monty added it. But up until removal of thd->locked boolean
      member, this state was ignored by SHOW PROCESSLIST code.  
     @ mysql-test/r/lock_multi.result
        A style fix.
     @ mysql-test/r/sp-threads.result
        Changed output of SHOW PROCESSLIST (new wait state).
     @ mysql-test/t/lock_multi.test
        Use a more accurate state description when waiting inside thr_lock.c.
     @ mysql-test/t/lock_sync.test
        Use a more accurate state description when waiting inside thr_lock.c.
     @ mysql-test/t/multi_update.test
        Use a more accurate state description when waiting inside thr_lock.c.
     @ mysql-test/t/query_cache_28249.test
        Use a more accurate state description when waiting inside thr_lock.c.
     @ mysql-test/t/sp_notembedded.test
        Use a more accurate state description when waiting inside thr_lock.c.
     @ mysql-test/t/status.test
        Use a more accurate state description when waiting inside thr_lock.c.
     @ mysys/thr_lock.c
        Update thread state while waiting for a table lock.
     @ sql/lock.cc
        State change was moved inside thr_lock.c.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091203200827-nwrw6lpatzf115q0) (pib:16)

Setting report to Need Merge pending push of Celosia into release tree.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)

Noted in 5.5.3 changelog.