Debian Codegraph @

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

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