1SET @debug_saved = @@global.debug; 2 3START TRANSACTION; 4 5CREATE TABLE t1(a INT PRIMARY KEY AUTO_INCREMENT, b VARCHAR(255)) engine = innodb; 6 7INSERT INTO t1(b) VALUES ('aaa'); 8INSERT INTO t1(b) VALUES ('bbb'); 9INSERT INTO t1(b) VALUES ('ccc'); 10 11COMMIT; 12 13# 1. Start outer transaction with SERIALIZABLE isolation level. 14# Check that SELECT in inner transaction will not take locks. 15 16# Start transaction. 17SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 18START TRANSACTION; 19# Insert a new row into t1. 20INSERT INTO t1(b) VALUES ('ddd'); 21SELECT * FROM t1; 22a b 231 aaa 242 bbb 253 ccc 264 ddd 27 28# [another connection] 29 30SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 31START TRANSACTION; 32SET @@global.debug = '+d,use_attachable_trx'; 33SELECT * FROM t1; 34a b 351 aaa 362 bbb 373 ccc 38SET @@global.debug = '-d,use_attachable_trx'; 39 40# [default connection] 41 42ROLLBACK; 43 44# 2. Check that inner transaction has different visibility scope than 45# the outer transaction. 46# Start READ ONLY transaction. 47SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 48START TRANSACTION READ ONLY; 49# SELECT to actually start a transaction. 50SELECT * FROM t1; 51a b 521 aaa 532 bbb 543 ccc 55 56# [another connection] 57 58START TRANSACTION; 59UPDATE t1 SET b = 'zzz' WHERE a = 2; 60COMMIT; 61 62# [default connection] 63 64# SELECT in the outer transaction doesn't see the changes. 65SELECT * FROM t1; 66a b 671 aaa 682 bbb 693 ccc 70 71# SELECT in the inner transaction sees the changes. 72SET @@global.debug = '+d,use_attachable_trx'; 73SELECT * FROM t1; 74a b 751 aaa 762 zzz 773 ccc 78SET @@global.debug = '-d,use_attachable_trx'; 79 80# COMMIT the outer transaction. 81COMMIT; 82# SELECT in the outer transaction now sees the changes. 83SELECT * FROM t1; 84a b 851 aaa 862 zzz 873 ccc 88COMMIT; 89 90# 3. Check that the inner transaction does not reset a save point set in 91# the outer transaction. 92 93# Start transaction. 94SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 95START TRANSACTION; 96SELECT * FROM t1; 97a b 981 aaa 992 zzz 1003 ccc 101 102# Set save point. 103SAVEPOINT sp1; 104 105# Do some changes. 106UPDATE t1 SET b = 'xxx' WHERE a = 2; 107SELECT * FROM t1; 108a b 1091 aaa 1102 xxx 1113 ccc 112 113# Do anything in the inner transaction. 114SET @@global.debug = '+d,use_attachable_trx'; 115SELECT * FROM t1; 116a b 1171 aaa 1182 zzz 1193 ccc 120SET @@global.debug = '-d,use_attachable_trx'; 121 122# Just make sure the changes are still there. 123SELECT * FROM t1; 124a b 1251 aaa 1262 xxx 1273 ccc 128 129# Rollback to the save point to make sure it was not reset. 130ROLLBACK TO SAVEPOINT sp1; 131 132# Check that the changes have been reverted. 133SELECT * FROM t1; 134a b 1351 aaa 1362 zzz 1373 ccc 138 139# Commit. 140COMMIT; 141 142# Cleanup. 143DROP TABLE t1; 144 145SET @@global.debug = @debug_saved; 146