1# Let's see if FLUSH TABLES WITH READ LOCK blocks COMMIT of existing 2# transactions. 3# We verify that we did not introduce a deadlock. 4# This is intended to mimick how mysqldump and innobackup work. 5 6# And it requires InnoDB 7--source include/have_innodb.inc 8 9--echo # Save the initial number of concurrent sessions 10--source include/count_sessions.inc 11 12--echo # Establish connection con1 (user=root) 13connect (con1,localhost,root,,); 14--echo # Establish connection con2 (user=root) 15connect (con2,localhost,root,,); 16--echo # Establish connection con3 (user=root) 17connect (con3,localhost,root,,); 18--echo # Switch to connection con1 19connection con1; 20 21--disable_warnings 22DROP TABLE IF EXISTS t1; 23--enable_warnings 24CREATE TABLE t1 (a INT) ENGINE=innodb; 25 26# blocks COMMIT ? 27 28BEGIN; 29INSERT INTO t1 VALUES(1); 30--echo # Switch to connection con2 31connection con2; 32FLUSH TABLES WITH READ LOCK; 33--echo # Switch to connection con1 34connection con1; 35--echo # Sending: 36--send COMMIT 37--echo # Switch to connection con2 38connection con2; 39--echo # Wait until COMMIT gets blocked. 40let $wait_condition= 41 select count(*) = 1 from information_schema.processlist 42 where state = "Waiting for commit lock" and info = "COMMIT"; 43--source include/wait_condition.inc 44--echo # Verify that 'con1' was blocked and data did not move. 45SELECT * FROM t1; 46UNLOCK TABLES; 47--echo # Switch to connection con1 48connection con1; 49--echo # Reaping COMMIT 50--reap 51 52# No deadlock ? 53 54--echo # Switch to connection con1 55connection con1; 56BEGIN; 57SELECT * FROM t1 FOR UPDATE; 58--echo # Switch to connection con2 59connection con2; 60BEGIN; 61send SELECT * FROM t1 FOR UPDATE; # blocked by con1 62sleep 1; 63--echo # Switch to connection con3 64connection con3; 65send FLUSH TABLES WITH READ LOCK; # blocked by con2 66--echo # Switch to connection con1 67connection con1; 68COMMIT; # should not be blocked by con3 69--echo # Switch to connection con2 70connection con2; 71reap; 72COMMIT; 73--echo # Switch to connection con3 74connection con3; 75reap; 76UNLOCK TABLES; 77 78# Bug#6732 FLUSH TABLES WITH READ LOCK + COMMIT hangs later FLUSH TABLES 79# WITH READ LOCK 80 81--echo # Switch to connection con2 82connection con2; 83COMMIT; # unlock InnoDB row locks to allow insertions 84--echo # Switch to connection con1 85connection con1; 86BEGIN; 87INSERT INTO t1 VALUES(10); 88FLUSH TABLES WITH READ LOCK; 89--echo # Switch to connection con2 90connection con2; 91FLUSH TABLES WITH READ LOCK; # bug caused hang here 92UNLOCK TABLES; 93 94# Bug#7358 SHOW CREATE DATABASE fails if open transaction 95 96BEGIN; 97SELECT * FROM t1; 98SHOW CREATE DATABASE test; 99COMMIT; 100 101 102--echo # Cleanup 103--echo # Switch to connection default and close connections con1, con2, con3 104connection default; 105disconnect con1; 106disconnect con2; 107disconnect con3; 108 109--echo # We commit open transactions when we disconnect: only then we can 110--echo # drop the table. 111DROP TABLE t1; 112--echo # End of 4.1 tests 113 114--echo # Wait till all disconnects are completed 115--source include/wait_until_count_sessions.inc 116 117