Edit File: tx_isolation_func.result
** Setup ** SET @session_tx_isolation = @@SESSION.tx_isolation; SET @global_tx_isolation = @@GLOBAL.tx_isolation; ** Connecting con0 using root ** ** Connection con0 ** SET SESSION AUTOCOMMIT = OFF; ** Connecting con1 using root ** ** Connection con1 ** SET SESSION AUTOCOMMIT = OFF; ** Connection default ** CREATE TABLE t1 (a int PRIMARY KEY, b int) ENGINE=INNODB; INSERT INTO t1 VALUES(2, 2); INSERT INTO t1 VALUES(4, 4); INSERT INTO t1 VALUES(6, 6); INSERT INTO t1 VALUES(8, 8); INSERT INTO t1 VALUES(16, 16); INSERT INTO t1 VALUES(18, 18); INSERT INTO t1 VALUES(20, 20); INSERT INTO t1 VALUES(22, 22); INSERT INTO t1 VALUES(24, 24); '#----------------------------FN_DYNVARS_184_01--------------------------------------#' ** Connection con0 ** SET SESSION tx_isolation = 'READ-UNCOMMITTED'; set binlog_format=mixed; ** Connection con1 ** SET SESSION tx_isolation = 'READ-UNCOMMITTED'; set binlog_format=mixed; ** Connection con0 ** START TRANSACTION; SELECT * FROM t1 WHERE a IN (2,4,6,8) FOR UPDATE; a b 2 2 4 4 6 6 8 8 UPDATE t1 SET b = 10 WHERE a IN (2,4,6,8); ** Connection con1 ** START TRANSACTION; SELECT * FROM t1; a b 2 10 4 10 6 10 8 10 16 16 18 18 20 20 22 22 24 24 INSERT INTO t1 VALUES(1, 1); INSERT INTO t1 VALUES(3, 3); SELECT * FROM t1; a b 1 1 2 10 3 3 4 10 6 10 8 10 16 16 18 18 20 20 22 22 24 24 COMMIT; ** Connection con0 ** DELETE FROM t1 WHERE a = 1 OR a = 3; COMMIT; '#----------------------------FN_DYNVARS_184_02--------------------------------------#' ** Connection con0 ** START TRANSACTION; SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE; a b 2 10 4 10 6 10 8 10 16 16 18 18 20 20 22 22 24 24 UPDATE t1 SET b = 10 WHERE a % 2 = 0; ** Connection con1 ** START TRANSACTION; SELECT * FROM t1; a b 2 10 4 10 6 10 8 10 16 10 18 10 20 10 22 10 24 10 INSERT INTO t1 VALUES(23, 23); INSERT INTO t1 VALUES(25, 25); SELECT * FROM t1; a b 2 10 4 10 6 10 8 10 16 10 18 10 20 10 22 10 23 23 24 10 25 25 COMMIT; ** Connection con0 ** COMMIT; '#----------------------------FN_DYNVARS_184_03--------------------------------------#' ** Connection con0 ** SET SESSION tx_isolation = 'READ-COMMITTED'; ** Connection con1 ** SET SESSION tx_isolation = 'READ-COMMITTED'; ** Connection con0 ** START TRANSACTION; SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE; a b 2 10 4 10 6 10 8 10 16 10 18 10 20 10 22 10 24 10 UPDATE t1 SET b = 11 WHERE a % 2 = 0; ** Connection con1 ** START TRANSACTION; SELECT * FROM t1; a b 2 10 4 10 6 10 8 10 16 10 18 10 20 10 22 10 23 23 24 10 25 25 INSERT INTO t1 VALUES(5, 5); INSERT INTO t1 VALUES(7, 7); SELECT * FROM t1; a b 2 10 4 10 5 5 6 10 7 7 8 10 16 10 18 10 20 10 22 10 23 23 24 10 25 25 COMMIT; ** Connection con0 ** COMMIT; '#----------------------------FN_DYNVARS_184_04--------------------------------------#' ** Connection con0 ** SET SESSION tx_isolation = 'REPEATABLE-READ'; ** Connection con1 ** SET SESSION tx_isolation = 'REPEATABLE-READ'; ** Connection con0 ** START TRANSACTION; SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE; a b 2 11 4 11 6 11 8 11 16 11 18 11 20 11 22 11 24 11 UPDATE t1 SET b = 12 WHERE a % 2 = 0; ** Connection con1 ** START TRANSACTION; SELECT * FROM t1; a b 2 11 4 11 5 5 6 11 7 7 8 11 16 11 18 11 20 11 22 11 23 23 24 11 25 25 INSERT INTO t1 VALUES(9, 9); ERROR HY000: Lock wait timeout exceeded; try restarting transaction INSERT INTO t1 VALUES(13, 13); ERROR HY000: Lock wait timeout exceeded; try restarting transaction Expected error "Lock wait timeout" SELECT * FROM t1; a b 2 11 4 11 5 5 6 11 7 7 8 11 16 11 18 11 20 11 22 11 23 23 24 11 25 25 COMMIT; ** Connection con0 ** COMMIT; '#----------------------------FN_DYNVARS_184_05--------------------------------------#' ** Connection con0 ** START TRANSACTION; SELECT * FROM t1 WHERE a IN (2,4,6,8,10,12,14,16,18,20,22,24,26) = 0 FOR UPDATE; a b 5 5 7 7 23 23 25 25 UPDATE t1 SET b = 13 WHERE a IN (2,4,6,8,10,12,14,16,18,20,22,24,26) = 0; ** Connection con1 ** START TRANSACTION; SELECT * FROM t1; a b 2 12 4 12 5 5 6 12 7 7 8 12 16 12 18 12 20 12 22 12 23 23 24 12 25 25 INSERT INTO t1 VALUES(9, 9); ERROR HY000: Lock wait timeout exceeded; try restarting transaction INSERT INTO t1 VALUES(13, 13); ERROR HY000: Lock wait timeout exceeded; try restarting transaction Expected error "Lock wait timeout" SELECT * FROM t1; a b 2 12 4 12 5 5 6 12 7 7 8 12 16 12 18 12 20 12 22 12 23 23 24 12 25 25 COMMIT; ** Connection con0 ** COMMIT; '#----------------------------FN_DYNVARS_184_06--------------------------------------#' ** Connection con0 ** START TRANSACTION; SELECT * FROM t1 WHERE a IN (2,4,6,8) = 0 FOR UPDATE; a b 5 13 7 13 16 12 18 12 20 12 22 12 23 13 24 12 25 13 UPDATE t1 SET b = 14 WHERE a IN (2,4,6,8) = 0; ** Connection con1 ** START TRANSACTION; SELECT * FROM t1; a b 2 12 4 12 5 13 6 12 7 13 8 12 16 12 18 12 20 12 22 12 23 13 24 12 25 13 INSERT INTO t1 VALUES(9, 9); ERROR HY000: Lock wait timeout exceeded; try restarting transaction INSERT INTO t1 VALUES(13, 13); ERROR HY000: Lock wait timeout exceeded; try restarting transaction Expected error "Lock wait timeout" SELECT * FROM t1; a b 2 12 4 12 5 13 6 12 7 13 8 12 16 12 18 12 20 12 22 12 23 13 24 12 25 13 COMMIT; ** Connection con0 ** COMMIT; '#----------------------------FN_DYNVARS_184_07--------------------------------------#' ** Connection con0 ** SET SESSION tx_isolation = 'SERIALIZABLE'; ** Connection con0 ** SET SESSION tx_isolation = 'SERIALIZABLE'; ** Connection con0 ** START TRANSACTION; SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE; a b 2 12 4 12 6 12 8 12 16 14 18 14 20 14 22 14 24 14 UPDATE t1 SET b = 15 WHERE a % 2 = 0; ** Connection con0 ** START TRANSACTION; SELECT * FROM t1; ERROR HY000: Lock wait timeout exceeded; try restarting transaction INSERT INTO t1 VALUES(15, 15); ERROR HY000: Lock wait timeout exceeded; try restarting transaction INSERT INTO t1 VALUES(17, 17); ERROR HY000: Lock wait timeout exceeded; try restarting transaction SELECT * FROM t1; ERROR HY000: Lock wait timeout exceeded; try restarting transaction COMMIT; ** Connection con0 ** COMMIT; '#----------------------------FN_DYNVARS_184_08--------------------------------------#' SET GLOBAL tx_isolation = 'READ-UNCOMMITTED'; ** Connecting con_int1 using root ** ** Connection con_int1 ** SELECT @@SESSION.tx_isolation; @@SESSION.tx_isolation READ-UNCOMMITTED READ-UNCOMMITTED Expected SET SESSION tx_isolation = 'SERIALIZABLE'; ** Connecting con_int2 using root ** ** Connection con_int2 ** SELECT @@SESSION.tx_isolation; @@SESSION.tx_isolation READ-UNCOMMITTED READ-UNCOMMITTED Expected SET SESSION tx_isolation = 'REPEATABLE-READ'; ** Connection con_int2 ** SELECT @@SESSION.tx_isolation; @@SESSION.tx_isolation REPEATABLE-READ REPEATABLE-READ Expected ** Connection con_int1 ** SELECT @@SESSION.tx_isolation; @@SESSION.tx_isolation SERIALIZABLE SERIALIZABLE Expected SELECT @@GLOBAL.tx_isolation; @@GLOBAL.tx_isolation READ-UNCOMMITTED READ-UNCOMMITTED Expected ** Connection default ** Disconnecting Connections con_int1, con_int2 SET @@SESSION.tx_isolation = @session_tx_isolation; SET @@GLOBAL.tx_isolation = @global_tx_isolation; ** Connection default ** Disconnecting Connections con0, con1 DROP TABLE t1;