diff options
Diffstat (limited to 'mysql-test/r/concurrent_innodb_unsafelog.result')
| -rw-r--r-- | mysql-test/r/concurrent_innodb_unsafelog.result | 130 |
1 files changed, 68 insertions, 62 deletions
diff --git a/mysql-test/r/concurrent_innodb_unsafelog.result b/mysql-test/r/concurrent_innodb_unsafelog.result index 4a140d9e610..39e757aeeb1 100644 --- a/mysql-test/r/concurrent_innodb_unsafelog.result +++ b/mysql-test/r/concurrent_innodb_unsafelog.result @@ -1,4 +1,5 @@ SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; +connection default; SET SQL_MODE=""; SELECT @@global.tx_isolation; @@global.tx_isolation @@ -13,7 +14,8 @@ drop table if exists t1; ** ** two UPDATE's running and both changing distinct result sets ** -** connection thread1 +connect thread1, localhost, mysqltest,,; +connection thread1; ** Set up table SET SESSION STORAGE_ENGINE = InnoDB; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); @@ -32,13 +34,14 @@ insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",10); get_lock("hello",10) 1 -** connection thread2 +connect thread2, localhost, mysqltest,,; +connection thread2; ** Start transaction for thread 2 begin; ** Update will cause a table scan and a new ULL will ** be created and blocked on the first row where tipo=11. update t1 set eta=1+get_lock("hello",10)*0 where tipo=11; -** connection thread1 +connection thread1; ** Start new transaction for thread 1 begin; ** Update on t1 will cause a table scan which will be blocked because @@ -64,7 +67,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread2 +connection thread2; ** Release the lock and collect result from update on thread 2 DO release_lock("hello"); ** Table should have eta updates where tipo=11 but updates made by @@ -84,7 +87,7 @@ eta tipo c 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk ** Sending commit on thread 2. commit; -** connection thread1 +connection thread1; ** Make sure table reads didn't change yet on thread 1. select * from t1; eta tipo c @@ -116,7 +119,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread2 +connection thread2; ** Make sure the output is similar for t1. select * from t1; eta tipo c @@ -131,7 +134,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread1 +connection thread1; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -145,13 +148,13 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection default +connection default; drop table t1; ** ** two UPDATE's running and one changing result set ** -** connection thread1 +connection thread1; ** Set up table SET SESSION STORAGE_ENGINE = InnoDB; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); @@ -170,14 +173,14 @@ insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",10); get_lock("hello",10) 1 -** connection thread2 +connection thread2; ** Start transaction on thread 2 begin; ** Update will cause a table scan. ** This will cause a hang on the first row where tipo=1 until the ** blocking ULL is released. update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; -** connection thread1 +connection thread1; ** Start transaction on thread 1 begin; ** Update on t1 will cause a table scan which will be blocked because @@ -202,7 +205,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread2 +connection thread2; ** Release the lock and collect result from thread 2: DO release_lock("hello"); ** Seen from thread 2 the table should have been updated on four @@ -221,7 +224,7 @@ eta tipo c 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk commit; -** connection thread1 +connection thread1; ** Thread 2 has committed but the result should remain the same for ** thread 1 (updated on three places): select * from t1; @@ -254,7 +257,7 @@ eta tipo c 1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread2 +connection thread2; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -268,7 +271,7 @@ eta tipo c 1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread1 +connection thread1; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -282,13 +285,13 @@ eta tipo c 1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection default +connection default; drop table t1; ** ** One UPDATE and one INSERT .... Monty's test ** -** connection thread1 +connection thread1; ** Set up table SET SESSION STORAGE_ENGINE = InnoDB; create table t1 (a int not null, b int not null); @@ -297,13 +300,13 @@ insert into t1 values (1,1),(2,1),(3,1),(4,1); select get_lock("hello2",10); get_lock("hello2",10) 1 -** connection thread2 +connection thread2; ** Begin a new transaction on thread 2 begin; ** Update will create a table scan which creates a ULL where a=2; ** this will hang waiting on thread 1. update t1 set b=10+get_lock(concat("hello",a),10)*0 where a=2; -** connection thread1 +connection thread1; ** Insert new values to t1 from thread 1; this created an implicit ** commit since there are no on-going transactions. insert into t1 values (1,1); @@ -317,7 +320,7 @@ a b 3 1 4 1 1 1 -** connection thread2 +connection thread2; ** Collect results from thread 2 and release the lock. DO release_lock("hello2"); ** The table should look like the original+updates for thread 2, @@ -331,13 +334,13 @@ a b 1 1 ** Commit changes from thread 2 commit; -** connection default +connection default; drop table t1; ** ** one UPDATE changing result set and SELECT ... FOR UPDATE ** -** connection thread1 +connection thread1; ** Set up table SET SESSION STORAGE_ENGINE = InnoDB; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); @@ -352,7 +355,7 @@ insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -** connection thread2 +connection thread2; ** Begin a new transaction on thread 2 begin; ** Select a range for update. @@ -361,7 +364,7 @@ eta tipo c 20 2 ddddddddddddddddddddddddddddddddddddddddddd 40 2 fffffffffffffffffffffffffffffffffffffffffff 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -** connection thread1 +connection thread1; ** Begin a new transaction on thread 1 begin; ** Update the same range which is marked for update on thread 2; this @@ -383,7 +386,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread2 +connection thread2; ** The table should look unmodified from thread 2. select * from t1; eta tipo c @@ -401,10 +404,10 @@ eta tipo c ** Sending a commit should release the row locks and enable ** thread 1 to complete the transaction. commit; -** connection thread1 +connection thread1; ** Commit on thread 1. commit; -** connection thread2 +connection thread2; ** The table should not have been changed. select * from t1; eta tipo c @@ -419,7 +422,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread1 +connection thread1; ** Even on thread 1: select * from t1; eta tipo c @@ -434,13 +437,13 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection default +connection default; drop table t1; ** ** one UPDATE not changing result set and SELECT ... FOR UPDATE ** -** connection thread1 +connection thread1; ** Set up table SET SESSION STORAGE_ENGINE = InnoDB; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); @@ -455,7 +458,7 @@ insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -** connection thread2 +connection thread2; ** Starting new transaction on thread 2. begin; ** Starting SELECT .. FOR UPDATE @@ -464,7 +467,7 @@ eta tipo c 20 2 ddddddddddddddddddddddddddddddddddddddddddd 40 2 fffffffffffffffffffffffffffffffffffffffffff 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -** connection thread1 +connection thread1; ** Starting new transaction on thread 1 begin; @@ -489,7 +492,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 11 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread2 +connection thread2; ** The same thing should hold true for the transaction on ** thread 2 select * from t1; @@ -506,9 +509,9 @@ eta tipo c 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk commit; -** connection thread1 +connection thread1; commit; -** connection thread2 +connection thread2; ** Even after committing: select * from t1; eta tipo c @@ -523,7 +526,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 11 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread1 +connection thread1; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -537,13 +540,13 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 11 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection default +connection default; drop table t1; ** ** two SELECT ... FOR UPDATE ** -** connection thread1 +connection thread1; ** Set up table SET SESSION STORAGE_ENGINE = InnoDB; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); @@ -558,7 +561,7 @@ insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -** connection thread2 +connection thread2; ** Begin a new transaction on thread 2 begin; select * from t1 where tipo=2 FOR UPDATE; @@ -566,14 +569,14 @@ eta tipo c 20 2 ddddddddddddddddddddddddddddddddddddddddddd 40 2 fffffffffffffffffffffffffffffffffffffffffff 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -** connection thread1 +connection thread1; ** Begin a new transaction on thread 1 begin; ** Selecting a range for update by table scan will be blocked ** because of on-going transaction on thread 2. select * from t1 where tipo=1 FOR UPDATE; ERROR HY000: Lock wait timeout exceeded; try restarting transaction -** connection thread2 +connection thread2; ** Table will be unchanged and the select command will not be ** blocked: select * from t1; @@ -591,10 +594,10 @@ eta tipo c 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk ** Commit transacton on thread 2. commit; -** connection thread1 +connection thread1; ** Commit transaction on thread 1. commit; -** connection thread2 +connection thread2; ** Make sure table isn't blocked on thread 2: select * from t1; eta tipo c @@ -609,7 +612,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread1 +connection thread1; ** Make sure table isn't blocked on thread 1: select * from t1; eta tipo c @@ -624,13 +627,13 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection default +connection default; drop table t1; ** ** one UPDATE changing result set and DELETE ** -** connection thread1 +connection thread1; ** Set up table SET SESSION STORAGE_ENGINE = InnoDB; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); @@ -645,10 +648,10 @@ insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -** connection thread2 +connection thread2; begin; delete from t1 where tipo=2; -** connection thread1 +connection thread1; begin; update t1 set tipo=1 where tipo=2; ERROR HY000: Lock wait timeout exceeded; try restarting transaction @@ -665,7 +668,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread2 +connection thread2; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -677,9 +680,9 @@ eta tipo c 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk commit; -** connection thread1 +connection thread1; commit; -** connection thread2 +connection thread2; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -690,7 +693,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread1 +connection thread1; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -701,13 +704,13 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection default +connection default; drop table t1; ** ** one UPDATE not changing result set and DELETE ** -** connection thread1 +connection thread1; ** Set up table SET SESSION STORAGE_ENGINE = InnoDB; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); @@ -722,10 +725,10 @@ insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -** connection thread2 +connection thread2; begin; delete from t1 where tipo=2; -** connection thread1 +connection thread1; begin; ** Update on t1 will cause a table scan which will be blocked because ** the previously initiated table scan applied exclusive key locks on @@ -746,7 +749,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread2 +connection thread2; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -758,9 +761,9 @@ eta tipo c 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk commit; -** connection thread1 +connection thread1; commit; -** connection thread2 +connection thread2; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -771,7 +774,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread1 +connection thread1; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -783,8 +786,11 @@ eta tipo c 80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk ** Cleanup -** connection thread2 -** connection default +connection thread1; +disconnect thread1; +connection thread2; +disconnect thread2; +connection default; drop table t1; drop user mysqltest@localhost; SET SQL_MODE=default; |