Debian Codegraph @

mysql-test/suite/innodb/t/innodb-read-view.test

raw
1 # DEBUG_SYNC must be compiled in.
2 --source include/have_debug_sync.inc
3
4 # Temporarily skipping the test when log-bin is enabled due to Bug#22328779
5 --source include/not_log_bin.inc
6
7 # We need to test the use case:
8 # a. Create a transaction T1 that will be promoted to RW.
9 # b. Create a transaction T2 that will be promoted to RW.
10 # a. Create a RO transaction T3
11 # d. T3 does a select - creates a read view that doesn't include T1 and T2
12 # e. T1 & T2 do some updates - this promotes T1 & T2 to RW transactions
13 # f. T1 & T2 Commit
14 # g. T3 Does a select - it should not see the changes of T1 & T2
15
16 --source include/count_sessions.inc
17
18 CREATE TABLE t1 (c1 INT , c2 CHAR(10), PRIMARY KEY (c1)) ENGINE = InnoDB;
19 INSERT INTO t1 VALUES(0, "0");
20 INSERT INTO t1 VALUES(1, "1");
21 INSERT INTO t1 VALUES(2, "2");
22 INSERT INTO t1 VALUES(3, "3");
23
24 CREATE TABLE t2 (c1 INT , c2 CHAR(10), PRIMARY KEY (c1)) ENGINE = InnoDB;
25 INSERT INTO t2 VALUES(0, "a");
26 INSERT INTO t2 VALUES(1, "b");
27 INSERT INTO t2 VALUES(2, "c");
28 INSERT INTO t2 VALUES(3, "d");
29
30 --connect (con1,localhost,root,,)
31 --connect (con2,localhost,root,,)
32
33 connection con1;
34 --echo 'T1'
35 SET AUTOCOMMIT=0;
36 BEGIN;
37 SELECT * FROM t2;
38
39 connection default;
40 --echo 'T2'
41 SET AUTOCOMMIT=0;
42 BEGIN;
43 SELECT * FROM t1;
44
45 connection con2;
46 --echo 'T3'
47 SET AUTOCOMMIT=0;
48 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
49 BEGIN;
50 SELECT * FROM t1;
51 SELECT * FROM t2;
52
53 connection con1;
54 --echo 'T1'
55 UPDATE t2 SET c1 = c1 + 100;
56 SELECT * FROM t2;
57 COMMIT;
58
59 connection default;
60 --echo 'T2'
61 UPDATE t1 SET c1 = c1 + 100;
62 SELECT * FROM t1;
63 COMMIT;
64
65 connection con2;
66 --echo 'T3'
67 SET DEBUG_SYNC='row_search_for_mysql_before_return WAIT_FOR waiting1';
68 --send SELECT * FROM t1;
69
70 connection default;
71 --echo 'T2'
72 SET DEBUG_SYNC='now SIGNAL waiting1';
73 --echo 'Signalled T3'
74
75 connection con2;
76 --echo 'T3'
77 reap;
78
79 connection con2;
80 --echo 'T3'
81 SET DEBUG_SYNC='row_search_for_mysql_before_return WAIT_FOR waiting1';
82 --send SELECT * FROM t2;
83
84 connection default;
85 --echo 'T2'
86 SET DEBUG_SYNC='now SIGNAL waiting1';
87 --echo 'Signalled T3'
88
89 connection con2;
90 --echo 'T3'
91 reap;
92
93 connection default;
94 disconnect con1;
95 disconnect con2;
96
97 # We need to test the use case:
98 # a. Create a transaction T1 that will be promoted to RW.
99 # b. Create a transaction T2 that will be promoted to RW.
100 # c. T2 does some updates - this promotes T2 to RW transactions
101 # d. T2 Commits
102 # e. Create a RO transaction T3
103 # f. T3 does a select - creates a read view that doesn't include T1
104 # g. T1 does some updates - this promotes T1 to RW transactions
105 # h. T1 Commits
106 # i. T3 Does a select - it should not see the changes made by T1 but should
107 # see the changes by T2
108
109 --connect (con1,localhost,root,,)
110 --connect (con2,localhost,root,,)
111
112 connection con1;
113 --echo 'T1'
114 SET AUTOCOMMIT=0;
115 BEGIN;
116 SELECT * FROM t1;
117
118 connection default;
119 --echo 'T2'
120 SET AUTOCOMMIT=0;
121 BEGIN;
122 SELECT * FROM t2;
123 UPDATE t2 SET c1 = c1 + 100;
124 SELECT * FROM t2;
125 COMMIT;
126
127 connection con2;
128 --echo 'T3'
129 SET AUTOCOMMIT=0;
130 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
131 BEGIN;
132 SELECT * FROM t1;
133 SELECT * FROM t2;
134
135 connection con1;
136 --echo 'T1'
137 UPDATE t1 SET c1 = c1 + 100;
138 SELECT * FROM t1;
139 COMMIT;
140
141 connection con2;
142 --echo 'T3'
143 SET DEBUG_SYNC='row_select_wait WAIT_FOR waiting1';
144 --send SELECT * FROM t1;
145
146 connection con1;
147 --echo 'T2'
148 SET DEBUG_SYNC='now SIGNAL waiting1';
149 --echo 'Signalled T3'
150
151 connection con2;
152 --echo 'T3'
153 reap;
154
155 connection con2;
156 --echo 'T3'
157 SET DEBUG_SYNC='row_select_wait WAIT_FOR waiting1';
158 --send SELECT * FROM t2;
159
160 connection default;
161 --echo 'T2'
162 SET DEBUG_SYNC='now SIGNAL waiting1';
163 --echo 'Signalled T3'
164
165 connection con2;
166 --echo 'T3'
167 reap;
168
169 connection default;
170 disconnect con1;
171 disconnect con2;
172
173 DROP TABLE t1;
174 DROP TABLE t2;
175
176 --echo #
177 --echo # Bug 21433768: NON-REPEATABLE READ WITH REPEATABLE READ ISOLATION
178 --echo #
179
180 --connect (con1,localhost,root,,)
181 --echo # connection con1
182
183 CREATE TABLE t1(col1 INT PRIMARY KEY, col2 INT) ENGINE = InnoDB;
184 INSERT INTO t1 values (1, 0), (2, 0);
185 SELECT * FROM t1 ORDER BY col1;
186
187 START TRANSACTION;
188 UPDATE t1 SET col2 = 100;
189 SET DEBUG_SYNC = 'after_trx_committed_in_memory SIGNAL s1 WAIT_FOR s2';
190 --send COMMIT;
191
192 connection default;
193 --echo # connection default
194 SET DEBUG_SYNC = 'now WAIT_FOR s1';
195 UPDATE t1 SET col2 = col2 + 10 where col1 = 1;
196 COMMIT;
197
198 SELECT * FROM t1 ORDER BY col1;
199 SET DEBUG_SYNC = 'now SIGNAL s2';
200
201 connection con1;
202 --echo # connection con1
203 --echo # reap COMMIT for con1
204 reap;
205
206 connection default;
207 --echo # connection default
208 disconnect con1;
209
210 DROP TABLE t1;
211
212 # Clean up resources used in this test case.
213 --disable_warnings
214 SET DEBUG_SYNC= 'RESET';
215 --enable_warnings
216 --source include/wait_until_count_sessions.inc