1** Setup ** 2SET @session_tx_isolation = @@SESSION.tx_isolation; 3SET @global_tx_isolation = @@GLOBAL.tx_isolation; 4connect con0,localhost,root,,; 5connection con0; 6SET SESSION AUTOCOMMIT = OFF; 7connect con1, localhost, root,,; 8connection con1; 9SET SESSION AUTOCOMMIT = OFF; 10connection default; 11CREATE TABLE t1 (a int PRIMARY KEY, b int) ENGINE=INNODB; 12INSERT INTO t1 VALUES(2, 2); 13INSERT INTO t1 VALUES(4, 4); 14INSERT INTO t1 VALUES(6, 6); 15INSERT INTO t1 VALUES(8, 8); 16INSERT INTO t1 VALUES(16, 16); 17INSERT INTO t1 VALUES(18, 18); 18INSERT INTO t1 VALUES(20, 20); 19INSERT INTO t1 VALUES(22, 22); 20INSERT INTO t1 VALUES(24, 24); 21'#----------------------------FN_DYNVARS_184_01--------------------------------------#' 22connection con0; 23SET SESSION tx_isolation = 'READ-UNCOMMITTED'; 24set binlog_format=mixed; 25connection con1; 26SET SESSION tx_isolation = 'READ-UNCOMMITTED'; 27set binlog_format=mixed; 28connection con0; 29START TRANSACTION; 30SELECT * FROM t1 WHERE a IN (2,4,6,8) FOR UPDATE; 31a b 322 2 334 4 346 6 358 8 36UPDATE t1 SET b = 10 WHERE a IN (2,4,6,8); 37connection con1; 38START TRANSACTION; 39SELECT * FROM t1; 40a b 412 10 424 10 436 10 448 10 4516 16 4618 18 4720 20 4822 22 4924 24 50INSERT INTO t1 VALUES(1, 1); 51INSERT INTO t1 VALUES(3, 3); 52SELECT * FROM t1; 53a b 541 1 552 10 563 3 574 10 586 10 598 10 6016 16 6118 18 6220 20 6322 22 6424 24 65COMMIT; 66connection con0; 67DELETE FROM t1 WHERE a = 1 OR a = 3; 68COMMIT; 69'#----------------------------FN_DYNVARS_184_02--------------------------------------#' 70connection con0; 71START TRANSACTION; 72SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE; 73a b 742 10 754 10 766 10 778 10 7816 16 7918 18 8020 20 8122 22 8224 24 83UPDATE t1 SET b = 10 WHERE a % 2 = 0; 84connection con1; 85START TRANSACTION; 86SELECT * FROM t1; 87a b 882 10 894 10 906 10 918 10 9216 10 9318 10 9420 10 9522 10 9624 10 97INSERT INTO t1 VALUES(23, 23); 98INSERT INTO t1 VALUES(25, 25); 99SELECT * FROM t1; 100a b 1012 10 1024 10 1036 10 1048 10 10516 10 10618 10 10720 10 10822 10 10923 23 11024 10 11125 25 112COMMIT; 113connection con0; 114COMMIT; 115'#----------------------------FN_DYNVARS_184_03--------------------------------------#' 116connection con0; 117SET SESSION tx_isolation = 'READ-COMMITTED'; 118connection con1; 119SET SESSION tx_isolation = 'READ-COMMITTED'; 120connection con0; 121START TRANSACTION; 122SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE; 123a b 1242 10 1254 10 1266 10 1278 10 12816 10 12918 10 13020 10 13122 10 13224 10 133UPDATE t1 SET b = 11 WHERE a % 2 = 0; 134connection con1; 135START TRANSACTION; 136SELECT * FROM t1; 137a b 1382 10 1394 10 1406 10 1418 10 14216 10 14318 10 14420 10 14522 10 14623 23 14724 10 14825 25 149INSERT INTO t1 VALUES(5, 5); 150INSERT INTO t1 VALUES(7, 7); 151SELECT * FROM t1; 152a b 1532 10 1544 10 1555 5 1566 10 1577 7 1588 10 15916 10 16018 10 16120 10 16222 10 16323 23 16424 10 16525 25 166COMMIT; 167connection con0; 168COMMIT; 169'#----------------------------FN_DYNVARS_184_04--------------------------------------#' 170connection con0; 171SET SESSION tx_isolation = 'REPEATABLE-READ'; 172connection con1; 173SET SESSION tx_isolation = 'REPEATABLE-READ'; 174connection con0; 175START TRANSACTION; 176SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE; 177a b 1782 11 1794 11 1806 11 1818 11 18216 11 18318 11 18420 11 18522 11 18624 11 187UPDATE t1 SET b = 12 WHERE a % 2 = 0; 188connection con1; 189START TRANSACTION; 190SELECT * FROM t1; 191a b 1922 11 1934 11 1945 5 1956 11 1967 7 1978 11 19816 11 19918 11 20020 11 20122 11 20223 23 20324 11 20425 25 205INSERT INTO t1 VALUES(9, 9); 206ERROR HY000: Lock wait timeout exceeded; try restarting transaction 207INSERT INTO t1 VALUES(13, 13); 208ERROR HY000: Lock wait timeout exceeded; try restarting transaction 209Expected error "Lock wait timeout" 210SELECT * FROM t1; 211a b 2122 11 2134 11 2145 5 2156 11 2167 7 2178 11 21816 11 21918 11 22020 11 22122 11 22223 23 22324 11 22425 25 225COMMIT; 226connection con0; 227COMMIT; 228'#----------------------------FN_DYNVARS_184_05--------------------------------------#' 229connection con0; 230START TRANSACTION; 231SELECT * FROM t1 WHERE a IN (2,4,6,8,10,12,14,16,18,20,22,24,26) = 0 FOR UPDATE; 232a b 2335 5 2347 7 23523 23 23625 25 237UPDATE t1 SET b = 13 WHERE a IN (2,4,6,8,10,12,14,16,18,20,22,24,26) = 0; 238connection con1; 239START TRANSACTION; 240SELECT * FROM t1; 241a b 2422 12 2434 12 2445 5 2456 12 2467 7 2478 12 24816 12 24918 12 25020 12 25122 12 25223 23 25324 12 25425 25 255INSERT INTO t1 VALUES(9, 9); 256ERROR HY000: Lock wait timeout exceeded; try restarting transaction 257INSERT INTO t1 VALUES(13, 13); 258ERROR HY000: Lock wait timeout exceeded; try restarting transaction 259Expected error "Lock wait timeout" 260SELECT * FROM t1; 261a b 2622 12 2634 12 2645 5 2656 12 2667 7 2678 12 26816 12 26918 12 27020 12 27122 12 27223 23 27324 12 27425 25 275COMMIT; 276connection con0; 277COMMIT; 278'#----------------------------FN_DYNVARS_184_06--------------------------------------#' 279connection con0; 280START TRANSACTION; 281SELECT * FROM t1 WHERE a IN (2,4,6,8) = 0 FOR UPDATE; 282a b 2835 13 2847 13 28516 12 28618 12 28720 12 28822 12 28923 13 29024 12 29125 13 292UPDATE t1 SET b = 14 WHERE a IN (2,4,6,8) = 0; 293connection con1; 294START TRANSACTION; 295SELECT * FROM t1; 296a b 2972 12 2984 12 2995 13 3006 12 3017 13 3028 12 30316 12 30418 12 30520 12 30622 12 30723 13 30824 12 30925 13 310INSERT INTO t1 VALUES(9, 9); 311ERROR HY000: Lock wait timeout exceeded; try restarting transaction 312INSERT INTO t1 VALUES(13, 13); 313ERROR HY000: Lock wait timeout exceeded; try restarting transaction 314Expected error "Lock wait timeout" 315SELECT * FROM t1; 316a b 3172 12 3184 12 3195 13 3206 12 3217 13 3228 12 32316 12 32418 12 32520 12 32622 12 32723 13 32824 12 32925 13 330COMMIT; 331connection con0; 332COMMIT; 333'#----------------------------FN_DYNVARS_184_07--------------------------------------#' 334connection con0; 335SET SESSION tx_isolation = 'SERIALIZABLE'; 336connection con1; 337SET SESSION tx_isolation = 'SERIALIZABLE'; 338connection con0; 339START TRANSACTION; 340SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE; 341a b 3422 12 3434 12 3446 12 3458 12 34616 14 34718 14 34820 14 34922 14 35024 14 351UPDATE t1 SET b = 15 WHERE a % 2 = 0; 352connection con1; 353START TRANSACTION; 354SELECT * FROM t1; 355ERROR HY000: Lock wait timeout exceeded; try restarting transaction 356INSERT INTO t1 VALUES(15, 15); 357ERROR HY000: Lock wait timeout exceeded; try restarting transaction 358INSERT INTO t1 VALUES(17, 17); 359ERROR HY000: Lock wait timeout exceeded; try restarting transaction 360SELECT * FROM t1; 361ERROR HY000: Lock wait timeout exceeded; try restarting transaction 362COMMIT; 363connection con0; 364COMMIT; 365'#----------------------------FN_DYNVARS_184_08--------------------------------------#' 366SET GLOBAL tx_isolation = 'READ-UNCOMMITTED'; 367connect con_int1,localhost,root,,; 368connection con_int1; 369SELECT @@SESSION.tx_isolation; 370@@SESSION.tx_isolation 371READ-UNCOMMITTED 372READ-UNCOMMITTED Expected 373SET SESSION tx_isolation = 'SERIALIZABLE'; 374connect con_int2,localhost,root,,; 375connection con_int2; 376SELECT @@SESSION.tx_isolation; 377@@SESSION.tx_isolation 378READ-UNCOMMITTED 379READ-UNCOMMITTED Expected 380SET SESSION tx_isolation = 'REPEATABLE-READ'; 381connection con_int2; 382SELECT @@SESSION.tx_isolation; 383@@SESSION.tx_isolation 384REPEATABLE-READ 385REPEATABLE-READ Expected 386connection con_int1; 387SELECT @@SESSION.tx_isolation; 388@@SESSION.tx_isolation 389SERIALIZABLE 390SERIALIZABLE Expected 391SELECT @@GLOBAL.tx_isolation; 392@@GLOBAL.tx_isolation 393READ-UNCOMMITTED 394READ-UNCOMMITTED Expected 395connection default; 396disconnect con_int1; 397disconnect con_int2; 398SET @@SESSION.tx_isolation = @session_tx_isolation; 399SET @@GLOBAL.tx_isolation = @global_tx_isolation; 400connection default; 401disconnect con0; 402disconnect con1; 403DROP TABLE t1; 404