1SET @save_timeout = @@GLOBAL.innodb_lock_wait_timeout; 2SET GLOBAL innodb_lock_wait_timeout = 1; 3SET @save_isolation = @@GLOBAL.tx_isolation; 4SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 5connection default; 6SET SQL_MODE=""; 7SELECT @@global.tx_isolation; 8@@global.tx_isolation 9READ-COMMITTED 10# keep_locks == 0 11GRANT USAGE ON test.* TO mysqltest@localhost; 12 13** 14** two UPDATE's running and both changing distinct result sets 15** 16connect thread1, localhost, mysqltest,,; 17connection thread1; 18** Set up table 19SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB; 20create table t1(eta int(11) not null, tipo int(11), c varchar(255)); 21insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); 22insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); 23insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); 24insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); 25insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); 26insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); 27insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); 28insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); 29insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); 30insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); 31insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); 32** Get user level lock (ULL) for thread 1 33select get_lock("hello",10); 34get_lock("hello",10) 351 36connect thread2, localhost, mysqltest,,; 37connection thread2; 38** Start transaction for thread 2 39begin; 40** Update will cause a table scan and a new ULL will 41** be created and blocked on the first row where tipo=11. 42update t1 set eta=1+get_lock("hello",10)*0 where tipo=11; 43connection thread1; 44** Start new transaction for thread 1 45begin; 46** Update on t1 will cause a table scan which will be blocked because 47** the previously initiated table scan applied exclusive key locks on 48** all primary keys. 49** Not so for READ UNCOMMITTED or READ COMMITTED. The locks that 50** do not match the WHERE condition are released. 51update t1 set eta=2 where tipo=22; 52** Release user level name lock from thread 1. This will cause the ULL 53** on thread 2 to end its wait. 54DO release_lock("hello"); 55** Table is now updated with a new eta on tipo=22 for thread 1. 56select * from t1; 57eta tipo c 587 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 598 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 6010 1 ccccccccccccccccccccccccccccccccccccccccccc 6120 2 ddddddddddddddddddddddddddddddddddddddddddd 6230 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 6340 2 fffffffffffffffffffffffffffffffffffffffffff 6450 1 ggggggggggggggggggggggggggggggggggggggggggg 6560 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 6670 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 672 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 6890 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 69connection thread2; 70** Release the lock and collect result from update on thread 2 71DO release_lock("hello"); 72** Table should have eta updates where tipo=11 but updates made by 73** thread 1 shouldn't be visible yet. 74select * from t1; 75eta tipo c 767 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 778 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 7810 1 ccccccccccccccccccccccccccccccccccccccccccc 7920 2 ddddddddddddddddddddddddddddddddddddddddddd 8030 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 8140 2 fffffffffffffffffffffffffffffffffffffffffff 8250 1 ggggggggggggggggggggggggggggggggggggggggggg 8360 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 8470 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 8580 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 861 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 87** Sending commit on thread 2. 88commit; 89connection thread1; 90** Make sure table reads didn't change yet on thread 1. 91select * from t1; 92eta tipo c 937 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 948 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 9510 1 ccccccccccccccccccccccccccccccccccccccccccc 9620 2 ddddddddddddddddddddddddddddddddddddddddddd 9730 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 9840 2 fffffffffffffffffffffffffffffffffffffffffff 9950 1 ggggggggggggggggggggggggggggggggggggggggggg 10060 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 10170 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 1022 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1031 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 104** And send final commit on thread 1. 105commit; 106** Table should now be updated by both updates in the order of 107** thread 1,2. 108select * from t1; 109eta tipo c 1107 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 1118 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 11210 1 ccccccccccccccccccccccccccccccccccccccccccc 11320 2 ddddddddddddddddddddddddddddddddddddddddddd 11430 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 11540 2 fffffffffffffffffffffffffffffffffffffffffff 11650 1 ggggggggggggggggggggggggggggggggggggggggggg 11760 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 11870 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 1192 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1201 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 121connection thread2; 122** Make sure the output is similar for t1. 123select * from t1; 124eta tipo c 1257 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 1268 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 12710 1 ccccccccccccccccccccccccccccccccccccccccccc 12820 2 ddddddddddddddddddddddddddddddddddddddddddd 12930 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 13040 2 fffffffffffffffffffffffffffffffffffffffffff 13150 1 ggggggggggggggggggggggggggggggggggggggggggg 13260 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 13370 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 1342 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1351 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 136connection thread1; 137select * from t1; 138eta tipo c 1397 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 1408 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 14110 1 ccccccccccccccccccccccccccccccccccccccccccc 14220 2 ddddddddddddddddddddddddddddddddddddddddddd 14330 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 14440 2 fffffffffffffffffffffffffffffffffffffffffff 14550 1 ggggggggggggggggggggggggggggggggggggggggggg 14660 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 14770 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 1482 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1491 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 150connection default; 151drop table t1; 152 153** 154** two UPDATE's running and one changing result set 155** 156connection thread1; 157** Set up table 158SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB; 159create table t1(eta int(11) not null, tipo int(11), c varchar(255)); 160insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); 161insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); 162insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); 163insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); 164insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); 165insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); 166insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); 167insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); 168insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); 169insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); 170insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); 171** Get ULL "hello" on thread 1 172select get_lock("hello",10); 173get_lock("hello",10) 1741 175connection thread2; 176** Start transaction on thread 2 177begin; 178** Update will cause a table scan. 179** This will cause a hang on the first row where tipo=1 until the 180** blocking ULL is released. 181update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; 182connection thread1; 183** Start transaction on thread 1 184begin; 185** Update on t1 will cause a table scan which will be blocked because 186** the previously initiated table scan applied exclusive key locks on 187** all primary keys. 188** Not so for READ UNCOMMITTED or READ COMMITTED. The locks that 189** do not match the WHERE condition are released. 190update t1 set tipo=1 where tipo=2; 191** Release ULL. This will release the next waiting ULL on thread 2. 192DO release_lock("hello"); 193** The table should still be updated with updates for thread 1 only: 194select * from t1; 195eta tipo c 1967 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 1978 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 19810 1 ccccccccccccccccccccccccccccccccccccccccccc 19920 1 ddddddddddddddddddddddddddddddddddddddddddd 20030 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 20140 1 fffffffffffffffffffffffffffffffffffffffffff 20250 1 ggggggggggggggggggggggggggggggggggggggggggg 20360 1 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 20470 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 20580 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 20690 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 207connection thread2; 208** Release the lock and collect result from thread 2: 209DO release_lock("hello"); 210** Seen from thread 2 the table should have been updated on four 211** places. 212select * from t1; 213eta tipo c 2147 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 2158 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2161 1 ccccccccccccccccccccccccccccccccccccccccccc 21720 2 ddddddddddddddddddddddddddddddddddddddddddd 2181 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 21940 2 fffffffffffffffffffffffffffffffffffffffffff 2201 1 ggggggggggggggggggggggggggggggggggggggggggg 22160 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 2221 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 22380 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 22490 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 225commit; 226connection thread1; 227** Thread 2 has committed but the result should remain the same for 228** thread 1 (updated on three places): 229select * from t1; 230eta tipo c 2317 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 2328 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2331 1 ccccccccccccccccccccccccccccccccccccccccccc 23420 1 ddddddddddddddddddddddddddddddddddddddddddd 2351 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 23640 1 fffffffffffffffffffffffffffffffffffffffffff 2371 1 ggggggggggggggggggggggggggggggggggggggggggg 23860 1 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 2391 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 24080 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 24190 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 242commit; 243** After a commit the table should be merged with the previous 244** commit. 245** This select should show both updates: 246select * from t1; 247eta tipo c 2487 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 2498 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2501 1 ccccccccccccccccccccccccccccccccccccccccccc 25120 1 ddddddddddddddddddddddddddddddddddddddddddd 2521 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 25340 1 fffffffffffffffffffffffffffffffffffffffffff 2541 1 ggggggggggggggggggggggggggggggggggggggggggg 25560 1 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 2561 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 25780 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 25890 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 259connection thread2; 260select * from t1; 261eta tipo c 2627 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 2638 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2641 1 ccccccccccccccccccccccccccccccccccccccccccc 26520 1 ddddddddddddddddddddddddddddddddddddddddddd 2661 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 26740 1 fffffffffffffffffffffffffffffffffffffffffff 2681 1 ggggggggggggggggggggggggggggggggggggggggggg 26960 1 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 2701 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 27180 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 27290 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 273connection thread1; 274select * from t1; 275eta tipo c 2767 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 2778 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2781 1 ccccccccccccccccccccccccccccccccccccccccccc 27920 1 ddddddddddddddddddddddddddddddddddddddddddd 2801 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 28140 1 fffffffffffffffffffffffffffffffffffffffffff 2821 1 ggggggggggggggggggggggggggggggggggggggggggg 28360 1 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 2841 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 28580 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 28690 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 287connection default; 288drop table t1; 289 290** 291** One UPDATE and one INSERT .... Monty's test 292** 293connection thread1; 294** Set up table 295SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB; 296create table t1 (a int not null, b int not null); 297insert into t1 values (1,1),(2,1),(3,1),(4,1); 298** Create ULL 'hello2' 299select get_lock("hello2",10); 300get_lock("hello2",10) 3011 302connection thread2; 303** Begin a new transaction on thread 2 304begin; 305** Update will create a table scan which creates a ULL where a=2; 306** this will hang waiting on thread 1. 307update t1 set b=10+get_lock(concat("hello",a),10)*0 where a=2; 308connection thread1; 309** Insert new values to t1 from thread 1; this created an implicit 310** commit since there are no on-going transactions. 311insert into t1 values (1,1); 312** Release the ULL (thread 2 updates will finish). 313DO release_lock("hello2"); 314** ..but thread 1 will still see t1 as if nothing has happend: 315select * from t1; 316a b 3171 1 3182 1 3193 1 3204 1 3211 1 322connection thread2; 323** Collect results from thread 2 and release the lock. 324DO release_lock("hello2"); 325** The table should look like the original+updates for thread 2, 326** and consist of new rows: 327select * from t1; 328a b 3291 1 3302 10 3313 1 3324 1 3331 1 334** Commit changes from thread 2 335commit; 336connection default; 337drop table t1; 338 339** 340** one UPDATE changing result set and SELECT ... FOR UPDATE 341** 342connection thread1; 343** Set up table 344SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB; 345create table t1(eta int(11) not null, tipo int(11), c varchar(255)); 346insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); 347insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); 348insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); 349insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); 350insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); 351insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); 352insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); 353insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); 354insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); 355insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); 356insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); 357connection thread2; 358** Begin a new transaction on thread 2 359begin; 360** Select a range for update. 361select * from t1 where tipo=2 FOR UPDATE; 362eta tipo c 36320 2 ddddddddddddddddddddddddddddddddddddddddddd 36440 2 fffffffffffffffffffffffffffffffffffffffffff 36560 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 366connection thread1; 367** Begin a new transaction on thread 1 368begin; 369** Update the same range which is marked for update on thread 2; this 370** will hang because of row locks. 371update t1 set tipo=1 where tipo=2; 372ERROR HY000: Lock wait timeout exceeded; try restarting transaction 373** After the update the table will be unmodified because the previous 374** transaction failed and was rolled back. 375select * from t1; 376eta tipo c 3777 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 3788 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 37910 1 ccccccccccccccccccccccccccccccccccccccccccc 38020 2 ddddddddddddddddddddddddddddddddddddddddddd 38130 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 38240 2 fffffffffffffffffffffffffffffffffffffffffff 38350 1 ggggggggggggggggggggggggggggggggggggggggggg 38460 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 38570 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 38680 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 38790 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 388connection thread2; 389** The table should look unmodified from thread 2. 390select * from t1; 391eta tipo c 3927 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 3938 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 39410 1 ccccccccccccccccccccccccccccccccccccccccccc 39520 2 ddddddddddddddddddddddddddddddddddddddddddd 39630 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 39740 2 fffffffffffffffffffffffffffffffffffffffffff 39850 1 ggggggggggggggggggggggggggggggggggggggggggg 39960 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 40070 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 40180 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 40290 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 403** Sending a commit should release the row locks and enable 404** thread 1 to complete the transaction. 405commit; 406connection thread1; 407** Commit on thread 1. 408commit; 409connection thread2; 410** The table should not have been changed. 411select * from t1; 412eta tipo c 4137 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 4148 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 41510 1 ccccccccccccccccccccccccccccccccccccccccccc 41620 2 ddddddddddddddddddddddddddddddddddddddddddd 41730 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 41840 2 fffffffffffffffffffffffffffffffffffffffffff 41950 1 ggggggggggggggggggggggggggggggggggggggggggg 42060 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 42170 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 42280 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 42390 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 424connection thread1; 425** Even on thread 1: 426select * from t1; 427eta tipo c 4287 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 4298 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 43010 1 ccccccccccccccccccccccccccccccccccccccccccc 43120 2 ddddddddddddddddddddddddddddddddddddddddddd 43230 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 43340 2 fffffffffffffffffffffffffffffffffffffffffff 43450 1 ggggggggggggggggggggggggggggggggggggggggggg 43560 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 43670 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 43780 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 43890 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 439connection default; 440drop table t1; 441 442** 443** one UPDATE not changing result set and SELECT ... FOR UPDATE 444** 445connection thread1; 446** Set up table 447SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB; 448create table t1(eta int(11) not null, tipo int(11), c varchar(255)); 449insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); 450insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); 451insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); 452insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); 453insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); 454insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); 455insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); 456insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); 457insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); 458insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); 459insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); 460connection thread2; 461** Starting new transaction on thread 2. 462begin; 463** Starting SELECT .. FOR UPDATE 464select * from t1 where tipo=2 FOR UPDATE; 465eta tipo c 46620 2 ddddddddddddddddddddddddddddddddddddddddddd 46740 2 fffffffffffffffffffffffffffffffffffffffffff 46860 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 469connection thread1; 470 471** Starting new transaction on thread 1 472begin; 473** Updating single row using a table scan. This will time out 474** because of ongoing transaction on thread 1 holding lock on 475** all primary keys in the scan. 476** Not so for READ UNCOMMITTED or READ COMMITTED. The locks that 477** do not match the WHERE condition are released. 478update t1 set tipo=11 where tipo=22; 479** After the time out the transaction is aborted; no rows should 480** have changed. 481select * from t1; 482eta tipo c 4837 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 4848 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 48510 1 ccccccccccccccccccccccccccccccccccccccccccc 48620 2 ddddddddddddddddddddddddddddddddddddddddddd 48730 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 48840 2 fffffffffffffffffffffffffffffffffffffffffff 48950 1 ggggggggggggggggggggggggggggggggggggggggggg 49060 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 49170 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 49280 11 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 49390 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 494connection thread2; 495** The same thing should hold true for the transaction on 496** thread 2 497select * from t1; 498eta tipo c 4997 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 5008 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 50110 1 ccccccccccccccccccccccccccccccccccccccccccc 50220 2 ddddddddddddddddddddddddddddddddddddddddddd 50330 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 50440 2 fffffffffffffffffffffffffffffffffffffffffff 50550 1 ggggggggggggggggggggggggggggggggggggggggggg 50660 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 50770 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 50880 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 50990 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 510commit; 511connection thread1; 512commit; 513connection thread2; 514** Even after committing: 515select * from t1; 516eta tipo c 5177 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 5188 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 51910 1 ccccccccccccccccccccccccccccccccccccccccccc 52020 2 ddddddddddddddddddddddddddddddddddddddddddd 52130 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 52240 2 fffffffffffffffffffffffffffffffffffffffffff 52350 1 ggggggggggggggggggggggggggggggggggggggggggg 52460 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 52570 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 52680 11 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 52790 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 528connection thread1; 529select * from t1; 530eta tipo c 5317 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 5328 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 53310 1 ccccccccccccccccccccccccccccccccccccccccccc 53420 2 ddddddddddddddddddddddddddddddddddddddddddd 53530 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 53640 2 fffffffffffffffffffffffffffffffffffffffffff 53750 1 ggggggggggggggggggggggggggggggggggggggggggg 53860 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 53970 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 54080 11 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 54190 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 542connection default; 543drop table t1; 544 545** 546** two SELECT ... FOR UPDATE 547** 548connection thread1; 549** Set up table 550SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB; 551create table t1(eta int(11) not null, tipo int(11), c varchar(255)); 552insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); 553insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); 554insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); 555insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); 556insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); 557insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); 558insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); 559insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); 560insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); 561insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); 562insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); 563connection thread2; 564** Begin a new transaction on thread 2 565begin; 566select * from t1 where tipo=2 FOR UPDATE; 567eta tipo c 56820 2 ddddddddddddddddddddddddddddddddddddddddddd 56940 2 fffffffffffffffffffffffffffffffffffffffffff 57060 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 571connection thread1; 572** Begin a new transaction on thread 1 573begin; 574** Selecting a range for update by table scan will be blocked 575** because of on-going transaction on thread 2. 576select * from t1 where tipo=1 FOR UPDATE; 577ERROR HY000: Lock wait timeout exceeded; try restarting transaction 578connection thread2; 579** Table will be unchanged and the select command will not be 580** blocked: 581select * from t1; 582eta tipo c 5837 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 5848 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 58510 1 ccccccccccccccccccccccccccccccccccccccccccc 58620 2 ddddddddddddddddddddddddddddddddddddddddddd 58730 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 58840 2 fffffffffffffffffffffffffffffffffffffffffff 58950 1 ggggggggggggggggggggggggggggggggggggggggggg 59060 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 59170 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 59280 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 59390 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 594** Commit transacton on thread 2. 595commit; 596connection thread1; 597** Commit transaction on thread 1. 598commit; 599connection thread2; 600** Make sure table isn't blocked on thread 2: 601select * from t1; 602eta tipo c 6037 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 6048 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 60510 1 ccccccccccccccccccccccccccccccccccccccccccc 60620 2 ddddddddddddddddddddddddddddddddddddddddddd 60730 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 60840 2 fffffffffffffffffffffffffffffffffffffffffff 60950 1 ggggggggggggggggggggggggggggggggggggggggggg 61060 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 61170 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 61280 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 61390 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 614connection thread1; 615** Make sure table isn't blocked on thread 1: 616select * from t1; 617eta tipo c 6187 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 6198 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 62010 1 ccccccccccccccccccccccccccccccccccccccccccc 62120 2 ddddddddddddddddddddddddddddddddddddddddddd 62230 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 62340 2 fffffffffffffffffffffffffffffffffffffffffff 62450 1 ggggggggggggggggggggggggggggggggggggggggggg 62560 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 62670 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 62780 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 62890 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 629connection default; 630drop table t1; 631 632** 633** one UPDATE changing result set and DELETE 634** 635connection thread1; 636** Set up table 637SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB; 638create table t1(eta int(11) not null, tipo int(11), c varchar(255)); 639insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); 640insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); 641insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); 642insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); 643insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); 644insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); 645insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); 646insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); 647insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); 648insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); 649insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); 650connection thread2; 651begin; 652delete from t1 where tipo=2; 653connection thread1; 654begin; 655update t1 set tipo=1 where tipo=2; 656ERROR HY000: Lock wait timeout exceeded; try restarting transaction 657select * from t1; 658eta tipo c 6597 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 6608 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 66110 1 ccccccccccccccccccccccccccccccccccccccccccc 66220 2 ddddddddddddddddddddddddddddddddddddddddddd 66330 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 66440 2 fffffffffffffffffffffffffffffffffffffffffff 66550 1 ggggggggggggggggggggggggggggggggggggggggggg 66660 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 66770 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 66880 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 66990 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 670connection thread2; 671select * from t1; 672eta tipo c 6737 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 6748 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 67510 1 ccccccccccccccccccccccccccccccccccccccccccc 67630 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 67750 1 ggggggggggggggggggggggggggggggggggggggggggg 67870 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 67980 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 68090 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 681commit; 682connection thread1; 683commit; 684connection thread2; 685select * from t1; 686eta tipo c 6877 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 6888 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 68910 1 ccccccccccccccccccccccccccccccccccccccccccc 69030 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 69150 1 ggggggggggggggggggggggggggggggggggggggggggg 69270 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 69380 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 69490 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 695connection thread1; 696select * from t1; 697eta tipo c 6987 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 6998 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 70010 1 ccccccccccccccccccccccccccccccccccccccccccc 70130 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 70250 1 ggggggggggggggggggggggggggggggggggggggggggg 70370 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 70480 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 70590 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 706connection default; 707drop table t1; 708 709** 710** one UPDATE not changing result set and DELETE 711** 712connection thread1; 713** Set up table 714SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB; 715create table t1(eta int(11) not null, tipo int(11), c varchar(255)); 716insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); 717insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); 718insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); 719insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); 720insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); 721insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); 722insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); 723insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); 724insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); 725insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); 726insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); 727connection thread2; 728begin; 729delete from t1 where tipo=2; 730connection thread1; 731begin; 732** Update on t1 will cause a table scan which will be blocked because 733** the previously initiated table scan applied exclusive key locks on 734** all primary keys. 735** Not so for READ UNCOMMITTED or READ COMMITTED. The locks that 736** do not match the WHERE condition are released. 737update t1 set tipo=1 where tipo=22; 738select * from t1; 739eta tipo c 7407 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 7418 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 74210 1 ccccccccccccccccccccccccccccccccccccccccccc 74320 2 ddddddddddddddddddddddddddddddddddddddddddd 74430 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 74540 2 fffffffffffffffffffffffffffffffffffffffffff 74650 1 ggggggggggggggggggggggggggggggggggggggggggg 74760 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 74870 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 74980 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 75090 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 751connection thread2; 752select * from t1; 753eta tipo c 7547 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 7558 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 75610 1 ccccccccccccccccccccccccccccccccccccccccccc 75730 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 75850 1 ggggggggggggggggggggggggggggggggggggggggggg 75970 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 76080 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 76190 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 762commit; 763connection thread1; 764commit; 765connection thread2; 766select * from t1; 767eta tipo c 7687 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 7698 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 77010 1 ccccccccccccccccccccccccccccccccccccccccccc 77130 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 77250 1 ggggggggggggggggggggggggggggggggggggggggggg 77370 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 77480 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 77590 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 776connection thread1; 777select * from t1; 778eta tipo c 7797 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 7808 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 78110 1 ccccccccccccccccccccccccccccccccccccccccccc 78230 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 78350 1 ggggggggggggggggggggggggggggggggggggggggggg 78470 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 78580 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 78690 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 787disconnect thread1; 788disconnect thread2; 789connection default; 790drop table t1; 791drop user mysqltest@localhost; 792SET SQL_MODE=default; 793SET GLOBAL innodb_lock_wait_timeout = @save_timeout; 794SET GLOBAL tx_isolation = @save_isolation; 795