1# 2# WL#9636: Rename tx_{read_only,isolation} variables to transaction_* 3# 4# Setup. 5SET @session_transaction_isolation = @@SESSION.transaction_isolation; 6SET @global_transaction_isolation = @@GLOBAL.transaction_isolation; 7# Creating connections 8connect con0,localhost,root,,; 9connection con0; 10SET SESSION AUTOCOMMIT = OFF; 11connect con1, localhost, root,,; 12connection con1; 13SET SESSION AUTOCOMMIT = OFF; 14connection default; 15# Creating tables 16CREATE TABLE t1 (a int PRIMARY KEY, b int) ENGINE=INNODB; 17INSERT INTO t1 VALUES(2, 2); 18INSERT INTO t1 VALUES(4, 4); 19INSERT INTO t1 VALUES(6, 6); 20INSERT INTO t1 VALUES(8, 8); 21INSERT INTO t1 VALUES(16, 16); 22INSERT INTO t1 VALUES(18, 18); 23INSERT INTO t1 VALUES(20, 20); 24INSERT INTO t1 VALUES(22, 22); 25INSERT INTO t1 VALUES(24, 24); 26# Testing for value READ-UNCOMMITTED 27connection con0; 28SET SESSION transaction_isolation = 'READ-UNCOMMITTED'; 29set binlog_format=mixed; 30connection con1; 31SET SESSION transaction_isolation = 'READ-UNCOMMITTED'; 32set binlog_format=mixed; 33# Testing WHERE on keys using IN clause 34connection con0; 35START TRANSACTION; 36SELECT * FROM t1 WHERE a IN (2,4,6,8) FOR UPDATE; 37a b 382 2 394 4 406 6 418 8 42UPDATE t1 SET b = 10 WHERE a IN (2,4,6,8); 43connection con1; 44START TRANSACTION; 45SELECT * FROM t1; 46a b 472 10 484 10 496 10 508 10 5116 16 5218 18 5320 20 5422 22 5524 24 56INSERT INTO t1 VALUES(1, 1); 57INSERT INTO t1 VALUES(3, 3); 58SELECT * FROM t1; 59a b 601 1 612 10 623 3 634 10 646 10 658 10 6616 16 6718 18 6820 20 6922 22 7024 24 71COMMIT; 72connection con0; 73DELETE FROM t1 WHERE a = 1 OR a = 3; 74COMMIT; 75# Testing WHERE on keys using # on even rows 76connection con0; 77START TRANSACTION; 78SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE; 79a b 802 10 814 10 826 10 838 10 8416 16 8518 18 8620 20 8722 22 8824 24 89UPDATE t1 SET b = 10 WHERE a % 2 = 0; 90connection con1; 91START TRANSACTION; 92SELECT * FROM t1; 93a b 942 10 954 10 966 10 978 10 9816 10 9918 10 10020 10 10122 10 10224 10 103INSERT INTO t1 VALUES(23, 23); 104INSERT INTO t1 VALUES(25, 25); 105SELECT * FROM t1; 106a b 1072 10 1084 10 1096 10 1108 10 11116 10 11218 10 11320 10 11422 10 11523 23 11624 10 11725 25 118COMMIT; 119connection con0; 120COMMIT; 121# Testing for value READ-COMMITTED 122connection con0; 123SET SESSION transaction_isolation = 'READ-COMMITTED'; 124connection con1; 125SET SESSION transaction_isolation = 'READ-COMMITTED'; 126# Testing WHERE on keys using % on even rows 127connection con0; 128START TRANSACTION; 129SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE; 130a b 1312 10 1324 10 1336 10 1348 10 13516 10 13618 10 13720 10 13822 10 13924 10 140UPDATE t1 SET b = 11 WHERE a % 2 = 0; 141connection con1; 142START TRANSACTION; 143SELECT * FROM t1; 144a b 1452 10 1464 10 1476 10 1488 10 14916 10 15018 10 15120 10 15222 10 15323 23 15424 10 15525 25 156INSERT INTO t1 VALUES(5, 5); 157INSERT INTO t1 VALUES(7, 7); 158SELECT * FROM t1; 159a b 1602 10 1614 10 1625 5 1636 10 1647 7 1658 10 16616 10 16718 10 16820 10 16922 10 17023 23 17124 10 17225 25 173COMMIT; 174connection con0; 175COMMIT; 176# Testing for value REPEATABLE-READ 177connection con0; 178SET SESSION transaction_isolation = 'REPEATABLE-READ'; 179connection con1; 180SET SESSION transaction_isolation = 'REPEATABLE-READ'; 181# Testing WHERE on keys using % on even rows 182connection con0; 183START TRANSACTION; 184SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE; 185a b 1862 11 1874 11 1886 11 1898 11 19016 11 19118 11 19220 11 19322 11 19424 11 195UPDATE t1 SET b = 12 WHERE a % 2 = 0; 196connection con1; 197START TRANSACTION; 198SELECT * FROM t1; 199a b 2002 11 2014 11 2025 5 2036 11 2047 7 2058 11 20616 11 20718 11 20820 11 20922 11 21023 23 21124 11 21225 25 213INSERT INTO t1 VALUES(9, 9); 214ERROR HY000: Lock wait timeout exceeded; try restarting transaction 215INSERT INTO t1 VALUES(13, 13); 216ERROR HY000: Lock wait timeout exceeded; try restarting transaction 217Expected error "Lock wait timeout" 218SELECT * FROM t1; 219a b 2202 11 2214 11 2225 5 2236 11 2247 7 2258 11 22616 11 22718 11 22820 11 22922 11 23023 23 23124 11 23225 25 233COMMIT; 234connection con0; 235COMMIT; 236# Testing WHERE on keys using IN clause 237connection con0; 238START TRANSACTION; 239SELECT * FROM t1 WHERE a IN (2,4,6,8,10,12,14,16,18,20,22,24,26) = 0 FOR UPDATE; 240a b 2415 5 2427 7 24323 23 24425 25 245UPDATE t1 SET b = 13 WHERE a IN (2,4,6,8,10,12,14,16,18,20,22,24,26) = 0; 246connection con1; 247START TRANSACTION; 248SELECT * FROM t1; 249a b 2502 12 2514 12 2525 5 2536 12 2547 7 2558 12 25616 12 25718 12 25820 12 25922 12 26023 23 26124 12 26225 25 263INSERT INTO t1 VALUES(9, 9); 264ERROR HY000: Lock wait timeout exceeded; try restarting transaction 265INSERT INTO t1 VALUES(13, 13); 266ERROR HY000: Lock wait timeout exceeded; try restarting transaction 267Expected error "Lock wait timeout" 268SELECT * FROM t1; 269a b 2702 12 2714 12 2725 5 2736 12 2747 7 2758 12 27616 12 27718 12 27820 12 27922 12 28023 23 28124 12 28225 25 283COMMIT; 284connection con0; 285COMMIT; 286# Testing WHERE on keys using IN clause 287connection con0; 288START TRANSACTION; 289SELECT * FROM t1 WHERE a IN (2,4,6,8) = 0 FOR UPDATE; 290a b 2915 13 2927 13 29316 12 29418 12 29520 12 29622 12 29723 13 29824 12 29925 13 300UPDATE t1 SET b = 14 WHERE a IN (2,4,6,8) = 0; 301connection con1; 302START TRANSACTION; 303SELECT * FROM t1; 304a b 3052 12 3064 12 3075 13 3086 12 3097 13 3108 12 31116 12 31218 12 31320 12 31422 12 31523 13 31624 12 31725 13 318INSERT INTO t1 VALUES(9, 9); 319ERROR HY000: Lock wait timeout exceeded; try restarting transaction 320INSERT INTO t1 VALUES(13, 13); 321ERROR HY000: Lock wait timeout exceeded; try restarting transaction 322Expected error "Lock wait timeout" 323SELECT * FROM t1; 324a b 3252 12 3264 12 3275 13 3286 12 3297 13 3308 12 33116 12 33218 12 33320 12 33422 12 33523 13 33624 12 33725 13 338COMMIT; 339connection con0; 340COMMIT; 341# Testing for value SERIALIZABLE 342connection con0; 343SET SESSION transaction_isolation = 'SERIALIZABLE'; 344connection con1; 345SET SESSION transaction_isolation = 'SERIALIZABLE'; 346# Testing WHERE on keys using # on even rows 347connection con0; 348START TRANSACTION; 349SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE; 350a b 3512 12 3524 12 3536 12 3548 12 35516 14 35618 14 35720 14 35822 14 35924 14 360UPDATE t1 SET b = 15 WHERE a % 2 = 0; 361connection con1; 362START TRANSACTION; 363SELECT * FROM t1; 364ERROR HY000: Lock wait timeout exceeded; try restarting transaction 365INSERT INTO t1 VALUES(15, 15); 366ERROR HY000: Lock wait timeout exceeded; try restarting transaction 367INSERT INTO t1 VALUES(17, 17); 368ERROR HY000: Lock wait timeout exceeded; try restarting transaction 369SELECT * FROM t1; 370ERROR HY000: Lock wait timeout exceeded; try restarting transaction 371COMMIT; 372connection con0; 373COMMIT; 374# Session data integrity check & GLOBAL Value check 375SET GLOBAL transaction_isolation = 'READ-UNCOMMITTED'; 376connect con_int1,localhost,root,,; 377connection con_int1; 378SELECT @@SESSION.transaction_isolation; 379@@SESSION.transaction_isolation 380READ-UNCOMMITTED 381READ-UNCOMMITTED Expected 382SET SESSION transaction_isolation = 'SERIALIZABLE'; 383connect con_int2,localhost,root,,; 384connection con_int2; 385SELECT @@SESSION.transaction_isolation; 386@@SESSION.transaction_isolation 387READ-UNCOMMITTED 388READ-UNCOMMITTED Expected 389SET SESSION transaction_isolation = 'REPEATABLE-READ'; 390connection con_int2; 391SELECT @@SESSION.transaction_isolation; 392@@SESSION.transaction_isolation 393REPEATABLE-READ 394REPEATABLE-READ Expected 395connection con_int1; 396SELECT @@SESSION.transaction_isolation; 397@@SESSION.transaction_isolation 398SERIALIZABLE 399SERIALIZABLE Expected 400SELECT @@GLOBAL.transaction_isolation; 401@@GLOBAL.transaction_isolation 402READ-UNCOMMITTED 403READ-UNCOMMITTED Expected 404connection default; 405disconnect con_int1; 406disconnect con_int2; 407# Cleanup 408SET @@SESSION.transaction_isolation = @session_transaction_isolation; 409SET @@GLOBAL.transaction_isolation = @global_transaction_isolation; 410connection default; 411disconnect con0; 412disconnect con1; 413DROP TABLE t1; 414