From 00496b7acd1f2ac8b099ba7e6a4c7bbf09178384 Mon Sep 17 00:00:00 2001 From: Dmitry Lenev Date: Tue, 27 Jul 2010 17:34:58 +0400 Subject: Fix for bug #52044 "FLUSH TABLES WITH READ LOCK and FLUSH TABLES WITH READ LOCK are incompatible". The problem was that FLUSH TABLES WITH READ LOCK which was issued when other connection has acquired global read lock using FLUSH TABLES WITH READ LOCK was blocked and has to wait until global read lock is released. This issue stemmed from the fact that FLUSH TABLES WITH READ LOCK implementation has acquired X metadata locks on tables to be flushed. Since these locks required acquiring of global IX lock this statement was incompatible with global read lock. This patch addresses problem by using SNW metadata type of lock for tables to be flushed by FLUSH TABLES WITH READ LOCK. It is OK to acquire them without global IX lock as long as we won't try to upgrade those locks. Since SNW locks allow concurrent statements using same table FLUSH TABLE WITH READ LOCK now has to wait until old versions of tables to be flushed go away after acquiring metadata locks. Since such waiting can lead to deadlock MDL deadlock detector was extended to take into account waits for flush and resolve such deadlocks. As a bonus code in open_tables() which was responsible for waiting old versions of tables to go away was refactored. Now when we encounter old version of table in open_table() we don't back-off and wait for all old version to go away, but instead wait for this particular table to be flushed. Such approach supported by deadlock detection should reduce number of scenarios in which FLUSH TABLES aborts concurrent multi-statement transactions. Note that active FLUSH TABLES WITH READ LOCK still blocks concurrent FLUSH TABLES WITH READ LOCK statement as the former keeps tables open and thus prevents the latter statement from doing flush. mysql-test/include/handler.inc: Adjusted test case after changing status which is set when FLUSH TABLES waits for tables to be flushed from "Flushing tables" to "Waiting for table". mysql-test/r/flush.result: Added test which checks that "flush tables with read lock" is compatible with active "flush tables with read lock" but not vice-versa. This test also covers bug #52044 "FLUSH TABLES WITH READ LOCK and FLUSH TABLES WITH READ LOCK are incompatible". mysql-test/r/mdl_sync.result: Added scenarios in which wait for table to be flushed causes deadlocks to the coverage of MDL deadlock detector. mysql-test/suite/perfschema/r/dml_setup_instruments.result: Adjusted test results after removal of COND_refresh condition variable. mysql-test/suite/perfschema/r/server_init.result: Adjusted test and its results after removal of COND_refresh condition variable. mysql-test/suite/perfschema/t/server_init.test: Adjusted test and its results after removal of COND_refresh condition variable. mysql-test/t/flush.test: Added test which checks that "flush tables with read lock" is compatible with active "flush tables with read lock" but not vice-versa. This test also covers bug #52044 "FLUSH TABLES WITH READ LOCK and FLUSH TABLES WITH READ LOCK are incompatible". mysql-test/t/kill.test: Adjusted test case after changing status which is set when FLUSH TABLES waits for tables to be flushed from "Flushing tables" to "Waiting for table". mysql-test/t/lock_multi.test: Adjusted test case after changing status which is set when FLUSH TABLES waits for tables to be flushed from "Flushing tables" to "Waiting for table". mysql-test/t/mdl_sync.test: Added scenarios in which wait for table to be flushed causes deadlocks to the coverage of MDL deadlock detector. sql/ha_ndbcluster.cc: Adjusted code after adding one more parameter for close_cached_tables() call - timeout for waiting for table to be flushed. sql/ha_ndbcluster_binlog.cc: Adjusted code after adding one more parameter for close_cached_tables() call - timeout for waiting for table to be flushed. sql/lock.cc: Removed COND_refresh condition variable. See comment for sql_base.cc for details. sql/mdl.cc: Now MDL deadlock detector takes into account information about waits for table flushes when searching for deadlock. To implement this change: - Declaration of enum_deadlock_weight and Deadlock_detection_visitor were moved to mdl.h header to make them available to the code in table.cc which implements deadlock detector traversal through edges of waiters graph representing waiting for flush. - Since now MDL_context may wait not only for metadata lock but also for table to be flushed an abstract Wait_for_edge class was introduced. Its descendants MDL_ticket and Flush_ticket incapsulate specifics of inspecting waiters graph when following through edge representing wait of particular type. We no longer require global IX metadata lock when acquiring SNW or SNRW locks. Such locks are needed only when metadata locks of these types are upgraded to X locks. This allows to use SNW locks in FLUSH TABLES WITH READ LOCK implementation and keep the latter compatible with global read lock. sql/mdl.h: Now MDL deadlock detector takes into account information about waits for table flushes when searching for deadlock. To implement this change: - Declaration of enum_deadlock_weight and Deadlock_detection_visitor were moved to mdl.h header to make them available to the code in table.cc which implements deadlock detector traversal through edges of waiters graph representing waiting for flush. - Since now MDL_context may wait not only for metadata lock but also for table to be flushed an abstract Wait_for_edge class was introduced. Its descendants MDL_ticket and Flush_ticket incapsulate specifics of inspecting waiters graph when following through edge representing wait of particular type. - Deadlock_detection_visitor now has m_table_shares_visited member which allows to support recursive locking for LOCK_open. This is required when deadlock detector inspects waiters graph which contains several edges representing waits for flushes or needs to come through the such edge more than once. sql/mysqld.cc: Removed COND_refresh condition variable. See comment for sql_base.cc for details. sql/mysqld.h: Removed COND_refresh condition variable. See comment for sql_base.cc for details. sql/sql_base.cc: Changed approach to how threads are waiting for table to be flushed. Now thread that wants to wait for old table to go away subscribes for notification by adding Flush_ticket to table's share and waits using MDL_context::m_wait object. Once table gets flushed (i.e. all tables are closed and table share is ready to be destroyed) all such waiters are notified individually. Thanks to this change MDL deadlock detector can take such waits into account. To implement this/as result of this change: - tdc_wait_for_old_versions() was replaced with tdc_wait_for_old_version() which waits for individual old share to go away and which is called by open_table() after finding out that share is outdated. We don't need to perform back-off before such waiting thanks to the fact that deadlock detector now sees such waits. - As result Open_table_ctx::m_mdl_requests became unnecessary and was removed. We no longer allocate copies of MDL_request objects on MEM_ROOT when MYSQL_OPEN_FORCE_SHARED/SHARED_HIGH_PRIO flags are in effect. - close_cached_tables() and tdc_wait_for_old_version() share code which implements waiting for share to be flushed - the both use TABLE_SHARE::wait_until_flush() method. Thanks to this close_cached_tables() supports timeouts and has extra parameter for this. - Open_table_context::OT_MDL_CONFLICT enum element was renamed to OT_CONFLICT as it is now also used in cases when back-off is required to resolve deadlock caused by waiting for flush and not metadata lock. - In cases when we discover that current connection tries to open tables from different generation we now simply back-off and restart process of opening tables. To support this Open_table_context::OT_REOPEN_TABLES enum element was added. - COND_refresh condition variable became unnecessary and was removed. - mysql_notify_thread_having_shared_lock() no longer wakes up connections waiting for flush as all such connections can be waken up by deadlock detector if necessary. sql/sql_base.h: - close_cached_tables() now has one more parameter - timeout for waiting for table to be flushed. - Open_table_context::OT_MDL_CONFLICT enum element was renamed to OT_CONFLICT as it is now also used in cases when back-off is required to resolve deadlock caused by waiting for flush and not metadata lock. Added new OT_REOPEN_TABLES enum element to be used in cases when we need to restart open tables process even in the middle of transaction. - Open_table_ctx::m_mdl_requests became unnecessary and was removed. sql/sql_class.h: Added assert ensuring that we won't use LOCK_open mutex with THD::enter_cond(). Otherwise deadlocks can arise in MDL deadlock detector. sql/sql_parse.cc: Changed FLUSH TABLES WITH READ LOCK to take SNW metadata locks instead of X locks on tables to be flushed. Since we no longer require global IX lock to be taken when SNW locks are taken this makes this statement compatible with FLUSH TABLES WITH READ LOCK statement. Since SNW locks allow other connections to have table opened FLUSH TABLES WITH READ LOCK now has to wait during open_tables() for old version to go away. Such waits can lead to deadlocks which will be detected by MDL deadlock detector which now takes waits for table to be flushed into account. Also adjusted code after adding one more parameter for close_cached_tables() call - timeout for waiting for table to be flushed. sql/sql_yacc.yy: FLUSH TABLES WITH READ LOCK now needs only SNW metadata locks on tables. sql/sys_vars.cc: Adjusted code after adding one more parameter for close_cached_tables() call - timeout for waiting for table to be flushed. sql/table.cc: Implemented new approach to how threads are waiting for table to be flushed. Now thread that wants to wait for old table to go away subscribes for notification by adding Flush_ticket to table's share and waits using MDL_context::m_wait object. Once table gets flushed (i.e. all tables are closed and table share is ready to be destroyed) all such waiters are notified individually. This change allows to make such waits visible inside of MDL deadlock detector. To do it: - Added list of waiters/Flush_tickets to TABLE_SHARE class. - Changed free_table_share() to postpone freeing of share memory until last waiter goes away and to wake up subscribed waiters. - Added TABLE_SHARE::wait_until_flushed() method which implements subscription to the list of waiters for table to be flushed and waiting for this event. Implemented interface which allows to expose waits for flushes to MDL deadlock detector: - Introduced Flush_ticket class a descendant of Wait_for_edge class. - Added TABLE_SHARE::find_deadlock() method which allows deadlock detector to find out what contexts are still using old version of table in question (i.e. to find out what contexts are waited for by owner of Flush_ticket). sql/table.h: In order to support new strategy of waiting for table flush (see comment for table.cc for details) added list of waiters/Flush_tickets to TABLE_SHARE class. Implemented interface which allows to expose waits for flushes to MDL deadlock detector: - Introduced Flush_ticket class a descendant of Wait_for_edge class. - Added TABLE_SHARE::find_deadlock() method which allows deadlock detector to find out what contexts are still using old version of table in question (i.e. to find out what contexts are waited for by owner of Flush_ticket). --- mysql-test/include/handler.inc | 2 +- mysql-test/r/flush.result | 14 ++ mysql-test/r/mdl_sync.result | 151 +++++++++++++ .../perfschema/r/dml_setup_instruments.result | 2 +- mysql-test/suite/perfschema/r/server_init.result | 4 - mysql-test/suite/perfschema/t/server_init.test | 3 - mysql-test/t/flush.test | 14 ++ mysql-test/t/kill.test | 2 +- mysql-test/t/lock_multi.test | 2 +- mysql-test/t/mdl_sync.test | 250 +++++++++++++++++++++ 10 files changed, 433 insertions(+), 11 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/include/handler.inc b/mysql-test/include/handler.inc index 98988ab55ba..a8c62d3994f 100644 --- a/mysql-test/include/handler.inc +++ b/mysql-test/include/handler.inc @@ -523,7 +523,7 @@ connection waiter; --echo connection: waiter let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Flushing tables"; + where state = "Waiting for table"; --source include/wait_condition.inc connection default; --echo connection: default diff --git a/mysql-test/r/flush.result b/mysql-test/r/flush.result index 3702443d04a..4251615a6e6 100644 --- a/mysql-test/r/flush.result +++ b/mysql-test/r/flush.result @@ -205,6 +205,20 @@ a insert into t2 (a) values (3); # --> connection default; unlock tables; +# +# Check that "flush tables with read lock" is +# compatible with active "flush tables with read lock". +# Vice versa is not true as tables read-locked by +# "flush tables with read lock" can't be flushed. +flush tables with read lock; +# --> connection con1; +flush table t1 with read lock; +select * from t1; +a +1 +unlock tables; +# --> connection default; +unlock tables; # --> connection con1 drop table t1, t2, t3; # diff --git a/mysql-test/r/mdl_sync.result b/mysql-test/r/mdl_sync.result index 0fd408b0208..2e3195c5a53 100644 --- a/mysql-test/r/mdl_sync.result +++ b/mysql-test/r/mdl_sync.result @@ -2034,6 +2034,157 @@ set debug_sync='now SIGNAL go2'; # Switching to connection 'default'. # Reaping ALTER. It should succeed and not produce ER_LOCK_DEADLOCK. drop table t1; +# +# Now, test for situation in which deadlock involves waiting not +# only in MDL subsystem but also for TDC. Such deadlocks should be +# successfully detected. If possible they should be resolved without +# resorting to ER_LOCK_DEADLOCK error. +# +create table t1(i int); +create table t2(j int); +# +# First, let us check how we handle simple scenario involving +# waits in MDL and TDC. +# +set debug_sync= 'RESET'; +# Switching to connection 'deadlock_con1'. +# Start statement which will acquire SR metadata lock on t1, open it +# and then will stop, before trying to acquire SW lock and opening t2. +set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go'; +# Sending: +select * from t1 where i in (select j from t2 for update); +# Switching to connection 'deadlock_con2'. +# Wait till the above SELECT stops. +set debug_sync='now WAIT_FOR parked'; +# The below FLUSH TABLES WITH READ LOCK should acquire +# SNW locks on t1 and t2 and wait till SELECT closes t1. +# Sending: +flush tables t1, t2 with read lock; +# Switching to connection 'deadlock_con3'. +# Wait until FLUSH TABLES WITH READ LOCK starts waiting +# for SELECT to close t1. +# Resume SELECT, so it tries to acquire SW lock on t1 and blocks, +# creating a deadlock. This deadlock should be detected and resolved +# by backing-off SELECT. As result FLUSH TABLES WITH READ LOCK should +# be able to finish. +set debug_sync='now SIGNAL go'; +# Switching to connection 'deadlock_con2'. +# Reap FLUSH TABLES WITH READ LOCK. +unlock tables; +# Switching to connection 'deadlock_con1'. +# Reap SELECT. +i +# +# The same scenario with a slightly different order of events +# which emphasizes that setting correct deadlock detector weights +# for flush waits is important. +# +set debug_sync= 'RESET'; +# Switching to connection 'deadlock_con2'. +set debug_sync='flush_tables_with_read_lock_after_acquire_locks SIGNAL parked WAIT_FOR go'; +# The below FLUSH TABLES WITH READ LOCK should acquire +# SNW locks on t1 and t2 and wait on debug sync point. +# Sending: +flush tables t1, t2 with read lock; +# Switching to connection 'deadlock_con1'. +# Wait till FLUSH TABLE WITH READ LOCK stops. +set debug_sync='now WAIT_FOR parked'; +# Start statement which will acquire SR metadata lock on t1, open +# it and then will block while trying to acquire SW lock on t2. +# Sending: +select * from t1 where i in (select j from t2 for update); +# Switching to connection 'deadlock_con3'. +# Wait till the above SELECT blocks. +# Resume FLUSH TABLES, so it tries to flush t1 creating a deadlock. +# This deadlock should be detected and resolved by backing-off SELECT. +# As result FLUSH TABLES WITH READ LOCK should be able to finish. +set debug_sync='now SIGNAL go'; +# Switching to connection 'deadlock_con2'. +# Reap FLUSH TABLES WITH READ LOCK. +unlock tables; +# Switching to connection 'deadlock_con1'. +# Reap SELECT. +i +# +# Now more complex scenario involving two connections +# waiting for MDL and one for TDC. +# +set debug_sync= 'RESET'; +# Switching to connection 'deadlock_con1'. +# Start statement which will acquire SR metadata lock on t2, open it +# and then will stop, before trying to acquire SR lock and opening t1. +set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go'; +# Sending: +select * from t2, t1; +# Switching to connection 'deadlock_con2'. +# Wait till the above SELECT stops. +set debug_sync='now WAIT_FOR parked'; +# The below FLUSH TABLES WITH READ LOCK should acquire +# SNW locks on t2 and wait till SELECT closes t2. +# Sending: +flush tables t2 with read lock; +# Switching to connection 'deadlock_con3'. +# Wait until FLUSH TABLES WITH READ LOCK starts waiting +# for SELECT to close t2. +# The below DROP TABLES should acquire X lock on t1 and start +# waiting for X lock on t2. +# Sending: +drop tables t1, t2; +# Switching to connection 'default'. +# Wait until DROP TABLES starts waiting for X lock on t2. +# Resume SELECT, so it tries to acquire SR lock on t1 and blocks, +# creating a deadlock. This deadlock should be detected and resolved +# by backing-off SELECT. As result FLUSH TABLES WITH READ LOCK should +# be able to finish. +set debug_sync='now SIGNAL go'; +# Switching to connection 'deadlock_con2'. +# Reap FLUSH TABLES WITH READ LOCK. +# Unblock DROP TABLES. +unlock tables; +# Switching to connection 'deadlock_con3'. +# Reap DROP TABLES. +# Switching to connection 'deadlock_con1'. +# Reap SELECT. It should emit error about missing table. +ERROR 42S02: Table 'test.t2' doesn't exist +# Switching to connection 'default'. +set debug_sync= 'RESET'; +# +# Test for scenario in which FLUSH TABLES WITH READ LOCK +# has been erroneously releasing metadata locks. +# +drop tables if exists t1, t2; +set debug_sync= 'RESET'; +create table t1(i int); +create table t2(j int); +# Switching to connection 'con2'. +set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go'; +# The below FLUSH TABLES WITH READ LOCK should acquire +# SNW locks on t1 and t2, open table t1 and wait on debug sync +# point. +# Sending: +flush tables t1, t2 with read lock; +# Switching to connection 'con1'. +# Wait till FLUSH TABLES WITH READ LOCK stops. +set debug_sync='now WAIT_FOR parked'; +# Start statement which will flush all tables and thus invalidate +# table t1 open by FLUSH TABLES WITH READ LOCK. +# Sending: +flush tables; +# Switching to connection 'default'. +# Wait till the above FLUSH TABLES blocks. +# Resume FLUSH TABLES WITH READ LOCK, so it tries to open t2 +# discovers that its t1 is obsolete and tries to reopen all tables. +# Such reopen should not cause releasing of SNW metadata locks +# which will result in assertion failures. +set debug_sync='now SIGNAL go'; +# Switching to connection 'con2'. +# Reap FLUSH TABLES WITH READ LOCK. +unlock tables; +# Switching to connection 'con1'. +# Reap FLUSH TABLES. +# Clean-up. +# Switching to connection 'default'. +drop tables t1, t2; set debug_sync= 'RESET'; # # Test for bug #46748 "Assertion in MDL_context::wait_for_locks() diff --git a/mysql-test/suite/perfschema/r/dml_setup_instruments.result b/mysql-test/suite/perfschema/r/dml_setup_instruments.result index 55256894743..2338252976c 100644 --- a/mysql-test/suite/perfschema/r/dml_setup_instruments.result +++ b/mysql-test/suite/perfschema/r/dml_setup_instruments.result @@ -40,12 +40,12 @@ wait/synch/cond/sql/COND_flush_thread_cache YES YES wait/synch/cond/sql/COND_global_read_lock YES YES wait/synch/cond/sql/COND_manager YES YES wait/synch/cond/sql/COND_queue_state YES YES -wait/synch/cond/sql/COND_refresh YES YES wait/synch/cond/sql/COND_rpl_status YES YES wait/synch/cond/sql/COND_server_started YES YES wait/synch/cond/sql/COND_thread_cache YES YES wait/synch/cond/sql/COND_thread_count YES YES wait/synch/cond/sql/Delayed_insert::cond YES YES +wait/synch/cond/sql/Delayed_insert::cond_client YES YES select * from performance_schema.SETUP_INSTRUMENTS where name='Wait'; select * from performance_schema.SETUP_INSTRUMENTS diff --git a/mysql-test/suite/perfschema/r/server_init.result b/mysql-test/suite/perfschema/r/server_init.result index a37a5805dd7..a73f12a0a20 100644 --- a/mysql-test/suite/perfschema/r/server_init.result +++ b/mysql-test/suite/perfschema/r/server_init.result @@ -184,10 +184,6 @@ where name like "wait/synch/cond/sql/COND_server_started"; count(name) 1 select count(name) from COND_INSTANCES -where name like "wait/synch/cond/sql/COND_refresh"; -count(name) -1 -select count(name) from COND_INSTANCES where name like "wait/synch/cond/sql/COND_thread_count"; count(name) 1 diff --git a/mysql-test/suite/perfschema/t/server_init.test b/mysql-test/suite/perfschema/t/server_init.test index 0bb3dd84ac4..080509b944f 100644 --- a/mysql-test/suite/perfschema/t/server_init.test +++ b/mysql-test/suite/perfschema/t/server_init.test @@ -209,9 +209,6 @@ select count(name) from RWLOCK_INSTANCES select count(name) from COND_INSTANCES where name like "wait/synch/cond/sql/COND_server_started"; -select count(name) from COND_INSTANCES - where name like "wait/synch/cond/sql/COND_refresh"; - select count(name) from COND_INSTANCES where name like "wait/synch/cond/sql/COND_thread_count"; diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test index 0d406338394..0157f2dc764 100644 --- a/mysql-test/t/flush.test +++ b/mysql-test/t/flush.test @@ -318,6 +318,20 @@ insert into t2 (a) values (3); --echo # --> connection default; connection default; unlock tables; +--echo # +--echo # Check that "flush tables with read lock" is +--echo # compatible with active "flush tables with read lock". +--echo # Vice versa is not true as tables read-locked by +--echo # "flush tables with read lock" can't be flushed. +flush tables with read lock; +--echo # --> connection con1; +connection con1; +flush table t1 with read lock; +select * from t1; +unlock tables; +--echo # --> connection default; +connection default; +unlock tables; --echo # --> connection con1 connection con1; disconnect con1; diff --git a/mysql-test/t/kill.test b/mysql-test/t/kill.test index b91feb3a1d5..7169ca5f7c3 100644 --- a/mysql-test/t/kill.test +++ b/mysql-test/t/kill.test @@ -536,7 +536,7 @@ connection ddl; connection dml; let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Flushing tables" and + where state = "Waiting for table" and info = "flush tables"; --source include/wait_condition.inc --send select * from t1 diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index 6983947d1c4..2a31392e8f8 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -982,7 +982,7 @@ connection con3; connection con2; let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist - WHERE state = "Flushing tables" AND info = "FLUSH TABLES"; + WHERE state = "Waiting for table" AND info = "FLUSH TABLES"; --source include/wait_condition.inc --error ER_LOCK_WAIT_TIMEOUT SELECT * FROM t1; diff --git a/mysql-test/t/mdl_sync.test b/mysql-test/t/mdl_sync.test index 6b721ace07f..13e6aef10be 100644 --- a/mysql-test/t/mdl_sync.test +++ b/mysql-test/t/mdl_sync.test @@ -2829,6 +2829,187 @@ connection default; drop table t1; +--echo # +--echo # Now, test for situation in which deadlock involves waiting not +--echo # only in MDL subsystem but also for TDC. Such deadlocks should be +--echo # successfully detected. If possible they should be resolved without +--echo # resorting to ER_LOCK_DEADLOCK error. +--echo # +create table t1(i int); +create table t2(j int); + +--echo # +--echo # First, let us check how we handle simple scenario involving +--echo # waits in MDL and TDC. +--echo # +set debug_sync= 'RESET'; + +--echo # Switching to connection 'deadlock_con1'. +connection deadlock_con1; +--echo # Start statement which will acquire SR metadata lock on t1, open it +--echo # and then will stop, before trying to acquire SW lock and opening t2. +set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go'; +--echo # Sending: +--send select * from t1 where i in (select j from t2 for update) + +--echo # Switching to connection 'deadlock_con2'. +connection deadlock_con2; +--echo # Wait till the above SELECT stops. +set debug_sync='now WAIT_FOR parked'; +--echo # The below FLUSH TABLES WITH READ LOCK should acquire +--echo # SNW locks on t1 and t2 and wait till SELECT closes t1. +--echo # Sending: +--send flush tables t1, t2 with read lock + +--echo # Switching to connection 'deadlock_con3'. +connection deadlock_con3; +--echo # Wait until FLUSH TABLES WITH READ LOCK starts waiting +--echo # for SELECT to close t1. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "flush tables t1, t2 with read lock"; +--source include/wait_condition.inc + +--echo # Resume SELECT, so it tries to acquire SW lock on t1 and blocks, +--echo # creating a deadlock. This deadlock should be detected and resolved +--echo # by backing-off SELECT. As result FLUSH TABLES WITH READ LOCK should +--echo # be able to finish. +set debug_sync='now SIGNAL go'; + +--echo # Switching to connection 'deadlock_con2'. +connection deadlock_con2; +--echo # Reap FLUSH TABLES WITH READ LOCK. +--reap +unlock tables; + +--echo # Switching to connection 'deadlock_con1'. +connection deadlock_con1; +--echo # Reap SELECT. +--reap + +--echo # +--echo # The same scenario with a slightly different order of events +--echo # which emphasizes that setting correct deadlock detector weights +--echo # for flush waits is important. +--echo # +set debug_sync= 'RESET'; + +--echo # Switching to connection 'deadlock_con2'. +connection deadlock_con2; +set debug_sync='flush_tables_with_read_lock_after_acquire_locks SIGNAL parked WAIT_FOR go'; + +--echo # The below FLUSH TABLES WITH READ LOCK should acquire +--echo # SNW locks on t1 and t2 and wait on debug sync point. +--echo # Sending: +--send flush tables t1, t2 with read lock + +--echo # Switching to connection 'deadlock_con1'. +connection deadlock_con1; +--echo # Wait till FLUSH TABLE WITH READ LOCK stops. +set debug_sync='now WAIT_FOR parked'; + +--echo # Start statement which will acquire SR metadata lock on t1, open +--echo # it and then will block while trying to acquire SW lock on t2. +--echo # Sending: +--send select * from t1 where i in (select j from t2 for update) + +--echo # Switching to connection 'deadlock_con3'. +connection deadlock_con3; +--echo # Wait till the above SELECT blocks. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and + info = "select * from t1 where i in (select j from t2 for update)"; +--source include/wait_condition.inc + +--echo # Resume FLUSH TABLES, so it tries to flush t1 creating a deadlock. +--echo # This deadlock should be detected and resolved by backing-off SELECT. +--echo # As result FLUSH TABLES WITH READ LOCK should be able to finish. +set debug_sync='now SIGNAL go'; + +--echo # Switching to connection 'deadlock_con2'. +connection deadlock_con2; +--echo # Reap FLUSH TABLES WITH READ LOCK. +--reap +unlock tables; + +--echo # Switching to connection 'deadlock_con1'. +connection deadlock_con1; +--echo # Reap SELECT. +--reap + +--echo # +--echo # Now more complex scenario involving two connections +--echo # waiting for MDL and one for TDC. +--echo # +set debug_sync= 'RESET'; + +--echo # Switching to connection 'deadlock_con1'. +connection deadlock_con1; +--echo # Start statement which will acquire SR metadata lock on t2, open it +--echo # and then will stop, before trying to acquire SR lock and opening t1. +set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go'; +--echo # Sending: +--send select * from t2, t1 + +--echo # Switching to connection 'deadlock_con2'. +connection deadlock_con2; +--echo # Wait till the above SELECT stops. +set debug_sync='now WAIT_FOR parked'; +--echo # The below FLUSH TABLES WITH READ LOCK should acquire +--echo # SNW locks on t2 and wait till SELECT closes t2. +--echo # Sending: +--send flush tables t2 with read lock + +--echo # Switching to connection 'deadlock_con3'. +connection deadlock_con3; +--echo # Wait until FLUSH TABLES WITH READ LOCK starts waiting +--echo # for SELECT to close t2. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "flush tables t2 with read lock"; +--source include/wait_condition.inc + +--echo # The below DROP TABLES should acquire X lock on t1 and start +--echo # waiting for X lock on t2. +--echo # Sending: +--send drop tables t1, t2 + +--echo # Switching to connection 'default'. +connection default; +--echo # Wait until DROP TABLES starts waiting for X lock on t2. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "drop tables t1, t2"; +--source include/wait_condition.inc + +--echo # Resume SELECT, so it tries to acquire SR lock on t1 and blocks, +--echo # creating a deadlock. This deadlock should be detected and resolved +--echo # by backing-off SELECT. As result FLUSH TABLES WITH READ LOCK should +--echo # be able to finish. +set debug_sync='now SIGNAL go'; + +--echo # Switching to connection 'deadlock_con2'. +connection deadlock_con2; +--echo # Reap FLUSH TABLES WITH READ LOCK. +--reap +--echo # Unblock DROP TABLES. +unlock tables; + +--echo # Switching to connection 'deadlock_con3'. +connection deadlock_con3; +--echo # Reap DROP TABLES. +--reap + +--echo # Switching to connection 'deadlock_con1'. +connection deadlock_con1; +--echo # Reap SELECT. It should emit error about missing table. +--error ER_NO_SUCH_TABLE +--reap + +--echo # Switching to connection 'default'. +connection default; + set debug_sync= 'RESET'; disconnect deadlock_con1; @@ -2836,6 +3017,75 @@ disconnect deadlock_con2; disconnect deadlock_con3; +--echo # +--echo # Test for scenario in which FLUSH TABLES WITH READ LOCK +--echo # has been erroneously releasing metadata locks. +--echo # +connect(con1,localhost,root,,); +connect(con2,localhost,root,,); +connection default; +--disable_warnings +drop tables if exists t1, t2; +--enable_warnings +set debug_sync= 'RESET'; +create table t1(i int); +create table t2(j int); + +--echo # Switching to connection 'con2'. +connection con2; +set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go'; + +--echo # The below FLUSH TABLES WITH READ LOCK should acquire +--echo # SNW locks on t1 and t2, open table t1 and wait on debug sync +--echo # point. +--echo # Sending: +--send flush tables t1, t2 with read lock + +--echo # Switching to connection 'con1'. +connection con1; +--echo # Wait till FLUSH TABLES WITH READ LOCK stops. +set debug_sync='now WAIT_FOR parked'; + +--echo # Start statement which will flush all tables and thus invalidate +--echo # table t1 open by FLUSH TABLES WITH READ LOCK. +--echo # Sending: +--send flush tables + +--echo # Switching to connection 'default'. +connection default; +--echo # Wait till the above FLUSH TABLES blocks. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and + info = "flush tables"; +--source include/wait_condition.inc + +--echo # Resume FLUSH TABLES WITH READ LOCK, so it tries to open t2 +--echo # discovers that its t1 is obsolete and tries to reopen all tables. +--echo # Such reopen should not cause releasing of SNW metadata locks +--echo # which will result in assertion failures. +set debug_sync='now SIGNAL go'; + +--echo # Switching to connection 'con2'. +connection con2; +--echo # Reap FLUSH TABLES WITH READ LOCK. +--reap +unlock tables; + +--echo # Switching to connection 'con1'. +connection con1; +--echo # Reap FLUSH TABLES. +--reap + +--echo # Clean-up. +--echo # Switching to connection 'default'. +connection default; +drop tables t1, t2; +set debug_sync= 'RESET'; +disconnect con1; +disconnect con2; + + --echo # --echo # Test for bug #46748 "Assertion in MDL_context::wait_for_locks() --echo # on INSERT + CREATE TRIGGER". -- cgit v1.2.1 From 8d0dc9b58bcb5f1cf13618eebe3fc6f60b8f2926 Mon Sep 17 00:00:00 2001 From: Dmitry Lenev Date: Fri, 6 Aug 2010 15:29:37 +0400 Subject: Part of fix for bug#52044 "FLUSH TABLES WITH READ LOCK and FLUSH TABLES WITH READ LOCK are incompatible" to be pushed as separate patch. Replaced thread state name "Waiting for table", which was used by threads waiting for a metadata lock or table flush, with a set of names which better reflect types of resources being waited for. Also replaced "Table lock" thread state name, which was used by threads waiting on thr_lock.c table level lock, with more elaborate "Waiting for table level lock", to make it more consistent with other thread state names. Updated test cases and their results according to these changes. Fixed sys_vars.query_cache_wlock_invalidate_func test to not to wait for timeout of wait_condition.inc script. mysql-test/r/query_cache.result: Added test coverage for query_cache_wlock_invalidate behavior for implicitly locked tables. mysql-test/suite/sys_vars/r/query_cache_wlock_invalidate_func.result: Fixed sys_vars.query_cache_wlock_invalidate_func test to not to wait for timeout of wait_condition.inc script. Reverted changes to test which introduced timeout and replaced waiting condition with a more appropriate one. Test coverage for query_cache_wlock_invalidate behavior for implicitly locked tables was added to query_cache.test. mysql-test/suite/sys_vars/t/query_cache_wlock_invalidate_func.test: Fixed sys_vars.query_cache_wlock_invalidate_func test to not to wait for timeout of wait_condition.inc script. Reverted changes to test which introduced timeout and replaced waiting condition with a more appropriate one. Test coverage for query_cache_wlock_invalidate behavior for implicitly locked tables was added to query_cache.test. mysql-test/t/query_cache.test: Added test coverage for query_cache_wlock_invalidate behavior for implicitly locked tables. mysys/thr_lock.c: Replaced "Table lock" thread state name, which was used by threads waiting on thr_lock.c table level lock, with more elaborate "Waiting for table level lock", to make it consistent with thread state names which are used while waiting for metadata locks and table flush. sql/mdl.cc: Replaced thread state name "Waiting for table", which was used by threads waiting for a metadata lock or table flush, with a set of names which better reflect types of resources being waited for. To implement this: - Adjusted MDL_wait::timed_wait() to take thread state name as parameter. - Introduced method of MDL_key class which allows to get thread state name to be used while waiting for resource corresponding to the key and changed code to use it. Added array translating namespaces to thread state names as part of this change. sql/mdl.h: To implement this: - Adjusted MDL_wait::timed_wait() to take thread state name as parameter. - Introduced method of MDL_key class which allows to get thread state name to be used while waiting for resource corresponding to the key and changed code to use it. Added array translating namespaces to thread state names as part of this change. sql/sql_base.cc: Replaced thread state name "Waiting for table", which was used by threads waiting for table flush, with a more elaborate "Waiting for table flush". --- mysql-test/extra/rpl_tests/rpl_innodb.test | 2 +- mysql-test/include/check_no_concurrent_insert.inc | 3 +- mysql-test/include/handler.inc | 58 ++-- mysql-test/include/mix1.inc | 2 +- mysql-test/r/query_cache.result | 16 +- mysql-test/r/sp-lock.result | 46 +-- mysql-test/r/sp-threads.result | 2 +- mysql-test/suite/binlog/t/binlog_stm_row.test | 2 +- .../suite/funcs_1/datadict/processlist_val.inc | 12 +- .../suite/funcs_1/r/processlist_val_no_prot.result | 6 +- .../suite/funcs_1/r/processlist_val_ps.result | 6 +- mysql-test/suite/innodb/t/innodb-lock.test | 3 +- mysql-test/suite/rpl/t/rpl_savepoint.test | 2 +- mysql-test/suite/rpl/t/rpl_sp.test | 5 +- mysql-test/suite/rpl/t/rpl_view_multi.test | 4 +- .../suite/sys_vars/r/concurrent_insert_func.result | 4 +- .../r/query_cache_wlock_invalidate_func.result | 13 +- .../suite/sys_vars/t/concurrent_insert_func.test | 5 +- .../sys_vars/t/delayed_insert_limit_func.test | 4 +- .../t/query_cache_wlock_invalidate_func.test | 14 +- .../sys_vars/t/sql_low_priority_updates_func.test | 14 +- mysql-test/t/delayed.test | 2 +- mysql-test/t/information_schema.test | 3 +- mysql-test/t/innodb_mysql_lock.test | 12 +- mysql-test/t/innodb_mysql_lock2.test | 8 +- mysql-test/t/insert_notembedded.test | 2 +- mysql-test/t/kill.test | 23 +- mysql-test/t/lock_multi.test | 72 +++-- mysql-test/t/lock_sync.test | 4 +- mysql-test/t/mdl_sync.test | 317 ++++++++++++++------- mysql-test/t/merge-big.test | 4 +- mysql-test/t/multi_update.test | 4 +- mysql-test/t/query_cache.test | 8 + mysql-test/t/query_cache_28249.test | 6 +- mysql-test/t/schema.test | 8 +- mysql-test/t/sp-lock.test | 106 ++++--- mysql-test/t/sp_notembedded.test | 2 +- mysql-test/t/sp_sync.test | 2 +- mysql-test/t/status.test | 3 +- mysql-test/t/trigger_notembedded.test | 2 +- mysql-test/t/view.test | 5 +- 41 files changed, 504 insertions(+), 312 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/extra/rpl_tests/rpl_innodb.test b/mysql-test/extra/rpl_tests/rpl_innodb.test index 4bc1c004ba7..8b9b7e7ff39 100644 --- a/mysql-test/extra/rpl_tests/rpl_innodb.test +++ b/mysql-test/extra/rpl_tests/rpl_innodb.test @@ -153,7 +153,7 @@ connection master; let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE info = "RENAME TABLE t1 TO t3, t2 TO t1" and - state = "Waiting for table"; + state = "Waiting for table metadata lock"; --source include/wait_condition.inc COMMIT; diff --git a/mysql-test/include/check_no_concurrent_insert.inc b/mysql-test/include/check_no_concurrent_insert.inc index 57772dddefc..c615ebd02cd 100644 --- a/mysql-test/include/check_no_concurrent_insert.inc +++ b/mysql-test/include/check_no_concurrent_insert.inc @@ -43,7 +43,8 @@ connection default; # of our statement. let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Table lock" and info = "insert into $table (i) values (0)"; + where state = "Waiting for table level lock" and + info = "insert into $table (i) values (0)"; --source include/wait_condition.inc --disable_result_log diff --git a/mysql-test/include/handler.inc b/mysql-test/include/handler.inc index 98988ab55ba..48cbd17b940 100644 --- a/mysql-test/include/handler.inc +++ b/mysql-test/include/handler.inc @@ -557,7 +557,8 @@ connection waiter; --echo connection: waiter let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Waiting for table" and info = "rename table t1 to t0"; + where state = "Waiting for table metadata lock" and + info = "rename table t1 to t0"; --source include/wait_condition.inc connection default; --echo connection: default @@ -743,7 +744,8 @@ send alter table t1 engine=memory; connection con2; let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Waiting for table" and info = "alter table t1 engine=memory"; + where state = "Waiting for table metadata lock" and + info = "alter table t1 engine=memory"; --source include/wait_condition.inc connection default; --error ER_ILLEGAL_HA @@ -764,7 +766,8 @@ send alter table t1 engine=memory; connection con2; let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Waiting for table" and info = "alter table t1 engine=memory"; + where state = "Waiting for table metadata lock" and + info = "alter table t1 engine=memory"; --source include/wait_condition.inc connection default; --echo # Since S metadata lock was already acquired at HANDLER OPEN time @@ -1024,7 +1027,9 @@ connection con1; --echo # --> connection con2 connection con2; --echo # Waitng for 'drop table t1' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist where state='Waiting for table' and info='drop table t1'; +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t1'; --source include/wait_condition.inc --echo # --> connection default connection default; @@ -1055,7 +1060,9 @@ connection con1; --echo # --> connection con2 connection con2; --echo # Waiting for 'drop table t1' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist where state='Waiting for table' and info='drop table t1'; +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t1'; --source include/wait_condition.inc --echo # --> connection default connection default; @@ -1097,7 +1104,8 @@ send rename table t0 to t3, t1 to t0, t3 to t1; connection con1; --echo # Waiting for 'rename table ...' to get blocked... let $wait_condition=select count(*)=1 from information_schema.processlist -where state='Waiting for table' and info='rename table t0 to t3, t1 to t0, t3 to t1'; + where state='Waiting for table metadata lock' and + info='rename table t0 to t3, t1 to t0, t3 to t1'; --source include/wait_condition.inc --echo # --> connection default connection default; @@ -1137,7 +1145,9 @@ connection con2; --echo # --> connection con1 connection con1; --echo # Waiting for 'drop table t2' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist where state='Waiting for table' and info='drop table t2'; +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t2'; --source include/wait_condition.inc --echo # --> connection default connection default; @@ -1146,7 +1156,9 @@ send select * from t2; --echo # --> connection con1 connection con1; --echo # Waiting for 'select * from t2' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist where state='Waiting for table' and info='select * from t2'; +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='select * from t2'; unlock tables; --echo # --> connection con2 connection con2; @@ -1190,10 +1202,14 @@ connection default; --echo # --> connection con3 connection con3; --echo # Waiting for 'drop table t1' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist where state='Waiting for table' and info='drop table t1'; +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t1'; --source include/wait_condition.inc --echo # Waiting for 'drop table t2' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist where state='Waiting for table' and info='drop table t2'; +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t2'; --source include/wait_condition.inc --echo # Demonstrate that t2 lock was released and t2 was dropped --echo # after ROLLBACK TO SAVEPOINT @@ -1255,10 +1271,14 @@ connection default; --echo # --> connection con3 connection con3; --echo # Waiting for 'drop table t1' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist where state='Waiting for table' and info='drop table t1'; +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t1'; --source include/wait_condition.inc --echo # Waiting for 'drop table t2' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist where state='Waiting for table' and info='drop table t2'; +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t2'; --source include/wait_condition.inc --echo # Demonstrate that t2 lock was released and t2 was dropped --echo # after ROLLBACK TO SAVEPOINT @@ -1314,7 +1334,9 @@ drop table t1, t2; --echo # --> connection con2 connection con2; --echo # Waiting for 'drop table t3' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist where state='Waiting for table' and info='drop table t3'; +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t3'; --source include/wait_condition.inc --echo # Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler --echo # lock. @@ -1348,7 +1370,9 @@ send drop table t2; --echo # --> connection con2 connection con2; --echo # Waiting for 'drop table t2' to get blocked... -let $wait_condition=select count(*)=1 from information_schema.processlist where state='Waiting for table' and info='drop table t2'; +let $wait_condition=select count(*)=1 from information_schema.processlist + where state='Waiting for table metadata lock' and + info='drop table t2'; --source include/wait_condition.inc --echo # --> connection con1 connection con1; @@ -1400,7 +1424,8 @@ connection con2; --echo # has read from the table commits. let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Waiting for table" and info = "lock tables t1 write"; + where state = "Waiting for table metadata lock" and + info = "lock tables t1 write"; --source include/wait_condition.inc --echo # --> connection default @@ -1427,7 +1452,8 @@ connection con1; --echo # Waiting for 'handler t1 read a next' to get blocked... let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Table lock" and info = "handler t1 read a next"; + where state = "Waiting for table level lock" and + info = "handler t1 read a next"; --source include/wait_condition.inc --echo # The below 'drop table t1' should be able to proceed without diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc index fe6abe13892..d1afb8bcf0b 100644 --- a/mysql-test/include/mix1.inc +++ b/mysql-test/include/mix1.inc @@ -1583,7 +1583,7 @@ connect (con1, localhost, root,,); --echo # Connection default connection default; let $wait_condition= SELECT COUNT(*)=1 FROM information_schema.processlist - WHERE state='Waiting for table' AND info='TRUNCATE TABLE t1'; + WHERE state='Waiting for table metadata lock' AND info='TRUNCATE TABLE t1'; --source include/wait_condition.inc SELECT * FROM t1 ORDER BY a; ROLLBACK; diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 112a86e0c88..f209e401764 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -701,6 +701,7 @@ drop table t1,t2,t3,t4; set query_cache_wlock_invalidate=1; create table t1 (a int not null); create table t2 (a int not null); +create view v1 as select * from t1; select * from t1; a select * from t2; @@ -713,6 +714,17 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 unlock table; +select * from t1; +a +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +lock table v1 write; +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +unlock table; +drop view v1; drop table t1,t2; set query_cache_wlock_invalidate=default; CREATE TABLE t1 (id INT PRIMARY KEY); @@ -853,7 +865,7 @@ Variable_name Value Qcache_queries_in_cache 0 show status like "Qcache_inserts"; Variable_name Value -Qcache_inserts 18 +Qcache_inserts 19 show status like "Qcache_hits"; Variable_name Value Qcache_hits 6 @@ -866,7 +878,7 @@ Variable_name Value Qcache_queries_in_cache 1 show status like "Qcache_inserts"; Variable_name Value -Qcache_inserts 19 +Qcache_inserts 20 show status like "Qcache_hits"; Variable_name Value Qcache_hits 7 diff --git a/mysql-test/r/sp-lock.result b/mysql-test/r/sp-lock.result index a7823175b3c..0d3e87f17e2 100644 --- a/mysql-test/r/sp-lock.result +++ b/mysql-test/r/sp-lock.result @@ -148,12 +148,12 @@ f1() # Sending 'drop procedure p1'... drop procedure p1; # --> connection con2 -# Waitng for 'drop procedure t1' to get blocked on MDL lock... +# Waiting for 'drop procedure t1' to get blocked on MDL lock... # Demonstrate that there is a pending exclusive lock. # Sending 'select f1()'... select f1(); # --> connection con3 -# Waitng for 'select f1()' to get blocked by a pending MDL lock... +# Waiting for 'select f1()' to get blocked by a pending MDL lock... # --> connection default commit; # --> connection con1 @@ -174,12 +174,12 @@ f1() # Sending 'create procedure p1'... create procedure p1() begin end; # --> connection con2 -# Waitng for 'create procedure t1' to get blocked on MDL lock... +# Waiting for 'create procedure t1' to get blocked on MDL lock... # Demonstrate that there is a pending exclusive lock. # Sending 'select f1()'... select f1(); # --> connection con3 -# Waitng for 'select f1()' to get blocked by a pending MDL lock... +# Waiting for 'select f1()' to get blocked by a pending MDL lock... # --> connection default commit; # --> connection con1 @@ -200,12 +200,12 @@ f1() # Sending 'alter procedure p1'... alter procedure p1 contains sql; # --> connection con2 -# Waitng for 'alter procedure t1' to get blocked on MDL lock... +# Waiting for 'alter procedure t1' to get blocked on MDL lock... # Demonstrate that there is a pending exclusive lock. # Sending 'select f1()'... select f1(); # --> connection con3 -# Waitng for 'select f1()' to get blocked by a pending MDL lock... +# Waiting for 'select f1()' to get blocked by a pending MDL lock... # --> connection default commit; # --> connection con1 @@ -226,12 +226,12 @@ f1() # Sending 'drop function f1'... drop function f1; # --> connection con2 -# Waitng for 'drop function f1' to get blocked on MDL lock... +# Waiting for 'drop function f1' to get blocked on MDL lock... # Demonstrate that there is a pending exclusive lock. # Sending 'select f1()'... select f1(); # --> connection con3 -# Waitng for 'select f1()' to get blocked by a pending MDL lock... +# Waiting for 'select f1()' to get blocked by a pending MDL lock... # --> connection default commit; # --> connection con1 @@ -252,12 +252,12 @@ f1() # Sending 'create function f1'... create function f1() returns int return 2; # --> connection con2 -# Waitng for 'create function f1' to get blocked on MDL lock... +# Waiting for 'create function f1' to get blocked on MDL lock... # Demonstrate that there is a pending exclusive lock. # Sending 'select f1()'... select f1(); # --> connection con3 -# Waitng for 'select f1()' to get blocked by a pending MDL lock... +# Waiting for 'select f1()' to get blocked by a pending MDL lock... # --> connection default commit; # --> connection con1 @@ -279,12 +279,12 @@ f1() # Sending 'alter function f1'... alter function f1 contains sql; # --> connection con2 -# Waitng for 'alter function f1' to get blocked on MDL lock... +# Waiting for 'alter function f1' to get blocked on MDL lock... # Demonstrate that there is a pending exclusive lock. # Sending 'select f1()'... select f1(); # --> connection con3 -# Waitng for 'select f1()' to get blocked by a pending MDL lock... +# Waiting for 'select f1()' to get blocked by a pending MDL lock... # --> connection default commit; # --> connection con1 @@ -360,7 +360,7 @@ insert into t1 (a) values (1); # Sending 'drop function f1' drop function f1; # --> connection con2 -# Waitng for 'drop function f1' to get blocked on MDL lock... +# Waiting for 'drop function f1' to get blocked on MDL lock... # --> connnection default commit; # --> connection con1 @@ -379,7 +379,7 @@ a # Sending 'drop function f1' drop function f1; # --> connection con2 -# Waitng for 'drop function f1' to get blocked on MDL lock... +# Waiting for 'drop function f1' to get blocked on MDL lock... # --> connnection default commit; # --> connection con1 @@ -403,7 +403,7 @@ a # Sending 'drop procedure p1' drop procedure p1; # --> connection con2 -# Waitng for 'drop procedure p1' to get blocked on MDL lock... +# Waiting for 'drop procedure p1' to get blocked on MDL lock... # --> connnection default commit; # --> connection con1 @@ -424,7 +424,7 @@ insert into t1 (a) values (3); # Sending 'drop function f2' drop function f2; # --> connection con2 -# Waitng for 'drop function f2' to get blocked on MDL lock... +# Waiting for 'drop function f2' to get blocked on MDL lock... # --> connnection default commit; # --> connection con1 @@ -479,11 +479,11 @@ f2() # Sending 'drop function f1'... drop function f1; # --> connection con2 -# Waitng for 'drop function f1' to get blocked on MDL lock... +# Waiting for 'drop function f1' to get blocked on MDL lock... # Sending 'drop function f2'... drop function f2; # --> connection default -# Waitng for 'drop function f2' to get blocked on MDL lock... +# Waiting for 'drop function f2' to get blocked on MDL lock... rollback to savepoint sv; # --> connection con2 # Reaping 'drop function f2'... @@ -537,10 +537,10 @@ f1() # Sending 'alter function f1 ...'... alter function f1 comment "comment"; # --> connection con2 -# Waitng for 'alter function f1 ...' to get blocked on MDL lock... +# Waiting for 'alter function f1 ...' to get blocked on MDL lock... # Sending 'call p1()'... call p1(); -# Waitng for 'call p1()' to get blocked on MDL lock on f1... +# Waiting for 'call p1()' to get blocked on MDL lock on f1... # Let 'alter function f1 ...' go through... commit; # --> connection con1 @@ -573,7 +573,7 @@ f1() # Sending 'alter function f1 ...'... alter function f1 comment "comment"; # --> connection con2 -# Waitng for 'alter function f1 ...' to get blocked on MDL lock... +# Waiting for 'alter function f1 ...' to get blocked on MDL lock... # # We just mention p1() in the body of f2() to make # sure that p1() is prelocked for f2(). @@ -595,7 +595,7 @@ select f2() into @var; end| # Sending 'call p1()'... call p1(); -# Waitng for 'call p1()' to get blocked on MDL lock on f1... +# Waiting for 'call p1()' to get blocked on MDL lock on f1... # Let 'alter function f1 ...' go through... commit; # --> connection con1 @@ -634,7 +634,7 @@ get_lock("30977", 0) # Sending 'select f3()'... select f3(); # --> connection con1 -# Waitng for 'select f3()' to get blocked on the user level lock... +# Waiting for 'select f3()' to get blocked on the user level lock... # Do something to change the cache version. create function f4() returns int return 4; drop function f4; diff --git a/mysql-test/r/sp-threads.result b/mysql-test/r/sp-threads.result index a14d099c673..9b458e25615 100644 --- a/mysql-test/r/sp-threads.result +++ b/mysql-test/r/sp-threads.result @@ -35,7 +35,7 @@ call bug9486(); show processlist; Id User Host db Command Time State Info # root localhost test Sleep # NULL -# root localhost test Query # Waiting for table update t1, t2 set val= 1 where id1=id2 +# root localhost test Query # Waiting for table metadata lock update t1, t2 set val= 1 where id1=id2 # root localhost test Query # NULL show processlist # root localhost test Sleep # NULL unlock tables; diff --git a/mysql-test/suite/binlog/t/binlog_stm_row.test b/mysql-test/suite/binlog/t/binlog_stm_row.test index 6165048c7d4..87758d57725 100644 --- a/mysql-test/suite/binlog/t/binlog_stm_row.test +++ b/mysql-test/suite/binlog/t/binlog_stm_row.test @@ -60,7 +60,7 @@ let $wait_condition= --echo # con1 let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE - state = "Table lock" and info = "INSERT INTO t2 VALUES (3)"; + state = "Waiting for table level lock" and info = "INSERT INTO t2 VALUES (3)"; --source include/wait_condition.inc SELECT RELEASE_LOCK('Bug#34306'); --connection con2 diff --git a/mysql-test/suite/funcs_1/datadict/processlist_val.inc b/mysql-test/suite/funcs_1/datadict/processlist_val.inc index 9c6bd585fde..1327338e761 100644 --- a/mysql-test/suite/funcs_1/datadict/processlist_val.inc +++ b/mysql-test/suite/funcs_1/datadict/processlist_val.inc @@ -367,14 +367,14 @@ echo ; connection default; echo -# Poll till INFO is no more NULL and State = 'Waiting for table'. +# Poll till INFO is no more NULL and State = 'Waiting for table metadata lock'. ; let $wait_condition= SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST - WHERE INFO IS NOT NULL AND STATE = 'Waiting for table'; + WHERE INFO IS NOT NULL AND STATE = 'Waiting for table metadata lock'; --source include/wait_condition.inc # -# Expect to see the state 'Waiting for table' for the third connection because the SELECT -# collides with the WRITE TABLE LOCK. +# Expect to see the state 'Waiting for table metadata lock' for the third +# connection because the SELECT collides with the WRITE TABLE LOCK. --replace_column 1 3 6