Debian Codegraph @

mysql-test/suite/innodb/r/attachable_trx.result

raw
1 SET @debug_saved = @@global.debug;
2
3 START TRANSACTION;
4
5 CREATE TABLE t1(a INT PRIMARY KEY AUTO_INCREMENT, b VARCHAR(255)) engine = innodb;
6
7 INSERT INTO t1(b) VALUES ('aaa');
8 INSERT INTO t1(b) VALUES ('bbb');
9 INSERT INTO t1(b) VALUES ('ccc');
10
11 COMMIT;
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.
17 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
18 START TRANSACTION;
19 # Insert a new row into t1.
20 INSERT INTO t1(b) VALUES ('ddd');
21 SELECT * FROM t1;
22 a b
23 1 aaa
24 2 bbb
25 3 ccc
26 4 ddd
27
28 # [another connection]
29
30 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
31 START TRANSACTION;
32 SET @@global.debug = '+d,use_attachable_trx';
33 SELECT * FROM t1;
34 a b
35 1 aaa
36 2 bbb
37 3 ccc
38 SET @@global.debug = '-d,use_attachable_trx';
39
40 # [default connection]
41
42 ROLLBACK;
43
44 # 2. Check that inner transaction has different visibility scope than
45 # the outer transaction.
46 # Start READ ONLY transaction.
47 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
48 START TRANSACTION READ ONLY;
49 # SELECT to actually start a transaction.
50 SELECT * FROM t1;
51 a b
52 1 aaa
53 2 bbb
54 3 ccc
55
56 # [another connection]
57
58 START TRANSACTION;
59 UPDATE t1 SET b = 'zzz' WHERE a = 2;
60 COMMIT;
61
62 # [default connection]
63
64 # SELECT in the outer transaction doesn't see the changes.
65 SELECT * FROM t1;
66 a b
67 1 aaa
68 2 bbb
69 3 ccc
70
71 # SELECT in the inner transaction sees the changes.
72 SET @@global.debug = '+d,use_attachable_trx';
73 SELECT * FROM t1;
74 a b
75 1 aaa
76 2 zzz
77 3 ccc
78 SET @@global.debug = '-d,use_attachable_trx';
79
80 # COMMIT the outer transaction.
81 COMMIT;
82 # SELECT in the outer transaction now sees the changes.
83 SELECT * FROM t1;
84 a b
85 1 aaa
86 2 zzz
87 3 ccc
88 COMMIT;
89
90 # 3. Check that the inner transaction does not reset a save point set in
91 # the outer transaction.
92
93 # Start transaction.
94 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
95 START TRANSACTION;
96 SELECT * FROM t1;
97 a b
98 1 aaa
99 2 zzz
100 3 ccc
101
102 # Set save point.
103 SAVEPOINT sp1;
104
105 # Do some changes.
106 UPDATE t1 SET b = 'xxx' WHERE a = 2;
107 SELECT * FROM t1;
108 a b
109 1 aaa
110 2 xxx
111 3 ccc
112
113 # Do anything in the inner transaction.
114 SET @@global.debug = '+d,use_attachable_trx';
115 SELECT * FROM t1;
116 a b
117 1 aaa
118 2 zzz
119 3 ccc
120 SET @@global.debug = '-d,use_attachable_trx';
121
122 # Just make sure the changes are still there.
123 SELECT * FROM t1;
124 a b
125 1 aaa
126 2 xxx
127 3 ccc
128
129 # Rollback to the save point to make sure it was not reset.
130 ROLLBACK TO SAVEPOINT sp1;
131
132 # Check that the changes have been reverted.
133 SELECT * FROM t1;
134 a b
135 1 aaa
136 2 zzz
137 3 ccc
138
139 # Commit.
140 COMMIT;
141
142 # Cleanup.
143 DROP TABLE t1;
144
145 SET @@global.debug = @debug_saved;