From: Date: June 17 2008 5:41pm Subject: bzr commit into mysql-5.1 branch (ingo.struewing:2656) Bug#21579 List-Archive: http://lists.mysql.com/commits/48018 X-Bug: 21579 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit #At file:///home2/mydev/bzrroot/mysql-5.1-amain/ 2656 Ingo Struewing 2008-06-17 Bug#21579 - innodb_concurrent random failures with varying differences Not to be pushed. Just an experiment showing how one could fix it. Added comments, beautified tests (to my taste). Added --innodb_lock_wait_timeout=1 to make the test faster. Added conditional execution based on $expect_timeout. Removed unneeded get_lock()/Release_lock() calls. added: mysql-test/r/concurrent_innodb_safelog.result mysql-test/t/concurrent_innodb_safelog-master.opt mysql-test/t/concurrent_innodb_safelog.test modified: mysql-test/include/concurrent.inc mysql-test/r/concurrent_innodb.result mysql-test/t/concurrent_innodb-master.opt mysql-test/t/concurrent_innodb.test per-file messages: mysql-test/include/concurrent.inc Bug#21579 - innodb_concurrent random failures with varying differences Added comments, beautified tests (to my taste). Added conditional execution based on $expect_timeout. Removed unneeded get_lock()/Release_lock() calls. mysql-test/r/concurrent_innodb.result Bug#21579 - innodb_concurrent random failures with varying differences Fixed result. mysql-test/r/concurrent_innodb_safelog.result Bug#21579 - innodb_concurrent random failures with varying differences New result file. mysql-test/t/concurrent_innodb-master.opt Bug#21579 - innodb_concurrent random failures with varying differences Added --innodb_lock_wait_timeout=1 to make the test faster. mysql-test/t/concurrent_innodb.test Bug#21579 - innodb_concurrent random failures with varying differences Added comments. Added setting of $expect_timeout for conditional execution. mysql-test/t/concurrent_innodb_safelog-master.opt Bug#21579 - innodb_concurrent random failures with varying differences Added new file. mysql-test/t/concurrent_innodb_safelog.test Bug#21579 - innodb_concurrent random failures with varying differences Added new test case. === 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-17 15:40:52 +0000 @@ -10,6 +10,9 @@ # $engine_type -- storage engine to be tested # has to be set before sourcing this script. # +# GLOBAL tx_isolation must be REPEATABLE READ +# GLOBAL innodb_locks_unsafe_for_binlog must be 1 +# # Last update: # 2006-08-02 ML test refactored # old name was t/innodb_concurrent.test @@ -21,10 +24,30 @@ eval SET SESSION STORAGE_ENGINE = $engin --disable_warnings drop table if exists t1; --enable_warnings + +# +# Show settings of important system variables. +# +SELECT @@global.tx_isolation; +SELECT @@global.innodb_locks_unsafe_for_binlog; + +# +# If you want to see the locks with SHOW INNODB STATUS below, uncomment: +# +#CREATE TABLE innodb_lock_monitor (c1 INT) ENGINE=InnoDB; + +# +# Set up privileges and remove user level locks, if exist. +# +GRANT USAGE ON test.* TO mysqltest@localhost; +DO release_lock("hello"); +DO release_lock("hello2"); + + +--echo # +--echo # two UPDATE's running and both changing distinct result sets +--echo # create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -connect (thread1, localhost, mysqltest,,); -connection thread1; -eval SET SESSION STORAGE_ENGINE = $engine_type; insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); @@ -36,38 +59,71 @@ insert into t1 values (60,2,"hhhhhhhhhhh 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); -connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send update t1 set eta=1+get_lock("hello",1)*0 where tipo=11; -sleep 1; -connection thread1; -begin; -update t1 set eta=2 where tipo=22; -select release_lock("hello"); -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -select * from t1; -commit; -select * from t1; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; +# + --echo # + --echo # connection thread1 + connect (thread1, localhost, mysqltest,,); + connection thread1; + select get_lock("hello",10); +# + --echo # + --echo # connection thread2 + connect (thread2, localhost, mysqltest,,); + connection thread2; + begin; + send update t1 set eta=1+get_lock("hello",10)*0 where tipo=11; + sleep 1; +# + --echo # + --echo # connection thread1 + connection thread1; + begin; + if ($expect_timeout) + { + --error ER_LOCK_WAIT_TIMEOUT + update t1 set eta=2 where tipo=22; + } + if (!$expect_timeout) + { + update t1 set eta=2 where tipo=22; + } + select release_lock("hello"); + select * from t1; +# + --echo # + --echo # connection thread2 + connection thread2; + reap; + select * from t1; + commit; +# + --echo # + --echo # connection thread1 + connection thread1; + select * from t1; + commit; + select * from t1; +# + --echo # + --echo # connection thread2 + connection thread2; + select * from t1; + select release_lock("hello"); +# + --echo # + --echo # connection thread1 + connection thread1; + select * from t1; +# +--echo # +--echo # connection default connection default; drop table t1; -# -# two UPDATE's running and one changing result set -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; + +--echo # +--echo # two UPDATE's running and one changing result set +--echo # 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"); @@ -80,69 +136,112 @@ insert into t1 values (60,2,"hhhhhhhhhhh 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 update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; -sleep 1; -connection thread1; -begin; -update t1 set tipo=1 where tipo=2; -select release_lock("hello"); -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -select * from t1; -commit; -select * from t1; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; +# + --echo # + --echo # connection thread1 + #connect (thread1, localhost, mysqltest,,); + connection thread1; + select get_lock("hello",10); +# + --echo # + --echo # connection thread2 + #connect (thread2, localhost, mysqltest,,); + connection thread2; + begin; + send update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; + sleep 1; +# + --echo # + --echo # connection thread1 + connection thread1; + begin; + if ($expect_timeout) + { + --error ER_LOCK_WAIT_TIMEOUT + update t1 set tipo=1 where tipo=2; + } + if (!$expect_timeout) + { + update t1 set tipo=1 where tipo=2; + } + select release_lock("hello"); + select * from t1; +# + --echo # + --echo # connection thread2 + connection thread2; + reap; + select * from t1; + commit; +# + --echo # + --echo # connection thread1 + connection thread1; + select * from t1; + commit; + select * from t1; +# + --echo # + --echo # connection thread2 + connection thread2; + select * from t1; + select release_lock("hello"); +# + --echo # + --echo # connection thread1 + connection thread1; + select * from t1; +# +--echo # +--echo # connection default connection default; drop table t1; -# -# One UPDATE and one INSERT .... Monty's test -# - -#connect (thread1, localhost, mysqltest,,); -connection thread1; +--echo # +--echo # One UPDATE and one INSERT .... Monty's test +--echo # create table t1 (a int not null, b int not null); insert into t1 values (1,1),(2,1),(3,1),(4,1); -select get_lock("hello2",1000); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send update t1 set b=10+get_lock(concat("hello",a),1000)*0 where -a=2; -sleep 1; -connection thread1; -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; +# + --echo # + --echo # connection thread1 + #connect (thread1, localhost, mysqltest,,); + connection thread1; + select get_lock("hello2",10); +# + --echo # + --echo # connection thread2 + #connect (thread2, localhost, mysqltest,,); + connection thread2; + begin; + send update t1 set b=10+get_lock(concat("hello",a),10)*0 where a=2; + sleep 1; +# + --echo # + --echo # connection thread1 + connection thread1; + insert into t1 values (1,1); + select release_lock("hello2"); + select * from t1; +# + --echo # + --echo # connection thread2 + connection thread2; + reap; + select * from t1; + commit; + select release_lock("hello2"); +# +--echo # +--echo # connection default connection default; drop table t1; -# -# one UPDATE changing result set and SELECT ... FOR UPDATE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; + +--echo # +--echo # one UPDATE changing result set and SELECT ... FOR UPDATE +--echo # 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"); @@ -155,37 +254,52 @@ insert into t1 values (60,2,"hhhhhhhhhhh 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; +# + --echo # + --echo # connection thread2 + #connect (thread2, localhost, mysqltest,,); + connection thread2; + begin; + select * from t1 where tipo=2 FOR UPDATE; +# + --echo # + --echo # connection thread1 + connection thread1; + begin; + --error 1205 + update t1 set tipo=1 where tipo=2; + select * from t1; +# + --echo # + --echo # connection thread2 + connection thread2; + select * from t1; + commit; +# + --echo # + --echo # connection thread1 + connection thread1; + commit; +# + --echo # + --echo # connection thread2 + connection thread2; + select * from t1; +# + --echo # + --echo # connection thread1 + connection thread1; + select * from t1; +# +--echo # +--echo # connection default connection default; drop table t1; -# -# one UPDATE not changing result set and SELECT ... FOR UPDATE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; + +--echo # +--echo # one UPDATE not changing result set and SELECT ... FOR UPDATE +--echo # 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"); @@ -198,37 +312,67 @@ insert into t1 values (60,2,"hhhhhhhhhhh 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; +# + --echo # + --echo # connection thread2 + #connect (thread2, localhost, mysqltest,,); + connection thread2; + begin; + select * from t1 where tipo=2 FOR UPDATE; +## +## If you want to see the locks with SHOW INNODB STATUS below, uncomment +## the "CREATE TABLE innodb_lock_monitor" statement at top of this file. +## +#--echo # +#--echo # connection default +#connection default; +#SHOW ENGINE INNODB STATUS; +# + --echo # + --echo # connection thread1 + connection thread1; + begin; + if ($expect_timeout) + { + --error ER_LOCK_WAIT_TIMEOUT + update t1 set tipo=11 where tipo=22; + } + if (!$expect_timeout) + { + update t1 set tipo=11 where tipo=22; + } + select * from t1; +# + --echo # + --echo # connection thread2 + connection thread2; + select * from t1; + commit; +# + --echo # + --echo # connection thread1 + connection thread1; + commit; +# + --echo # + --echo # connection thread2 + connection thread2; + select * from t1; +# + --echo # + --echo # connection thread1 + connection thread1; + select * from t1; +# +--echo # +--echo # connection default connection default; drop table t1; -# -# two SELECT ... FOR UPDATE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; + +--echo # +--echo # two SELECT ... FOR UPDATE +--echo # 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"); @@ -241,37 +385,52 @@ insert into t1 values (60,2,"hhhhhhhhhhh 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 -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; +# + --echo # + --echo # connection thread2 + #connect (thread2, localhost, mysqltest,,); + connection thread2; + begin; + select * from t1 where tipo=2 FOR UPDATE; +# + --echo # + --echo # connection thread1 + connection thread1; + begin; + --error 1205 + select * from t1 where tipo=1 FOR UPDATE; +# + --echo # + --echo # connection thread2 + connection thread2; + select * from t1; + send commit; +# + --echo # + --echo # connection thread1 + connection thread1; + commit; +# + --echo # + --echo # connection thread2 + connection thread2; + reap; + select * from t1; +# + --echo # + --echo # connection thread1 + connection thread1; + select * from t1; +# +--echo # +--echo # connection default connection default; drop table t1; -# -# one UPDATE changing result set and DELETE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; +--echo # +--echo # one UPDATE changing result set and DELETE +--echo # 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"); @@ -284,38 +443,52 @@ insert into t1 values (60,2,"hhhhhhhhhhh 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; +# + --echo # + --echo # connection thread2 + #connect (thread2, localhost, mysqltest,,); + connection thread2; + begin; + delete from t1 where tipo=2; +# + --echo # + --echo # connection thread1 + connection thread1; + begin; + --error 1205 + update t1 set tipo=1 where tipo=2; + select * from t1; +# + --echo # + --echo # connection thread2 + connection thread2; + select * from t1; + commit; +# + --echo # + --echo # connection thread1 + connection thread1; + commit; +# + --echo # + --echo # connection thread2 + connection thread2; + select * from t1; +# + --echo # + --echo # connection thread1 + connection thread1; + select * from t1; +# +--echo # +--echo # connection default connection default; drop table t1; -# -# one UPDATE not changing result set and DELETE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; +--echo # +--echo # one UPDATE not changing result set and DELETE +--echo # 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"); @@ -328,30 +501,57 @@ insert into t1 values (60,2,"hhhhhhhhhhh 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; +# + --echo # + --echo # connection thread2 + #connect (thread2, localhost, mysqltest,,); + connection thread2; + begin; + delete from t1 where tipo=2; +# + --echo # + --echo # connection thread1 + connection thread1; + begin; + if ($expect_timeout) + { + --error ER_LOCK_WAIT_TIMEOUT + update t1 set tipo=1 where tipo=22; + } + if (!$expect_timeout) + { + update t1 set tipo=1 where tipo=22; + } + select * from t1; +# + --echo # + --echo # connection thread2 + connection thread2; + select * from t1; + commit; +# + --echo # + --echo # connection thread1 + connection thread1; + commit; +# + --echo # + --echo # connection thread2 + connection thread2; + select * from t1; +# + --echo # + --echo # connection thread1 + connection thread1; + select * from t1; +# +--echo # +--echo # connection default connection default; -sleep 1; drop table t1; + +# +# Cleanup +# disconnect thread1; disconnect thread2; === 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-17 15:40:52 +0000 @@ -1,7 +1,18 @@ SET SESSION STORAGE_ENGINE = InnoDB; drop table if exists t1; +SELECT @@global.tx_isolation; +@@global.tx_isolation +REPEATABLE-READ +SELECT @@global.innodb_locks_unsafe_for_binlog; +@@global.innodb_locks_unsafe_for_binlog +1 +GRANT USAGE ON test.* TO mysqltest@localhost; +DO release_lock("hello"); +DO release_lock("hello2"); +# +# two UPDATE's running and both changing distinct result sets +# create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -SET SESSION STORAGE_ENGINE = InnoDB; insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); @@ -13,11 +24,17 @@ insert into t1 values (60,2,"hhhhhhhhhhh 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) +# +# connection thread1 +select get_lock("hello",10); +get_lock("hello",10) 1 +# +# connection thread2 begin; - update t1 set eta=1+get_lock("hello",1)*0 where tipo=11; +update t1 set eta=1+get_lock("hello",10)*0 where tipo=11; +# +# connection thread1 begin; update t1 set eta=2 where tipo=22; select release_lock("hello"); @@ -36,6 +53,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -49,7 +68,9 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; +commit; +# +# connection thread1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -77,6 +98,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -90,6 +113,11 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +select release_lock("hello"); +release_lock("hello") +1 +# +# connection thread1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -103,7 +131,12 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection default 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 +149,22 @@ insert into t1 values (60,2,"hhhhhhhhhhh insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); +# +# connection thread1 select get_lock("hello",10); get_lock("hello",10) -0 +1 +# +# connection thread2 begin; - update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; +update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; +# +# connection thread1 begin; update t1 set tipo=1 where tipo=2; select release_lock("hello"); release_lock("hello") -0 +1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -139,6 +178,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -152,7 +193,9 @@ eta tipo c 1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; +commit; +# +# connection thread1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -171,15 +214,17 @@ 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 +# +# connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -193,6 +238,11 @@ eta tipo c 1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +select release_lock("hello"); +release_lock("hello") +1 +# +# connection thread1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -206,15 +256,25 @@ eta tipo c 1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection default 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); -select get_lock("hello2",1000); -get_lock("hello2",1000) -1 -begin; - update t1 set b=10+get_lock(concat("hello",a),1000)*0 where -a=2; +# +# connection thread1 +select get_lock("hello2",10); +get_lock("hello2",10) +1 +# +# connection thread2 +begin; +update t1 set b=10+get_lock(concat("hello",a),10)*0 where a=2; +# +# connection thread1 insert into t1 values (1,1); select release_lock("hello2"); release_lock("hello2") @@ -226,6 +286,8 @@ a b 3 1 4 1 1 1 +# +# connection thread2 select * from t1; a b 1 1 @@ -233,8 +295,16 @@ a b 3 1 4 1 1 1 - commit; +commit; +select release_lock("hello2"); +release_lock("hello2") +1 +# +# connection default 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"); @@ -247,16 +317,18 @@ insert into t1 values (60,2,"hhhhhhhhhhh 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 +# +# connection thread2 begin; - select * from t1 where tipo=2 FOR UPDATE; +select * from t1 where tipo=2 FOR UPDATE; +eta tipo c +20 2 ddddddddddddddddddddddddddddddddddddddddddd +40 2 fffffffffffffffffffffffffffffffffffffffffff +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +# +# connection thread1 begin; -select release_lock("hello"); -release_lock("hello") -1 -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; +update t1 set tipo=1 where tipo=2; ERROR HY000: Lock wait timeout exceeded; try restarting transaction select * from t1; eta tipo c @@ -271,10 +343,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -eta tipo c -20 2 ddddddddddddddddddddddddddddddddddddddddddd -40 2 fffffffffffffffffffffffffffffffffffffffffff -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +# +# connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -288,8 +358,12 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; commit; +# +# connection thread1 +commit; +# +# connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -303,6 +377,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection thread1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -316,7 +392,12 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection default drop table t1; +# +# one UPDATE not 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"); @@ -329,17 +410,18 @@ insert into t1 values (60,2,"hhhhhhhhhhh 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 +# +# connection thread2 begin; - select * from t1 where tipo=2 FOR UPDATE; +select * from t1 where tipo=2 FOR UPDATE; +eta tipo c +20 2 ddddddddddddddddddddddddddddddddddddddddddd +40 2 fffffffffffffffffffffffffffffffffffffffffff +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +# +# connection thread1 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 +update t1 set tipo=11 where tipo=22; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -351,12 +433,10 @@ eta tipo c 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +80 11 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -eta tipo c -20 2 ddddddddddddddddddddddddddddddddddddddddddd -40 2 fffffffffffffffffffffffffffffffffffffffffff -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +# +# connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -370,8 +450,12 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; commit; +# +# connection thread1 +commit; +# +# connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -383,8 +467,10 @@ eta tipo c 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +80 11 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection thread1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -396,9 +482,14 @@ eta tipo c 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +80 11 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection default 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"); @@ -411,21 +502,21 @@ insert into t1 values (60,2,"hhhhhhhhhhh 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; +# +# connection thread2 begin; -select release_lock("hello"); -release_lock("hello") -1 -select * from t1 where tipo=1 FOR UPDATE; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction +select * from t1 where tipo=2 FOR UPDATE; eta tipo c 20 2 ddddddddddddddddddddddddddddddddddddddddddd 40 2 fffffffffffffffffffffffffffffffffffffffffff 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +# +# connection thread1 +begin; +select * from t1 where tipo=1 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +# +# connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -439,8 +530,12 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; commit; +# +# connection thread1 +commit; +# +# connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -454,6 +549,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection thread1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -467,7 +564,12 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection default drop table t1; +# +# one UPDATE changing result set and DELETE +# 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"); @@ -480,16 +582,14 @@ insert into t1 values (60,2,"hhhhhhhhhhh 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 +# +# connection thread2 begin; - delete from t1 where tipo=2; +delete from t1 where tipo=2; +# +# connection thread1 begin; -select release_lock("hello"); -release_lock("hello") -1 -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; +update t1 set tipo=1 where tipo=2; ERROR HY000: Lock wait timeout exceeded; try restarting transaction select * from t1; eta tipo c @@ -504,6 +604,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -514,8 +616,12 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; commit; +# +# connection thread1 +commit; +# +# connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -526,6 +632,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection thread1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -536,7 +644,12 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection default drop table t1; +# +# one UPDATE not changing result set and DELETE +# 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"); @@ -549,16 +662,14 @@ insert into t1 values (60,2,"hhhhhhhhhhh 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 +# +# connection thread2 begin; - delete from t1 where tipo=2; +delete from t1 where tipo=2; +# +# connection thread1 begin; -select release_lock("hello"); -release_lock("hello") -1 -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=22; +update t1 set tipo=1 where tipo=22; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -572,6 +683,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -582,8 +695,12 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; commit; +# +# connection thread1 +commit; +# +# connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -594,6 +711,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection thread1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -604,4 +723,6 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection default drop table t1; === added file 'mysql-test/r/concurrent_innodb_safelog.result' --- a/mysql-test/r/concurrent_innodb_safelog.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/r/concurrent_innodb_safelog.result 2008-06-17 15:40:52 +0000 @@ -0,0 +1,732 @@ +SET SESSION STORAGE_ENGINE = InnoDB; +drop table if exists t1; +SELECT @@global.tx_isolation; +@@global.tx_isolation +REPEATABLE-READ +SELECT @@global.innodb_locks_unsafe_for_binlog; +@@global.innodb_locks_unsafe_for_binlog +0 +GRANT USAGE ON test.* TO mysqltest@localhost; +DO release_lock("hello"); +DO release_lock("hello2"); +# +# two UPDATE's running and both changing distinct result sets +# +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"); +# +# connection thread1 +select get_lock("hello",10); +get_lock("hello",10) +1 +# +# connection thread2 +begin; +update t1 set eta=1+get_lock("hello",10)*0 where tipo=11; +# +# connection thread1 +begin; +update t1 set eta=2 where tipo=22; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +select release_lock("hello"); +release_lock("hello") +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 +# +# connection 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 +1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +commit; +# +# connection 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 +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 +1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection 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 +1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +select release_lock("hello"); +release_lock("hello") +1 +# +# connection 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 +1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection default +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"); +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"); +# +# connection thread1 +select get_lock("hello",10); +get_lock("hello",10) +1 +# +# connection thread2 +begin; +update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; +# +# connection thread1 +begin; +update t1 set tipo=1 where tipo=2; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +select release_lock("hello"); +release_lock("hello") +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 +# +# connection thread2 +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +1 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +1 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +1 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +commit; +# +# connection 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 +commit; +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +1 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +1 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +1 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection thread2 +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +1 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +1 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +1 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +select release_lock("hello"); +release_lock("hello") +1 +# +# connection thread1 +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +1 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +1 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +1 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +# +# connection default +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); +# +# connection thread1 +select get_lock("hello2",10); +get_lock("hello2",10) +1 +# +# connection thread2 +begin; +update t1 set b=10+get_lock(concat("hello",a),10)*0 where a=2; +# +# connection thread1 +insert into t1 values (1,1); +select release_lock("hello2"); +release_lock("hello2") +1 +select * from t1; +a b +1 1 +2 1 +3 1 +4 1 +1 1 +# +# connection thread2 +select * from t1; +a b +1 1 +2 10 +3 1 +4 1 +1 1 +commit; +select release_lock("hello2"); +release_lock("hello2") +1 +# +# connection default +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"); +# +# connection thread2 +begin; +select * from t1 where tipo=2 FOR UPDATE; +eta tipo c +20 2 ddddddddddddddddddddddddddddddddddddddddddd +40 2 fffffffffffffffffffffffffffffffffffffffffff +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +# +# connection thread1 +begin; +update t1 set tipo=1 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 +# +# connection 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 +commit; +# +# connection thread1 +commit; +# +# connection 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 +# +# connection 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 +# +# connection default +drop table t1; +# +# one UPDATE not 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"); +# +# connection thread2 +begin; +select * from t1 where tipo=2 FOR UPDATE; +eta tipo c +20 2 ddddddddddddddddddddddddddddddddddddddddddd +40 2 fffffffffffffffffffffffffffffffffffffffffff +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +# +# connection thread1 +begin; +update t1 set tipo=11 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 +# +# connection 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 +commit; +# +# connection thread1 +commit; +# +# connection 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 +# +# connection 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 +# +# connection default +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"); +# +# connection thread2 +begin; +select * from t1 where tipo=2 FOR UPDATE; +eta tipo c +20 2 ddddddddddddddddddddddddddddddddddddddddddd +40 2 fffffffffffffffffffffffffffffffffffffffffff +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +# +# connection thread1 +begin; +select * from t1 where tipo=1 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +# +# connection 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 +commit; +# +# connection thread1 +commit; +# +# connection 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 +# +# connection 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 +# +# connection default +drop table t1; +# +# one UPDATE changing result set and DELETE +# +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"); +# +# connection thread2 +begin; +delete from t1 where tipo=2; +# +# connection thread1 +begin; +update t1 set tipo=1 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 +# +# connection thread2 +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; +# +# connection thread1 +commit; +# +# connection thread2 +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 +# +# connection thread1 +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 +# +# connection default +drop table t1; +# +# one UPDATE not changing result set and DELETE +# +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"); +# +# connection thread2 +begin; +delete from t1 where tipo=2; +# +# connection thread1 +begin; +update t1 set tipo=1 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 +# +# connection thread2 +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; +# +# connection thread1 +commit; +# +# connection thread2 +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 +# +# connection thread1 +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 +# +# connection default +drop table t1; === modified file 'mysql-test/t/concurrent_innodb-master.opt' --- a/mysql-test/t/concurrent_innodb-master.opt 2006-08-16 12:58:49 +0000 +++ b/mysql-test/t/concurrent_innodb-master.opt 2008-06-17 15:40:52 +0000 @@ -1 +1,2 @@ --innodb_locks_unsafe_for_binlog +--innodb_lock_wait_timeout=1 === modified file 'mysql-test/t/concurrent_innodb.test' --- a/mysql-test/t/concurrent_innodb.test 2006-08-16 12:58:49 +0000 +++ b/mysql-test/t/concurrent_innodb.test 2008-06-17 15:40:52 +0000 @@ -6,6 +6,9 @@ # # two non-interfering UPDATE's not changing result set # +# GLOBAL tx_isolation must be REPEATABLE READ +# GLOBAL innodb_locks_unsafe_for_binlog must be 1 +# # Last update: # 2006-07-26 ML test refactored (MySQL 5.1) # main code t/innodb_concurrent.test -> include/concurrent.inc @@ -17,4 +20,9 @@ --source include/have_innodb.inc let $engine_type= InnoDB; +# +# Several statements will not time out as we have less locks +# with innodb_locks_unsafe_for_binlog set. +let $expect_timeout= 0; + --source include/concurrent.inc === added file 'mysql-test/t/concurrent_innodb_safelog-master.opt' --- a/mysql-test/t/concurrent_innodb_safelog-master.opt 1970-01-01 00:00:00 +0000 +++ b/mysql-test/t/concurrent_innodb_safelog-master.opt 2008-06-17 15:40:52 +0000 @@ -0,0 +1,2 @@ +--skip-innodb_locks_unsafe_for_binlog +--innodb_lock_wait_timeout=1 === added file 'mysql-test/t/concurrent_innodb_safelog.test' --- a/mysql-test/t/concurrent_innodb_safelog.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/t/concurrent_innodb_safelog.test 2008-06-17 15:40:52 +0000 @@ -0,0 +1,28 @@ +# t/concurrent_innodb.test +# +# Concurrent InnoDB tests, mainly in UPDATE's +# Bug#3300 +# Designed and tested by Sinisa Milivojevic, sinisa@stripped +# +# two non-interfering UPDATE's not changing result set +# +# GLOBAL tx_isolation must be REPEATABLE READ +# GLOBAL innodb_locks_unsafe_for_binlog must be 0 +# +# Last update: +# 2006-07-26 ML test refactored (MySQL 5.1) +# main code t/innodb_concurrent.test -> include/concurrent.inc +# new wrapper t/concurrent_innodb.test + +# test takes circa 5 minutes to run, so it's big +--source include/big_test.inc + +--source include/have_innodb.inc +let $engine_type= InnoDB; + +# +# Several statements will time out as we have more locks +# with innodb_locks_unsafe_for_binlog not set. +let $expect_timeout= 1; + +--source include/concurrent.inc