summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/concurrent_innodb_safelog.result
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/concurrent_innodb_safelog.result
parentInitial commit. (diff)
downloadmariadb-upstream.tar.xz
mariadb-upstream.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/concurrent_innodb_safelog.result')
-rw-r--r--mysql-test/main/concurrent_innodb_safelog.result796
1 files changed, 796 insertions, 0 deletions
diff --git a/mysql-test/main/concurrent_innodb_safelog.result b/mysql-test/main/concurrent_innodb_safelog.result
new file mode 100644
index 00000000..40f89c7c
--- /dev/null
+++ b/mysql-test/main/concurrent_innodb_safelog.result
@@ -0,0 +1,796 @@
+SET @save_timeout = @@GLOBAL.innodb_lock_wait_timeout;
+SET GLOBAL innodb_lock_wait_timeout = 1;
+SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+connection default;
+SET SQL_MODE="";
+SELECT @@global.tx_isolation;
+@@global.tx_isolation
+REPEATABLE-READ
+# keep_locks == 1
+GRANT ALL ON test.* TO mysqltest@localhost;
+
+**
+** two UPDATE's running and both changing distinct result sets
+**
+connect thread1, localhost, mysqltest,,;
+connection thread1;
+** Set up table
+SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB;
+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");
+** Get user level lock (ULL) for thread 1
+select get_lock("hello",10);
+get_lock("hello",10)
+1
+connect thread2, localhost, mysqltest,,;
+connection thread2;
+** Start transaction for thread 2
+begin;
+** Update will cause a table scan and a new ULL will
+** be created and blocked on the first row where tipo=11.
+update t1 set eta=1+get_lock("hello",10)*0 where tipo=11;
+connection thread1;
+** Start new transaction for thread 1
+begin;
+** Update on t1 will cause a table scan which will be blocked because
+** the previously initiated table scan applied exclusive key locks on
+** all primary keys.
+** Not so for READ UNCOMMITTED or READ COMMITTED. The locks that
+** do not match the WHERE condition are released.
+update t1 set eta=2 where tipo=22;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+** Release user level name lock from thread 1. This will cause the ULL
+** on thread 2 to end its wait.
+DO release_lock("hello");
+** Table is now updated with a new eta on tipo=22 for thread 1.
+select * from t1;
+eta tipo c
+7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
+10 1 ccccccccccccccccccccccccccccccccccccccccccc
+20 2 ddddddddddddddddddddddddddddddddddddddddddd
+30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
+40 2 fffffffffffffffffffffffffffffffffffffffffff
+50 1 ggggggggggggggggggggggggggggggggggggggggggg
+60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
+70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii
+80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj
+90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
+connection thread2;
+** Release the lock and collect result from update on thread 2
+DO release_lock("hello");
+** Table should have eta updates where tipo=11 but updates made by
+** thread 1 shouldn't be visible yet.
+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
+** Sending commit on thread 2.
+commit;
+connection thread1;
+** Make sure table reads didn't change yet on thread 1.
+select * from t1;
+eta tipo c
+7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
+10 1 ccccccccccccccccccccccccccccccccccccccccccc
+20 2 ddddddddddddddddddddddddddddddddddddddddddd
+30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
+40 2 fffffffffffffffffffffffffffffffffffffffffff
+50 1 ggggggggggggggggggggggggggggggggggggggggggg
+60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
+70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii
+80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj
+90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
+** And send final commit on thread 1.
+commit;
+** Table should now be updated by both updates in the order of
+** thread 1,2.
+select * from t1;
+eta tipo c
+7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+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;
+** Make sure the output is similar for t1.
+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 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
+**
+connection thread1;
+** Set up table
+SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB;
+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");
+** Get ULL "hello" on thread 1
+select get_lock("hello",10);
+get_lock("hello",10)
+1
+connection thread2;
+** Start transaction on thread 2
+begin;
+** Update will cause a table scan.
+** This will cause a hang on the first row where tipo=1 until the
+** blocking ULL is released.
+update t1 set eta=1+get_lock("hello",10)*0 where tipo=1;
+connection thread1;
+** Start transaction on thread 1
+begin;
+** Update on t1 will cause a table scan which will be blocked because
+** the previously initiated table scan applied exclusive key locks on
+** all primary keys.
+** Not so for READ UNCOMMITTED or READ COMMITTED. The locks that
+** do not match the WHERE condition are released.
+update t1 set tipo=1 where tipo=2;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+** Release ULL. This will release the next waiting ULL on thread 2.
+DO release_lock("hello");
+** The table should still be updated with updates for thread 1 only:
+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;
+** Release the lock and collect result from thread 2:
+DO release_lock("hello");
+** Seen from thread 2 the table should have been updated on four
+** places.
+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;
+** Thread 2 has committed but the result should remain the same for
+** thread 1 (updated on three places):
+select * from t1;
+eta tipo c
+7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+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;
+** After a commit the table should be merged with the previous
+** commit.
+** This select should show both updates:
+select * from t1;
+eta tipo c
+7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
+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
+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
+**
+connection thread1;
+** Set up table
+SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB;
+create table t1 (a int not null, b int not null);
+insert into t1 values (1,1),(2,1),(3,1),(4,1);
+** Create ULL 'hello2'
+select get_lock("hello2",10);
+get_lock("hello2",10)
+1
+connection thread2;
+** Begin a new transaction on thread 2
+begin;
+** Update will create a table scan which creates a ULL where a=2;
+** this will hang waiting on thread 1.
+update t1 set b=10+get_lock(concat("hello",a),10)*0 where a=2;
+connection thread1;
+** Insert new values to t1 from thread 1; this created an implicit
+** commit since there are no on-going transactions.
+insert into t1 values (1,1);
+** Release the ULL (thread 2 updates will finish).
+DO release_lock("hello2");
+** ..but thread 1 will still see t1 as if nothing has happend:
+select * from t1;
+a b
+1 1
+2 1
+3 1
+4 1
+1 1
+connection thread2;
+** Collect results from thread 2 and release the lock.
+DO release_lock("hello2");
+** The table should look like the original+updates for thread 2,
+** and consist of new rows:
+select * from t1;
+a b
+1 1
+2 10
+3 1
+4 1
+1 1
+** Commit changes from thread 2
+commit;
+connection default;
+drop table t1;
+
+**
+** one UPDATE changing result set and SELECT ... FOR UPDATE
+**
+connection thread1;
+** Set up table
+SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB;
+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 a new transaction on thread 2
+begin;
+** Select a range for update.
+select * from t1 where tipo=2 FOR UPDATE;
+eta tipo c
+20 2 ddddddddddddddddddddddddddddddddddddddddddd
+40 2 fffffffffffffffffffffffffffffffffffffffffff
+60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
+connection thread1;
+** Begin a new transaction on thread 1
+begin;
+** Update the same range which is marked for update on thread 2; this
+** will hang because of row locks.
+update t1 set tipo=1 where tipo=2;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+** After the update the table will be unmodified because the previous
+** transaction failed and was rolled back.
+select * from t1;
+eta tipo c
+7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
+10 1 ccccccccccccccccccccccccccccccccccccccccccc
+20 2 ddddddddddddddddddddddddddddddddddddddddddd
+30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
+40 2 fffffffffffffffffffffffffffffffffffffffffff
+50 1 ggggggggggggggggggggggggggggggggggggggggggg
+60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
+70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii
+80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj
+90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
+connection thread2;
+** The table should look unmodified from thread 2.
+select * from t1;
+eta tipo c
+7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
+10 1 ccccccccccccccccccccccccccccccccccccccccccc
+20 2 ddddddddddddddddddddddddddddddddddddddddddd
+30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
+40 2 fffffffffffffffffffffffffffffffffffffffffff
+50 1 ggggggggggggggggggggggggggggggggggggggggggg
+60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
+70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii
+80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj
+90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
+** Sending a commit should release the row locks and enable
+** thread 1 to complete the transaction.
+commit;
+connection thread1;
+** Commit on thread 1.
+commit;
+connection thread2;
+** The table should not have been changed.
+select * from t1;
+eta tipo c
+7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
+10 1 ccccccccccccccccccccccccccccccccccccccccccc
+20 2 ddddddddddddddddddddddddddddddddddddddddddd
+30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
+40 2 fffffffffffffffffffffffffffffffffffffffffff
+50 1 ggggggggggggggggggggggggggggggggggggggggggg
+60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
+70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii
+80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj
+90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
+connection thread1;
+** Even on thread 1:
+select * from t1;
+eta tipo c
+7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
+10 1 ccccccccccccccccccccccccccccccccccccccccccc
+20 2 ddddddddddddddddddddddddddddddddddddddddddd
+30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
+40 2 fffffffffffffffffffffffffffffffffffffffffff
+50 1 ggggggggggggggggggggggggggggggggggggggggggg
+60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
+70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii
+80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj
+90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
+connection default;
+drop table t1;
+
+**
+** one UPDATE not changing result set and SELECT ... FOR UPDATE
+**
+connection thread1;
+** Set up table
+SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB;
+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;
+** Starting new transaction on thread 2.
+begin;
+** Starting SELECT .. FOR UPDATE
+select * from t1 where tipo=2 FOR UPDATE;
+eta tipo c
+20 2 ddddddddddddddddddddddddddddddddddddddddddd
+40 2 fffffffffffffffffffffffffffffffffffffffffff
+60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
+connection thread1;
+
+** Starting new transaction on thread 1
+begin;
+** Updating single row using a table scan. This will time out
+** because of ongoing transaction on thread 1 holding lock on
+** all primary keys in the scan.
+** Not so for READ UNCOMMITTED or READ COMMITTED. The locks that
+** do not match the WHERE condition are released.
+update t1 set tipo=11 where tipo=22;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+** After the time out the transaction is aborted; no rows should
+** have changed.
+select * from t1;
+eta tipo c
+7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
+10 1 ccccccccccccccccccccccccccccccccccccccccccc
+20 2 ddddddddddddddddddddddddddddddddddddddddddd
+30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
+40 2 fffffffffffffffffffffffffffffffffffffffffff
+50 1 ggggggggggggggggggggggggggggggggggggggggggg
+60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
+70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii
+80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj
+90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
+connection thread2;
+** The same thing should hold true for the transaction on
+** thread 2
+select * from t1;
+eta tipo c
+7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
+10 1 ccccccccccccccccccccccccccccccccccccccccccc
+20 2 ddddddddddddddddddddddddddddddddddddddddddd
+30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
+40 2 fffffffffffffffffffffffffffffffffffffffffff
+50 1 ggggggggggggggggggggggggggggggggggggggggggg
+60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
+70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii
+80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj
+90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
+commit;
+connection thread1;
+commit;
+connection thread2;
+** Even after committing:
+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
+**
+connection thread1;
+** Set up table
+SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB;
+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 a new transaction on thread 2
+begin;
+select * from t1 where tipo=2 FOR UPDATE;
+eta tipo c
+20 2 ddddddddddddddddddddddddddddddddddddddddddd
+40 2 fffffffffffffffffffffffffffffffffffffffffff
+60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
+connection thread1;
+** Begin a new transaction on thread 1
+begin;
+** Selecting a range for update by table scan will be blocked
+** because of on-going transaction on thread 2.
+select * from t1 where tipo=1 FOR UPDATE;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+connection thread2;
+** Table will be unchanged and the select command will not be
+** blocked:
+select * from t1;
+eta tipo c
+7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
+10 1 ccccccccccccccccccccccccccccccccccccccccccc
+20 2 ddddddddddddddddddddddddddddddddddddddddddd
+30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
+40 2 fffffffffffffffffffffffffffffffffffffffffff
+50 1 ggggggggggggggggggggggggggggggggggggggggggg
+60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
+70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii
+80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj
+90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
+** Commit transacton on thread 2.
+commit;
+connection thread1;
+** Commit transaction on thread 1.
+commit;
+connection thread2;
+** Make sure table isn't blocked on thread 2:
+select * from t1;
+eta tipo c
+7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
+10 1 ccccccccccccccccccccccccccccccccccccccccccc
+20 2 ddddddddddddddddddddddddddddddddddddddddddd
+30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
+40 2 fffffffffffffffffffffffffffffffffffffffffff
+50 1 ggggggggggggggggggggggggggggggggggggggggggg
+60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
+70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii
+80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj
+90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
+connection thread1;
+** Make sure table isn't blocked on thread 1:
+select * from t1;
+eta tipo c
+7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
+10 1 ccccccccccccccccccccccccccccccccccccccccccc
+20 2 ddddddddddddddddddddddddddddddddddddddddddd
+30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
+40 2 fffffffffffffffffffffffffffffffffffffffffff
+50 1 ggggggggggggggggggggggggggggggggggggggggggg
+60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
+70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii
+80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj
+90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk
+connection default;
+drop table t1;
+
+**
+** one UPDATE changing result set and DELETE
+**
+connection thread1;
+** Set up table
+SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB;
+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
+**
+connection thread1;
+** Set up table
+SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB;
+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 on t1 will cause a table scan which will be blocked because
+** the previously initiated table scan applied exclusive key locks on
+** all primary keys.
+** Not so for READ UNCOMMITTED or READ COMMITTED. The locks that
+** do not match the WHERE condition are released.
+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
+disconnect thread1;
+disconnect thread2;
+connection default;
+drop table t1;
+drop user mysqltest@localhost;
+SET SQL_MODE=default;
+SET GLOBAL innodb_lock_wait_timeout = @save_timeout;