diff options
Diffstat (limited to 'mysql-test/main/mdl_sync.test')
| -rw-r--r-- | mysql-test/main/mdl_sync.test | 4013 |
1 files changed, 4013 insertions, 0 deletions
diff --git a/mysql-test/main/mdl_sync.test b/mysql-test/main/mdl_sync.test new file mode 100644 index 00000000..3df19aca --- /dev/null +++ b/mysql-test/main/mdl_sync.test @@ -0,0 +1,4013 @@ +# +# We need the Debug Sync Facility. +# +--source include/have_debug_sync.inc +--source include/have_metadata_lock_info.inc + +# We need InnoDB tables for some of the tests. +--source include/have_innodb.inc + +# Save the initial number of concurrent sessions. +--source include/count_sessions.inc + + +# Clean up resources used in this test case. +--disable_warnings +SET DEBUG_SYNC= 'RESET'; +--enable_warnings + +# +# Test the case of when a exclusive lock request waits for a +# shared lock being upgraded to a exclusive lock. +# + +connect (con1,localhost,root,,test,,); +connect (con2,localhost,root,,test,,); +connect (con3,localhost,root,,test,,); + +connection default; + +--disable_warnings +drop table if exists t1,t2,t3; +--enable_warnings + +create table t1 (i int); +create table t2 (i int); + +--echo connection: default +lock tables t2 read; + +connection con1; +--echo connection: con1 +set debug_sync='mdl_upgrade_lock SIGNAL parked WAIT_FOR go'; +--send alter table t1 rename t3 + +connection default; +--echo connection: default +set debug_sync= 'now WAIT_FOR parked'; + +connection con2; +--echo connection: con2 +set debug_sync='mdl_acquire_lock_wait SIGNAL go'; +--send drop table t1,t2 + +connection con1; +--echo connection: con1 +--reap + +connection default; +--echo connection: default +unlock tables; + +connection con2; +--echo connection: con2 +--error ER_BAD_TABLE_ERROR +--reap + +connection default; +drop table t3; + +disconnect con1; +disconnect con2; +disconnect con3; + +# Clean up resources used in this test case. +--disable_warnings +SET DEBUG_SYNC= 'RESET'; +--enable_warnings + + +--echo # +--echo # Basic test coverage for type-of-operation aware metadata locks. +--echo # +--disable_warnings +drop table if exists t1, t2, t3; +--enable_warnings +connect(mdl_con1,localhost,root,,); +connect(mdl_con2,localhost,root,,); +connect(mdl_con3,localhost,root,,); +connection default; +set debug_sync= 'RESET'; +create table t1 (c1 int); + +--echo # +--echo # A) First let us check compatibility rules between differend kinds of +--echo # type-of-operation aware metadata locks. +--echo # Of course, these rules are already covered by the tests scattered +--echo # across the test suite. But it still makes sense to have one place +--echo # which covers all of them. +--echo # + +--echo # 1) Acquire S (simple shared) lock on the table (by using HANDLER): +--echo # +handler t1 open; +--echo # +connection mdl_con1; +--echo # Check that S, SH, SR and SW locks are compatible with it. +handler t1 open t; +handler t close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +select count(*) from t1; +insert into t1 values (1), (1); +--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE +--echo # which will fail when constructing .frm and thus obtaining SU metadata +--echo # lock. +--error ER_KEY_COLUMN_DOES_NOT_EXIST +alter table t1 add index (not_exist); +--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE +--echo # which will fail during copying the table and thus obtaining SNW metadata +--echo # lock. +--error ER_DUP_ENTRY +alter table t1 add primary key (c1); +--echo # Check that X lock is incompatible with S lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con2; +--echo # Check that the above RENAME is blocked because of S lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # +connection default; +--echo # Unblock RENAME TABLE. +handler t1 close; +--echo # +connection mdl_con1; +--echo # Reaping RENAME TABLE. +--reap +--echo # Restore the original state of the things. +rename table t2 to t1; +--echo # +connection default; +handler t1 open; +--echo # +connection mdl_con1; +--echo # Check that upgrade from SNW to X is blocked by presence of S lock. +--echo # Sending: +--send alter table t1 add column c2 int; +--echo # +connection mdl_con2; +--echo # Check that the above ALTER TABLE is blocked because of S lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 add column c2 int"; +--source include/wait_condition.inc +--echo # +connection default; +--echo # Unblock ALTER TABLE. +handler t1 close; +--echo # +connection mdl_con1; +--echo # Reaping ALTER TABLE. +--reap +--echo # Restore the original state of the things. +alter table t1 drop column c2; +--echo # +connection default; +--echo # +--echo # 2) Acquire SH (shared high-priority) lock on the table. +--echo # We have to involve DEBUG_SYNC facility for this as usually +--echo # such kind of locks are short-lived. +--echo # +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; +--echo # +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that S, SH, SR and SW locks are compatible with it. +handler t1 open; +handler t1 close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +select count(*) from t1; +insert into t1 values (1); +--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE +--echo # which will fail when constructing .frm and thus obtaining SU metadata +--echo # lock. +--error ER_KEY_COLUMN_DOES_NOT_EXIST +alter table t1 add index (not_exist); +--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE +--echo # which will fail during copying the table and thus obtaining SNW metadata +--echo # lock. +--error ER_DUP_ENTRY +alter table t1 add primary key (c1); +--echo # Check that SNRW lock is compatible with SH lock. +lock table t1 write; +delete from t1 limit 1; +unlock tables; +--echo # Check that X lock is incompatible with SH lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con2; +--echo # Check that the above RENAME is blocked because of SH lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +set debug_sync= 'now SIGNAL finish'; +--echo # +connection default; +--echo # Reaping SELECT ... FROM I_S. +--reap +--echo # +connection mdl_con1; +--echo # Reaping RENAME TABLE. +--reap +--echo # Restore the original state of the things. +rename table t2 to t1; +--echo # +connection default; +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; +--echo # +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that upgrade from SNW to X is blocked by presence of SH lock. +--echo # Sending: +--send alter table t1 add column c2 int; +--echo # +connection mdl_con2; +--echo # Check that the above ALTER TABLE is blocked because of SH lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 add column c2 int"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +set debug_sync= 'now SIGNAL finish'; +--echo # +connection default; +--echo # Reaping SELECT ... FROM I_S. +--reap +--echo # +connection mdl_con1; +--echo # Reaping ALTER TABLE. +--reap +--echo # Restore the original state of the things. +alter table t1 drop column c2; +--echo # +connection default; +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; +--echo # +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that upgrade from SNRW to X is blocked by presence of S lock. +lock table t1 write; +--echo # Sending: +--send alter table t1 add column c2 int; +--echo # +connection mdl_con2; +--echo # Check that the above upgrade of SNRW to X in ALTER TABLE is blocked +--echo # because of S lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 add column c2 int"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +set debug_sync= 'now SIGNAL finish'; +--echo # +connection default; +--echo # Reaping SELECT ... FROM I_S. +--reap +--echo # +connection mdl_con1; +--echo # Reaping ALTER TABLE. +--reap +--echo # Restore the original state of the things. +alter table t1 drop column c2; +unlock tables; +--echo # +connection default; +--echo # +--echo # +--echo # 3) Acquire SR lock on the table. +--echo # +--echo # +begin; +select count(*) from t1; +--echo # +connection mdl_con1; +--echo # Check that S, SH, SR and SW locks are compatible with it. +handler t1 open; +handler t1 close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +select count(*) from t1; +insert into t1 values (1); +--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE +--echo # which will fail when constructing .frm and thus obtaining SU metadata +--echo # lock. +--error ER_KEY_COLUMN_DOES_NOT_EXIST +alter table t1 add index (not_exist); +--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE +--echo # which will fail during copying the table and thus obtaining SNW metadata +--echo # lock. +--error ER_DUP_ENTRY +alter table t1 add primary key (c1); +--echo # Check that SNRW lock is not compatible with SR lock. +--echo # Sending: +--send lock table t1 write; +--echo # +connection default; +--echo # Check that the above LOCK TABLES is blocked because of SR lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Unblock LOCK TABLES. +commit; +--echo # +connection mdl_con1; +--echo # Reaping LOCK TABLES. +--reap +delete from t1 limit 1; +unlock tables; +--echo # +connection default; +begin; +select count(*) from t1; +--echo # +connection mdl_con1; +--echo # Check that X lock is incompatible with SR lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con2; +--echo # Check that the above RENAME is blocked because of SR lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # +connection default; +--echo # Unblock RENAME TABLE. +commit; +--echo # +connection mdl_con1; +--echo # Reaping RENAME TABLE. +--reap +--echo # Restore the original state of the things. +rename table t2 to t1; +--echo # +connection default; +begin; +select count(*) from t1; +--echo # +connection mdl_con1; +--echo # Check that upgrade from SNW to X is blocked by presence of SR lock. +--echo # Sending: +--send alter table t1 add column c2 int; +--echo # +connection mdl_con2; +--echo # Check that the above ALTER TABLE is blocked because of SR lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 add column c2 int"; +--source include/wait_condition.inc +--echo # +connection default; +--echo # Unblock ALTER TABLE. +commit; +--echo # +connection mdl_con1; +--echo # Reaping ALTER TABLE. +--reap +--echo # Restore the original state of the things. +alter table t1 drop column c2; +--echo # +--echo # There is no need to check that upgrade from SNRW to X is blocked +--echo # by presence of SR lock because SNRW is incompatible with SR anyway. +--echo # +--echo # +connection default; +--echo # +--echo # +--echo # 4) Acquire SW lock on the table. +--echo # +--echo # +begin; +insert into t1 values (1); +--echo # +connection mdl_con1; +--echo # Check that S, SH, SR and SW locks are compatible with it. +handler t1 open; +handler t1 close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +--echo # Disable result log to make test robust against +--echo # effects of concurrent insert. +--disable_result_log +select * from t1; +--enable_result_log +insert into t1 values (1); +--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE +--echo # which will fail when constructing .frm and thus obtaining SU metadata +--echo # lock. +--error ER_KEY_COLUMN_DOES_NOT_EXIST +alter table t1 add index (not_exist); +--echo # Check that SNW lock is not compatible with SW lock. +--echo # Again we use ALTER TABLE which fails during copying +--echo # the table to avoid upgrade of SNW -> X. +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +connection default; +--echo # Check that the above ALTER TABLE is blocked because of SW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # Unblock ALTER TABLE. +commit; +--echo # +connection mdl_con1; +--echo # Reaping ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +connection default; +begin; +insert into t1 values (1); +--echo # +connection mdl_con1; +--echo # Check that SNRW lock is not compatible with SW lock. +--echo # Sending: +--send lock table t1 write; +--echo # +connection default; +--echo # Check that the above LOCK TABLES is blocked because of SW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Unblock LOCK TABLES. +commit; +--echo # +connection mdl_con1; +--echo # Reaping LOCK TABLES. +--reap +delete from t1 limit 2; +unlock tables; +--echo # +connection default; +begin; +insert into t1 values (1); +--echo # +connection mdl_con1; +--echo # Check that X lock is incompatible with SW lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con2; +--echo # Check that the above RENAME is blocked because of SW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # +connection default; +--echo # Unblock RENAME TABLE. +commit; +--echo # +connection mdl_con1; +--echo # Reaping RENAME TABLE. +--reap +--echo # Restore the original state of the things. +rename table t2 to t1; +--echo # +--echo # There is no need to check that upgrade from SNW/SNRW to X is +--echo # blocked by presence of SW lock because SNW/SNRW is incompatible +--echo # with SW anyway. +--echo # +--echo # +connection default; +--echo # +--echo # +--echo # 5) Acquire SU lock on the table. We have to use DEBUG_SYNC for +--echo # this, to prevent SU from being immediately upgraded to X. +--echo # +set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that S, SH, SR and SW locks are compatible with it. +handler t1 open; +handler t1 close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +select count(*) from t1; +delete from t1 limit 1; +--echo # Check that SU lock is incompatible with SU lock. +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +connection mdl_con2; +--echo # Check that the above ALTER is blocked because of SU lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # Unblock ALTERs. +set debug_sync= 'now SIGNAL finish'; +--echo # +connection default; +--echo # Reaping first ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +connection mdl_con1; +--echo # Reaping another ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +connection default; +set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that SNRW lock is incompatible with SU lock. +--echo # Sending: +--send lock table t1 write; +--echo # +connection mdl_con2; +--echo # Check that the above LOCK TABLES is blocked because of SU lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Unblock ALTER and thus LOCK TABLES. +set debug_sync= 'now SIGNAL finish'; +--echo # +connection default; +--echo # Reaping ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +connection mdl_con1; +--echo # Reaping LOCK TABLES +--reap +insert into t1 values (1); +unlock tables; +--echo # +connection default; +set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that X lock is incompatible with SU lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con2; +--echo # Check that the above RENAME is blocked because of SU lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Unblock ALTER and thus RENAME TABLE. +set debug_sync= 'now SIGNAL finish'; +--echo # +connection default; +--echo # Now we have ALTER TABLE with SU->SNW and RENAME TABLE with pending +--echo # X-lock. In this case ALTER TABLE should be chosen as victim. +--echo # Reaping ALTER TABLE. +--error ER_LOCK_DEADLOCK +--reap +--echo # +connection mdl_con1; +--echo # Reaping RENAME TABLE +--reap +--echo # Revert back to original state of things. +rename table t2 to t1; +--echo # +--echo # There is no need to check that upgrade from SNW/SNRW to X is +--echo # blocked by presence of another SU lock because SNW/SNRW is +--echo # incompatible with SU anyway. +--echo # +connection default; +--echo # +--echo # +--echo # 6) Acquire SNW lock on the table. We have to use DEBUG_SYNC for +--echo # this, to prevent SNW from being immediately upgraded to X. +--echo # +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1), lock=shared, algorithm=copy; +--echo # +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that S, SH and SR locks are compatible with it. +handler t1 open; +handler t1 close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +select count(*) from t1; +--echo # Check that SW lock is incompatible with SNW lock. +--echo # Sending: +--send delete from t1 limit 2; +--echo # +connection mdl_con2; +--echo # Check that the above DELETE is blocked because of SNW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "delete from t1 limit 2"; +--source include/wait_condition.inc +--echo # Unblock ALTER and thus DELETE. +set debug_sync= 'now SIGNAL finish'; +--echo # +connection default; +--echo # Reaping ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +connection mdl_con1; +--echo # Reaping DELETE. +--reap +--echo # +connection default; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1), lock=shared, algorithm=copy; +--echo # +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that SU lock is incompatible with SNW lock. +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +connection mdl_con2; +--echo # Check that the above ALTER is blocked because of SNW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # Unblock ALTERs. +set debug_sync= 'now SIGNAL finish'; +--echo # +connection default; +--echo # Reaping first ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +connection mdl_con1; +--echo # Reaping another ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Note that we can't easily check SNW vs SNW locks since +--echo # SNW is only used by ALTER TABLE after upgrading from SU +--echo # and SU is also incompatible with SNW. +--echo # +connection default; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1), lock=shared, algorithm=copy; +--echo # +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that SNRW lock is incompatible with SNW lock. +--echo # Sending: +--send lock table t1 write; +--echo # +connection mdl_con2; +--echo # Check that the above LOCK TABLES is blocked because of SNW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Unblock ALTER and thus LOCK TABLES. +set debug_sync= 'now SIGNAL finish'; +--echo # +connection default; +--echo # Reaping ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +connection mdl_con1; +--echo # Reaping LOCK TABLES +--reap +insert into t1 values (1); +unlock tables; +--echo # +connection default; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1), algorithm=copy, lock=shared; +--echo # +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that X lock is incompatible with SNW lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con2; +--echo # Check that the above RENAME is blocked because of SNW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Unblock ALTER and thus RENAME TABLE. +set debug_sync= 'now SIGNAL finish'; +--echo # +connection default; +--echo # Reaping ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +connection mdl_con1; +--echo # Reaping RENAME TABLE +--reap +--echo # Revert back to original state of things. +rename table t2 to t1; +--echo # +--echo # There is no need to check that upgrade from SNW/SNRW to X is +--echo # blocked by presence of another SNW lock because SNW/SNRW is +--echo # incompatible with SNW anyway. +--echo # +connection default; +--echo # +--echo # +--echo # 7) Acquire SNRW lock on the table. +--echo # +--echo # +lock table t1 write; +--echo # +connection mdl_con1; +--echo # Check that S and SH locks are compatible with it. +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +--echo # Check that SR lock is incompatible with SNRW lock. +--echo # Sending: +--send select count(*) from t1; +--echo # +connection default; +--echo # Check that the above SELECT is blocked because of SNRW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "select count(*) from t1"; +--source include/wait_condition.inc +--echo # Unblock SELECT. +unlock tables; +--echo # +connection mdl_con1; +--echo # Reaping SELECT. +--reap +--echo # +connection default; +lock table t1 write; +--echo # +connection mdl_con1; +--echo # Check that SW lock is incompatible with SNRW lock. +--echo # Sending: +--send delete from t1 limit 1; +--echo # +connection default; +--echo # Check that the above DELETE is blocked because of SNRW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "delete from t1 limit 1"; +--source include/wait_condition.inc +--echo # Unblock DELETE. +unlock tables; +--echo # +connection mdl_con1; +--echo # Reaping DELETE. +--reap +--echo # +connection default; +lock table t1 write; +--echo # +connection mdl_con1; +--echo # Check that SU lock is incompatible with SNRW lock. +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +connection default; +--echo # Check that the above ALTER is blocked because of SNRW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # Unblock ALTER. +unlock tables; +--echo # +connection mdl_con1; +--echo # Reaping ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Note that we can't easily check SNW vs SNRW locks since +--echo # SNW is only used by ALTER TABLE after upgrading from SU +--echo # and SU is also incompatible with SNRW. +--echo # +connection default; +lock table t1 write; +--echo # +connection mdl_con1; +--echo # Check that SNRW lock is incompatible with SNRW lock. +--echo # Sending: +--send lock table t1 write; +--echo # +connection default; +--echo # Check that the above LOCK TABLES is blocked because of SNRW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Unblock waiting LOCK TABLES. +unlock tables; +--echo # +connection mdl_con1; +--echo # Reaping LOCK TABLES +--reap +insert into t1 values (1); +unlock tables; +--echo # +connection default; +lock table t1 write; +--echo # +connection mdl_con1; +--echo # Check that X lock is incompatible with SNRW lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection default; +--echo # Check that the above RENAME is blocked because of SNRW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +connection mdl_con1; +--echo # Reaping RENAME TABLE +--reap +--echo # Revert back to original state of things. +rename table t2 to t1; +--echo # +--echo # There is no need to check that upgrade from SNW/SNRW to X is +--echo # blocked by presence of another SNRW lock because SNW/SNRW is +--echo # incompatible with SNRW anyway. +--echo # +connection default; +--echo # +--echo # +--echo # 8) Now do the same round of tests for X lock. We use additional +--echo # table to get long-lived lock of this type. +--echo # +create table t2 (c1 int); +--echo # +connection mdl_con2; +--echo # Take a lock on t2, so RENAME TABLE t1 TO t2 will get blocked +--echo # after acquiring X lock on t1. +lock tables t2 read; +--echo # +connection default; +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con1; +--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that S lock in incompatible with X lock. +--echo # Sending: +--send handler t1 open; +--echo # +connection mdl_con2; +--echo # Check that the above HANDLER statement is blocked because of X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "handler t1 open"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +connection mdl_con1; +--echo # Reaping HANDLER. +--reap +handler t1 close; +--echo # +connection mdl_con2; +--echo # Prepare for blocking RENAME TABLE. +lock tables t2 read; +--echo # +connection default; +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con1; +--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SH lock in incompatible with X lock. +--echo # Sending: +--send select column_name from information_schema.columns where table_schema='test' and table_name='t1'; +--echo # +connection mdl_con2; +--echo # Check that the above SELECT ... FROM I_S ... statement is blocked +--echo # because of X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info like "select column_name from information_schema.columns%"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +connection mdl_con1; +--echo # Reaping SELECT ... FROM I_S. +--reap +--echo # +connection mdl_con2; +--echo # Prepare for blocking RENAME TABLE. +lock tables t2 read; +--echo # +connection default; +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con1; +--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SR lock in incompatible with X lock. +--echo # Sending: +--send select count(*) from t1; +--echo # +connection mdl_con2; +--echo # Check that the above SELECT statement is blocked +--echo # because of X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "select count(*) from t1"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +connection mdl_con1; +--echo # Reaping SELECT. +--reap +--echo # +connection mdl_con2; +--echo # Prepare for blocking RENAME TABLE. +lock tables t2 read; +--echo # +connection default; +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con1; +--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SW lock in incompatible with X lock. +--echo # Sending: +--send delete from t1 limit 1; +--echo # +connection mdl_con2; +--echo # Check that the above DELETE statement is blocked +--echo # because of X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "delete from t1 limit 1"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +connection mdl_con1; +--echo # Reaping DELETE. +--reap +--echo # +connection mdl_con2; +--echo # Prepare for blocking RENAME TABLE. +lock tables t2 read; +--echo # +connection default; +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con1; +--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SU lock is incompatible with X lock. +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +connection mdl_con2; +--echo # Check that the above ALTER statement is blocked +--echo # because of X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +connection default; +--echo # Reaping RENAME TABLE +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +connection mdl_con1; +--echo # Reaping ALTER. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Note that we can't easily check SNW vs X locks since +--echo # SNW is only used by ALTER TABLE after upgrading from SU +--echo # and SU is also incompatible with X. +--echo # +connection mdl_con2; +--echo # Prepare for blocking RENAME TABLE. +lock tables t2 read; +--echo # +connection default; +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con1; +--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SNRW lock is incompatible with X lock. +--echo # Sending: +--send lock table t1 write; +--echo # +connection mdl_con2; +--echo # Check that the above LOCK TABLE statement is blocked +--echo # because of X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +connection default; +--echo # Reaping RENAME TABLE +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +connection mdl_con1; +--echo # Reaping LOCK TABLE. +--reap +unlock tables; +--echo # +connection mdl_con2; +--echo # Prepare for blocking RENAME TABLE. +lock tables t2 read; +--echo # +connection default; +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con1; +--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that X lock is incompatible with X lock. +--echo # Sending: +--send rename table t1 to t3; +--echo # +connection mdl_con2; +--echo # Check that the above RENAME statement is blocked +--echo # because of X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t1 to t3"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +connection default; +--echo # Reaping RENAME TABLE +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +connection mdl_con1; +--echo # Reaping RENAME. +--reap +rename table t3 to t1; + +--echo # +--echo # B) Now let us test compatibility in cases when both locks +--echo # are pending. I.e. let us test rules for priorities between +--echo # different types of metadata locks. +--echo # +--echo # Note: No tests for pending SU lock as this lock requires +--echo # even stronger active or pending lock. +--echo # + +--echo # +connection mdl_con2; +--echo # +--echo # 1) Check compatibility for pending SNW lock. +--echo # +--echo # Acquire SW lock in order to create pending SNW lock later. +begin; +insert into t1 values (1); +--echo # +connection default; +--echo # Add pending SNW lock. +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +connection mdl_con1; +--echo # Check that ALTER TABLE is waiting with pending SNW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # Check that S, SH and SR locks are compatible with pending SNW +handler t1 open t; +handler t close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +select count(*) from t1; +--echo # Check that SW is incompatible with pending SNW +--echo # Sending: +--send delete from t1 limit 1; +--echo # +connection mdl_con2; +--echo # Check that the above DELETE is blocked because of pending SNW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "delete from t1 limit 1"; +--source include/wait_condition.inc +--echo # Unblock ALTER TABLE. +commit; +--echo # +connection default; +--echo # Reaping ALTER. +--error ER_DUP_ENTRY +--reap +--echo # +connection mdl_con1; +--echo # Reaping DELETE. +--reap +--echo # +--echo # We can't do similar check for SNW, SNRW and X locks because +--echo # they will also be blocked by active SW lock. +--echo # +--echo # +connection mdl_con2; +--echo # +--echo # 2) Check compatibility for pending SNRW lock. +--echo # +--echo # Acquire SR lock in order to create pending SNRW lock. +begin; +select count(*) from t1; +--echo # +connection default; +--echo # Add pending SNRW lock. +--echo # Sending: +--send lock table t1 write; +--echo # +connection mdl_con1; +--echo # Check that LOCK TABLE is waiting with pending SNRW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Check that S and SH locks are compatible with pending SNRW +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +--echo # Check that SR is incompatible with pending SNRW +--echo # Sending: +--send select count(*) from t1; +--echo # +connection mdl_con2; +--echo # Check that the above SELECT is blocked because of pending SNRW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "select count(*) from t1"; +--source include/wait_condition.inc +--echo # Unblock LOCK TABLE. +commit; +--echo # +connection default; +--echo # Reaping LOCK TABLE. +--reap +unlock tables; +--echo # +connection mdl_con1; +--echo # Reaping SELECT. +--reap +--echo # Restore pending SNRW lock. +--echo # +connection mdl_con2; +begin; +select count(*) from t1; +--echo # +connection default; +--echo # Sending: +--send lock table t1 write; +--echo # +connection mdl_con1; +--echo # Check that LOCK TABLE is waiting with pending SNRW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Check that SW is incompatible with pending SNRW +--echo # Sending: +--send insert into t1 values (1); +--echo # +connection mdl_con2; +--echo # Check that the above INSERT is blocked because of pending SNRW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "insert into t1 values (1)"; +--source include/wait_condition.inc +--echo # Unblock LOCK TABLE. +commit; +--echo # +connection default; +--echo # Reaping LOCK TABLE. +--reap +unlock tables; +--echo # +connection mdl_con1; +--echo # Reaping INSERT. +--reap +--echo # Restore pending SNRW lock. +--echo # +connection mdl_con2; +begin; +select count(*) from t1; +--echo # +connection default; +--echo # Sending: +--send lock table t1 write; +--echo # +connection mdl_con1; +--echo # Check that LOCK TABLE is waiting with pending SNRW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Check that SNW is compatible with pending SNRW +--echo # So ALTER TABLE statements are not starved by LOCK TABLEs. +--error ER_DUP_ENTRY +alter table t1 add primary key (c1); +--echo # +connection mdl_con2; +--echo # Unblock LOCK TABLE. +commit; +--echo # +connection default; +--echo # Reaping LOCK TABLE. +--reap +unlock tables; +--echo # +--echo # We can't do similar check for SNRW and X locks because +--echo # they will also be blocked by active SR lock. +--echo # +--echo # +connection mdl_con2; +--echo # +--echo # 3) Check compatibility for pending X lock. +--echo # +--echo # Acquire SR lock in order to create pending X lock. +begin; +select count(*) from t1; +--echo # +connection default; +--echo # Add pending X lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con1; +--echo # Check that RENAME TABLE is waiting with pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SH locks are compatible with pending X +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +--echo # Check that S is incompatible with pending X +--echo # Sending: +--send handler t1 open; +--echo # +connection mdl_con2; +--echo # Check that the above HANDLER OPEN is blocked because of pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "handler t1 open"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +commit; +--echo # +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +connection mdl_con1; +--echo # Reaping HANDLER t1 OPEN. +--reap +handler t1 close; +--echo # Restore pending X lock. +--echo # +connection mdl_con2; +begin; +select count(*) from t1; +--echo # +connection default; +--echo # Add pending X lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con1; +--echo # Check that RENAME TABLE is waiting with pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SR is incompatible with pending X +--echo # Sending: +--send select count(*) from t1; +--echo # +connection mdl_con2; +--echo # Check that the above SELECT is blocked because of pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "select count(*) from t1"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +commit; +--echo # +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +connection mdl_con1; +--echo # Reaping SELECT. +--reap +--echo # Restore pending X lock. +--echo # +connection mdl_con2; +begin; +select count(*) from t1; +--echo # +connection default; +--echo # Add pending X lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con1; +--echo # Check that RENAME TABLE is waiting with pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SW is incompatible with pending X +--echo # Sending: +--send delete from t1 limit 1; +--echo # +connection mdl_con2; +--echo # Check that the above DELETE is blocked because of pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "delete from t1 limit 1"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +commit; +--echo # +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +connection mdl_con1; +--echo # Reaping DELETE. +--reap +--echo # Restore pending X lock. +--echo # +connection mdl_con2; +begin; +select count(*) from t1; +--echo # +connection default; +--echo # Add pending X lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con1; +--echo # Check that RENAME TABLE is waiting with pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SNW is incompatible with pending X +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +connection mdl_con2; +--echo # Check that the above ALTER TABLE is blocked because of pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +commit; +--echo # +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +connection mdl_con1; +--echo # Reaping ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # Restore pending X lock. +--echo # +connection mdl_con2; +handler t1 open; +--echo # +connection default; +--echo # Add pending X lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection mdl_con1; +--echo # Check that RENAME TABLE is waiting with pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SNRW is incompatible with pending X +--echo # Sending: +--send lock table t1 write; +--echo # +connection mdl_con3; +--echo # Check that the above LOCK TABLES is blocked because of pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # +connection mdl_con2; +--echo # Unblock RENAME TABLE. +handler t1 close; +--echo # +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +connection mdl_con1; +--echo # Reaping LOCK TABLES. +--reap +unlock tables; +--echo # +connection default; + +--echo # +--echo # +--echo # C) Now let us test how type-of-operation locks are handled in +--echo # transactional context. Obviously we are mostly interested +--echo # in conflicting types of locks. +--echo # +--echo # Note: No tests for active/pending SU lock since +--echo # ALTER TABLE is in its own transaction. +--echo # + +--echo # +--echo # 1) Let us check how various locks used within transactional +--echo # context interact with active/pending SNW lock. +--echo # +--echo # We start with case when we are acquiring lock on the table +--echo # which was not used in the transaction before. +begin; +select count(*) from t1; +--echo # +connection mdl_con1; +--echo # Create an active SNW lock on t2. +--echo # We have to use DEBUG_SYNC facility as otherwise SNW lock +--echo # will be immediately released (or upgraded to X lock). +insert into t2 values (1), (1); +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t2 add primary key (c1), algorithm=copy, lock=shared; +--echo # +connection default; +set debug_sync= 'now WAIT_FOR locked'; +--echo # SR lock should be acquired without any waiting. +select count(*) from t2; +commit; +--echo # Now let us check that we will wait in case of SW lock. +begin; +select count(*) from t1; +--echo # Sending: +--send insert into t2 values (1); +--echo # +connection mdl_con2; +--echo # Check that the above INSERT is blocked. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "insert into t2 values (1)"; +--source include/wait_condition.inc +--echo # Unblock ALTER TABLE and thus INSERT. +set debug_sync= 'now SIGNAL finish'; +--echo # +connection mdl_con1; +--echo # Reap ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +connection default; +--echo # Reap INSERT. +--reap +commit; +--echo # +--echo # Now let us see what happens when we are acquiring lock on the table +--echo # which is already used in transaction. +--echo # +--echo # *) First, case when transaction which has SR lock on the table also +--echo # locked in SNW mode acquires yet another SR lock and then tries +--echo # to acquire SW lock. +begin; +select count(*) from t1; +--echo # +connection mdl_con1; +--echo # Create an active SNW lock on t1. +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1), algorithm=copy, lock=shared; +--echo # +connection default; +set debug_sync= 'now WAIT_FOR locked'; +--echo # We should still be able to get SR lock without waiting. +select count(*) from t1; +--echo # Since the above ALTER TABLE is not upgrading SNW lock to X by waiting +--echo # for SW lock we won't create deadlock. +--echo # So the below INSERT should not end-up with ER_LOCK_DEADLOCK error. +--echo # Sending: +--send insert into t1 values (1); +--echo # +connection mdl_con2; +--echo # Check that the above INSERT is blocked. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "insert into t1 values (1)"; +--source include/wait_condition.inc +--echo # Unblock ALTER TABLE and thus INSERT. +set debug_sync= 'now SIGNAL finish'; +--echo # +connection mdl_con1; +--echo # Reap ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +connection default; +--echo # Reap INSERT. +--reap +commit; +--echo # +--echo # **) Now test in which transaction that has SW lock on the table +--echo # against which there is pending SNW lock acquires SR and SW +--echo # locks on this table. +--echo # +begin; +insert into t1 values (1); +--echo # +connection mdl_con1; +--echo # Create pending SNW lock on t1. +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +connection default; +--echo # Wait until ALTER TABLE starts waiting for SNW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # We should still be able to get both SW and SR locks without waiting. +select count(*) from t1; +delete from t1 limit 1; +--echo # Unblock ALTER TABLE. +commit; +--echo # +connection mdl_con1; +--echo # Reap ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +connection default; +--echo # +--echo # 2) Now similar tests for active SNW lock which is being upgraded +--echo # to X lock. +--echo # +--echo # Again we start with case when we are acquiring lock on the +--echo # table which was not used in the transaction before. +begin; +select count(*) from t1; +--echo # +connection mdl_con2; +--echo # Start transaction which will prevent SNW -> X upgrade from +--echo # completing immediately. +begin; +select count(*) from t2; +--echo # +connection mdl_con1; +--echo # Create SNW lock pending upgrade to X on t2. +--echo # Sending: +--send alter table t2 add column c2 int; +--echo # +connection default; +--echo # Wait until ALTER TABLE starts waiting X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "alter table t2 add column c2 int"; +--source include/wait_condition.inc +--echo # Check that attempt to acquire SR lock on t2 causes waiting. +--echo # Sending: +--send select count(*) from t2; +--echo # +connection mdl_con2; +--echo # Check that the above SELECT is blocked. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "select count(*) from t2"; +--source include/wait_condition.inc +--echo # Unblock ALTER TABLE. +commit; +--echo # +connection mdl_con1; +--echo # Reap ALTER TABLE. +--reap +--echo # +connection default; +--echo # Reap SELECT. +--reap +commit; +--echo # Do similar check for SW lock. +begin; +select count(*) from t1; +--echo # +connection mdl_con2; +--echo # Start transaction which will prevent SNW -> X upgrade from +--echo # completing immediately. +begin; +select count(*) from t2; +--echo # +connection mdl_con1; +--echo # Create SNW lock pending upgrade to X on t2. +--echo # Sending: +--send alter table t2 drop column c2; +--echo # +connection default; +--echo # Wait until ALTER TABLE starts waiting X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "alter table t2 drop column c2"; +--source include/wait_condition.inc +--echo # Check that attempt to acquire SW lock on t2 causes waiting. +--echo # Sending: +--send insert into t2 values (1); +--echo # +connection mdl_con2; +--echo # Check that the above INSERT is blocked. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "insert into t2 values (1)"; +--source include/wait_condition.inc +--echo # Unblock ALTER TABLE. +commit; +--echo # +connection mdl_con1; +--echo # Reap ALTER TABLE. +--reap +--echo # +connection default; +--echo # Reap INSERT. +--reap +commit; +--echo # +--echo # Test for the case in which we are acquiring lock on the table +--echo # which is already used in transaction. +--echo # +begin; +select count(*) from t1; +--echo # +connection mdl_con1; +--echo # Create SNW lock pending upgrade to X. +--echo # Sending: +--send alter table t1 add column c2 int; +--echo # +connection default; +--echo # Wait until ALTER TABLE starts waiting X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "alter table t1 add column c2 int"; +--source include/wait_condition.inc +--echo # Check that transaction is still able to acquire SR lock. +select count(*) from t1; +--echo # Waiting trying to acquire SW lock will cause deadlock and +--echo # therefore should cause an error. +--error ER_LOCK_DEADLOCK +delete from t1 limit 1; +--echo # Unblock ALTER TABLE. +commit; +--echo # +connection mdl_con1; +--echo # Reap ALTER TABLE. +--reap +--echo # +connection default; +--echo # +--echo # 3) Check how various locks used within transactional context +--echo # interact with active/pending SNRW lock. +--echo # +--echo # Once again we start with case when we are acquiring lock on +--echo # the table which was not used in the transaction before. +begin; +select count(*) from t1; +--echo # +connection mdl_con1; +lock table t2 write; +--echo # +connection default; +--echo # Attempt to acquire SR should be blocked. It should +--echo # not cause errors as it does not creates deadlock. +--echo # Sending: +--send select count(*) from t2; +--echo # +connection mdl_con1; +--echo # Check that the above SELECT is blocked +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "select count(*) from t2"; +--source include/wait_condition.inc +--echo # Unblock SELECT. +unlock tables; +--echo # +connection default; +--echo # Reap SELECT. +--reap +commit; +--echo # Repeat the same test for SW lock. +begin; +select count(*) from t1; +--echo # +connection mdl_con1; +lock table t2 write; +--echo # +connection default; +--echo # Again attempt to acquire SW should be blocked and should +--echo # not cause any errors. +--echo # Sending: +--send delete from t2 limit 1; +--echo # +connection mdl_con1; +--echo # Check that the above DELETE is blocked +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "delete from t2 limit 1"; +--source include/wait_condition.inc +--echo # Unblock DELETE. +unlock tables; +--echo # +connection default; +--echo # Reap DELETE. +--reap +commit; +--echo # +--echo # Now coverage for the case in which we are acquiring lock on +--echo # the table which is already used in transaction and against +--echo # which there is a pending SNRW lock request. +--echo # +--echo # *) Let us start with case when transaction has only a SR lock. +--echo # +begin; +select count(*) from t1; +--echo # +connection mdl_con1; +--echo # Sending: +--send lock table t1 write; +--echo # +connection default; +--echo # Wait until LOCK TABLE is blocked creating pending request for X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Check that another instance of SR lock is granted without waiting. +select count(*) from t1; +--echo # Attempt to wait for SW lock will lead to deadlock, thus +--echo # the below statement should end with ER_LOCK_DEADLOCK error. +--error ER_LOCK_DEADLOCK +delete from t1 limit 1; +--echo # Unblock LOCK TABLES. +commit; +--echo # +connection mdl_con1; +--echo # Reap LOCK TABLES. +--reap +unlock tables; +--echo # +connection default; +--echo # +--echo # **) Now case when transaction has a SW lock. +--echo # +begin; +delete from t1 limit 1; +--echo # +connection mdl_con1; +--echo # Sending: +--send lock table t1 write; +--echo # +connection default; +--echo # Wait until LOCK TABLE is blocked creating pending request for X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Check that both SR and SW locks are granted without waiting +--echo # and errors. +select count(*) from t1; +insert into t1 values (1, 1); +--echo # Unblock LOCK TABLES. +commit; +--echo # +connection mdl_con1; +--echo # Reap LOCK TABLES. +--reap +unlock tables; +--echo # +connection default; +--echo # +--echo # 4) Check how various locks used within transactional context +--echo # interact with active/pending X lock. +--echo # +--echo # As usual we start with case when we are acquiring lock on +--echo # the table which was not used in the transaction before. +begin; +select count(*) from t1; +--echo # +connection mdl_con2; +--echo # Start transaction which will prevent X lock from going away +--echo # immediately. +begin; +select count(*) from t2; +--echo # +connection mdl_con1; +--echo # Create pending X lock on t2. +--echo # Sending: +--send rename table t2 to t3; +--echo # +connection default; +--echo # Wait until RENAME TABLE starts waiting with pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t2 to t3"; +--source include/wait_condition.inc +--echo # Check that attempt to acquire SR lock on t2 causes waiting. +--echo # Sending: +--send select count(*) from t2; +--echo # +connection mdl_con2; +--echo # Check that the above SELECT is blocked. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "select count(*) from t2"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +commit; +--echo # +connection mdl_con1; +--echo # Reap RENAME TABLE. +--reap +--echo # +connection default; +--echo # Reap SELECT. +--error ER_NO_SUCH_TABLE +--reap +commit; +rename table t3 to t2; +--echo # The same test for SW lock. +begin; +select count(*) from t1; +--echo # +connection mdl_con2; +--echo # Start transaction which will prevent X lock from going away +--echo # immediately. +begin; +select count(*) from t2; +--echo # +connection mdl_con1; +--echo # Create pending X lock on t2. +--echo # Sending: +--send rename table t2 to t3; +--echo # +connection default; +--echo # Wait until RENAME TABLE starts waiting with pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t2 to t3"; +--source include/wait_condition.inc +--echo # Check that attempt to acquire SW lock on t2 causes waiting. +--echo # Sending: +--send delete from t2 limit 1; +--echo # +connection mdl_con2; +--echo # Check that the above DELETE is blocked. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "delete from t2 limit 1"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +commit; +--echo # +connection mdl_con1; +--echo # Reap RENAME TABLE. +--reap +--echo # +connection default; +--echo # Reap DELETE. +--error ER_NO_SUCH_TABLE +--reap +commit; +rename table t3 to t2; +--echo # +--echo # Coverage for the case in which we are acquiring lock on +--echo # the table which is already used in transaction and against +--echo # which there is a pending X lock request. +--echo # +--echo # *) The first case is when transaction has only a SR lock. +--echo # +begin; +select count(*) from t1; +--echo # +connection mdl_con1; +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection default; +--echo # Wait until RENAME TABLE is blocked creating pending request for X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that another instance of SR lock is granted without waiting. +select count(*) from t1; +--echo # Attempt to wait for SW lock will lead to deadlock, thus +--echo # the below statement should end with ER_LOCK_DEADLOCK error. +--error ER_LOCK_DEADLOCK +delete from t1 limit 1; +--echo # Unblock RENAME TABLE. +commit; +--echo # +connection mdl_con1; +--echo # Reap RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +connection default; +--echo # +--echo # **) The second case is when transaction has a SW lock. +--echo # +begin; +delete from t1 limit 1; +--echo # +connection mdl_con1; +--echo # Sending: +--send rename table t1 to t2; +--echo # +connection default; +--echo # Wait until RENAME TABLE is blocked creating pending request for X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table metadata lock" and + info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that both SR and SW locks are granted without waiting +--echo # and errors. +select count(*) from t1; +insert into t1 values (1, 1); +--echo # Unblock RENAME TABLE. +commit; +--echo # +connection mdl_con1; +--echo # Reap RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +connection default; + +--echo # Clean-up. +disconnect mdl_con1; +disconnect mdl_con2; +disconnect mdl_con3; +set debug_sync= 'RESET'; +drop table t1, t2; + +--echo # +--echo # Test coverage for basic deadlock detection in metadata +--echo # locking subsystem. +--echo # +--disable_warnings +drop tables if exists t0, t1, t2, t3, t4, t5; +--enable_warnings +set debug_sync= 'RESET'; + +connect(deadlock_con1,localhost,root,,); +connect(deadlock_con2,localhost,root,,); +connect(deadlock_con3,localhost,root,,); +connection default; +create table t1 (i int); +create table t2 (j int); +create table t3 (k int); +create table t4 (k int); + +--echo # +--echo # Test for the case in which no deadlock occurs. +--echo # + +--echo # +connection deadlock_con1; +begin; +insert into t1 values (1); + +--echo # +connection deadlock_con2; +begin; +insert into t2 values (1); + +--echo # +connection default; +--echo # Send: +--send rename table t2 to t0, t3 to t2, t0 to t3; + +--echo # +connection deadlock_con1; +--echo # Wait until the above RENAME TABLE is blocked because it has to wait +--echo # for 'deadlock_con2' which holds shared metadata lock on 't2'. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "rename table t2 to t0, t3 to t2, t0 to t3"; +--source include/wait_condition.inc +--echo # The below statement should wait for exclusive metadata lock +--echo # on 't2' to go away and should not produce ER_LOCK_DEADLOCK +--echo # as no deadlock is possible in this situation. +--echo # Send: +--send select * from t2; + +--echo # +connection deadlock_con2; +--echo # Wait until the above SELECT * FROM t2 is starts waiting +--echo # for an exclusive metadata lock to go away. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "select * from t2"; +--source include/wait_condition.inc +--echo # +--echo # Unblock RENAME TABLE by releasing shared metadata lock on t2. +commit; + +--echo # +connection default; +--echo # Reap RENAME TABLE. +--reap + +--echo # +connection deadlock_con1; +--echo # Reap SELECT. +--reap + +--echo # +connection default; +--echo # +--echo # Let us check that in the process of waiting for conflicting lock +--echo # on table 't2' to go away transaction in connection 'deadlock_con1' +--echo # has not released metadata lock on table 't1'. +--echo # Send: +--send rename table t1 to t0, t3 to t1, t0 to t3; + +--echo # +connection deadlock_con1; +--echo # Wait until the above RENAME TABLE is blocked because it has to wait +--echo # for 'deadlock_con1' which should still hold shared metadata lock on +--echo # table 't1'. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "rename table t1 to t0, t3 to t1, t0 to t3"; +--source include/wait_condition.inc +--echo # Commit transaction to unblock RENAME TABLE. +commit; + +--echo # +connection default; +--echo # Reap RENAME TABLE. +--reap + +--echo # +--echo # Test for case when deadlock occurs and should be detected immediately. +--echo # + +--echo # +connection deadlock_con1; +begin; +insert into t2 values (2); + +--echo # +connection default; +--echo # Send: +--send rename table t2 to t0, t1 to t2, t0 to t1; + +--echo # +connection deadlock_con1; +--echo # Wait until the above RENAME TABLE is blocked because it has to wait +--echo # for 'deadlock_con1' which holds shared metadata lock on 't2'. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "rename table t2 to t0, t1 to t2, t0 to t1"; +--source include/wait_condition.inc +--echo # +--echo # The below statement should not wait as doing so will cause deadlock. +--echo # Instead it should fail and emit ER_LOCK_DEADLOCK statement and +--echo # transaction should be rolled back. +--error ER_LOCK_DEADLOCK +select * from t1; + +--echo # +connection default; +--echo # Reap RENAME TABLE. +--reap + +--echo # +--echo # Test for the case in which deadlock also occurs but not immediately. +--echo # + +--echo # +connection deadlock_con1; +begin; +insert into t2 values (1); + +--echo # +connection default; +lock table t1 write; + +--echo # +connection deadlock_con1; +--echo # The below SELECT statement should wait for metadata lock +--echo # on table 't1' and should not produce ER_LOCK_DEADLOCK +--echo # immediately as no deadlock is possible at the moment. +--send select * from t1; + +--echo # +connection deadlock_con2; +--echo # Wait until the above SELECT * FROM t1 is starts waiting +--echo # for an UNRW metadata lock to go away. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and info = "select * from t1"; +--source include/wait_condition.inc + +--echo # Send RENAME TABLE statement that will deadlock with the +--echo # SELECT statement and thus should abort the latter. +--send rename table t1 to t0, t2 to t1, t0 to t2; + +--echo # +connection default; +--echo # Wait till above RENAME TABLE is blocked while holding +--echo # pending X lock on t1. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "rename table t1 to t0, t2 to t1, t0 to t2"; +--source include/wait_condition.inc +--echo # Allow the above RENAME TABLE to acquire lock on t1 and +--echo # create pending lock on t2 thus creating deadlock. +unlock tables; + +--echo # +connection deadlock_con1; +--echo # Since the latest RENAME TABLE entered in deadlock with SELECT +--echo # statement the latter should be aborted and emit ER_LOCK_DEADLOCK +--echo # error and transaction should be rolled back. +--echo # Reap SELECT * FROM t1. +--error ER_LOCK_DEADLOCK +--reap + +--echo # +connection deadlock_con2; +--echo # Reap RENAME TABLE ... . +--reap; + +--echo # +connection default; + +drop tables t1, t2, t3, t4; + +--echo # +--echo # Now, test case which shows that deadlock detection empiric +--echo # also takes into account requests for metadata lock upgrade. +--echo # +create table t1 (i int); +insert into t1 values (1); +--echo # Avoid race which occurs when SELECT in 'deadlock_con1' connection +--echo # accesses table before the above INSERT unlocks the table and thus +--echo # its result becomes visible to other connections. +select * from t1; + +--echo # +connection deadlock_con1; +begin; +select * from t1; + +--echo # +connection default; +--echo # Send: +--send alter table t1 add column j int, rename to t2; + +--echo # +connection deadlock_con1; +--echo # Wait until the above ALTER TABLE ... RENAME acquires exclusive +--echo # metadata lock on 't2' and starts waiting for connection +--echo # 'deadlock_con1' which holds shared lock on 't1'. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 add column j int, rename to t2"; +--source include/wait_condition.inc + +--echo # The below statement should not wait as it will cause deadlock. +--echo # An appropriate error should be reported instead and transaction +--echo # should be rolled back. +--error ER_LOCK_DEADLOCK +select * from t2; + +--echo # +connection default; +--echo # Reap ALTER TABLE ... RENAME. +--reap + +drop table t2; + +--echo # +--echo # Test that in situation when MDL subsystem detects a deadlock +--echo # but it turns out that it can be resolved by backing-off locks +--echo # acquired by one of participating transactions (which is +--echo # possible when one of transactions consists only of currently +--echo # executed statement, e.g. in autocommit mode) no error is +--echo # reported. +--echo # +create table t1 (i int); +create table t2 (j int); +--echo # Ensure that the below SELECT stops once it has acquired metadata +--echo # lock on table 't2'. +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send select * from t2, t1 + +--echo # +connection deadlock_con1; +--echo # Wait till SELECT acquires MDL on 't2' and starts waiting for signal. +set debug_sync= 'now WAIT_FOR locked'; +--echo # Sending: +--send lock tables t1 write, t2 write + +--echo # +connection deadlock_con2; +--echo # Wait until LOCK TABLES acquires SNRW lock on 't1' and is blocked +--echo # while trying to acquire SNRW lock on 't1'. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "lock tables t1 write, t2 write"; +--source include/wait_condition.inc +--echo # Resume SELECT execution, this should eventually unblock LOCK TABLES. +set debug_sync= 'now SIGNAL finish'; + +--echo # +connection deadlock_con1; +--echo # Reaping LOCK TABLES. +--reap +unlock tables; + +--echo # +connection default; +--echo # Reaping SELECT. It succeed and not report ER_LOCK_DEADLOCK error. +--reap + +drop tables t1, t2; + +--echo # +--echo # Test coverage for situation in which a race has happened +--echo # during deadlock detection process which led to unwarranted +--echo # ER_LOCK_DEADLOCK error. +--echo # +create table t1 (i int); + +--echo # Ensure that ALTER waits once it has acquired SNW lock. +set debug_sync='alter_table_copy_after_lock_upgrade SIGNAL parked1 WAIT_FOR go1'; +--echo # Sending: +--send alter table t1 add column j int + +--echo # +connection deadlock_con1; +--echo # Wait till ALTER acquires SNW lock and stops. +set debug_sync='now WAIT_FOR parked1'; +--echo # Ensure that INSERT is paused once it detects that there is +--echo # a conflicting metadata lock so it has to wait, but before +--echo # deadlock detection is run. +set debug_sync='mdl_acquire_lock_wait SIGNAL parked2 WAIT_FOR go2'; +--echo # Sending: +--send insert into t1 values () + +--echo # +connection deadlock_con2; +--echo # Wait till INSERT is paused. +set debug_sync='now WAIT_FOR parked2'; +--echo # Resume ALTER execution. Eventually it will release its +--echo # metadata lock and INSERT's request for SW lock will be +--echo # satisified. +set debug_sync='now SIGNAL go1'; + +--echo # +connection default; +--echo # Reaping ALTER TABLE. +--reap +--echo # Add a new request for SNW lock to waiting graph. +--echo # Sending: +--send alter table t1 drop column j + +--echo # +connection deadlock_con2; +--echo # Wait until ALTER is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 drop column j"; +--source include/wait_condition.inc +--echo # Resume INSERT so it can start deadlock detection. +--echo # +--echo # At this point there is a discrepancy between the fact that INSERT's +--echo # SW lock is already satisfied, but INSERT's connection is still +--echo # marked as waiting for it. Looking for a loop in waiters graph +--echo # without additional checks has detected a deadlock (INSERT waits +--echo # for SW lock; which is not granted because of pending SNW lock from +--echo # ALTER; which waits for active SW lock from INSERT). Since requests +--echo # for SW and SNW locks have same weight ALTER was selected as a victim +--echo # and ended with ER_LOCK_DEADLOCK error. +set debug_sync='now SIGNAL go2'; + +--echo # +connection deadlock_con1; +--echo # Reaping INSERT. +--reap + +--echo # +connection default; +--echo # Reaping ALTER. It should succeed and not produce ER_LOCK_DEADLOCK. +--reap + +drop table t1; + +set debug_sync= 'RESET'; + +disconnect deadlock_con1; +disconnect deadlock_con2; +disconnect deadlock_con3; + + +--echo # +--echo # Test for a scenario in which FLUSH TABLES <list> WITH READ LOCK +--echo # used to erroneously release 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); + +connection con2; +set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go'; + +--echo # The below FLUSH TABLES <list> WITH READ LOCK should acquire +--echo # SNW locks on t1 and t2, open table t1 and block on the debug +--echo # sync point. +--echo # Sending: +send flush tables t1, t2 with read lock; + +connection con1; +--echo # Wait till FLUSH TABLES <list> WITH READ LOCK stops. +set debug_sync='now WAIT_FOR parked'; + +# Simple flush tables should not block +flush tables; + +--echo # Start a statement which will flush all tables and thus +--echo # invalidate table t1 open by FLUSH TABLES <list> WITH READ LOCK. +--echo # Sending: +send flush tables t1; + +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 metadata lock" and + info = "flush tables t1"; +--source include/wait_condition.inc + +--echo # Resume FLUSH TABLES <list> 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 would result in assertion failures. +set debug_sync='now SIGNAL go'; + +connection con2; +--echo # Reap FLUSH TABLES <list> WITH READ LOCK. +reap; +unlock tables; + +connection con1; +--echo # Reap FLUSH TABLES. +reap; + +--echo # Clean-up. +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". +--echo # +--disable_warnings +drop tables if exists t1, t2, t3, t4, t5; +--enable_warnings +--echo # Let us simulate scenario in which we open some tables from extended +--echo # part of prelocking set but then encounter conflicting metadata lock, +--echo # so have to back-off and wait for it to go away. +connect (con1root,localhost,root,,test,,); +connect (con2root,localhost,root,,test,,); +connection default; +create table t1 (i int); +create table t2 (j int); +create table t3 (k int); +create table t4 (l int); +create trigger t1_bi before insert on t1 for each row + insert into t2 values (new.i); +create trigger t2_bi before insert on t2 for each row + insert into t3 values (new.j); +--echo # +connection con1root; +lock tables t4 read; +--echo # +connection con2root; +--echo # Send : +--send rename table t3 to t5, t4 to t3; +--echo # +connection default; +--echo # Wait until the above RENAME TABLE adds pending requests for exclusive +--echo # metadata lock on its tables and blocks due to 't4' being used by LOCK +--echo # TABLES. +let $wait_condition= select count(*)= 1 from information_schema.processlist + where state= 'Waiting for table metadata lock' and + info='rename table t3 to t5, t4 to t3'; +--source include/wait_condition.inc +--echo # Send : +--send insert into t1 values (1); +--echo # +connection con1root; +--echo # Wait until INSERT statement waits due to encountering pending +--echo # exclusive metadata lock on 't3'. +let $wait_condition= select count(*)= 1 from information_schema.processlist + where state= 'Waiting for table metadata lock' and + info='insert into t1 values (1)'; +--source include/wait_condition.inc +unlock tables; +--echo # +connection con2root; +--echo # Reap RENAME TABLE. +--reap +--echo # +connection default; +--echo # Reap INSERT. +--reap +--echo # Clean-up. +disconnect con1root; +disconnect con2root; +drop tables t1, t2, t3, t5; + + +--echo # +--echo # Bug#42546 - Backup: RESTORE fails, thinking it finds an existing table +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +set @save_log_output=@@global.log_output; +set global log_output=file; + +connect(con2, localhost, root,,); + +--echo # +--echo # Test 1: CREATE TABLE +--echo # + +connection con2; +--echo # Start insert on the not-yet existing table +--echo # Wait after taking the MDL lock +SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +--send INSERT INTO t1 VALUES(1,"def") + +connection default; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Now INSERT has a MDL on the non-existent table t1. + +--echo # +--echo # Continue the INSERT once CREATE waits for exclusive lock +SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish'; +--echo # Try to create that table. +--send CREATE TABLE t1 (c1 INT, c2 VARCHAR(100), KEY(c1)) + +--echo # Insert fails +connection con2; +--error ER_NO_SUCH_TABLE +--reap + +connection default; +--reap; +SET DEBUG_SYNC= 'RESET'; +SHOW TABLES; + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +--echo # +--echo # Test 2: CREATE TABLE LIKE +--echo # + +CREATE TABLE t2 (c1 INT, c2 VARCHAR(100), KEY(c1)); + +connection con2; +--echo # Start insert on the not-yet existing table +--echo # Wait after taking the MDL +SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +--send INSERT INTO t1 VALUES(1,"def") + +connection default; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Now INSERT has a MDL on the non-existent table t1. + +--echo # +--echo # Continue the INSERT once CREATE waits for exclusive lock +SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish'; +--echo # Try to create that table. +--send CREATE TABLE t1 LIKE t2 + +--echo # Insert fails +connection con2; +--error ER_NO_SUCH_TABLE +--reap + +connection default; +--reap +SET DEBUG_SYNC= 'RESET'; +SHOW TABLES; + +DROP TABLE t2; +disconnect con2; +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +set global log_output=@save_log_output; + + +--echo # +--echo # Bug #46044 "MDL deadlock on LOCK TABLE + CREATE TABLE HIGH_PRIORITY +--echo # FOR UPDATE" +--echo # +--disable_warnings +drop tables if exists t1, t2; +--enable_warnings +connect (con46044, localhost, root,,); +connect (con46044_2, localhost, root,,); +connect (con46044_3, localhost, root,,); +connection default; +create table t1 (i int); +insert into t1 values(1); + +--echo # Let us check that we won't deadlock if during filling +--echo # of I_S table we encounter conflicting metadata lock +--echo # which owner is in its turn waiting for our connection. +lock tables t1 read; + +connection con46044_2; +--echo # Sending: +--send update t1 set i = 2 + +connection con46044; + +--echo # Waiting until UPDATE t1 SET ... is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "update t1 set i = 2"; +--source include/wait_condition.inc + +--echo # Sending: +--send create table t2 select * from t1; + +connection default; +--echo # Waiting until CREATE TABLE ... SELECT ... is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "create table t2 select * from t1"; +--source include/wait_condition.inc + +--echo # First let us check that SHOW FIELDS/DESCRIBE doesn't +--echo # gets blocked and emits and error. +--error ER_WARN_I_S_SKIPPED_TABLE +show fields from t2; + +--echo # Now test for I_S query which reads only .FRMs. +--echo # +--echo # Query below should only emit a warning. +select column_name from information_schema.columns + where table_schema='test' and table_name='t2'; + +--echo # Finally, test for I_S query which does full-blown table open. +--echo # +--echo # Query below should not be blocked. Warning message should be +--echo # stored in the 'table_comment' column. +select table_name, table_type, auto_increment, table_comment + from information_schema.tables where table_schema='test' and table_name='t2'; + +connection default; +unlock tables; + +connection con46044; +--echo # Reaping CREATE TABLE ... SELECT ... . +--reap +drop table t2; + +connection con46044_2; +--echo # Reaping UPDATE t1 statement +--reap + +--echo # +--echo # Let us also check that queries to I_S wait for conflicting metadata +--echo # locks to go away instead of skipping table with a warning in cases +--echo # when deadlock is not possible. This is a nice thing from compatibility +--echo # and ease of use points of view. +--echo # +--echo # We check same three queries to I_S in this new situation. + +connection con46044_2; +lock tables t1 read; + +connection con46044_3; +--echo # Sending: +send update t1 set i = 3; + +connection con46044; + +--echo # Waiting until UPDATE t1 SET ... is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "update t1 set i = 3"; +--source include/wait_condition.inc + +--echo # Sending: +--send create table t2 select * from t1; + +connection default; +--echo # Waiting until CREATE TABLE ... SELECT ... is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "create table t2 select * from t1"; +--source include/wait_condition.inc + +--echo # Let us check that SHOW FIELDS/DESCRIBE gets blocked. +--echo # Sending: +--send show fields from t2; + +connection con46044_2; +--echo # Wait until SHOW FIELDS gets blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "show fields from t2"; +--source include/wait_condition.inc + +unlock tables; + +connection con46044; +--echo # Reaping CREATE TABLE ... SELECT ... . +--reap + +connection default; +--echo # Reaping SHOW FIELDS ... +--reap +drop table t2; + +connection con46044_3; +--echo # Reaping UPDATE t1 statement +--reap + +connection con46044_2; +lock tables t1 read; + +connection con46044_3; +--echo # Sending: +--send update t1 set i = 4 + +connection con46044; + +--echo # Waiting until UPDATE t1 SET ... is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "update t1 set i = 4"; +--source include/wait_condition.inc + +--echo # Sending: +--send create table t2 select * from t1; + +connection default; +--echo # Waiting until CREATE TABLE ... SELECT ... is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "create table t2 select * from t1"; +--source include/wait_condition.inc + +--echo # Check that I_S query which reads only .FRMs gets blocked. +--echo # Sending: +--send select column_name from information_schema.columns where table_schema='test' and table_name='t2'; + +connection con46044_2; +--echo # Wait until SELECT COLUMN_NAME FROM I_S.COLUMNS gets blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info like "select column_name from information_schema.columns%"; +--source include/wait_condition.inc + +unlock tables; + +connection con46044; +--echo # Reaping CREATE TABLE ... SELECT ... . +--reap + +connection default; +--echo # Reaping SELECT COLUMN_NAME FROM I_S.COLUMNS +--reap +drop table t2; + +connection con46044_3; +--echo # Reaping UPDATE t1 statement +--reap + +connection con46044_2; +lock tables t1 read; + +connection con46044_3; +--echo # Sending: +--send update t1 set i = 5 + +connection con46044; + +--echo # Waiting until UPDATE t1 SET ... is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "update t1 set i = 5"; +--source include/wait_condition.inc + +--echo # Sending: +--send create table t2 select * from t1; + +connection default; +--echo # Waiting until CREATE TABLE ... SELECT ... is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table level lock" and + info = "create table t2 select * from t1"; +--source include/wait_condition.inc + +--echo # Finally, check that I_S query which does full-blown table open +--echo # also gets blocked. +--echo # Sending: +--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; + +connection con46044_2; +--echo # Wait until SELECT ... FROM I_S.TABLES gets blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info like "select table_name, table_type, auto_increment, table_comment from information_schema.tables%"; +--source include/wait_condition.inc + +unlock tables; + +connection con46044; +--echo # Reaping CREATE TABLE ... SELECT ... . +--reap + +connection default; +--echo # Reaping SELECT ... FROM I_S.TABLES +--reap +drop table t2; + +connection con46044_3; +--echo # Reaping UPDATE t1 statement +--reap + +connection default; +--echo # Clean-up. +disconnect con46044; +disconnect con46044_2; +disconnect con46044_3; +drop table t1; + + +--echo # +--echo # Test for bug #46273 "MySQL 5.4.4 new MDL: Bug#989 is not fully fixed +--echo # in case of ALTER". +--echo # +--disable_warnings +drop table if exists t1; +--enable_warnings +set debug_sync= 'RESET'; +connect (con46273,localhost,root,,test,,); +connection default; +create table t1 (c1 int primary key, c2 int, c3 int); +insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0); + +begin; +select * from t1 where c2 = 3; + +--echo # +connection con46273; +set debug_sync='alter_table_copy_after_lock_upgrade SIGNAL alter_table_locked WAIT_FOR alter_go'; +--send alter table t1 add column e int, rename to t2; + +--echo # +connection default; +set debug_sync='now WAIT_FOR alter_table_locked'; +set debug_sync='mdl_acquire_lock_wait SIGNAL alter_go'; +--echo # The below statement should get ER_LOCK_DEADLOCK error +--echo # (i.e. it should not allow ALTER to proceed, and then +--echo # fail due to 't1' changing its name to 't2'). +--error ER_LOCK_DEADLOCK +update t1 set c3=c3+1 where c2 = 3; + +--echo # +connection con46273; +--echo # Reap ALTER TABLE. +--reap + +--echo # +connection default; +disconnect con46273; +--echo # Clean-up. +set debug_sync= 'RESET'; +drop table t2; + + +--echo # +--echo # Test for bug #46673 "Deadlock between FLUSH TABLES WITH READ LOCK +--echo # and DML". +--echo # +--disable_warnings +drop tables if exists t1; +--enable_warnings +connect (con46673, localhost, root,,); +connection default; +create table t1 (i int); + +connection con46673; +begin; +insert into t1 values (1); + +connection default; +--echo # Statement below should not get blocked. And if after some +--echo # changes to code it is there should not be a deadlock between +--echo # it and transaction from connection 'con46673'. +flush tables with read lock; +unlock tables; + +connection con46673; +delete from t1 where i = 1; +commit; + +connection default; +--echo # Clean-up +disconnect con46673; +drop table t1; + + +--echo # +--echo # Bug#48210 FLUSH TABLES WITH READ LOCK deadlocks +--echo # against concurrent CREATE PROCEDURE +--echo # + +connect (con2, localhost, root); + +--echo # Test 1: CREATE PROCEDURE + +connection default; +--echo # Start CREATE PROCEDURE and open mysql.proc +SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait'; +--send CREATE PROCEDURE p1() SELECT 1 + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; +--echo # Check that FLUSH must wait to get the GRL +--echo # and let CREATE PROCEDURE continue +SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait'; +--send FLUSH TABLES WITH READ LOCK + +connection default; +--reap + +connection con2; +--reap +UNLOCK TABLES; + +connection default; +SET DEBUG_SYNC= 'RESET'; + +--echo # Test 2: DROP PROCEDURE + +connection default; +--echo # Start DROP PROCEDURE and open tables +SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait'; +--send DROP PROCEDURE p1 + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; +--echo # Check that FLUSH must wait to get the GRL +--echo # and let DROP PROCEDURE continue +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; +SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait'; +--send FLUSH TABLES WITH READ LOCK + +connection default; +--echo # Once FLUSH TABLES WITH READ LOCK starts waiting +--echo # DROP PROCEDURE will be waked up and will drop +--echo # procedure. Global read lock will be granted after +--echo # this statement ends. +--echo # +--echo # Reaping DROP PROCEDURE. +--reap + +connection con2; +--echo # Reaping FTWRL. +--reap +UNLOCK TABLES; +connection default; +SET DEBUG_SYNC= 'RESET'; + +--echo # +--echo # UPDATE should wait for FTWRL with non transactional table second +--echo # + +create table t1 (a int) engine=myisam; +create table t2 (a int) engine=innodb; +insert into t1 values (1); +insert into t2 values (1); + +SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait execute 2'; +--send update t1,t2 set t1.a=2,t2.a=3 + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; +SET DEBUG_SYNC= 'now SIGNAL grlwait'; +SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; +SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait'; +FLUSH TABLES WITH READ LOCK; + +connection default; +--echo # Reaping UPDATE +--reap + +connection con2; +UNLOCK TABLES; + +connection default; +SET DEBUG_SYNC= 'RESET'; + +# This will cause a wait as we first get lock for innodb table t2 but FTWRL +# will cause lock for t1 to wait + +SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait execute 2'; +--send update t2,t1 set t1.a=2,t2.a=3 + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; +SET DEBUG_SYNC= 'now SIGNAL grlwait'; +SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; +SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait'; +FLUSH TABLES WITH READ LOCK; + +let $wait_condition= SELECT COUNT(*)=1 FROM information_schema.metadata_lock_info; +--source include/wait_condition.inc +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; + +unlock tables; + +connection default; +--echo # Reaping UPDATE +--reap +SET DEBUG_SYNC= 'RESET'; +drop table t1,t2; +disconnect con2; + +--echo # +--echo # Bug#50786 Assertion `thd->mdl_context.trans_sentinel() == __null' +--echo # failed in open_ltable() +--echo # + +--echo # Supress warnings written to the log file +call mtr.add_suppression("Wait on a lock was aborted due to a pending exclusive lock"); + +connect (con1,localhost,root); +connect (con2,localhost,root); +connect (con3,localhost,root); +connection default; + +CREATE TABLE t1 (i INT); +CREATE TABLE t2 (i INT); + +SET @old_general_log= @@global.general_log; +SET @@global.general_log= 1; + +SET @old_log_output= @@global.log_output; +SET @@global.log_output= 'TABLE'; + +SET @old_sql_log_off= @@session.sql_log_off; +SET @@session.sql_log_off= 1; + +--echo # connection: con1 +connection con1; +HANDLER t1 OPEN; + +--echo # connection: con3 +connection con3; +SET @@session.sql_log_off= 1; + +--echo # connection: con2 +connection con2; +SET DEBUG_SYNC= 'thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go'; + +# The below statement will block on the debug sync point +# after it gets write lock on mysql.general_log table. +--echo # Sending: +--send SELECT 1 + +--echo # connection: con3 +connection con3; +SET DEBUG_SYNC= 'now WAIT_FOR parked'; + +--echo # connection: con1 +connection con1; +# This statement will block in open_ltable() when +# trying to write into mysql.general_log. +--echo # Sending: +--send SELECT 1 + +--echo # connection: con3 +connection con3; +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table level lock" and info = "SELECT 1"; +--source include/wait_condition.inc +# The ALTER below will try to abort the statement in connection con1, +# since the latter waits on a table-level lock while having a HANDLER +# open. This will cause mysql_lock_tables() in con1 fail which before +# triggered the assert. +ALTER TABLE t1 ADD COLUMN j INT; + +--echo # connection: default +connection default; +SET DEBUG_SYNC= 'now SIGNAL go'; + +--echo # connection: con1 +connection con1; +--echo # Reaping SELECT 1 +--reap +HANDLER t1 CLOSE; + +--echo # connection: con2 +connection con2; +--echo # Reaping SELECT 1 +--reap + +--echo # connection: default +connection default; +DROP TABLE t1, t2; +SET DEBUG_SYNC= 'RESET'; +disconnect con1; +disconnect con2; +disconnect con3; +SET @@global.general_log= @old_general_log; +SET @@global.log_output= @old_log_output; +SET @@session.sql_log_off= @old_sql_log_off; + + +--echo # +--echo # Additional coverage for bug #50913 "Deadlock between +--echo # open_and_lock_tables_derived and MDL". The main test +--echo # case is in lock_multi.test +--echo # +--disable_warnings +drop table if exists t1; +--enable_warnings +set debug_sync= 'RESET'; +connect (con50913_1,localhost,root); +connect (con50913_2,localhost,root); +connection default; +create table t1 (i int) engine=InnoDB; + +connection con50913_1; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL parked WAIT_FOR go'; +--echo # Sending: +--send alter table t1 add column j int, ALGORITHM=COPY + +connection default; +--echo # Wait until ALTER TABLE gets blocked on a sync point after +--echo # acquiring thr_lock.c lock. +set debug_sync= 'now WAIT_FOR parked'; +--echo # The below statement should wait on MDL lock and not deadlock on +--echo # thr_lock.c lock. +--echo # Sending: +--send truncate table t1 + +connection con50913_2; +--echo # Wait until TRUNCATE TABLE is blocked on MDL lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "truncate table t1"; +--source include/wait_condition.inc +--echo # Unblock ALTER TABLE. +set debug_sync= 'now SIGNAL go'; + +connection con50913_1; +--echo # Reaping ALTER TABLE. +--reap + +connection default; +--echo # Reaping TRUNCATE TABLE. +--reap +disconnect con50913_1; +disconnect con50913_2; +set debug_sync= 'RESET'; +drop table t1; + + +--echo # +--echo # Test for bug #50998 "Deadlock in MDL code during test +--echo # rqg_mdl_stability". +--echo # Also provides coverage for the case when addition of +--echo # waiting statement adds several loops in the waiters +--echo # graph and therefore several searches for deadlock +--echo # should be performed. +--disable_warnings +drop table if exists t1; +--enable_warnings +set debug_sync= 'RESET'; +connect (con1,localhost,root); +connect (con2,localhost,root); +connect (con3,localhost,root); +connection default; +create table t1 (i int); + +connection con1; +begin; +select * from t1; + +connection con2; +begin; +select * from t1; + +connection default; +--echo # Start ALTER TABLE which will acquire SNW lock and +--echo # table lock and get blocked on sync point. +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL parked WAIT_FOR go'; +--echo # Sending: +--send alter table t1 add column j int + +connection con1; +--echo # Wait until ALTER TABLE gets blocked on a sync point. +set debug_sync= 'now WAIT_FOR parked'; +--echo # Sending: +--send insert into t1 values (1) + +connection con2; +--echo # Sending: +--send insert into t1 values (1) + +connection con3; +--echo # Wait until both 'con1' and 'con2' are blocked trying to acquire +--echo # SW lock on the table. +let $wait_condition= + select count(*) = 2 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "insert into t1 values (1)"; +--source include/wait_condition.inc +--echo # Unblock ALTER TABLE. Since it will try to upgrade SNW to X lock +--echo # deadlock with two loops in waiting graph will occur. Both loops +--echo # should be found and DML statements in both 'con1' and 'con2' +--echo # should be aborted with ER_LOCK_DEADLOCK errors. +set debug_sync= 'now SIGNAL go'; + +connection con1; +--echo # Reaping INSERT. It should end with ER_LOCK_DEADLOCK error and +--echo # not wait indefinitely (as it happened before the bugfix). +--error ER_LOCK_DEADLOCK +--reap +commit; + +connection con2; +--echo # Reaping INSERT. +--error ER_LOCK_DEADLOCK +--reap +commit; + +connection default; +--echo # Reap ALTER TABLE. +--reap + +disconnect con1; +disconnect con2; +disconnect con3; +connection default; +set debug_sync= 'RESET'; +drop table t1; + +--echo # +--echo # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE +--echo # +--echo # Ensure that a acquired lock is not given up due to a conflict. +--echo # + +connect (con1,localhost,root,,test,,); +connect (con2,localhost,root,,test,,); +connect (con3,localhost,root,,test,,); + +connection default; + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2),(3); + +connection con1; +LOCK TABLES t1 WRITE; +SET debug_sync='upgrade_lock_for_truncate SIGNAL parked_truncate WAIT_FOR go_truncate'; +send TRUNCATE TABLE t1; + +connection default; +SET debug_sync='now WAIT_FOR parked_truncate'; + +connection con2; +SET debug_sync='after_open_table_ignore_flush SIGNAL parked_show WAIT_FOR go_show'; +send SHOW FIELDS FROM t1; + +connection default; +SET debug_sync='now WAIT_FOR parked_show'; + +connection con3; +SET debug_sync='after_flush_unlock SIGNAL parked_flush WAIT_FOR go_flush'; +send FLUSH TABLES t1; + +connection default; +SET debug_sync='now WAIT_FOR parked_flush'; +SET debug_sync='now SIGNAL go_truncate'; +--echo # Ensure that truncate waits for a exclusive lock +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for table metadata lock' AND info='TRUNCATE TABLE t1'; +--source include/wait_condition.inc +SET debug_sync= 'now SIGNAL go_show'; + +connection con1; +--echo # Reaping... +reap; +UNLOCK TABLES; + +connection con2; +--echo # Reaping... +reap; + +connection default; +SET debug_sync= 'now SIGNAL go_flush'; + +connection con3; +--echo # Reaping... +reap; + +disconnect con1; +disconnect con2; +disconnect con3; + +connection default; +SET debug_sync= 'RESET'; +DROP TABLE t1; + + +--echo # +--echo # Bug#52856 concurrent show columns or show full columns causes a crash!!! +--echo # +CREATE TABLE t1(a CHAR(255)); + +connect(con1, localhost, root); +SET DEBUG_SYNC= "get_schema_column SIGNAL waiting WAIT_FOR completed"; +--send SHOW FULL COLUMNS FROM t1 + +connection default; +SET DEBUG_SYNC= "now WAIT_FOR waiting"; +--replace_column 8 # +SHOW FULL COLUMNS FROM t1; +SET DEBUG_SYNC= "now SIGNAL completed"; +--replace_column 8 # +connection con1; +--reap +connection default; +DROP TABLE t1; +disconnect con1; + + +--echo # +--echo # Tests for schema-scope locks +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS db1; +DROP DATABASE IF EXISTS db2; +--enable_warnings + +connect (con2, localhost, root); +connect (con3, localhost, root); + +--echo # Test 1: +--echo # CREATE DATABASE blocks database DDL on the same database, but +--echo # not database DDL on different databases. Tests X vs X lock. +--echo # + +connection default; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send CREATE DATABASE db1 + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send CREATE DATABASE db1 + +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for schema metadata lock' AND info='CREATE DATABASE db1'; +--source include/wait_condition.inc +# This should not block. +CREATE DATABASE db2; +ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; +DROP DATABASE db2; +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +connection default; +--echo # Reaping: CREATE DATABASE db1 +--reap + +connection con2; +--echo # Reaping: CREATE DATABASE db1 +--error ER_DB_CREATE_EXISTS +--reap + +--echo # Test 2: +--echo # ALTER DATABASE blocks database DDL on the same database, but +--echo # not database DDL on different databases. Tests X vs X lock. +--echo # + +connection default; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 + +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for schema metadata lock' + AND info='ALTER DATABASE db1 DEFAULT CHARACTER SET utf8'; +--source include/wait_condition.inc +# This should not block. +CREATE DATABASE db2; +ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; +DROP DATABASE db2; +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +connection default; +--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 +--reap + +connection con2; +--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 +--reap + +connection default; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should also block. +--send DROP DATABASE db1 + +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for schema metadata lock' AND info='DROP DATABASE db1'; +--source include/wait_condition.inc +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +connection default; +--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 +--reap + +connection con2; +--echo # Reaping: DROP DATABASE db1 +--reap +# Recreate the database +CREATE DATABASE db1; + +--echo # Test 3: +--echo # Two ALTER..UPGRADE of the same database are mutually exclusive, but +--echo # two ALTER..UPGRADE of different databases are not. Tests X vs X lock. +--echo # + +let $MYSQLD_DATADIR= `select @@datadir`; +# Manually make a 5.0 database from the template +--mkdir $MYSQLD_DATADIR/a-b-c +--copy_file $MYSQLD_DATADIR/db1/db.opt $MYSQLD_DATADIR/a-b-c/db.opt +--mkdir $MYSQLD_DATADIR/a-b-c-d +--copy_file $MYSQLD_DATADIR/db1/db.opt $MYSQLD_DATADIR/a-b-c-d/db.opt + +connection default; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME + +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for schema metadata lock' + AND info='ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME'; +--source include/wait_condition.inc +# This should not block. +ALTER DATABASE `#mysql50#a-b-c-d` UPGRADE DATA DIRECTORY NAME; +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +connection default; +--echo # Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME +--reap + +connection con2; +--echo # Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME +--error ER_BAD_DB_ERROR +--reap +DROP DATABASE `a-b-c`; +DROP DATABASE `a-b-c-d`; + +--echo # Test 4: +--echo # DROP DATABASE blocks database DDL on the same database, but +--echo # not database DDL on different databases. Tests X vs X lock. +--echo # + +connection default; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send DROP DATABASE db1 + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send DROP DATABASE db1 + +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for schema metadata lock' AND info='DROP DATABASE db1'; +--source include/wait_condition.inc +# This should not block. +CREATE DATABASE db2; +ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; +DROP DATABASE db2; +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +connection default; +--echo # Reaping: DROP DATABASE db1 +--reap + +connection con2; +--echo # Reaping: DROP DATABASE db1 +--error ER_DB_DROP_EXISTS +--reap + +connection default; +CREATE DATABASE db1; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send DROP DATABASE db1 + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should also block. +--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 + +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for schema metadata lock' + AND info='ALTER DATABASE db1 DEFAULT CHARACTER SET utf8'; +--source include/wait_condition.inc +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +connection default; +--echo # Reaping: DROP DATABASE db1 +--reap + +connection con2; +--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 +# Error 1 is from ALTER DATABASE when the database does not exist. +# Listing the error twice to prevent result diffences based on filename. +--error 1,1 +--reap + + +--echo # Test 5: +--echo # Locked database name prevents CREATE of tables in that database. +--echo # Tests X vs IX lock. +--echo # + +connection default; +CREATE DATABASE db1; +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send DROP DATABASE db1 + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send CREATE TABLE db1.t1 (a INT) + +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for schema metadata lock' AND + info='CREATE TABLE db1.t1 (a INT)'; +--source include/wait_condition.inc +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +connection default; +--echo # Reaping: DROP DATABASE db1 +--reap + +connection con2; +--echo # Reaping: CREATE TABLE db1.t1 (a INT) +--error ER_BAD_DB_ERROR +--reap + +--echo # Test 6: +--echo # Locked database name prevents RENAME of tables to/from that database. +--echo # Tests X vs IX lock. +--echo # + +connection default; +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT); +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send DROP DATABASE db1 + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send RENAME TABLE db1.t1 TO test.t1 + +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for schema metadata lock' AND + info='RENAME TABLE db1.t1 TO test.t1'; +--source include/wait_condition.inc +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +connection default; +--echo # Reaping: DROP DATABASE db1 +--reap + +connection con2; +--echo # Reaping: RENAME TABLE db1.t1 TO test.t1 +--error ER_NO_SUCH_TABLE +--reap + +connection default; +CREATE DATABASE db1; +CREATE TABLE test.t2 (a INT); +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send DROP DATABASE db1 + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send RENAME TABLE test.t2 TO db1.t2 + +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for schema metadata lock' AND + info='RENAME TABLE test.t2 TO db1.t2'; +--source include/wait_condition.inc +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +connection default; +--echo # Reaping: DROP DATABASE db1 +--reap + +connection con2; +--echo # Reaping: RENAME TABLE test.t2 TO db1.t2 +# Error 7 is from RENAME TABLE where the target database does not exist. +# Listing the error twice to prevent result diffences based on filename. +--error 7, 7 +--reap +DROP TABLE test.t2; + + +--echo # Test 7: +--echo # Locked database name prevents DROP of tables in that database. +--echo # Tests X vs IX lock. +--echo # + +connection default; +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT); +SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; +--echo # Sending: +--send DROP DATABASE db1 + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +--echo # Sending: +# This should block. +--send DROP TABLE db1.t1 + +connection con3; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for schema metadata lock' AND info='DROP TABLE db1.t1'; +--source include/wait_condition.inc +SET DEBUG_SYNC= 'now SIGNAL blocked'; + +connection default; +--echo # Reaping: DROP DATABASE db1 +--reap + +connection con2; +--echo # Reaping: DROP TABLE db1.t1 +--error ER_BAD_TABLE_ERROR +--reap + +connection default; +disconnect con2; +disconnect con3; +SET DEBUG_SYNC= 'RESET'; + +--echo # +--echo # End of tests for schema-scope locks +--echo # + +--echo # +--echo # Tests of granted global S lock (FLUSH TABLE WITH READ LOCK) +--echo # + +CREATE DATABASE db1; +CREATE TABLE db1.t1(a INT); +connect(con2, localhost, root); +connect(con3, localhost, root); + +connection default; +FLUSH TABLE WITH READ LOCK; + +connection con2; +# IX global lock should block +--send CREATE TABLE db1.t2(a INT) + +connection default; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for backup lock' + AND info='CREATE TABLE db1.t2(a INT)'; +--source include/wait_condition.inc +UNLOCK TABLES; + +connection con2; +--echo # Reaping CREATE TABLE db1.t2(a INT) +--reap + +connection default; +FLUSH TABLE WITH READ LOCK; + +connection con2; +# X global lock should block +--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 + +connection default; +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for backup lock' + AND info='ALTER DATABASE db1 DEFAULT CHARACTER SET utf8'; +--source include/wait_condition.inc +UNLOCK TABLES; + +connection con2; +--echo # Reaping ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 +--reap + +connection default; +FLUSH TABLE WITH READ LOCK; + +connection con2; +# S global lock should not block +FLUSH TABLE WITH READ LOCK; +UNLOCK TABLES; + +connection default; +UNLOCK TABLES; +DROP DATABASE db1; +disconnect con2; +disconnect con3; + + +--echo # +--echo # Bug#56292 Deadlock with ALTER TABLE and MERGE tables +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1, t2, m1; +--enable_warnings + +CREATE TABLE t1(a INT) engine=MyISAM; +CREATE TABLE t2(a INT) engine=MyISAM; +CREATE TABLE m1(a INT) engine=MERGE UNION=(t1, t2); + +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (3), (4); + +connect(con1, localhost, root); +connect(con2, localhost, root); +connect(con3, localhost, root); + +connection con1; +--echo # We need EXECUTE 2 since ALTER TABLE does SU => SNW => X and we want +--echo # to stop at the second upgrade. +SET DEBUG_SYNC= 'mdl_upgrade_lock SIGNAL upgrade WAIT_FOR continue EXECUTE 2'; +--echo # Sending: +--send ALTER TABLE m1 engine=MERGE UNION=(t2, t1) + +connection con2; +--echo # Waiting for ALTER TABLE to try lock upgrade +SET DEBUG_SYNC= 'now WAIT_FOR upgrade'; +SET DEBUG_SYNC= 'now SIGNAL continue'; +SET DEBUG_SYNC= 'now WAIT_FOR upgrade'; +--echo # Sending: +--send DELETE FROM t2 WHERE a = 3 + +connection con3; +--echo # Check that DELETE is waiting on a metadata lock and not a table lock. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" AND + info = "DELETE FROM t2 WHERE a = 3"; +--source include/wait_condition.inc +--echo # Now that DELETE blocks on a metadata lock, we should be able to do +--echo # SELECT * FROM m1 here. SELECT used to be blocked by a DELETE table +--echo # lock request. +--send SELECT * FROM m1 WHERE a < 3 + +connection default; +--echo # Resuming ALTER TABLE +SET DEBUG_SYNC= 'now SIGNAL continue'; + +connection con1; +--echo # Reaping: ALTER TABLE m1 engine=MERGE UNION=(t2, t1) +--reap +connection con2; +--echo # Reaping: DELETE FROM t2 WHERE a = 3 +--reap +connection con3; +--echo # Reaping: SELECT * FROM m1 WHERE a < 3 +--reap +connection default; +DROP TABLE m1, t1, t2; +SET DEBUG_SYNC= 'RESET'; +disconnect con1; +disconnect con2; +disconnect con3; + + +--echo # +--echo # MDEV-12620 - set lock_wait_timeout = 1;flush tables with read lock; +--echo # lock not released after timeout +--echo # + +CREATE TABLE t1(a INT) ENGINE=InnoDB; +SET debug_sync='open_tables_after_open_and_process_table SIGNAL ready WAIT_FOR go'; +send INSERT INTO t1 values (1); + +connect (con1,localhost,root,,); +SET debug_sync='now WAIT_FOR ready'; +# lock_wait_timeout should be 0 in 10.3, so that we don't have to wait at all +SET lock_wait_timeout=1; +--error ER_LOCK_WAIT_TIMEOUT +FLUSH TABLES WITH READ LOCK; +SET debug_sync='now SIGNAL go'; + +connection default; +reap; + +--echo # After MDEV-5536, SELECT will not block FLUSH TABLES + +SET debug_sync='RESET'; +SET debug_sync='open_tables_after_open_and_process_table SIGNAL ready WAIT_FOR go'; +send SELECT * FROM t1; + +connection con1; +SET debug_sync='now WAIT_FOR ready'; +# lock_wait_timeout should be 0 in 10.3, so that we don't have to wait at all +SET lock_wait_timeout=1; +FLUSH TABLES WITH READ LOCK; +SET debug_sync='now SIGNAL go'; + +connection default; +reap; +connection con1; +unlock tables; +connection default; + +SET debug_sync='RESET'; +DROP TABLE t1; + +disconnect con1; + +# Check that all connections opened by test cases in this file are really +# gone so execution of other tests won't be affected by their presence. +--source include/wait_until_count_sessions.inc |