diff options
Diffstat (limited to 'mysql-test/r/concurrent_innodb_safelog.result')
| -rw-r--r-- | mysql-test/r/concurrent_innodb_safelog.result | 130 |
1 files changed, 68 insertions, 62 deletions
diff --git a/mysql-test/r/concurrent_innodb_safelog.result b/mysql-test/r/concurrent_innodb_safelog.result index 03d20f40be3..27889777ac1 100644 --- a/mysql-test/r/concurrent_innodb_safelog.result +++ b/mysql-test/r/concurrent_innodb_safelog.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 @@ -65,7 +68,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 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 @@ -85,7 +88,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 @@ -117,7 +120,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread2 +connection thread2; ** Make sure the output is similar for t1. select * from t1; eta tipo c @@ -132,7 +135,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread1 +connection thread1; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -146,13 +149,13 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 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)); @@ -171,14 +174,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 @@ -204,7 +207,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 @@ -223,7 +226,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; @@ -256,7 +259,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 @@ -270,7 +273,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 @@ -284,13 +287,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); @@ -299,13 +302,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); @@ -319,7 +322,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, @@ -333,13 +336,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)); @@ -354,7 +357,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. @@ -363,7 +366,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 @@ -385,7 +388,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 @@ -403,10 +406,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 @@ -421,7 +424,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 @@ -436,13 +439,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)); @@ -457,7 +460,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 @@ -466,7 +469,7 @@ eta tipo c 20 2 ddddddddddddddddddddddddddddddddddddddddddd 40 2 fffffffffffffffffffffffffffffffffffffffffff 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -** connection thread1 +connection thread1; ** Starting new transaction on thread 1 begin; @@ -492,7 +495,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -** connection thread2 +connection thread2; ** The same thing should hold true for the transaction on ** thread 2 select * from t1; @@ -509,9 +512,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 @@ -526,7 +529,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 @@ -540,13 +543,13 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 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)); @@ -561,7 +564,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; @@ -569,14 +572,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; @@ -594,10 +597,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 @@ -612,7 +615,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 @@ -627,13 +630,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)); @@ -648,10 +651,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 @@ -668,7 +671,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 @@ -680,9 +683,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 @@ -693,7 +696,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 @@ -704,13 +707,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)); @@ -725,10 +728,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 @@ -750,7 +753,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 @@ -762,9 +765,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 @@ -775,7 +778,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 @@ -787,8 +790,11 @@ eta tipo c 80 22 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; |