diff options
Diffstat (limited to 'mysql-test/r/handler_innodb.result')
| -rw-r--r-- | mysql-test/r/handler_innodb.result | 154 |
1 files changed, 143 insertions, 11 deletions
diff --git a/mysql-test/r/handler_innodb.result b/mysql-test/r/handler_innodb.result index a3e3e325e7d..6ed7b572403 100644 --- a/mysql-test/r/handler_innodb.result +++ b/mysql-test/r/handler_innodb.result @@ -745,11 +745,29 @@ drop table t1; handler t1 read a next; ERROR 42S02: Unknown table 't1' in HANDLER drop table if exists t1; -create table t1 (a int, key a (a)); +# First test case which is supposed trigger the execution +# path on which problem was discovered. +create table t1 (a int); insert into t1 values (1); handler t1 open; +lock table t1 write; alter table t1 engine=memory; handler t1 read a next; +ERROR HY000: Table storage engine for 't1' doesn't have this option +handler t1 close; +unlock tables; +drop table t1; +# Now test case which was reported originally but which no longer +# triggers execution path which has caused the problem. +create table t1 (a int, key(a)); +insert into t1 values (1); +handler t1 open; +alter table t1 engine=memory; +# Since S metadata lock was already acquired at HANDLER OPEN time +# and TL_READ lock requested by HANDLER READ is compatible with +# ALTER's TL_WRITE_ALLOW_READ the below statement should succeed +# without waiting. The old version of table should be used in it. +handler t1 read a next; a 1 handler t1 close; @@ -1217,14 +1235,19 @@ create table t1 (a int, key a(a)); create table t2 like t1; handler t1 open; # --> connection con1 -lock table t2 read; +lock table t1 write, t2 write; # --> connection default drop table t2; +# --> connection con2 +# Waiting for 'drop table t2' to get blocked... +# --> connection con1 +drop table t1; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -rename table t2 to t3; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +unlock tables; +# --> connection default # 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; # Sending: flush table t2; # --> connection con2 @@ -1239,29 +1262,43 @@ drop table t2; # lead to deadlocks # create table t1 (a int, key a(a)); +insert into t1 values (1), (2); # --> connection default begin; select * from t1; a +1 +2 handler t1 open; # --> connection con1 +# Sending: lock tables t1 write; +# --> connection con2 +# Check that 'lock tables t1 write' waits until transaction which +# has read from the table commits. +# --> connection default +# The below 'handler t1 read ...' should not be blocked as +# 'lock tables t1 write' has not succeeded yet. +handler t1 read a next; +a +1 +# Unblock 'lock tables t1 write'. +commit; +# --> connection con1 +# Reap 'lock tables t1 write'. # --> connection default # Sending: handler t1 read a next; # --> connection con1 # Waiting for 'handler t1 read a next' to get blocked... -# Sending: +# The below 'drop table t1' should be able to proceed without +# waiting as it will force HANDLER to be closed. drop table t1; -# --> connection con2 -# Waiting for 'drop table t1' to get blocked... +unlock tables; # --> connection default # Reaping 'handler t1 read a next'... -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +ERROR 42S02: Table 'test.t1' doesn't exist handler t1 close; -commit; -# --> connection con1 -# Reaping 'drop table t1'... # --> connection con1 # --> connection con2 # --> connection con3 @@ -1324,3 +1361,98 @@ a b handler t2 read first; a b drop table t1, t2, t3, t4; +# +# A test for FLUSH TABLES WITH READ LOCK and HANDLER statements. +# +set autocommit=0; +create table t1 (a int, b int, key a (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +create table t2 like t1; +insert into t2 (a, b) select a, b from t1; +create table t3 like t1; +insert into t3 (a, b) select a, b from t1; +commit; +flush tables with read lock; +handler t1 open; +lock table t1 read; +handler t1 read next; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +# This implicitly leaves LOCK TABLES but doesn't drop the GLR +lock table not_exists_write read; +ERROR 42S02: Table 'test.not_exists_write' doesn't exist +# We still have the read lock. +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +handler t1 open; +select a from t2; +a +1 +2 +3 +4 +5 +handler t1 read next; +a b +1 1 +flush tables with read lock; +handler t2 open; +flush tables with read lock; +handler t1 read next; +a b +1 1 +select a from t3; +a +1 +2 +3 +4 +5 +handler t2 read next; +a b +1 1 +handler t1 close; +rollback; +handler t2 close; +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +commit; +flush tables; +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +unlock tables; +drop table t1; +set autocommit=default; +drop table t2, t3; +# +# HANDLER statement and operation-type aware metadata locks. +# Check that when we clone a ticket for HANDLER we downrade +# the lock. +# +# Establish an auxiliary connection con1. +# -> connection default +create table t1 (a int, b int, key a (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +begin; +insert into t1 (a, b) values (6, 6); +handler t1 open; +handler t1 read a last; +a b +6 6 +insert into t1 (a, b) values (7, 7); +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 +6 6 +handler t1 close; +# Cleanup. +drop table t1; +# -> connection con1 +# -> connection default |