| List: | Commits | « Previous MessageNext Message » | |
| From: | Ingo Struewing | Date: | June 17 2008 5:41pm |
| Subject: | bzr commit into mysql-5.1 branch (ingo.struewing:2656) Bug#21579 | ||
| View as plain text | |||
#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
| Thread | ||
|---|---|---|
| • bzr commit into mysql-5.1 branch (ingo.struewing:2656) Bug#21579 | Ingo Struewing | 17 Jun |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 1.095 sec. using MySQL 5.1.14-beta-log