EVOLUTION-MANAGER

Edit File: concurrent_innodb_safelog.result

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; connection default; SET SQL_MODE=""; SELECT @@global.tx_isolation; @@global.tx_isolation REPEATABLE-READ SELECT @@global.innodb_locks_unsafe_for_binlog; @@global.innodb_locks_unsafe_for_binlog 0 # keep_locks == 1 GRANT USAGE ON test.* TO mysqltest@localhost; drop table if exists t1; ** ** two UPDATE's running and both changing distinct result sets ** connect thread1, localhost, mysqltest,,; connection thread1; ** Set up table SET SESSION STORAGE_ENGINE = InnoDB; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); 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 if innodb_locks_unsafe_for_binlog is set. 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 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 if innodb_locks_unsafe_for_binlog is set. 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 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 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 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 if innodb_locks_unsafe_for_binlog is set. 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 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 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 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 if innodb_locks_unsafe_for_binlog is set. 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 ** Cleanup connection thread1; disconnect thread1; connection thread2; disconnect thread2; connection default; drop table t1; drop user mysqltest@localhost; SET SQL_MODE=default;