Debian Codegraph @

mysql-test/suite/innodb/t/lock_isolation.test

raw
1 --source include/have_innodb.inc
2 --source include/count_sessions.inc
3 --source include/have_debug.inc
4 --source include/have_debug_sync.inc
5
6 --disable_query_log
7 call mtr.add_suppression("InnoDB: Transaction was aborted due to ");
8 --enable_query_log
9
10 --connect disable_purging,localhost,root
11 START TRANSACTION WITH CONSISTENT SNAPSHOT;
12
13 --connection default
14 --echo #
15 --echo # MDEV-26642 Weird SELECT view when a record is
16 --echo # modified to the same value by two transactions
17 --echo # MDEV-32898 Phantom rows caused by updates of PRIMARY KEY
18 --echo #
19
20 CREATE TABLE t(a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
21 INSERT INTO t VALUES (1,1),(2,2);
22 BEGIN; SELECT * FROM t LOCK IN SHARE MODE;
23 --connect con_weird,localhost,root
24 SET innodb_snapshot_isolation=OFF;
25 BEGIN;
26 SELECT * FROM t;
27 --connect consistent,localhost,root
28 SET innodb_snapshot_isolation=ON;
29 BEGIN;
30 SELECT * FROM t;
31 --connection default
32 UPDATE t SET a=3 WHERE b=2;
33 COMMIT;
34 --connection consistent
35 --error ER_CHECKREAD
36 UPDATE t SET b=3;
37 SELECT * FROM t;
38 COMMIT;
39 --connection con_weird
40 UPDATE t SET b=3;
41 SELECT * FROM t;
42 COMMIT;
43 --connection default
44 SELECT * FROM t;
45 DROP TABLE t;
46
47 --echo #
48 --echo # MDEV-26643 Inconsistent behaviors of UPDATE under
49 --echo # READ UNCOMMITTED and READ COMMITTED isolation level
50 --echo #
51
52 CREATE TABLE t(a INT, b INT) ENGINE=InnoDB STATS_PERSISTENT=0;
53 INSERT INTO t VALUES(NULL, 1), (2, 2);
54 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
55 BEGIN; UPDATE t SET a = 10;
56
57 --connection consistent
58 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
59 SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
60 --send UPDATE t SET b = 20 WHERE a
61
62 --connection default
63 SET DEBUG_SYNC="now WAIT_FOR select_blocked";
64 COMMIT;
65
66 --connection consistent
67 --reap
68 SELECT * FROM t;
69
70 --connection default
71 TRUNCATE TABLE t;
72 INSERT INTO t VALUES(NULL, 1), (2, 2);
73 BEGIN; UPDATE t SET a = 10;
74
75 --connection consistent
76 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
77 SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
78 --send UPDATE t SET b = 20 WHERE a
79
80 --connection default
81 SET DEBUG_SYNC="now WAIT_FOR select_blocked";
82 COMMIT;
83
84 --connection consistent
85 --reap
86 SELECT * FROM t;
87
88 --connection default
89 TRUNCATE TABLE t;
90 INSERT INTO t VALUES(NULL, 1), (2, 2);
91 BEGIN; UPDATE t SET a = 10;
92
93 --connection con_weird
94 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
95 SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
96 send UPDATE t SET b = 20 WHERE a;
97
98 --connection default
99 SET DEBUG_SYNC="now WAIT_FOR select_blocked";
100 SELECT * FROM t;
101 COMMIT;
102
103 --connection con_weird
104 --reap
105 COMMIT;
106
107 --connection default
108 SELECT * FROM t;
109 TRUNCATE TABLE t;
110
111 --echo #
112 --echo # MDEV-34108 Inappropriate semi-consistent read in snapshot isolation
113 --echo #
114 INSERT INTO t VALUES(NULL, 1), (1, 1);
115 BEGIN;
116 UPDATE t SET b = 3;
117
118 --connection consistent
119 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
120 BEGIN;
121 # As semi-consistent read is disabled for innodb_snapshot_isolation=ON, the
122 # following UPDATE must be blocked on the first record.
123 SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
124 --send UPDATE t SET b = 2 WHERE a
125
126 --connection default
127 SET DEBUG_SYNC="now WAIT_FOR select_blocked";
128 UPDATE t SET a = 1;
129 COMMIT;
130 --connection consistent
131 # If the bug wouldn't be fixed, the result would be (1,3),(1,2), because
132 # "UPDATE t SET b = 2 WHERE a" would be blocked on the second (1,3) record,
133 # as semi-consistent read would filter out the first (null,3) record without
134 # blocking.
135 --reap
136 COMMIT;
137
138 --connection default
139 SELECT * FROM t;
140 DROP TABLE t;
141
142 --echo #
143 --echo # MDEV-33802 Weird read view after ROLLBACK of other transactions
144 --echo #
145
146 CREATE TABLE t(a INT PRIMARY KEY, b INT UNIQUE) ENGINE=InnoDB STATS_PERSISTENT=0;
147
148 --connection consistent
149 START TRANSACTION WITH CONSISTENT SNAPSHOT;
150
151 --connection default
152 INSERT INTO t SET a=1;
153
154 --connection consistent
155 SAVEPOINT sp1;
156 --disable_ps2_protocol
157 --error ER_CHECKREAD
158 SELECT * FROM t FORCE INDEX (b) FOR UPDATE;
159 --enable_ps2_protocol
160 SAVEPOINT sp1;
161
162 --connection default
163 BEGIN; INSERT INTO t SET a=2;
164
165 --connection con_weird
166 START TRANSACTION WITH CONSISTENT SNAPSHOT;
167 SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
168 --send SELECT * FROM t FORCE INDEX (b) FOR UPDATE
169
170 --connection default
171 SET DEBUG_SYNC="now WAIT_FOR select_blocked";
172 ROLLBACK;
173
174 --connection con_weird
175 --reap
176 SELECT * FROM t FORCE INDEX (b) FOR UPDATE;
177 COMMIT;
178
179 --connection consistent
180 SELECT * FROM t FORCE INDEX (b) FOR UPDATE;
181 COMMIT;
182
183 --connection default
184 TRUNCATE TABLE t;
185
186 --echo #
187 --echo # MDEV-36639 innodb_snapshot_isolation=1 gives error for not comitted row changes
188 --echo #
189 INSERT INTO t VALUES (1,1),(2,2);
190
191 --connection default
192 --echo # Case 1: Transaction A modifies a record, transaction B with snapshot
193 --echo # isolation level is blocked by A, then A is committed.
194 --echo # Expected behaviour: B gets ER_CHECKREAD.
195 BEGIN;
196 UPDATE t SET b=3 WHERE a = 1;
197
198 --connection consistent
199 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
200 BEGIN;
201 SELECT * FROM t;
202 SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
203 --send SELECT * FROM t WHERE a=1 FOR UPDATE
204
205 --connection default
206 SET DEBUG_SYNC="now WAIT_FOR select_blocked";
207 COMMIT;
208
209 --connection consistent
210 --error ER_CHECKREAD
211 --reap
212
213 --echo # Case 2: Transaction A modifies a record, transaction B with snapshot
214 --echo # isolation level is blocked by A, then A is rolled back.
215 --echo # Expected behaviour: B continues execution.
216
217 --connection default
218 BEGIN;
219 UPDATE t SET b=4 WHERE a=1;
220
221 --connection consistent
222 BEGIN;
223 SELECT * FROM t;
224 SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
225 --send SELECT * FROM t WHERE a=1 FOR UPDATE
226
227 --connection default
228 SET DEBUG_SYNC="now WAIT_FOR select_blocked";
229 ROLLBACK;
230
231 --connection consistent
232 --reap
233 ROLLBACK;
234
235 --echo # Case 3: Transaction B with snapshot isolation level started with
236 --echo # consistent snapshot. Transaction A modifies a record and is committed.
237 --echo # Both B tries to read modified by A record.
238 --echo # Expected behavior: B gets ER_CHECKREAD.
239
240 --connection consistent
241 START TRANSACTION WITH CONSISTENT SNAPSHOT;
242
243 --connection default
244 UPDATE t SET b=4 WHERE a=1;
245
246 --connection consistent
247 --error ER_CHECKREAD
248 SELECT * FROM t WHERE a=1 FOR UPDATE;
249 --disconnect disable_purging
250
251 --connection default
252 SET DEBUG_SYNC="RESET";
253 DROP TABLE t;
254
255 CREATE TABLE t1(a INT) ENGINE=InnoDB STATS_PERSISTENT=0;
256 CREATE TABLE t2(a INT) ENGINE=InnoDB STATS_PERSISTENT=0;
257 BEGIN; INSERT INTO t1 SET a=1;
258 --connection con_weird
259 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
260 BEGIN; INSERT INTO t2 SET a=1;
261 --connection consistent
262 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
263 BEGIN; INSERT INTO t2 SET a=2;
264 --connection default
265 COMMIT;
266 --connection con_weird
267 SELECT * FROM t1;
268 COMMIT;
269 --connection consistent
270 --disable_ps2_protocol
271 --error ER_CHECKREAD
272 SELECT * FROM t1;
273 --enable_ps2_protocol
274 COMMIT;
275 --connection default
276 BEGIN; INSERT INTO t1 SET a=2;
277 --connection con_weird
278 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
279 START TRANSACTION WITH CONSISTENT SNAPSHOT; INSERT INTO t2 SET a=3;
280 --connection consistent
281 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
282 START TRANSACTION WITH CONSISTENT SNAPSHOT; INSERT INTO t2 SET a=2;
283 --connection default
284 COMMIT;
285 --connection con_weird
286 SELECT * FROM t1;
287 COMMIT;
288 --disconnect con_weird
289 --connection consistent
290 --disable_ps2_protocol
291 --error ER_CHECKREAD
292 SELECT * FROM t1;
293 --enable_ps2_protocol
294 COMMIT;
295 --connection default
296 DROP TABLE t1,t2;
297
298 --echo #
299 --echo # MDEV-37215 SELECT...FOR UPDATE crash under SERIALIZABLE
300 --echo #
301
302 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
303 CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB;
304 SELECT * FROM t1 FOR UPDATE;
305 DROP TABLE t1;
306 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
307
308 --echo #
309 --echo # MDEV-39263 Lost update despite innodb_snapshot_isolation
310 --echo #
311
312 CREATE TABLE t1 (f1 INT PRIMARY KEY, val TEXT) ENGINE=InnoDB;
313
314 --connection consistent
315 START TRANSACTION WITH CONSISTENT SNAPSHOT;
316 SELECT * FROM t1 WHERE f1 = 1;
317
318 # Stop autocommit INSERT execution in commit_in_memory() between commit_state()
319 # and trx_sys.deregister_rw(), before locks are released
320 --connection default
321 SET DEBUG_SYNC="trx_after_commit_state SIGNAL committed WAIT_FOR cleanup";
322 send SET STATEMENT innodb_lock_wait_timeout=3 FOR INSERT INTO t1 VALUES(1,'6');
323
324 --connection consistent
325 SET DEBUG_SYNC="now WAIT_FOR committed";
326 --error ER_CHECKREAD
327 SET STATEMENT innodb_lock_wait_timeout=3 FOR
328 UPDATE t1 SET val = CONCAT(val, ',', '3') WHERE f1 = 1;
329
330 SET DEBUG_SYNC="now SIGNAL cleanup";
331 --disconnect consistent
332
333 --connection default
334
335 --reap
336 SET DEBUG_SYNC="RESET";
337 DROP TABLE t1;
338
339 --source include/wait_until_count_sessions.inc
340 --echo # End of 10.6 tests