diff options
| author | Sergey Vojtovich <svoj@mariadb.org> | 2016-05-06 13:44:07 +0400 |
|---|---|---|
| committer | Sergey Vojtovich <svoj@mariadb.org> | 2016-11-03 16:35:15 +0400 |
| commit | d665e79c5b8582f44dc280e5e6df4a8ff4945623 (patch) | |
| tree | 58f1b236eb85db2182493a71bf91e1b5e8fe8067 | |
| parent | e2d6912609c976bad04cee76874d4f986cd58cef (diff) | |
| download | mariadb-git-d665e79c5b8582f44dc280e5e6df4a8ff4945623.tar.gz | |
MDEV-7660 - MySQL WL#6671 "Improve scalability by not using thr_lock.c locks
for InnoDB tables"
Don't use thr_lock.c locks for InnoDB tables. Below is list of changes that
were needed to implement this:
- HANDLER OPEN acquireis MDL_SHARED_READ instead of MDL_SHARED
- HANDLER READ calls external_lock() even if SE is not going to be locked by
THR_LOCK
- InnoDB lock wait timeouts are now honored which are much shorter by default
than server lock wait timeouts (1 year vs 50 seconds)
- with @@autocommit= 1 LOCK TABLES disables autocommit implicitely, though
user still sees @@autocommt= 1
- the above starts implicit transaction
- transactions started by LOCK TABLES are now rolled back on disconnect
(previously everything was committed due to autocommit)
- transactions started by LOCK TABLES are now rolled back by ROLLBACK
(previously everything was committed due to autocommit)
- it is now impossible to change BINLOG_FORMAT under LOCK TABLES (at least
to statement) due to running transaction
- LOCK TABLES WRITE is additionally handled by MDL
- ...in contrast LOCK TABLES READ protection against DML is pure InnoDB
- combining transactional and non-transactional tables under LOCK TABLES
may cause rolled back changes in transactional table and "committed"
changes in non-transactional table
- user may disable innodb_table_locks, which will cause LOCK TABLES to be
noop basically
Removed tests for BUG#45143 and BUG#55930 which cover InnoDB + THR_LOCK. To
operate properly these tests require code flow to go through THR_LOCK debug
sync points, which is not the case after this patch. These tests are removed
by WL#6671 as well. An alternative is to port them to different storage engine.
31 files changed, 151 insertions, 1034 deletions
diff --git a/mysql-test/include/mtr_warnings.sql b/mysql-test/include/mtr_warnings.sql index 278add92ba5..635dfd9b8c3 100644 --- a/mysql-test/include/mtr_warnings.sql +++ b/mysql-test/include/mtr_warnings.sql @@ -206,14 +206,6 @@ INSERT INTO global_suppressions VALUES ("==[0-9]*== Warning: invalid file descriptor -1 in syscall read()"), /* - BUG#42147 - Concurrent DML and LOCK TABLE ... READ for InnoDB - table cause warnings in errlog - Note: This is a temporary suppression until Bug#42147 can be - fixed properly. See bug page for more information. - */ - ("Found lock of type 6 that is write and read locked"), - - /* Transient network failures that cause warnings on reconnect. BUG#47743 and BUG#47983. */ diff --git a/mysql-test/lib/v1/mtr_report.pl b/mysql-test/lib/v1/mtr_report.pl index 738236a731e..0af70d96647 100644 --- a/mysql-test/lib/v1/mtr_report.pl +++ b/mysql-test/lib/v1/mtr_report.pl @@ -361,9 +361,6 @@ sub mtr_report_stats ($) { /Slave: Can't DROP 'c7'.* 1091/ or /Slave: Key column 'c6'.* 1072/ or - # Warnings generated until bug#42147 is properly resolved - /Found lock of type 6 that is write and read locked/ or - # rpl_idempotency.test produces warnings for the slave. ($testname eq 'rpl.rpl_idempotency' and (/Slave: Can\'t find record in \'t1\' error.* 1032/ or diff --git a/mysql-test/r/innodb_mysql_lock.result b/mysql-test/r/innodb_mysql_lock.result index a7f45d355f1..6d2eef2e62e 100644 --- a/mysql-test/r/innodb_mysql_lock.result +++ b/mysql-test/r/innodb_mysql_lock.result @@ -95,8 +95,6 @@ LOCK TABLES t1 READ; SELECT release_lock('bug42147_lock'); release_lock('bug42147_lock') 1 -connection default; -connection con2; UNLOCK TABLES; connection default; disconnect con2; diff --git a/mysql-test/r/lock_sync.result b/mysql-test/r/lock_sync.result index f075262c3db..5eae1af7982 100644 --- a/mysql-test/r/lock_sync.result +++ b/mysql-test/r/lock_sync.result @@ -338,6 +338,7 @@ Success: 'load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, # 2.8 REPLACE with a subquery. # # Same is true for this statement as well. +# Suppress warnings for REPLACE ... SELECT connection default; Success: 'replace into t2 select i+5 from t1' doesn't allow concurrent inserts into 't1'. connection default; @@ -704,87 +705,6 @@ disconnect con1; disconnect con2; set @@global.concurrent_insert= @old_concurrent_insert; # -# Test for bug #45143 "All connections hang on concurrent ALTER TABLE". -# -# Concurrent execution of statements which required weak write lock -# (TL_WRITE_ALLOW_WRITE) on several instances of the same table and -# statements which tried to acquire stronger write lock (TL_WRITE, -# TL_WRITE_ALLOW_READ) on this table might have led to deadlock. -drop table if exists t1; -drop view if exists v1; -# Create auxiliary connections used through the test. -connect con_bug45143_1,localhost,root,,test,,; -connect con_bug45143_3,localhost,root,,test,,; -connect con_bug45143_2,localhost,root,,test,,; -connection default; -# Reset DEBUG_SYNC facility before using it. -set debug_sync= 'RESET'; -# Turn off logging so calls to locking subsystem performed -# for general_log table won't interfere with our test. -set @old_general_log = @@global.general_log; -set @@global.general_log= OFF; -create table t1 (i int) engine=InnoDB; -# We have to use view in order to make LOCK TABLES avoid -# acquiring SNRW metadata lock on table. -create view v1 as select * from t1; -insert into t1 values (1); -# Prepare user lock which will be used for resuming execution of -# the first statement after it acquires TL_WRITE_ALLOW_WRITE lock. -select get_lock("lock_bug45143_wait", 0); -get_lock("lock_bug45143_wait", 0) -1 -connection con_bug45143_1; -# Sending: -insert into t1 values (get_lock("lock_bug45143_wait", 100));; -connection con_bug45143_2; -# Wait until the above INSERT takes TL_WRITE_ALLOW_WRITE lock on 't1' -# and then gets blocked on user lock 'lock_bug45143_wait'. -# Ensure that upcoming SELECT waits after acquiring TL_WRITE_ALLOW_WRITE -# lock for the first instance of 't1'. -set debug_sync='thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go'; -# Sending: -select count(*) > 0 from t1 as a, t1 as b for update;; -connection con_bug45143_3; -# Wait until the above SELECT ... FOR UPDATE is blocked after -# acquiring lock for the the first instance of 't1'. -set debug_sync= 'now WAIT_FOR parked'; -# Send LOCK TABLE statement which will try to get TL_WRITE lock on 't1': -lock table v1 write;; -connection default; -# Wait until this LOCK TABLES statement starts waiting for table lock. -# Allow SELECT ... FOR UPDATE to resume. -# Since it already has TL_WRITE_ALLOW_WRITE lock on the first instance -# of 't1' it should be able to get lock on the second instance without -# waiting, even although there is another thread which has such lock -# on this table and also there is a thread waiting for a TL_WRITE on it. -set debug_sync= 'now SIGNAL go'; -connection con_bug45143_2; -# Reap SELECT ... FOR UPDATE -count(*) > 0 -1 -connection default; -# Resume execution of the INSERT statement. -select release_lock("lock_bug45143_wait"); -release_lock("lock_bug45143_wait") -1 -connection con_bug45143_1; -# Reap INSERT statement. -# In Statement and Mixed replication mode we get here "Unsafe -# for binlog" warnings. In row mode there are no warnings. -# Hide the discrepancy. -connection con_bug45143_3; -# Reap LOCK TABLES statement. -unlock tables; -connection default; -# Do clean-up. -disconnect con_bug45143_1; -disconnect con_bug45143_2; -disconnect con_bug45143_3; -set debug_sync= 'RESET'; -set @@global.general_log= @old_general_log; -drop view v1; -drop table t1; -# # Bug#50821 Deadlock between LOCK TABLES and ALTER TABLE # DROP TABLE IF EXISTS t1, t2; @@ -827,44 +747,6 @@ connection default; DROP EVENT e2; SET DEBUG_SYNC="RESET"; # -# Bug#55930 Assertion `thd->transaction.stmt.is_empty() || -# thd->in_sub_stmt || (thd->state.. -# -DROP TABLE IF EXISTS t1; -CREATE TABLE t1(a INT) engine=InnoDB; -INSERT INTO t1 VALUES (1), (2); -connect con1, localhost, root; -connect con2, localhost, root; -connection con1; -SET SESSION lock_wait_timeout= 1; -SET DEBUG_SYNC= 'ha_admin_open_ltable SIGNAL opti_recreate WAIT_FOR opti_analyze'; -# Sending: -OPTIMIZE TABLE t1; -connection con2; -SET DEBUG_SYNC= 'now WAIT_FOR opti_recreate'; -SET DEBUG_SYNC= 'after_lock_tables_takes_lock SIGNAL thrlock WAIT_FOR release_thrlock'; -# Sending: -INSERT INTO t1 VALUES (3); -connection default; -SET DEBUG_SYNC= 'now WAIT_FOR thrlock'; -SET DEBUG_SYNC= 'now SIGNAL opti_analyze'; -connection con1; -# Reaping: OPTIMIZE TABLE t1 -Table Op Msg_type Msg_text -test.t1 optimize note Table does not support optimize, doing recreate + analyze instead -test.t1 optimize error Lock wait timeout exceeded; try restarting transaction -test.t1 optimize status Operation failed -Warnings: -Error 1205 Lock wait timeout exceeded; try restarting transaction -SET DEBUG_SYNC= 'now SIGNAL release_thrlock'; -disconnect con1; -connection con2; -# Reaping: INSERT INTO t1 VALUES (3) -disconnect con2; -connection default; -DROP TABLE t1; -SET DEBUG_SYNC= 'RESET'; -# # Bug#57130 crash in Item_field::print during SHOW CREATE TABLE or VIEW # DROP TABLE IF EXISTS t1; diff --git a/mysql-test/r/lock_tables_lost_commit.result b/mysql-test/r/lock_tables_lost_commit.result index 769e9734c7a..394ef0a9d1c 100644 --- a/mysql-test/r/lock_tables_lost_commit.result +++ b/mysql-test/r/lock_tables_lost_commit.result @@ -9,7 +9,6 @@ disconnect con1; connection con2; SELECT * FROM t1; a -10 DROP TABLE t1; connection default; disconnect con2; diff --git a/mysql-test/r/mdl_sync.result b/mysql-test/r/mdl_sync.result index 865e874f63e..1e285650c77 100644 --- a/mysql-test/r/mdl_sync.result +++ b/mysql-test/r/mdl_sync.result @@ -76,10 +76,6 @@ ERROR 42000: Key column 'not_exist' doesn't exist in table # lock. alter table t1 add primary key (c1); ERROR 23000: Duplicate entry '1' for key 'PRIMARY' -# Check that SNRW lock is compatible with S lock. -lock table t1 write; -insert into t1 values (1); -unlock tables; # Check that X lock is incompatible with S lock. # Sending: rename table t1 to t2;; @@ -117,29 +113,6 @@ connection mdl_con1; alter table t1 drop column c2; # connection default; -handler t1 open; -# -connection mdl_con1; -# Check that upgrade from SNRW to X is blocked by presence of S lock. -lock table t1 write; -# Sending: -alter table t1 add column c2 int;; -# -connection mdl_con2; -# Check that the above upgrade of SNRW to X in ALTER TABLE is blocked -# because of S lock. -# -connection default; -# Unblock ALTER TABLE. -handler t1 close; -# -connection mdl_con1; -# Reaping ALTER TABLE. -# Restore the original state of the things. -alter table t1 drop column c2; -unlock tables; -# -connection default; # # 2) Acquire SH (shared high-priority) lock on the table. # We have to involve DEBUG_SYNC facility for this as usually @@ -160,7 +133,7 @@ column_name c1 select count(*) from t1; count(*) -3 +2 insert into t1 values (1); # Check that SU lock is compatible with it. To do this use ALTER TABLE # which will fail when constructing .frm and thus obtaining SU metadata @@ -258,7 +231,7 @@ connection default; begin; select count(*) from t1; count(*) -3 +2 # connection mdl_con1; # Check that S, SH, SR and SW locks are compatible with it. @@ -270,7 +243,7 @@ column_name c1 select count(*) from t1; count(*) -3 +2 insert into t1 values (1); # Check that SU lock is compatible with it. To do this use ALTER TABLE # which will fail when constructing .frm and thus obtaining SU metadata @@ -300,7 +273,7 @@ connection default; begin; select count(*) from t1; count(*) -3 +2 # connection mdl_con1; # Check that X lock is incompatible with SR lock. @@ -323,7 +296,7 @@ connection default; begin; select count(*) from t1; count(*) -3 +2 # connection mdl_con1; # Check that upgrade from SNW to X is blocked by presence of SR lock. @@ -453,7 +426,7 @@ column_name c1 select count(*) from t1; count(*) -5 +4 delete from t1 limit 1; # Check that SU lock is incompatible with SU lock. # Sending: @@ -549,7 +522,7 @@ column_name c1 select count(*) from t1; count(*) -5 +4 # Check that SW lock is incompatible with SNW lock. # Sending: delete from t1 limit 2;; @@ -658,8 +631,6 @@ lock table t1 write; # connection mdl_con1; # Check that S and SH 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'; column_name @@ -676,7 +647,7 @@ unlock tables; connection mdl_con1; # Reaping SELECT. count(*) -4 +3 # connection default; lock table t1 write; @@ -847,7 +818,7 @@ ERROR 42S01: Table 't2' already exists connection mdl_con1; # Reaping SELECT. count(*) -4 +3 # connection mdl_con2; # Prepare for blocking RENAME TABLE. @@ -996,7 +967,7 @@ column_name c1 select count(*) from t1; count(*) -4 +3 # Check that SW is incompatible with pending SNW # Sending: delete from t1 limit 1;; @@ -1025,7 +996,7 @@ connection mdl_con2; begin; select count(*) from t1; count(*) -3 +2 # connection default; # Add pending SNRW lock. @@ -1035,8 +1006,6 @@ lock table t1 write;; connection mdl_con1; # Check that LOCK TABLE is waiting with pending SNRW lock. # Check that S and SH locks are compatible with pending SNRW -handler t1 open t; -handler t close; select column_name from information_schema.columns where table_schema='test' and table_name='t1'; column_name @@ -1057,14 +1026,14 @@ unlock tables; connection mdl_con1; # Reaping SELECT. count(*) -3 +2 # Restore pending SNRW lock. # connection mdl_con2; begin; select count(*) from t1; count(*) -3 +2 # connection default; # Sending: @@ -1093,7 +1062,7 @@ connection mdl_con2; begin; select count(*) from t1; count(*) -4 +3 # connection default; # Sending: @@ -1126,7 +1095,7 @@ connection mdl_con2; begin; select count(*) from t1; count(*) -4 +3 # connection default; # Add pending X lock. @@ -1162,7 +1131,7 @@ connection mdl_con2; begin; select count(*) from t1; count(*) -4 +3 # connection default; # Add pending X lock. @@ -1187,14 +1156,14 @@ ERROR 42S01: Table 't2' already exists connection mdl_con1; # Reaping SELECT. count(*) -4 +3 # Restore pending X lock. # connection mdl_con2; begin; select count(*) from t1; count(*) -4 +3 # connection default; # Add pending X lock. @@ -1224,7 +1193,7 @@ connection mdl_con2; begin; select count(*) from t1; count(*) -3 +2 # connection default; # Add pending X lock. @@ -1299,7 +1268,7 @@ connection default; begin; select count(*) from t1; count(*) -3 +2 # connection mdl_con1; # Create an active SNW lock on t2. @@ -1321,7 +1290,7 @@ commit; begin; select count(*) from t1; count(*) -3 +2 # Sending: insert into t2 values (1);; # @@ -1347,7 +1316,7 @@ commit; begin; select count(*) from t1; count(*) -3 +2 # connection mdl_con1; # Create an active SNW lock on t1. @@ -1360,7 +1329,7 @@ set debug_sync= 'now WAIT_FOR locked'; # We should still be able to get SR lock without waiting. select count(*) from t1; count(*) -3 +2 # Since the above ALTER TABLE is not upgrading SNW lock to X by waiting # for SW lock we won't create deadlock. # So the below INSERT should not end-up with ER_LOCK_DEADLOCK error. @@ -1397,7 +1366,7 @@ connection default; # We should still be able to get both SW and SR locks without waiting. select count(*) from t1; count(*) -5 +4 delete from t1 limit 1; # Unblock ALTER TABLE. commit; @@ -1416,7 +1385,7 @@ connection default; begin; select count(*) from t1; count(*) -4 +3 # connection mdl_con2; # Start transaction which will prevent SNW -> X upgrade from @@ -1454,7 +1423,7 @@ commit; begin; select count(*) from t1; count(*) -4 +3 # connection mdl_con2; # Start transaction which will prevent SNW -> X upgrade from @@ -1493,7 +1462,7 @@ commit; begin; select count(*) from t1; count(*) -4 +3 # connection mdl_con1; # Create SNW lock pending upgrade to X. @@ -1505,7 +1474,7 @@ connection default; # Check that transaction is still able to acquire SR lock. select count(*) from t1; count(*) -4 +3 # Waiting trying to acquire SW lock will cause deadlock and # therefore should cause an error. delete from t1 limit 1; @@ -1526,7 +1495,7 @@ connection default; begin; select count(*) from t1; count(*) -4 +3 # connection mdl_con1; lock table t2 write; @@ -1551,7 +1520,7 @@ commit; begin; select count(*) from t1; count(*) -4 +3 # connection mdl_con1; lock table t2 write; @@ -1580,7 +1549,7 @@ commit; begin; select count(*) from t1; count(*) -4 +3 # connection mdl_con1; # Sending: @@ -1591,7 +1560,7 @@ connection default; # Check that another instance of SR lock is granted without waiting. select count(*) from t1; count(*) -4 +3 # Attempt to wait for SW lock will lead to deadlock, thus # the below statement should end with ER_LOCK_DEADLOCK error. delete from t1 limit 1; @@ -1620,7 +1589,7 @@ connection default; # and errors. select count(*) from t1; count(*) -3 +2 insert into t1 values (1, 1); # Unblock LOCK TABLES. commit; @@ -1639,7 +1608,7 @@ connection default; begin; select count(*) from t1; count(*) -4 +3 # connection mdl_con2; # Start transaction which will prevent X lock from going away @@ -1677,7 +1646,7 @@ rename table t3 to t2; begin; select count(*) from t1; count(*) -4 +3 # connection mdl_con2; # Start transaction which will prevent X lock from going away @@ -1721,7 +1690,7 @@ rename table t3 to t2; begin; select count(*) from t1; count(*) -4 +3 # connection mdl_con1; # Sending: @@ -1732,7 +1701,7 @@ connection default; # Check that another instance of SR lock is granted without waiting. select count(*) from t1; count(*) -4 +3 # Attempt to wait for SW lock will lead to deadlock, thus # the below statement should end with ER_LOCK_DEADLOCK error. delete from t1 limit 1; @@ -1761,7 +1730,7 @@ connection default; # and errors. select count(*) from t1; count(*) -3 +2 insert into t1 values (1, 1); # Unblock RENAME TABLE. commit; @@ -1778,149 +1747,6 @@ disconnect mdl_con3; set debug_sync= 'RESET'; drop table t1, t2; # -# Additional coverage for some scenarios in which not quite -# correct use of S metadata locks by HANDLER statement might -# have caused deadlocks. -# -drop table if exists t1, t2; -connect handler_con1,localhost,root,,; -connect handler_con2,localhost,root,,; -connection default; -create table t1 (i int); -create table t2 (j int); -insert into t1 values (1); -# -# First, check scenario in which we upgrade SNRW lock to X lock -# on a table while having HANDLER READ trying to acquire TL_READ -# on the same table. -# -handler t1 open; -# -connection handler_con1; -lock table t1 write; -# Upgrade SNRW to X lock. -# Sending: -alter table t1 add column j int;; -# -connection handler_con2; -# Wait until ALTER is blocked during upgrade. -# -connection default; -# The below statement should not cause deadlock. -handler t1 read first;; -# -connection handler_con1; -# Reap ALTER TABLE. -unlock tables; -# -connection default; -# Reap HANDLER READ. -i j -1 NULL -handler t1 close; -# -# Now, check scenario in which upgrade of SNRW lock to X lock -# can be blocked by HANDLER which is open in connection currently -# waiting to get table-lock owned by connection doing upgrade. -# -handler t1 open; -# -connection handler_con1; -lock table t1 write, t2 read; -# -connection default; -# Execute statement which will be blocked on table-level lock -# owned by connection 'handler_con1'. -# Sending: -insert into t2 values (1);; -# -connection handler_con1; -# Wait until INSERT is blocked on table-level lock. -# Sending 'alter table t1 drop column j'. It should not cause -# deadlock. -alter table t1 drop column j; -connection handler_con2; -# Wait until ALTER is blocked during upgrade. -# -connection default; -# Reap INSERT. -ERROR HY000: Wait on a lock was aborted due to a pending exclusive lock -handler t1 close; -# -connection handler_con1; -# Reaping 'alter table t1 drop column j' -unlock tables; -connection default; -# Then, check the scenario in which upgrade of SNRW lock to X -# lock is blocked by HANDLER which is open in connection currently -# waiting to get SW lock on the same table. -# -handler t1 open; -# -connection handler_con1; -lock table t1 write; -# -connection default; -# The below insert should be blocked because active SNRW lock on 't1'. -# Sending: -insert into t1 values (1);; -# -connection handler_con1; -# Wait until INSERT is blocked because of SNRW lock. -# The below ALTER TABLE will be blocked because of presence of HANDLER. -# Sending: -alter table t1 add column j int;; -# -connection default; -# INSERT should be chosen as victim for resolving deadlock. -# Reaping INSERT. -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -# Close HANDLER to unblock ALTER TABLE. -handler t1 close; -# -connection handler_con1; -# Reaping ALTER TABLE. -unlock tables; -# -connection default; -# -# Finally, test in which upgrade of SNRW lock to X lock is blocked -# by HANDLER which is open in connection currently waiting to get -# SR lock on the table on which lock is upgraded. -# -handler t1 open; -# -connection handler_con1; -lock table t1 write, t2 write; -# -connection default; -# The below insert should be blocked because active SNRW lock on 't1'. -# Sending: -insert into t2 values (1);; -# -connection handler_con1; -# Wait until INSERT is blocked because of SNRW lock. -# The below ALTER TABLE will be blocked because of presence of HANDLER. -# Sending: -alter table t1 drop column j;; -# -connection default; -# INSERT should be chosen as victim for resolving deadlock. -# Reaping INSERT. -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -# Close HANDLER to unblock ALTER TABLE. -handler t1 close; -# -connection handler_con1; -# Reaping ALTER TABLE. -unlock tables; -# -connection default; -# Clean-up. -disconnect handler_con1; -disconnect handler_con2; -drop tables t1, t2; -# # Test coverage for basic deadlock detection in metadata # locking subsystem. # diff --git a/mysql-test/r/partition_debug_sync.result b/mysql-test/r/partition_debug_sync.result index 971bc63e2e7..b79385686b7 100644 --- a/mysql-test/r/partition_debug_sync.result +++ b/mysql-test/r/partition_debug_sync.result @@ -64,29 +64,3 @@ disconnect con1; connection default; SET DEBUG_SYNC= 'RESET'; End of 5.1 tests -# -# Coverage test for non pruned ha_partition::store_lock() -# -CREATE TABLE t1 (a int) ENGINE = InnoDB; -CREATE TABLE t2 (a int PRIMARY KEY) -ENGINE = InnoDB PARTITION BY HASH (a) PARTITIONS 3; -HANDLER t1 OPEN; -connect con1, localhost, root,,; -LOCK TABLES t1 WRITE, t2 READ; -connection default; -SET DEBUG_SYNC="wait_for_lock SIGNAL locking"; -INSERT INTO t2 VALUES (1), (2), (3); -connection con1; -SET DEBUG_SYNC="now WAIT_FOR locking"; -ALTER TABLE t1 ADD COLUMN b int; -connection default; -ERROR HY000: Wait on a lock was aborted due to a pending exclusive lock -SELECT 1; -1 -1 -connection con1; -UNLOCK TABLES; -disconnect con1; -connection default; -SET DEBUG_SYNC = 'RESET'; -DROP TABLE t1, t2; diff --git a/mysql-test/r/partition_explicit_prune.result b/mysql-test/r/partition_explicit_prune.result index 0cf9bde225c..1272ff8c5fc 100644 --- a/mysql-test/r/partition_explicit_prune.result +++ b/mysql-test/r/partition_explicit_prune.result @@ -281,7 +281,7 @@ UNLOCK TABLES; SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE -HANDLER_COMMIT 2 +HANDLER_COMMIT 3 HANDLER_READ_RND_NEXT 54 HANDLER_TMP_WRITE 75 HANDLER_WRITE 2 @@ -440,7 +440,7 @@ UNLOCK TABLES; SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE -HANDLER_COMMIT 5 +HANDLER_COMMIT 6 HANDLER_READ_FIRST 3 HANDLER_READ_NEXT 4 HANDLER_READ_RND_NEXT 108 @@ -665,7 +665,7 @@ UNLOCK TABLES; SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE -HANDLER_COMMIT 5 +HANDLER_COMMIT 6 HANDLER_DELETE 2 HANDLER_READ_FIRST 1 HANDLER_READ_KEY 3 @@ -758,7 +758,7 @@ UNLOCK TABLES; SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE -HANDLER_COMMIT 2 +HANDLER_COMMIT 3 HANDLER_READ_RND_NEXT 54 HANDLER_TMP_WRITE 75 HANDLER_WRITE 10 @@ -953,7 +953,7 @@ UNLOCK TABLES; SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE -HANDLER_COMMIT 3 +HANDLER_COMMIT 4 HANDLER_DELETE 1 HANDLER_READ_KEY 2 HANDLER_READ_RND 2 @@ -1039,7 +1039,7 @@ UNLOCK TABLES; SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE -HANDLER_COMMIT 3 +HANDLER_COMMIT 4 HANDLER_DELETE 2 HANDLER_READ_KEY 3 HANDLER_READ_NEXT 1 diff --git a/mysql-test/r/truncate_coverage.result b/mysql-test/r/truncate_coverage.result index 95e649912e5..078de1ef3ab 100644 --- a/mysql-test/r/truncate_coverage.result +++ b/mysql-test/r/truncate_coverage.result @@ -6,48 +6,6 @@ DROP TABLE IF EXISTS t1; CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); connect con1, localhost, root,,; -HANDLER t1 OPEN; -connection default; -LOCK TABLE t1 WRITE; -SET DEBUG_SYNC='mdl_upgrade_lock SIGNAL waiting'; -TRUNCATE TABLE t1; -connect con2, localhost, root,,; -SET DEBUG_SYNC='now WAIT_FOR waiting'; -KILL QUERY @id; -disconnect con2; -connection default; -ERROR 70100: Query execution was interrupted -UNLOCK TABLES; -connection con1; -# Release shared metadata lock by closing HANDLER. -HANDLER t1 CLOSE; -disconnect con1; -connection default; -DROP TABLE t1; -SET DEBUG_SYNC='RESET'; -CREATE TABLE t1 (c1 INT); -INSERT INTO t1 VALUES (1); -connect con1, localhost, root,,; -HANDLER t1 OPEN; -connection default; -LOCK TABLE t1 WRITE; -SET DEBUG_SYNC='mdl_upgrade_lock SIGNAL waiting'; -TRUNCATE TABLE t1; -connect con2, localhost, root,,; -SET DEBUG_SYNC='now WAIT_FOR waiting'; -disconnect con2; -connection con1; -HANDLER t1 CLOSE; -disconnect con1; -connection default; -ERROR 42S02: Table 'test.t1' doesn't exist -UNLOCK TABLES; -DROP TABLE t1; -ERROR 42S02: Unknown table 'test.t1' -SET DEBUG_SYNC='RESET'; -CREATE TABLE t1 (c1 INT); -INSERT INTO t1 VALUES (1); -connect con1, localhost, root,,; START TRANSACTION; INSERT INTO t1 VALUES (2); connection default; diff --git a/mysql-test/suite/handler/aria.result b/mysql-test/suite/handler/aria.result index b8ed1fd98c8..6b02ac9b085 100644 --- a/mysql-test/suite/handler/aria.result +++ b/mysql-test/suite/handler/aria.result @@ -545,7 +545,6 @@ optimize table t1; connection default; handler t1 read next; c1 -1 handler t1 close; connection con2; Table Op Msg_type Msg_text @@ -1296,19 +1295,27 @@ commit; # an open HANDLER, ER_LOCK_DEADLOCK is reported. # create table t1 (a int, key a(a)); -create table t2 like t1; handler t1 open; connection con1; -lock table t1 write, t2 write; +select get_lock('lock1', 10); +get_lock('lock1', 10) +1 connection default; -drop table t2; +select get_lock('lock1', 10); connection con2; -# Waiting for 'drop table t2' to get blocked... +# Waiting for 'select get_lock('lock1', 10)' to get blocked... connection con1; drop table t1; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -unlock tables; +select release_lock('lock1'); +release_lock('lock1') +1 connection default; +get_lock('lock1', 10) +1 +select release_lock('lock1'); +release_lock('lock1') +1 # Demonstrate that there is no deadlock with FLUSH TABLE, # even though it is waiting for the other table to go away create table t2 like t1; @@ -1347,6 +1354,10 @@ handler t1 read a next; a 1 # Unblock 'lock tables t1 write'. +select * from t1; +a +1 +2 commit; connection con1; # Reap 'lock tables t1 write'. @@ -1516,10 +1527,6 @@ handler t1 read a last; a b 7 7 commit; -connection con1; -# Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE. -lock table t1 write; -unlock tables; connection default; handler t1 read a prev; a b diff --git a/mysql-test/suite/handler/handler.inc b/mysql-test/suite/handler/handler.inc index a4ab5f1ed32..2432ff13e55 100644 --- a/mysql-test/suite/handler/handler.inc +++ b/mysql-test/suite/handler/handler.inc @@ -1054,24 +1054,24 @@ commit; --echo # an open HANDLER, ER_LOCK_DEADLOCK is reported. --echo # create table t1 (a int, key a(a)); -create table t2 like t1; handler t1 open; connection con1; -lock table t1 write, t2 write; +select get_lock('lock1', 10); connection default; -send drop table t2; +send select get_lock('lock1', 10); connection con2; ---echo # Waiting for 'drop table t2' to get blocked... +--echo # Waiting for 'select get_lock('lock1', 10)' to get blocked... let $wait_condition=select count(*)=1 from information_schema.processlist - where state='Waiting for table metadata lock' and - info='drop table t2'; + where state='User lock' and + info='select get_lock(\'lock1\', 10)'; --source include/wait_condition.inc connection con1; --error ER_LOCK_DEADLOCK drop table t1; -unlock tables; +select release_lock('lock1'); connection default; reap; +select release_lock('lock1'); --echo # Demonstrate that there is no deadlock with FLUSH TABLE, --echo # even though it is waiting for the other table to go away @@ -1118,6 +1118,7 @@ connection default; handler t1 read a next; --echo # Unblock 'lock tables t1 write'. +select * from t1; # Release MDL_SHARED_READ held by HANDLER commit; connection con1; @@ -1132,7 +1133,7 @@ connection con1; --echo # Waiting for 'handler t1 read a next' to get blocked... let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Waiting for table level lock" and + where state = "Waiting for table metadata lock" and info = "handler t1 read a next"; --source include/wait_condition.inc @@ -1259,10 +1260,6 @@ handler t1 read a last; insert into t1 (a, b) values (7, 7); handler t1 read a last; commit; -connection con1; ---echo # Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE. -lock table t1 write; -unlock tables; connection default; handler t1 read a prev; handler t1 close; diff --git a/mysql-test/suite/handler/heap.result b/mysql-test/suite/handler/heap.result index 70dcefe4ff3..fc42e43f710 100644 --- a/mysql-test/suite/handler/heap.result +++ b/mysql-test/suite/handler/heap.result @@ -545,7 +545,6 @@ optimize table t1; connection default; handler t1 read next; c1 -1 handler t1 close; connection con2; Table Op Msg_type Msg_text @@ -1296,19 +1295,27 @@ commit; # an open HANDLER, ER_LOCK_DEADLOCK is reported. # create table t1 (a int, key a(a)); -create table t2 like t1; handler t1 open; connection con1; -lock table t1 write, t2 write; +select get_lock('lock1', 10); +get_lock('lock1', 10) +1 connection default; -drop table t2; +select get_lock('lock1', 10); connection con2; -# Waiting for 'drop table t2' to get blocked... +# Waiting for 'select get_lock('lock1', 10)' to get blocked... connection con1; drop table t1; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -unlock tables; +select release_lock('lock1'); +release_lock('lock1') +1 connection default; +get_lock('lock1', 10) +1 +select release_lock('lock1'); +release_lock('lock1') +1 # Demonstrate that there is no deadlock with FLUSH TABLE, # even though it is waiting for the other table to go away create table t2 like t1; @@ -1347,6 +1354,10 @@ handler t1 read a next; a 1 # Unblock 'lock tables t1 write'. +select * from t1; +a +1 +2 commit; connection con1; # Reap 'lock tables t1 write'. @@ -1516,10 +1527,6 @@ handler t1 read a last; a b 7 7 commit; -connection con1; -# Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE. -lock table t1 write; -unlock tables; connection default; handler t1 read a prev; a b diff --git a/mysql-test/suite/handler/innodb.result b/mysql-test/suite/handler/innodb.result index 102237617fd..80e8ed679a9 100644 --- a/mysql-test/suite/handler/innodb.result +++ b/mysql-test/suite/handler/innodb.result @@ -1299,19 +1299,27 @@ commit; # an open HANDLER, ER_LOCK_DEADLOCK is reported. # create table t1 (a int, key a(a)); -create table t2 like t1; handler t1 open; connection con1; -lock table t1 write, t2 write; +select get_lock('lock1', 10); +get_lock('lock1', 10) +1 connection default; -drop table t2; +select get_lock('lock1', 10); connection con2; -# Waiting for 'drop table t2' to get blocked... +# Waiting for 'select get_lock('lock1', 10)' to get blocked... connection con1; drop table t1; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -unlock tables; +select release_lock('lock1'); +release_lock('lock1') +1 connection default; +get_lock('lock1', 10) +1 +select release_lock('lock1'); +release_lock('lock1') +1 # Demonstrate that there is no deadlock with FLUSH TABLE, # even though it is waiting for the other table to go away create table t2 like t1; @@ -1350,6 +1358,10 @@ handler t1 read a next; a 1 # Unblock 'lock tables t1 write'. +select * from t1; +a +1 +2 commit; connection con1; # Reap 'lock tables t1 write'. @@ -1519,10 +1531,6 @@ handler t1 read a last; a b 7 7 commit; -connection con1; -# Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE. -lock table t1 write; -unlock tables; connection default; handler t1 read a prev; a b diff --git a/mysql-test/suite/handler/interface.result b/mysql-test/suite/handler/interface.result index 4d5a385df0f..a4ac32c16b4 100644 --- a/mysql-test/suite/handler/interface.result +++ b/mysql-test/suite/handler/interface.result @@ -272,24 +272,6 @@ handler t1 read a next; ERROR 42S02: Unknown table 't1' in HANDLER connect con1,localhost,root,,; connect con2,localhost,root,,; -connection default; -drop table if exists t1; -# First test case which is supposed trigger the execution -# path on which problem was discovered. -create table t1 (a int not null); -insert into t1 values (1); -handler t1 open; -connection con1; -lock table t1 write; -alter table t1 engine=csv; -connection con2; -connection default; -handler t1 read a next; -ERROR HY000: Storage engine CSV of the table `test`.`t1` doesn't have this option -handler t1 close; -connection con1; -unlock tables; -drop table t1; # Now test case which was reported originally but which no longer # triggers execution path which has caused the problem. connection default; diff --git a/mysql-test/suite/handler/interface.test b/mysql-test/suite/handler/interface.test index a82412799eb..2f576c9b291 100644 --- a/mysql-test/suite/handler/interface.test +++ b/mysql-test/suite/handler/interface.test @@ -298,32 +298,6 @@ handler t1 read a next; connect(con1,localhost,root,,); connect(con2,localhost,root,,); -connection default; ---disable_warnings -drop table if exists t1; ---enable_warnings ---echo # First test case which is supposed trigger the execution ---echo # path on which problem was discovered. -create table t1 (a int not null); -insert into t1 values (1); -handler t1 open; -connection con1; -lock table t1 write; -send alter table t1 engine=csv; -connection con2; -let $wait_condition= - select count(*) = 1 from information_schema.processlist - where state = "Waiting for table metadata lock" and - info = "alter table t1 engine=csv"; ---source include/wait_condition.inc -connection default; ---error ER_ILLEGAL_HA -handler t1 read a next; -handler t1 close; -connection con1; ---reap -unlock tables; -drop table t1; --echo # Now test case which was reported originally but which no longer --echo # triggers execution path which has caused the problem. connection default; diff --git a/mysql-test/suite/handler/myisam.result b/mysql-test/suite/handler/myisam.result index fca75f3b7a6..90e1767a1f3 100644 --- a/mysql-test/suite/handler/myisam.result +++ b/mysql-test/suite/handler/myisam.result @@ -545,7 +545,6 @@ optimize table t1; connection default; handler t1 read next; c1 -1 handler t1 close; connection con2; Table Op Msg_type Msg_text @@ -1296,19 +1295,27 @@ commit; # an open HANDLER, ER_LOCK_DEADLOCK is reported. # create table t1 (a int, key a(a)); -create table t2 like t1; handler t1 open; connection con1; -lock table t1 write, t2 write; +select get_lock('lock1', 10); +get_lock('lock1', 10) +1 connection default; -drop table t2; +select get_lock('lock1', 10); connection con2; -# Waiting for 'drop table t2' to get blocked... +# Waiting for 'select get_lock('lock1', 10)' to get blocked... connection con1; drop table t1; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -unlock tables; +select release_lock('lock1'); +release_lock('lock1') +1 connection default; +get_lock('lock1', 10) +1 +select release_lock('lock1'); +release_lock('lock1') +1 # Demonstrate that there is no deadlock with FLUSH TABLE, # even though it is waiting for the other table to go away create table t2 like t1; @@ -1347,6 +1354,10 @@ handler t1 read a next; a 1 # Unblock 'lock tables t1 write'. +select * from t1; +a +1 +2 commit; connection con1; # Reap 'lock tables t1 write'. @@ -1516,10 +1527,6 @@ handler t1 read a last; a b 7 7 commit; -connection con1; -# Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE. -lock table t1 write; -unlock tables; connection default; handler t1 read a prev; a b diff --git a/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result b/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result index 6c709945111..3b5f67efe78 100644 --- a/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result +++ b/mysql-test/suite/rpl/r/rpl_switch_stm_row_mixed.result @@ -410,6 +410,7 @@ LOCK TABLES t11 WRITE; SET SESSION BINLOG_FORMAT=ROW; INSERT INTO t11 VALUES('Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict'); SET SESSION BINLOG_FORMAT=STATEMENT; +ERROR HY000: Cannot modify @@session.binlog_format inside a transaction INSERT INTO t11 VALUES('Careful With That Axe, Eugene'); UNLOCK TABLES; SELECT * FROM t11; diff --git a/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test b/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test index 575fdb2e89d..e5e2f7a381d 100644 --- a/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test +++ b/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test @@ -524,6 +524,7 @@ CREATE TABLE t11 (song VARCHAR(255)); LOCK TABLES t11 WRITE; SET SESSION BINLOG_FORMAT=ROW; INSERT INTO t11 VALUES('Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict'); +--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT SET SESSION BINLOG_FORMAT=STATEMENT; INSERT INTO t11 VALUES('Careful With That Axe, Eugene'); UNLOCK TABLES; diff --git a/mysql-test/t/innodb_mysql_lock.test b/mysql-test/t/innodb_mysql_lock.test index cb57c092e40..85ba41860df 100644 --- a/mysql-test/t/innodb_mysql_lock.test +++ b/mysql-test/t/innodb_mysql_lock.test @@ -150,14 +150,16 @@ let $wait_condition= --source include/wait_condition.inc LOCK TABLES t1 READ; SELECT release_lock('bug42147_lock'); +let $wait_condition= + SELECT COUNT(*) > 0 FROM information_schema.processlist + WHERE state = 'executing' + AND info = 'INSERT INTO t1 SELECT get_lock(\'bug42147_lock\', 60)'; +--source include/wait_condition.inc +UNLOCK TABLES; connection default; --reap -connection con2; -UNLOCK TABLES; - -connection default; disconnect con2; DROP TABLE t1; diff --git a/mysql-test/t/lock_sync.test b/mysql-test/t/lock_sync.test index c090e3a1d93..07c16acc72a 100644 --- a/mysql-test/t/lock_sync.test +++ b/mysql-test/t/lock_sync.test @@ -406,6 +406,12 @@ let $restore_table= t2; --echo # 2.8 REPLACE with a subquery. --echo # --echo # Same is true for this statement as well. + +--echo # Suppress warnings for REPLACE ... SELECT +--disable_query_log +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); +--enable_query_log + let $statement= replace into t2 select i+5 from t1; let $restore_table= t2; --source include/check_no_concurrent_insert.inc @@ -873,116 +879,6 @@ set @@global.concurrent_insert= @old_concurrent_insert; --echo # ---echo # Test for bug #45143 "All connections hang on concurrent ALTER TABLE". ---echo # ---echo # Concurrent execution of statements which required weak write lock ---echo # (TL_WRITE_ALLOW_WRITE) on several instances of the same table and ---echo # statements which tried to acquire stronger write lock (TL_WRITE, ---echo # TL_WRITE_ALLOW_READ) on this table might have led to deadlock. -# -# Suppress warnings for INSERTs that use get_lock(). -# -disable_query_log; -call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); -enable_query_log; - ---disable_warnings -drop table if exists t1; -drop view if exists v1; ---enable_warnings ---echo # Create auxiliary connections used through the test. -connect (con_bug45143_1,localhost,root,,test,,); -connect (con_bug45143_3,localhost,root,,test,,); -connect (con_bug45143_2,localhost,root,,test,,); -connection default; ---echo # Reset DEBUG_SYNC facility before using it. -set debug_sync= 'RESET'; ---echo # Turn off logging so calls to locking subsystem performed ---echo # for general_log table won't interfere with our test. -set @old_general_log = @@global.general_log; -set @@global.general_log= OFF; - -create table t1 (i int) engine=InnoDB; ---echo # We have to use view in order to make LOCK TABLES avoid ---echo # acquiring SNRW metadata lock on table. -create view v1 as select * from t1; -insert into t1 values (1); ---echo # Prepare user lock which will be used for resuming execution of ---echo # the first statement after it acquires TL_WRITE_ALLOW_WRITE lock. -select get_lock("lock_bug45143_wait", 0); - -connection con_bug45143_1; ---echo # Sending: ---send insert into t1 values (get_lock("lock_bug45143_wait", 100)); - -connection con_bug45143_2; ---echo # Wait until the above INSERT takes TL_WRITE_ALLOW_WRITE lock on 't1' ---echo # and then gets blocked on user lock 'lock_bug45143_wait'. -let $wait_condition= select count(*)= 1 from information_schema.processlist - where state= 'User lock' and - info='insert into t1 values (get_lock("lock_bug45143_wait", 100))'; ---source include/wait_condition.inc ---echo # Ensure that upcoming SELECT waits after acquiring TL_WRITE_ALLOW_WRITE ---echo # lock for the first instance of 't1'. -set debug_sync='thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go'; ---echo # Sending: ---send select count(*) > 0 from t1 as a, t1 as b for update; - -connection con_bug45143_3; ---echo # Wait until the above SELECT ... FOR UPDATE is blocked after ---echo # acquiring lock for the the first instance of 't1'. -set debug_sync= 'now WAIT_FOR parked'; ---echo # Send LOCK TABLE statement which will try to get TL_WRITE lock on 't1': ---send lock table v1 write; - -connection default; ---echo # Wait until this LOCK TABLES statement starts waiting for table lock. -let $wait_condition= select count(*)= 1 from information_schema.processlist - where state= 'Waiting for table level lock' and - info='lock table v1 write'; ---source include/wait_condition.inc ---echo # Allow SELECT ... FOR UPDATE to resume. ---echo # Since it already has TL_WRITE_ALLOW_WRITE lock on the first instance ---echo # of 't1' it should be able to get lock on the second instance without ---echo # waiting, even although there is another thread which has such lock ---echo # on this table and also there is a thread waiting for a TL_WRITE on it. -set debug_sync= 'now SIGNAL go'; - -connection con_bug45143_2; ---echo # Reap SELECT ... FOR UPDATE ---reap - -connection default; ---echo # Resume execution of the INSERT statement. -select release_lock("lock_bug45143_wait"); - -connection con_bug45143_1; ---echo # Reap INSERT statement. ---echo # In Statement and Mixed replication mode we get here "Unsafe ---echo # for binlog" warnings. In row mode there are no warnings. ---echo # Hide the discrepancy. ---disable_warnings ---reap ---enable_warnings - - -connection con_bug45143_3; ---echo # Reap LOCK TABLES statement. ---reap -unlock tables; - -connection default; ---echo # Do clean-up. -disconnect con_bug45143_1; -disconnect con_bug45143_2; -disconnect con_bug45143_3; -set debug_sync= 'RESET'; -set @@global.general_log= @old_general_log; -drop view v1; -drop table t1; - - ---echo # --echo # Bug#50821 Deadlock between LOCK TABLES and ALTER TABLE --echo # @@ -1051,55 +947,6 @@ SET DEBUG_SYNC="RESET"; --echo # ---echo # Bug#55930 Assertion `thd->transaction.stmt.is_empty() || ---echo # thd->in_sub_stmt || (thd->state.. ---echo # - ---disable_warnings -DROP TABLE IF EXISTS t1; ---enable_warnings - -CREATE TABLE t1(a INT) engine=InnoDB; -INSERT INTO t1 VALUES (1), (2); - -connect (con1, localhost, root); -connect (con2, localhost, root); - -connection con1; -SET SESSION lock_wait_timeout= 1; -SET DEBUG_SYNC= 'ha_admin_open_ltable SIGNAL opti_recreate WAIT_FOR opti_analyze'; ---echo # Sending: ---send OPTIMIZE TABLE t1 - -connection con2; -SET DEBUG_SYNC= 'now WAIT_FOR opti_recreate'; -SET DEBUG_SYNC= 'after_lock_tables_takes_lock SIGNAL thrlock WAIT_FOR release_thrlock'; ---echo # Sending: ---send INSERT INTO t1 VALUES (3) - -connection default; -SET DEBUG_SYNC= 'now WAIT_FOR thrlock'; -SET DEBUG_SYNC= 'now SIGNAL opti_analyze'; - -connection con1; ---echo # Reaping: OPTIMIZE TABLE t1 ---reap -SET DEBUG_SYNC= 'now SIGNAL release_thrlock'; -disconnect con1; ---source include/wait_until_disconnected.inc - -connection con2; ---echo # Reaping: INSERT INTO t1 VALUES (3) ---reap -disconnect con2; ---source include/wait_until_disconnected.inc - -connection default; -DROP TABLE t1; -SET DEBUG_SYNC= 'RESET'; - - ---echo # --echo # Bug#57130 crash in Item_field::print during SHOW CREATE TABLE or VIEW --echo # diff --git a/mysql-test/t/mdl_sync.test b/mysql-test/t/mdl_sync.test index 0b6d6f58013..4aa191d3dfc 100644 --- a/mysql-test/t/mdl_sync.test +++ b/mysql-test/t/mdl_sync.test @@ -119,10 +119,6 @@ alter table t1 add index (not_exist); --echo # lock. --error ER_DUP_ENTRY alter table t1 add primary key (c1); ---echo # Check that SNRW lock is compatible with S lock. -lock table t1 write; -insert into t1 values (1); -unlock tables; --echo # Check that X lock is incompatible with S lock. --echo # Sending: --send rename table t1 to t2; @@ -172,35 +168,6 @@ connection mdl_con1; alter table t1 drop column c2; --echo # connection default; -handler t1 open; ---echo # -connection mdl_con1; ---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 # -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; -unlock tables; ---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 @@ -797,8 +764,6 @@ lock table t1 write; --echo # connection mdl_con1; --echo # Check that S and SH 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 # Check that SR lock is incompatible with SNRW lock. @@ -1293,8 +1258,6 @@ 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 -handler t1 open t; -handler t close; select column_name from information_schema.columns where table_schema='test' and table_name='t1'; --echo # Check that SR is incompatible with pending SNRW @@ -2162,190 +2125,6 @@ disconnect mdl_con3; set debug_sync= 'RESET'; drop table t1, t2; - ---echo # ---echo # Additional coverage for some scenarios in which not quite ---echo # correct use of S metadata locks by HANDLER statement might ---echo # have caused deadlocks. ---echo # ---disable_warnings -drop table if exists t1, t2; ---enable_warnings -connect(handler_con1,localhost,root,,); -connect(handler_con2,localhost,root,,); -connection default; -create table t1 (i int); -create table t2 (j int); -insert into t1 values (1); - ---echo # ---echo # First, check scenario in which we upgrade SNRW lock to X lock ---echo # on a table while having HANDLER READ trying to acquire TL_READ ---echo # on the same table. ---echo # -handler t1 open; ---echo # -connection handler_con1; -lock table t1 write; ---echo # Upgrade SNRW to X lock. ---echo # Sending: ---send alter table t1 add column j int; ---echo # -connection handler_con2; ---echo # Wait until ALTER is blocked during upgrade. -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"; ---source include/wait_condition.inc ---echo # -connection default; ---echo # The below statement should not cause deadlock. ---send handler t1 read first; ---echo # -connection handler_con1; ---echo # Reap ALTER TABLE. ---reap -unlock tables; ---echo # -connection default; ---echo # Reap HANDLER READ. ---reap -handler t1 close; - ---echo # ---echo # Now, check scenario in which upgrade of SNRW lock to X lock ---echo # can be blocked by HANDLER which is open in connection currently ---echo # waiting to get table-lock owned by connection doing upgrade. ---echo # -handler t1 open; ---echo # -connection handler_con1; -lock table t1 write, t2 read; ---echo # -connection default; ---echo # Execute statement which will be blocked on table-level lock ---echo # owned by connection 'handler_con1'. ---echo # Sending: ---send insert into t2 values (1); ---echo # -connection handler_con1; ---echo # Wait until INSERT is blocked on table-level lock. -let $wait_condition= - select count(*) = 1 from information_schema.processlist - where state = "Waiting for table level lock" and - info = "insert into t2 values (1)"; ---source include/wait_condition.inc ---echo # Sending 'alter table t1 drop column j'. It should not cause ---echo # deadlock. -send alter table t1 drop column j; -connection handler_con2; ---echo # Wait until ALTER is blocked during upgrade. -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 # -connection default; ---echo # Reap INSERT. ---error ER_LOCK_ABORTED ---reap -handler t1 close; ---echo # -connection handler_con1; ---echo # Reaping 'alter table t1 drop column j' ---reap -unlock tables; -connection default; - ---echo # Then, check the scenario in which upgrade of SNRW lock to X ---echo # lock is blocked by HANDLER which is open in connection currently ---echo # waiting to get SW lock on the same table. ---echo # -handler t1 open; ---echo # -connection handler_con1; -lock table t1 write; ---echo # -connection default; ---echo # The below insert should be blocked because active SNRW lock on 't1'. ---echo # Sending: ---send insert into t1 values (1); ---echo # -connection handler_con1; ---echo # Wait until INSERT 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 = "insert into t1 values (1)"; ---source include/wait_condition.inc ---echo # The below ALTER TABLE will be blocked because of presence of HANDLER. ---echo # Sending: ---send alter table t1 add column j int; ---echo # -connection default; ---echo # INSERT should be chosen as victim for resolving deadlock. ---echo # Reaping INSERT. ---error ER_LOCK_DEADLOCK ---reap ---echo # Close HANDLER to unblock ALTER TABLE. -handler t1 close; ---echo # -connection handler_con1; ---echo # Reaping ALTER TABLE. ---reap -unlock tables; ---echo # -connection default; - ---echo # ---echo # Finally, test in which upgrade of SNRW lock to X lock is blocked ---echo # by HANDLER which is open in connection currently waiting to get ---echo # SR lock on the table on which lock is upgraded. ---echo # -handler t1 open; ---echo # -connection handler_con1; -lock table t1 write, t2 write; ---echo # -connection default; ---echo # The below insert should be blocked because active SNRW lock on 't1'. ---echo # Sending: ---send insert into t2 values (1); ---echo # -connection handler_con1; ---echo # Wait until INSERT 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 = "insert into t2 values (1)"; ---source include/wait_condition.inc ---echo # The below ALTER TABLE will be blocked because of presence of HANDLER. ---echo # Sending: ---send alter table t1 drop column j; ---echo # -connection default; ---echo # INSERT should be chosen as victim for resolving deadlock. ---echo # Reaping INSERT. ---error ER_LOCK_DEADLOCK ---reap ---echo # Close HANDLER to unblock ALTER TABLE. -handler t1 close; ---echo # -connection handler_con1; ---echo # Reaping ALTER TABLE. ---reap -unlock tables; ---echo # -connection default; - ---echo # Clean-up. -disconnect handler_con1; -disconnect handler_con2; -drop tables t1, t2; - - --echo # --echo # Test coverage for basic deadlock detection in metadata --echo # locking subsystem. diff --git a/mysql-test/t/partition_debug_sync.test b/mysql-test/t/partition_debug_sync.test index 11af9b06cdd..f085ea3fb83 100644 --- a/mysql-test/t/partition_debug_sync.test +++ b/mysql-test/t/partition_debug_sync.test @@ -82,43 +82,3 @@ connection default; SET DEBUG_SYNC= 'RESET'; --echo End of 5.1 tests - ---echo # ---echo # Coverage test for non pruned ha_partition::store_lock() ---echo # -CREATE TABLE t1 (a int) ENGINE = InnoDB; -CREATE TABLE t2 (a int PRIMARY KEY) -ENGINE = InnoDB PARTITION BY HASH (a) PARTITIONS 3; - -HANDLER t1 OPEN; - -connect (con1, localhost, root,,); - -LOCK TABLES t1 WRITE, t2 READ; - -connection default; - -SET DEBUG_SYNC="wait_for_lock SIGNAL locking"; -send INSERT INTO t2 VALUES (1), (2), (3); - -connection con1; -SET DEBUG_SYNC="now WAIT_FOR locking"; - -send ALTER TABLE t1 ADD COLUMN b int; - -connection default; ---error ER_LOCK_ABORTED ---reap - -SELECT 1; - -connection con1; ---reap - -UNLOCK TABLES; ---disconnect con1 - -connection default; -SET DEBUG_SYNC = 'RESET'; - -DROP TABLE t1, t2; diff --git a/mysql-test/t/truncate_coverage.test b/mysql-test/t/truncate_coverage.test index 0834f7a3eca..3351ce84232 100644 --- a/mysql-test/t/truncate_coverage.test +++ b/mysql-test/t/truncate_coverage.test @@ -17,86 +17,6 @@ DROP TABLE IF EXISTS t1; --echo # Bug#20667 - Truncate table fails for a write locked table --echo # ######## -# Attack wait_while_table_is_used(). Kill query while trying to -# upgrade MDL. -# -CREATE TABLE t1 (c1 INT); -INSERT INTO t1 VALUES (1); -# -# Acquire a shared metadata lock on table by opening HANDLER for it and wait. -# TRUNCATE shall block on this metadata lock. -# We can't use normal DML as such statements would also block LOCK TABLES. -# ---connect (con1, localhost, root,,) -HANDLER t1 OPEN; -# -# Get connection id of default connection. -# Lock the table and start TRUNCATE, which will block on MDL upgrade. -# ---connection default -let $ID= `SELECT @id := CONNECTION_ID()`; -LOCK TABLE t1 WRITE; -SET DEBUG_SYNC='mdl_upgrade_lock SIGNAL waiting'; -send TRUNCATE TABLE t1; -# -# Get the default connection ID into a variable in an invisible statement. -# Kill the TRUNCATE query. This shall result in an error return -# from wait_while_table_is_used(). -# ---connect (con2, localhost, root,,) -SET DEBUG_SYNC='now WAIT_FOR waiting'; -let $invisible_assignment_in_select = `SELECT @id := $ID`; -KILL QUERY @id; ---disconnect con2 ---connection default ---error ER_QUERY_INTERRUPTED -reap; -UNLOCK TABLES; ---connection con1 ---echo # Release shared metadata lock by closing HANDLER. -HANDLER t1 CLOSE; ---disconnect con1 ---connection default -DROP TABLE t1; -SET DEBUG_SYNC='RESET'; -######## -# Attack reopen_tables(). Remove form file. -# -CREATE TABLE t1 (c1 INT); -INSERT INTO t1 VALUES (1); -# -# Acquire a shared metadata lock on table by opening HANDLER for it and wait. -# TRUNCATE shall block on this metadata lock. -# We can't use normal DML as such statements would also block LOCK TABLES. -# ---connect (con1, localhost, root,,) -HANDLER t1 OPEN; -# -# Lock the table and start TRUNCATE, which will block on MDL upgrade. -# ---connection default -LOCK TABLE t1 WRITE; -SET DEBUG_SYNC='mdl_upgrade_lock SIGNAL waiting'; -send TRUNCATE TABLE t1; -# -# Remove datafile. -# Commit to let TRUNCATE continue. -# ---connect (con2, localhost, root,,) -SET DEBUG_SYNC='now WAIT_FOR waiting'; ---remove_file $MYSQLD_DATADIR/test/t1.frm ---disconnect con2 ---connection con1 -HANDLER t1 CLOSE; ---disconnect con1 ---connection default ---error ER_NO_SUCH_TABLE -reap; -UNLOCK TABLES; ---error ER_BAD_TABLE_ERROR -DROP TABLE t1; -SET DEBUG_SYNC='RESET'; -######## # Attack acquire_exclusive_locks(). Hold a global read lock. # Non-LOCK TABLE case. # diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 2d40631d79f..e1d788ba367 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -2150,6 +2150,11 @@ Locked_tables_list::unlock_locked_tables(THD *thd) request for metadata locks and TABLE_LIST elements. */ reset(); + if (thd->variables.option_bits & OPTION_AUTOCOMMIT) + { + thd->variables.option_bits&= ~(OPTION_NOT_AUTOCOMMIT); + thd->server_status|= SERVER_STATUS_AUTOCOMMIT; + } } diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc index 95417c73c74..405e7ce8c38 100644 --- a/sql/sql_handler.cc +++ b/sql/sql_handler.cc @@ -282,7 +282,7 @@ bool mysql_ha_open(THD *thd, TABLE_LIST *tables, SQL_HANDLER *reopen) back-off for such locks. */ tables->mdl_request.init(MDL_key::TABLE, tables->db, tables->table_name, - MDL_SHARED, MDL_TRANSACTION); + MDL_SHARED_READ, MDL_TRANSACTION); mdl_savepoint= thd->mdl_context.mdl_savepoint(); /* for now HANDLER can be used only for real TABLES */ @@ -750,11 +750,12 @@ retry: tables->table= table; // This is used by fix_fields table->pos_in_table_list= tables; - if (handler->lock->lock_count > 0) + if (handler->lock->table_count > 0) { int lock_error; - handler->lock->locks[0]->type= handler->lock->locks[0]->org_type; + if (handler->lock->lock_count > 0) + handler->lock->locks[0]->type= handler->lock->locks[0]->org_type; /* save open_tables state */ TABLE* backup_open_tables= thd->open_tables; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 74b5ac79ad1..a2a4ed2b77e 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -4794,7 +4794,8 @@ end_with_restore_list: if (lock_tables_precheck(thd, all_tables)) goto error; - thd->variables.option_bits|= OPTION_TABLE_LOCK; + thd->variables.option_bits|= OPTION_TABLE_LOCK | OPTION_NOT_AUTOCOMMIT; + thd->server_status&= ~SERVER_STATUS_AUTOCOMMIT; res= lock_tables_open_and_lock_tables(thd, all_tables); diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 30765ddd357..73217bdddd9 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -18932,7 +18932,6 @@ free_share( mysql_mutex_unlock(&innobase_share_mutex); } -#if 0 /*********************************************************************//** Returns number of THR_LOCK locks used for one instance of InnoDB table. InnoDB no longer relies on THR_LOCK locks so 0 value is returned. @@ -18948,7 +18947,6 @@ ha_innobase::lock_count(void) const { return 0; } -#endif /*****************************************************************//** Supposed to convert a MySQL table lock stored in the 'lock' field of the @@ -19196,8 +19194,6 @@ ha_innobase::store_lock( lock.type = lock_type; } - *to++= &lock; - if (!trx_is_started(trx) && (m_prebuilt->select_lock_type != LOCK_NONE || m_prebuilt->stored_select_lock_type != LOCK_NONE)) { diff --git a/storage/innobase/handler/ha_innodb.h b/storage/innobase/handler/ha_innodb.h index b436453e610..84feea119c1 100644 --- a/storage/innobase/handler/ha_innodb.h +++ b/storage/innobase/handler/ha_innodb.h @@ -274,7 +274,7 @@ public: void free_foreign_key_create_info(char* str); - //uint lock_count(void) const; + uint lock_count(void) const; THR_LOCK_DATA** store_lock( THD* thd, diff --git a/storage/tokudb/mysql-test/tokudb/r/nested_txn_implicit_commit.result b/storage/tokudb/mysql-test/tokudb/r/nested_txn_implicit_commit.result index ac1a13d5523..f1ea7e20147 100644 --- a/storage/tokudb/mysql-test/tokudb/r/nested_txn_implicit_commit.result +++ b/storage/tokudb/mysql-test/tokudb/r/nested_txn_implicit_commit.result @@ -46,7 +46,6 @@ a b 1 10 2 20 3 30 -4 40 insert into t2 values (1); ERROR HY000: Table 't2' was not locked with LOCK TABLES commit; @@ -59,7 +58,6 @@ a b 1 10 2 20 3 30 -4 40 select * from t2; a 1 @@ -72,7 +70,6 @@ a b 1 10 2 20 3 30 -4 40 5 50 select * from t2; a @@ -84,7 +81,6 @@ a b 1 10 2 20 3 30 -4 40 5 50 select * from t2; a diff --git a/storage/tokudb/mysql-test/tokudb_bugs/r/db806.result b/storage/tokudb/mysql-test/tokudb_bugs/r/db806.result index ae87dbab281..8578bb3b36c 100644 --- a/storage/tokudb/mysql-test/tokudb_bugs/r/db806.result +++ b/storage/tokudb/mysql-test/tokudb_bugs/r/db806.result @@ -2,8 +2,8 @@ drop table if exists t1,t3; CREATE TABLE t3(a int,c int,d int)engine=TOKUDB; lock table t3 read; create temporary table t1 engine=tokudb as SELECT 1; +unlock tables; select * from t1; 1 1 -unlock tables; drop table t1,t3; diff --git a/storage/tokudb/mysql-test/tokudb_bugs/t/db806.test b/storage/tokudb/mysql-test/tokudb_bugs/t/db806.test index 3815e59f78c..8dcebe1bb6b 100644 --- a/storage/tokudb/mysql-test/tokudb_bugs/t/db806.test +++ b/storage/tokudb/mysql-test/tokudb_bugs/t/db806.test @@ -7,7 +7,7 @@ enable_warnings; CREATE TABLE t3(a int,c int,d int)engine=TOKUDB; lock table t3 read; create temporary table t1 engine=tokudb as SELECT 1; -select * from t1; unlock tables; +select * from t1; -drop table t1,t3;
\ No newline at end of file +drop table t1,t3; |