=== modified file 'mysql-test/include/concurrent.inc' --- a/mysql-test/include/concurrent.inc 2006-08-16 12:58:49 +0000 +++ b/mysql-test/include/concurrent.inc 2008-06-05 10:16:23 +0000 @@ -14,14 +14,21 @@ # 2006-08-02 ML test refactored # old name was t/innodb_concurrent.test # main code went into include/concurrent.inc +# 2008-06-03 KP test refactored; removed name locks, added comments. +# +# IMPORTANT: This test assumes that innodb is executed with +# the innodb_locks_unsafe_for_binlog flag in REPEATABLE READ +# transaction isolation levels. # connection default; eval SET SESSION STORAGE_ENGINE = $engine_type; +SELECT @@global.tx_isolation; --disable_warnings drop table if exists t1; --enable_warnings create table t1(eta int(11) not null, tipo int(11), c varchar(255)); +grant usage on test.* to mysqltest@localhost; connect (thread1, localhost, mysqltest,,); connection thread1; eval SET SESSION STORAGE_ENGINE = $engine_type; @@ -36,36 +43,57 @@ insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",1); +--echo ** Get user level name lock for thread 1 +select get_lock("hello",100); connect (thread2, localhost, mysqltest,,); connection thread2; +--echo ** Start transaction for thread2 begin; -send update t1 set eta=1+get_lock("hello",1)*0 where tipo=11; +--echo ** Update will cause a table scan and a new name lock will be created for each successfully matched condition. This will cause a hang on the first row where tipo=11. +send update t1 set eta=1+get_lock("hello",100)*0 where tipo=11; sleep 1; connection thread1; +--echo +--echo ** Start new transaction for thread 1 begin; +--echo ** Update on t1 will cause a table scan which won't be blocked because InnoDB will read the last committed rows. update t1 set eta=2 where tipo=22; +--echo ** Release user level name lock from thread 1. This will cause name lock on thread 2 to awaken. select release_lock("hello"); +--echo ** Table is now updated with a new version. select * from t1; connection thread2; +--echo +--echo ** Release the lock and collect result from update on thread 2 reap; +select release_lock("hello"); +--echo ** Table shouldn't be updated in thread 2 because thread 1 hasn't committed yet. select * from t1; +--echo ** Sending commit on thread 2. This will hang because thread 1 started first. send commit; connection thread1; +--echo +--echo ** Make sure table reads didn't change yet on thread 1. select * from t1; +--echo ** And send final commit on thread 1. commit; +--echo ** Table should now be updated by both updates in the order of thread 1,2. select * from t1; connection thread2; reap; +--echo +--echo ** Make sure the output is similar for t1. select * from t1; connection thread1; select * from t1; connection default; drop table t1; -# -# two UPDATE's running and one changing result set -# +--echo +--echo ** +--echo ** two UPDATE's running and one changing result set +--echo ** +--echo #connect (thread1, localhost, mysqltest,,); connection thread1; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); @@ -80,24 +108,42 @@ insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); +--echo ** Get named lock "hello" on thread 1 +select get_lock("hello",100); #connect (thread2, localhost, mysqltest,,); connection thread2; +--echo +--echo ** Start transaction on thread 2 begin; +--echo ** Update will cause a table scan and a new name lock will be created for each successfully matched condition (Only last ULL will remain). This will cause a hang on the first row where tipo=1 until the blocking name lock is released. send update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; sleep 1; connection thread1; +--echo +--echo ** Start transaction on thread 1 begin; +--echo ** Update will cause a table scan which won't be blocked because InnoDB will read the last committed values. update t1 set tipo=1 where tipo=2; +--echo +--echo ** Release name lock. This will release the next waiting name lock on thread 2. select release_lock("hello"); +--echo ** The table should still be updated with updates for thread 1 only: select * from t1; connection thread2; +--echo +--echo ** Release the lock and collect result from thread 2: reap; +select release_lock("hello"); +--echo +--echo ** Seen from thread 2 the table should have been updated on four places. select * from t1; send commit; connection thread1; +--echo ** Thread 2 has committed but the result should remain the same for thread 1 (updated on three places): select * from t1; commit; +--echo ** After a commit the table should be merged with the previous commit. +--echo ** This select should show both updates: select * from t1; connection thread2; reap; @@ -107,251 +153,145 @@ connection default; drop table t1; - -# -# One UPDATE and one INSERT .... Monty's test -# - +--echo +--echo ** +--echo ** One UPDATE and one INSERT .... Monty's test +--echo ** #connect (thread1, localhost, mysqltest,,); connection thread1; create table t1 (a int not null, b int not null); insert into t1 values (1,1),(2,1),(3,1),(4,1); +--echo ** Create named lock 'hello2' select get_lock("hello2",1000); #connect (thread2, localhost, mysqltest,,); connection thread2; +--echo +--echo ** Begin a new transaction on thread 2 begin; +--echo ** Update will create a table scan which creates a named lock where a=2; this will hang waiting on thread 1. send update t1 set b=10+get_lock(concat("hello",a),1000)*0 where a=2; sleep 1; connection thread1; +--echo ** 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); -select release_lock("hello2"); -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -sleep 1; -connection thread2; -reap; -connection default; -drop table t1; - -# -# one UPDATE changing result set and SELECT ... FOR UPDATE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -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"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send select * from t1 where tipo=2 FOR UPDATE; -sleep 1; -connection thread1; -begin; -select release_lock("hello"); ---error 1205 -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -commit; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; -connection default; -drop table t1; - -# -# one UPDATE not changing result set and SELECT ... FOR UPDATE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -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"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send select * from t1 where tipo=2 FOR UPDATE; -sleep 1; -connection thread1; -begin; -select release_lock("hello"); ---error 1205 -update t1 set tipo=11+get_lock("hello",10)*0 where tipo=22; -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -commit; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; -connection default; -drop table t1; - -# -# two SELECT ... FOR UPDATE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -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"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send select * from t1 where tipo=2 FOR UPDATE; -sleep 1; -connection thread1; -begin; -select release_lock("hello"); +--echo ** Release the name lock (thread 2 updates will finish). +select release_lock("hello2"); +--echo ** ..but thread 1 will still see t1 as if nothing has happend: +select * from t1; +connection thread2; +--echo ** Collect results from thread 2 and release the lock. +reap; +select release_lock("hello2"); +--echo ** The table should look like the original+updates for thread 2, and consist of new rows: +select * from t1; +--echo ** Commit changes from thread2 +send commit; +connection thread1; +sleep 1; +connection thread2; +reap; +connection default; +drop table t1; + +--echo +--echo ** +--echo ** one UPDATE changing result set and SELECT ... FOR UPDATE +--echo ** +#connect (thread1, localhost, mysqltest,,); +connection thread1; +create table t1(eta int(11) not null, tipo int(11), c varchar(255)); +insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); +insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); +insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); +insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); +insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); +insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); +insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); +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"); +#connect (thread2, localhost, mysqltest,,); +connection thread2; +--echo ** Begin a new transaction on thread 2 +begin; +--echo ** Select a range for update. +select * from t1 where tipo=2 FOR UPDATE; +connection thread1; +--echo +--echo ** Begin a new transaction on thread 1 +begin; +--echo ** Update the same range which is marked for update on thread 2; this will hang because of row locks +--error 1205 +update t1 set tipo=1 where tipo=2; +--echo ** After the update the table will be unmodified because the previous transaction failed and was rolled back. +select * from t1; +connection thread2; +--echo ** The table should look unmodified from thread 2. +select * from t1; +--echo ** Sending a commit should release the row locks and enable thread 1 to complete the transaction. +commit; +connection thread1; +--echo ** Commit on thread 1. +commit; +connection thread2; +--echo ** The table not have been changed. +select * from t1; +connection thread1; +--echo ** Even on thread 1: +select * from t1; +connection default; +drop table t1; + +--echo +--echo ** +--echo ** two SELECT ... FOR UPDATE +--echo ** +#connect (thread1, localhost, mysqltest,,); +connection thread1; +create table t1(eta int(11) not null, tipo int(11), c varchar(255)); +insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); +insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); +insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); +insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); +insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); +insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); +insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); +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"); +#connect (thread2, localhost, mysqltest,,); +connection thread2; +--echo +--echo ** Begin a new transaction on thread 2 +begin; +select * from t1 where tipo=2 FOR UPDATE; +connection thread1; +--echo +--echo ** Begin a new transaction on thread 1 +begin; +--echo ** Selecting a range for update by table scan will be blocked because of on-going transaction on thread 2. --error 1205 select * from t1 where tipo=1 FOR UPDATE; connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -commit; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; -connection default; -drop table t1; - - -# -# one UPDATE changing result set and DELETE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -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"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send delete from t1 where tipo=2; -sleep 1; -connection thread1; -begin; -select release_lock("hello"); ---error 1205 -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -commit; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; -connection default; -drop table t1; - - -# -# one UPDATE not changing result set and DELETE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -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"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send delete from t1 where tipo=2; -sleep 1; -connection thread1; -begin; -select release_lock("hello"); -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=22; -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -commit; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; -connection default; -sleep 1; -drop table t1; -disconnect thread1; -disconnect thread2; +--echo +--echo ** Table will be unchanged and the select command will not be blocked: +select * from t1; +--echo ** Commit transacton on thread 2. +commit; +connection thread1; +--echo ** Commit transaction on thread 1. +commit; +connection thread2; +--echo ** Make sure table isn't blocked on thread 2: +select * from t1; +connection thread1; +--echo ** Make sure table isn't blocked on thread 1: +select * from t1; +connection default; +drop table t1; + + + === modified file 'mysql-test/r/concurrent_innodb.result' --- a/mysql-test/r/concurrent_innodb.result 2006-08-16 12:58:49 +0000 +++ b/mysql-test/r/concurrent_innodb.result 2008-06-05 10:16:23 +0000 @@ -1,6 +1,10 @@ SET SESSION STORAGE_ENGINE = InnoDB; +SELECT @@global.tx_isolation; +@@global.tx_isolation +REPEATABLE-READ drop table if exists t1; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); +grant usage on test.* to mysqltest@localhost; SET SESSION STORAGE_ENGINE = InnoDB; insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); @@ -13,16 +17,24 @@ insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",1); -get_lock("hello",1) +** Get user level name lock for thread 1 +select get_lock("hello",100); +get_lock("hello",100) 1 -begin; - update t1 set eta=1+get_lock("hello",1)*0 where tipo=11; -begin; +** Start transaction for thread2 +begin; +** Update will cause a table scan and a new name lock will be created for each successfully matched condition. This will cause a hang on the first row where tipo=11. +update t1 set eta=1+get_lock("hello",100)*0 where tipo=11; + +** Start new transaction for thread 1 +begin; +** Update on t1 will cause a table scan which won't be blocked because InnoDB will read the last committed rows. update t1 set eta=2 where tipo=22; +** Release user level name lock from thread 1. This will cause name lock on thread 2 to awaken. select release_lock("hello"); release_lock("hello") 1 +** Table is now updated with a new version. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -36,6 +48,12 @@ 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk + +** Release the lock and collect result from update on thread 2 +select release_lock("hello"); +release_lock("hello") +1 +** Table shouldn't be updated in thread 2 because thread 1 hasn't committed yet. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -49,7 +67,10 @@ 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; +** Sending commit on thread 2. This will hang because thread 1 started first. +commit; + +** Make sure table reads didn't change yet on thread 1. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -63,7 +84,9 @@ 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** And send final commit on thread 1. commit; +** Table should now be updated by both updates in the order of thread 1,2. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -77,6 +100,8 @@ 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk + +** Make sure the output is similar for t1. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -104,6 +129,11 @@ 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk drop table t1; + +** +** two UPDATE's running and one changing result set +** + create table t1(eta int(11) not null, tipo int(11), c varchar(255)); insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); @@ -116,16 +146,26 @@ insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -get_lock("hello",10) -0 -begin; - update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; -begin; +** Get named lock "hello" on thread 1 +select get_lock("hello",100); +get_lock("hello",100) +1 + +** Start transaction on thread 2 +begin; +** Update will cause a table scan and a new name lock will be created for each successfully matched condition (Only last ULL will remain). This will cause a hang on the first row where tipo=1 until the blocking name lock is released. +update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; + +** Start transaction on thread 1 +begin; +** Update will cause a table scan which won't be blocked because InnoDB will read the last committed values. update t1 set tipo=1 where tipo=2; + +** Release name lock. This will release the next waiting name lock on thread 2. select release_lock("hello"); release_lock("hello") -0 +1 +** The table should still be updated with updates for thread 1 only: select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -139,6 +179,13 @@ 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk + +** Release the lock and collect result from thread 2: +select release_lock("hello"); +release_lock("hello") +1 + +** Seen from thread 2 the table should have been updated on four places. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -152,7 +199,8 @@ 1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; +commit; +** Thread 2 has committed but the result should remain the same for thread 1 (updated on three places): select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -167,17 +215,19 @@ 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk commit; +** After a commit the table should be merged with the previous commit. +** This select should show both updates: select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc +1 1 ccccccccccccccccccccccccccccccccccccccccccc 20 1 ddddddddddddddddddddddddddddddddddddddddddd -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +1 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 1 fffffffffffffffffffffffffffffffffffffffffff -50 1 ggggggggggggggggggggggggggggggggggggggggggg +1 1 ggggggggggggggggggggggggggggggggggggggggggg 60 1 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk select * from t1; @@ -207,18 +257,29 @@ 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk drop table t1; + +** +** One UPDATE and one INSERT .... Monty's test +** create table t1 (a int not null, b int not null); insert into t1 values (1,1),(2,1),(3,1),(4,1); +** Create named lock 'hello2' select get_lock("hello2",1000); get_lock("hello2",1000) 1 + +** Begin a new transaction on thread 2 begin; - update t1 set b=10+get_lock(concat("hello",a),1000)*0 where +** Update will create a table scan which creates a named lock where a=2; this will hang waiting on thread 1. +update t1 set b=10+get_lock(concat("hello",a),1000)*0 where a=2; +** 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); +** Release the name lock (thread 2 updates will finish). select release_lock("hello2"); release_lock("hello2") 1 +** ..but thread 1 will still see t1 as if nothing has happend: select * from t1; a b 1 1 @@ -226,6 +287,11 @@ 3 1 4 1 1 1 +** Collect results from thread 2 and release the lock. +select release_lock("hello2"); +release_lock("hello2") +1 +** The table should look like the original+updates for thread 2, and consist of new rows: select * from t1; a b 1 1 @@ -233,375 +299,175 @@ 3 1 4 1 1 1 - commit; -drop table t1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -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"); -select get_lock("hello",10); -get_lock("hello",10) -1 -begin; - select * from t1 where tipo=2 FOR UPDATE; -begin; -select release_lock("hello"); -release_lock("hello") -1 -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -20 2 ddddddddddddddddddddddddddddddddddddddddddd -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -40 2 fffffffffffffffffffffffffffffffffffffffffff -50 1 ggggggggggggggggggggggggggggggggggggggggggg -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -eta tipo c -20 2 ddddddddddddddddddddddddddddddddddddddddddd -40 2 fffffffffffffffffffffffffffffffffffffffffff -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -20 2 ddddddddddddddddddddddddddddddddddddddddddd -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -40 2 fffffffffffffffffffffffffffffffffffffffffff -50 1 ggggggggggggggggggggggggggggggggggggggggggg -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; -commit; -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -20 2 ddddddddddddddddddddddddddddddddddddddddddd -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -40 2 fffffffffffffffffffffffffffffffffffffffffff -50 1 ggggggggggggggggggggggggggggggggggggggggggg -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -20 2 ddddddddddddddddddddddddddddddddddddddddddd -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -40 2 fffffffffffffffffffffffffffffffffffffffffff -50 1 ggggggggggggggggggggggggggggggggggggggggggg -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -drop table t1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -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"); -select get_lock("hello",10); -get_lock("hello",10) -1 -begin; - select * from t1 where tipo=2 FOR UPDATE; -begin; -select release_lock("hello"); -release_lock("hello") -1 -update t1 set tipo=11+get_lock("hello",10)*0 where tipo=22; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -20 2 ddddddddddddddddddddddddddddddddddddddddddd -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -40 2 fffffffffffffffffffffffffffffffffffffffffff -50 1 ggggggggggggggggggggggggggggggggggggggggggg -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -eta tipo c -20 2 ddddddddddddddddddddddddddddddddddddddddddd -40 2 fffffffffffffffffffffffffffffffffffffffffff -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -20 2 ddddddddddddddddddddddddddddddddddddddddddd -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -40 2 fffffffffffffffffffffffffffffffffffffffffff -50 1 ggggggggggggggggggggggggggggggggggggggggggg -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; -commit; -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -20 2 ddddddddddddddddddddddddddddddddddddddddddd -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -40 2 fffffffffffffffffffffffffffffffffffffffffff -50 1 ggggggggggggggggggggggggggggggggggggggggggg -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -20 2 ddddddddddddddddddddddddddddddddddddddddddd -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -40 2 fffffffffffffffffffffffffffffffffffffffffff -50 1 ggggggggggggggggggggggggggggggggggggggggggg -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -drop table t1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -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"); -select get_lock("hello",10); -get_lock("hello",10) -1 -begin; - select * from t1 where tipo=2 FOR UPDATE; -begin; -select release_lock("hello"); -release_lock("hello") -1 +** Commit changes from thread2 +commit; +drop table t1; + +** +** one UPDATE changing result set and SELECT ... FOR UPDATE +** +create table t1(eta int(11) not null, tipo int(11), c varchar(255)); +insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); +insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); +insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); +insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); +insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); +insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); +insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); +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"); +** Begin a new transaction on thread 2 +begin; +** Select a range for update. +select * from t1 where tipo=2 FOR UPDATE; +eta tipo c +20 2 ddddddddddddddddddddddddddddddddddddddddddd +40 2 fffffffffffffffffffffffffffffffffffffffffff +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh + +** Begin a new transaction on thread 1 +begin; +** Update the same range which is marked for update on thread 2; this will hang because of row locks +update t1 set tipo=1 where tipo=2; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +** After the update the table will be unmodified because the previous transaction failed and was rolled back. +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** The table should look unmodified from thread 2. +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** Sending a commit should release the row locks and enable thread 1 to complete the transaction. +commit; +** Commit on thread 1. +commit; +** The table not have been changed. +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** Even on thread 1: +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +drop table t1; + +** +** two SELECT ... FOR UPDATE +** +create table t1(eta int(11) not null, tipo int(11), c varchar(255)); +insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); +insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); +insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); +insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); +insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); +insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); +insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); +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"); + +** Begin a new transaction on thread 2 +begin; +select * from t1 where tipo=2 FOR UPDATE; +eta tipo c +20 2 ddddddddddddddddddddddddddddddddddddddddddd +40 2 fffffffffffffffffffffffffffffffffffffffffff +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh + +** 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 -eta tipo c -20 2 ddddddddddddddddddddddddddddddddddddddddddd -40 2 fffffffffffffffffffffffffffffffffffffffffff -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -20 2 ddddddddddddddddddddddddddddddddddddddddddd -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -40 2 fffffffffffffffffffffffffffffffffffffffffff -50 1 ggggggggggggggggggggggggggggggggggggggggggg -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; -commit; -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -20 2 ddddddddddddddddddddddddddddddddddddddddddd -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -40 2 fffffffffffffffffffffffffffffffffffffffffff -50 1 ggggggggggggggggggggggggggggggggggggggggggg -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -20 2 ddddddddddddddddddddddddddddddddddddddddddd -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -40 2 fffffffffffffffffffffffffffffffffffffffffff -50 1 ggggggggggggggggggggggggggggggggggggggggggg -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -drop table t1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -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"); -select get_lock("hello",10); -get_lock("hello",10) -1 -begin; - delete from t1 where tipo=2; -begin; -select release_lock("hello"); -release_lock("hello") -1 -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -20 2 ddddddddddddddddddddddddddddddddddddddddddd -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -40 2 fffffffffffffffffffffffffffffffffffffffffff -50 1 ggggggggggggggggggggggggggggggggggggggggggg -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -50 1 ggggggggggggggggggggggggggggggggggggggggggg -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; -commit; -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -50 1 ggggggggggggggggggggggggggggggggggggggggggg -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -50 1 ggggggggggggggggggggggggggggggggggggggggggg -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -drop table t1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -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"); -select get_lock("hello",10); -get_lock("hello",10) -1 -begin; - delete from t1 where tipo=2; -begin; -select release_lock("hello"); -release_lock("hello") -1 -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=22; -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -20 2 ddddddddddddddddddddddddddddddddddddddddddd -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -40 2 fffffffffffffffffffffffffffffffffffffffffff -50 1 ggggggggggggggggggggggggggggggggggggggggggg -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -50 1 ggggggggggggggggggggggggggggggggggggggggggg -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; -commit; -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -50 1 ggggggggggggggggggggggggggggggggggggggggggg -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj -90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -select * from t1; -eta tipo c -7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee -50 1 ggggggggggggggggggggggggggggggggggggggggggg -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj + +** Table will be unchanged and the select command will not be blocked: +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** Commit transacton on thread 2. +commit; +** Commit transaction on thread 1. +commit; +** Make sure table isn't blocked on thread 2: +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** Make sure table isn't blocked on thread 1: +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk drop table t1; === added file 'mysql-test/r/concurrent_innodb_repeatable_read.result' --- a/mysql-test/r/concurrent_innodb_repeatable_read.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/r/concurrent_innodb_repeatable_read.result 2008-06-05 10:16:23 +0000 @@ -0,0 +1,91 @@ +SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; +grant usage on test.* to mysqltest@localhost; +create table t1(eta int(11) not null, tipo int(11), c varchar(255)) engine=innodb; +insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); +insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); +insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); +insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); +insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); +insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); +insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); +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"); + +** Starting new transaction on thread 2. +begin; +** Starting SELECT .. FOR UPDATE +select * from t1 where tipo=2 FOR UPDATE; +eta tipo c +20 2 ddddddddddddddddddddddddddddddddddddddddddd +40 2 fffffffffffffffffffffffffffffffffffffffffff +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh + +** Starting new transaction on thread 1 +begin; +** Updating single row using a table scan. This will time out because of ongoing transaction on thread 1 holding lock on one element in the scan. +update t1 set tipo=11 where tipo=22; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction + +** After the time out the transaction is aborted; no rows should have changed. +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk + +** The same thing should hold true for the transaction on thread 2 +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +commit; +commit; +** Even after committing (thread2): +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk + +** (thread1): +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +drop table t1; === added file 'mysql-test/t/concurrent_innodb_repeatable_read.test' --- a/mysql-test/t/concurrent_innodb_repeatable_read.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/t/concurrent_innodb_repeatable_read.test 2008-06-05 10:16:23 +0000 @@ -0,0 +1,55 @@ +--source include/have_innodb.inc +# +# one UPDATE not changing result set and SELECT ... FOR UPDATE +# +SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; +grant usage on test.* to mysqltest@localhost; +connect (thread1, localhost, mysqltest,,); +connection thread1; + +create table t1(eta int(11) not null, tipo int(11), c varchar(255)) engine=innodb; +insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); +insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); +insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); +insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); +insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); +insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); +insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); +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"); +connect (thread2, localhost, mysqltest,,); +connection thread2; +--echo +--echo ** Starting new transaction on thread 2. +begin; +--echo ** Starting SELECT .. FOR UPDATE +select * from t1 where tipo=2 FOR UPDATE; +connection thread1; +--echo +--echo ** Starting new transaction on thread 1 +begin; +--echo ** Updating single row using a table scan. This will time out because of ongoing transaction on thread 1 holding lock on one element in the scan. +--error 1205 +update t1 set tipo=11 where tipo=22; +--echo +--echo ** After the time out the transaction is aborted; no rows should have changed. +select * from t1; +connection thread2; +--echo +--echo ** The same thing should hold true for the transaction on thread 2 +select * from t1; +send commit; +connection thread1; +commit; +connection thread2; +--echo ** Even after committing (thread2): +reap; +select * from t1; +connection thread1; +--echo +--echo ** (thread1): +select * from t1; +connection default; +drop table t1;