Debian Codegraph @

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

raw
1 CREATE TABLE t0 (
2 id INT PRIMARY KEY,
3 trx_mysql_thread_id BIGINT
4 ) ENGINE=InnoDB;
5 CREATE TABLE t1 (
6 id INT PRIMARY KEY
7 ) ENGINE=InnoDB;
8 INSERT INTO t1 VALUES (1);
9 CREATE TABLE t2 (
10 id INT PRIMARY KEY
11 ) ENGINE=InnoDB;
12 INSERT INTO t2 VALUES (1);
13 CREATE TABLE t3 (
14 id INT PRIMARY KEY
15 ) ENGINE=InnoDB;
16 INSERT INTO t3 VALUES (1);
17 CREATE TABLE t4 (
18 id INT PRIMARY KEY
19 ) ENGINE=InnoDB;
20 INSERT INTO t4 VALUES (1);
21 INSERT INTO t0 VALUES (1, CONNECTION_ID());
22 BEGIN;
23 SELECT * FROM t1 FOR UPDATE;
24 id
25 1
26 INSERT INTO t0 VALUES (2, CONNECTION_ID());
27 BEGIN;
28 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL will_wait_c2';
29 SELECT * FROM t1 FOR UPDATE;
30 INSERT INTO t0 VALUES (3, CONNECTION_ID());
31 SET DEBUG_SYNC = 'now WAIT_FOR will_wait_c2';
32 BEGIN;
33 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL will_wait_c3';
34 SELECT * FROM t1 FOR UPDATE;
35 INSERT INTO t0 VALUES (4, CONNECTION_ID());
36 SET DEBUG_SYNC = 'now WAIT_FOR will_wait_c3';
37 BEGIN;
38 SELECT * FROM t2 FOR UPDATE;
39 id
40 1
41 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL will_wait_c4';
42 SELECT * FROM t1 FOR UPDATE;
43 INSERT INTO t0 VALUES (5, CONNECTION_ID());
44 SET DEBUG_SYNC = 'now WAIT_FOR will_wait_c4';
45 BEGIN;
46 SELECT * FROM t3 FOR UPDATE;
47 id
48 1
49 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL will_wait_c5';
50 SELECT * FROM t1 FOR UPDATE;
51 INSERT INTO t0 VALUES (6, CONNECTION_ID());
52 SET DEBUG_SYNC = 'now WAIT_FOR will_wait_c5';
53 BEGIN;
54 SELECT * FROM t4 FOR UPDATE;
55 id
56 1
57 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL will_wait_c6';
58 SELECT * FROM t1 FOR UPDATE;
59 INSERT INTO t0 VALUES (7, CONNECTION_ID());
60 SET DEBUG_SYNC = 'now WAIT_FOR will_wait_c6';
61 BEGIN;
62 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL will_wait_c7';
63 SELECT * FROM t2 FOR UPDATE;
64 INSERT INTO t0 VALUES (8, CONNECTION_ID());
65 SET DEBUG_SYNC = 'now WAIT_FOR will_wait_c7';
66 BEGIN;
67 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL will_wait_c8';
68 SELECT * FROM t3 FOR UPDATE;
69 INSERT INTO t0 VALUES (9, CONNECTION_ID());
70 SET DEBUG_SYNC = 'now WAIT_FOR will_wait_c8';
71 BEGIN;
72 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL will_wait_c9';
73 SELECT * FROM t4 FOR UPDATE;
74 INSERT INTO t0 VALUES (10, CONNECTION_ID());
75 SET DEBUG_SYNC = 'now WAIT_FOR will_wait_c9';
76 BEGIN;
77 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL will_wait_c10';
78 SELECT * FROM t4 FOR UPDATE;
79 SET DEBUG_SYNC = 'now WAIT_FOR will_wait_c10';
80 SELECT t0.id, trx_state, trx_schedule_weight
81 FROM t0
82 JOIN INFORMATION_SCHEMA.INNODB_TRX USING (trx_mysql_thread_id)
83 ORDER BY t0.id;
84 id trx_state trx_schedule_weight
85 1 RUNNING NULL
86 2 LOCK WAIT 1
87 3 LOCK WAIT 1
88 4 LOCK WAIT 2
89 5 LOCK WAIT 2
90 6 LOCK WAIT 3
91 7 LOCK WAIT 1
92 8 LOCK WAIT 1
93 9 LOCK WAIT 1
94 10 LOCK WAIT 1
95 COMMIT;
96 id
97 1
98 COMMIT;
99 id
100 1
101 COMMIT;
102 id
103 1
104 COMMIT;
105 id
106 1
107 COMMIT;
108 id
109 1
110 COMMIT;
111 id
112 1
113 COMMIT;
114 id
115 1
116 COMMIT;
117 id
118 1
119 COMMIT;
120 id
121 1
122 COMMIT;
123 DROP TABLE t0;
124 DROP TABLE t1;
125 DROP TABLE t2;
126 DROP TABLE t3;
127 DROP TABLE t4;
128 ################################################################
129 # #
130 # Bug #89829: lock_rec_has_to_wait_vats ignores locks held by #
131 # transactions being rolled back #
132 # #
133 ################################################################
134 CREATE TABLE t1 (
135 id INT PRIMARY KEY,
136 val INT NOT NULL
137 ) ENGINE=InnoDB;
138 INSERT INTO t1 (id, val) VALUES (1,1), (3,3), (5,5);
139 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
140 SET @@global.innodb_lock_wait_timeout = 100000;
141 # too_big_to_fail:
142 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
143 BEGIN;
144 INSERT INTO t1 (id, val)
145 VALUES (100, 100), (200, 200), (300, 300), (400, 400), (500, 500);
146 SELECT * FROM t1 WHERE id = 2 FOR UPDATE;
147 id val
148 # victim:
149 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
150 BEGIN;
151 SELECT * FROM t1 WHERE id = 4 FOR UPDATE;
152 id val
153 UPDATE t1 SET val = 200 WHERE id = 3;
154 SET DEBUG_SYNC =
155 'lock_wait_has_finished_waiting WAIT_FOR victim_can_rollback';
156 INSERT INTO t1 (id, val) VALUES (2,2);
157 # seer:
158 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
159 BEGIN;
160 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL seer_will_wait';
161 UPDATE t1 SET val = val + 10 WHERE id = 3;
162 # too_big_to_fail:
163 SET DEBUG_SYNC = 'now WAIT_FOR seer_will_wait';
164 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL too_big_to_fail_will_wait';
165 INSERT INTO t1 (id, val) VALUES (4, 4);
166 # default:
167 SET DEBUG_SYNC = 'now WAIT_FOR too_big_to_fail_will_wait';
168 # Number of X locks granted: 1
169 SET DEBUG_SYNC = 'now SIGNAL victim_can_rollback';
170 # seer:
171 SELECT * FROM t1 WHERE id = 3 FOR UPDATE;
172 id val
173 3 13
174 SET DEBUG_SYNC = 'now SIGNAL victim_can_rollback';
175 COMMIT;
176 # victim:
177 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
178 ROLLBACK;
179 # too_big_to_fail:
180 ROLLBACK;
181 # default:
182 SELECT * FROM t1;
183 id val
184 1 1
185 3 13
186 5 5
187 DROP TABLE t1;
188 SET DEBUG_SYNC = 'RESET';
189 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;
190 #####################
191 # #
192 # End of Bug #89829 #
193 # #
194 #####################
195 #################################################################
196 # #
197 # Bug #89932: RecLock::add_to_waitq calls lock_update_age on #
198 # DB_DEADLOCK inflating trx->age transactions being #
199 # rolled back #
200 # #
201 #################################################################
202 ##############
203 # #
204 # Scenario 1 #
205 # #
206 ##############
207 CREATE TABLE t1 (
208 id INT PRIMARY KEY
209 ) ENGINE=InnoDB;
210 INSERT INTO t1 (id) VALUES (0);
211 INSERT INTO t1 (id) VALUES (1);
212 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
213 SET @@global.innodb_lock_wait_timeout = 100000;
214 # too_big_to_fail:
215 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
216 BEGIN;
217 INSERT INTO t1 (id) VALUES (-100), (-200), (-300), (-400), (-500);
218 #connection victim
219 BEGIN;
220 SELECT * FROM t1 WHERE id = 0 FOR UPDATE;
221 id
222 0
223 #connection too_big_to_fail
224 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL too_big_to_fail_waits';
225 SELECT * FROM t1 WHERE id = 0 FOR UPDATE;
226 SELECT trx_state, trx_schedule_weight
227 FROM INFORMATION_SCHEMA.INNODB_TRX
228 WHERE trx_mysql_thread_id=@too_big_to_fail_id;
229 trx_state trx_schedule_weight
230 LOCK WAIT 1
231 #connection victim
232 SET DEBUG_SYNC = 'now WAIT_FOR too_big_to_fail_waits';
233 SELECT * FROM t1 WHERE id = -100 FOR UPDATE;
234 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
235 #connection too_big_to_fail
236 id
237 0
238 #connection victim
239 BEGIN;
240 SELECT * FROM t1 WHERE id = 1 FOR UPDATE;
241 id
242 1
243 #connection too_big_to_fail
244 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL too_big_to_fail_waits';
245 SELECT * FROM t1 WHERE id = 1 FOR UPDATE;
246 SELECT trx_state, trx_schedule_weight
247 FROM INFORMATION_SCHEMA.INNODB_TRX
248 WHERE trx_mysql_thread_id=@too_big_to_fail_id;
249 trx_state trx_schedule_weight
250 LOCK WAIT 1
251 #connection victim
252 SET DEBUG_SYNC = 'now WAIT_FOR too_big_to_fail_waits';
253 SELECT * FROM t1 WHERE id = -100 FOR UPDATE;
254 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
255 #connection too_big_to_fail
256 id
257 1
258 ROLLBACK;
259 SET GLOBAL DEBUG = '-d,lock_update_trx_age_check_age_limit';
260 DROP TABLE t1;
261 SET DEBUG_SYNC = 'RESET';
262 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;
263 ##############
264 # #
265 # Scenario 2 #
266 # #
267 ##############
268 CREATE TABLE t1 (
269 id INT PRIMARY KEY
270 ) ENGINE=InnoDB;
271 INSERT INTO t1 (id) VALUES (0),(1);
272 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
273 SET @@global.innodb_lock_wait_timeout = 100000;
274 # beneficiary:
275 BEGIN;
276 SELECT * FROM t1 WHERE id = 0 FOR SHARE;
277 id
278 0
279 # too_big_to_fail:
280 BEGIN;
281 INSERT INTO t1 (id) VALUES (-100), (-200), (-300), (-400), (-500);
282 SELECT * FROM t1 WHERE id = 0 FOR SHARE;
283 id
284 0
285 #connection victim
286 BEGIN;
287 SELECT * FROM t1 WHERE id = 1 FOR UPDATE;
288 id
289 1
290 #connection too_big_to_fail
291 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL too_big_to_fail_waits';
292 SELECT * FROM t1 WHERE id = 1 FOR UPDATE;
293 SELECT trx_state, trx_schedule_weight
294 FROM INFORMATION_SCHEMA.INNODB_TRX
295 WHERE trx_mysql_thread_id=@too_big_to_fail_id;
296 trx_state trx_schedule_weight
297 LOCK WAIT 1
298 #connection victim
299 SET DEBUG_SYNC = 'now WAIT_FOR too_big_to_fail_waits';
300 SELECT * FROM t1 WHERE id = 0 FOR UPDATE;
301 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
302 #connection too_big_to_fail
303 id
304 1
305 ROLLBACK;
306 # too_big_to_fail:
307 BEGIN;
308 INSERT INTO t1 (id) VALUES (-100), (-200), (-300), (-400), (-500);
309 SELECT * FROM t1 WHERE id = 0 FOR SHARE;
310 id
311 0
312 #connection victim
313 BEGIN;
314 SELECT * FROM t1 WHERE id = 1 FOR UPDATE;
315 id
316 1
317 #connection too_big_to_fail
318 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL too_big_to_fail_waits';
319 SELECT * FROM t1 WHERE id = 1 FOR UPDATE;
320 SELECT trx_state, trx_schedule_weight
321 FROM INFORMATION_SCHEMA.INNODB_TRX
322 WHERE trx_mysql_thread_id=@too_big_to_fail_id;
323 trx_state trx_schedule_weight
324 LOCK WAIT 1
325 #connection victim
326 SET DEBUG_SYNC = 'now WAIT_FOR too_big_to_fail_waits';
327 SELECT * FROM t1 WHERE id = 0 FOR UPDATE;
328 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
329 #connection too_big_to_fail
330 id
331 1
332 ROLLBACK;
333 ROLLBACK;
334 SET GLOBAL DEBUG = '-d,lock_update_trx_age_check_age_limit';
335 DROP TABLE t1;
336 SET DEBUG_SYNC = 'RESET';
337 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;
338 #####################
339 # #
340 # End of Bug #89932 #
341 # #
342 #####################
343 #####################################################################
344 # #
345 # Bug #89737: Using VATS with spatial index can lead to transaction #
346 # never being woken up #
347 # #
348 #####################################################################
349 CREATE TABLE t1 (
350 id INT PRIMARY KEY,
351 p GEOMETRY NOT NULL SRID 0,
352 SPATIAL KEY p_idx(p)
353 ) ENGINE=InnoDB;
354 INSERT INTO t1 (id, p) VALUES
355 (1,POINT(1,1)),
356 (2,POINT(2,2)),
357 (3,POINT(3,3)),
358 (4,POINT(4,4));
359 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
360 SET @@global.innodb_lock_wait_timeout = 10;
361 # Using CATS
362 # in selecting_thread
363 USE test;
364 BEGIN;
365 SELECT id
366 FROM t1
367 WHERE MBRContains(
368 ST_GeomFromText('Polygon((2 2, 2 4, 4 4, 4 2, 2 2))'),
369 p
370 )
371 FOR UPDATE;
372 id
373 3
374 # in inserting_thread
375 USE test;
376 BEGIN;
377 SET DEBUG_SYNC =
378 'lock_wait_will_wait SIGNAL inserting_thread_waits';
379 INSERT INTO t1 (id, p) VALUES (0, POINT (3, 3));;
380 # in selecting_thread
381 SET DEBUG_SYNC = 'now WAIT_FOR inserting_thread_waits';
382 COMMIT;
383 # in inserting_thread
384 ROLLBACK;
385 DROP TABLE t1;
386 SET DEBUG_SYNC = 'RESET';
387 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;
388 #####################
389 # #
390 # End of Bug #89737 #
391 # #
392 #####################