Debian Codegraph @

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

raw
1 # Bug #27491839 INNODB: ASSERTION FAILURE:
2 # LOCK0LOCK.CC:NNN:!LOCK_REC_OTHER_TRX_HOLDS_EXPL( LOCK
3 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
4 SET @@global.innodb_lock_wait_timeout = 100000;
5 # Scenario 1
6 CREATE TABLE t1(
7 id INT NOT NULL,
8 c1 INT NOT NULL,
9 c2 INT NOT NULL,
10 v1 INT AS (c1) VIRTUAL,
11 PRIMARY KEY (id DESC),
12 UNIQUE KEY(v1)
13 ) Engine=InnoDB;
14 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
15 BEGIN;
16 UPDATE t1 SET c2=13 WHERE id = 3;
17 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
18 BEGIN;
19 SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
20 1
21 1
22 BEGIN;
23 UPDATE t1 SET c2=13 WHERE id=1;
24 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
25 SELECT 1 FROM t1 WHERE v1=1 FOR UPDATE;;
26 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
27 ROLLBACK;
28 1
29 1
30 ROLLBACK;
31 ROLLBACK;
32 DROP TABLE t1;
33 # Scenario 2
34 CREATE TABLE t1(
35 id INT NOT NULL,
36 c1 INT NOT NULL,
37 c2 INT NOT NULL,
38 v1 INT AS (c1) VIRTUAL,
39 PRIMARY KEY (id DESC),
40 UNIQUE KEY(v1)
41 ) Engine=InnoDB;
42 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
43 BEGIN;
44 UPDATE t1 SET c2=13 WHERE id = 3;
45 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
46 UPDATE t1 SET c1=55 WHERE id=1;
47 BEGIN;
48 SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
49 1
50 BEGIN;
51 UPDATE t1 SET c2=13 WHERE id=1;
52 SELECT 1 FROM t1 WHERE v1=1 FOR UPDATE;
53 1
54 ROLLBACK;
55 ROLLBACK;
56 ROLLBACK;
57 DROP TABLE t1;
58 # Scenario 3
59 CREATE TABLE t1(
60 id INT NOT NULL,
61 c1 INT NOT NULL,
62 c2 INT NOT NULL,
63 v1 INT AS (c1) VIRTUAL,
64 PRIMARY KEY (id DESC),
65 UNIQUE KEY(v1)
66 ) Engine=InnoDB;
67 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
68 BEGIN;
69 UPDATE t1 SET c2=13 WHERE id = 3;
70 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
71 BEGIN;
72 DELETE FROM t1 WHERE id=1;
73 BEGIN;
74 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
75 SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
76 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
77 COMMIT;
78 1
79 ROLLBACK;
80 DROP TABLE t1;
81 # Scenario 4
82 # Scenario 4a
83 CREATE TABLE t1(
84 id INT NOT NULL,
85 c1 INT NOT NULL,
86 c2 INT NOT NULL,
87 v1 INT AS (c1) VIRTUAL,
88 PRIMARY KEY (id DESC),
89 UNIQUE KEY(v1)
90 ) Engine=InnoDB;
91 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
92 BEGIN;
93 UPDATE t1 SET c2=13 WHERE id = 3;
94 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
95 BEGIN;
96 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
97 UPDATE t1 SET c2=13 WHERE id=2;
98 BEGIN;
99 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
100 SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
101 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
102 COMMIT;
103 1
104 1
105 ROLLBACK;
106 DROP TABLE t1;
107 # Scenario 4b
108 CREATE TABLE t1(
109 id INT NOT NULL,
110 c1 INT NOT NULL,
111 c2 INT NOT NULL,
112 v1 INT AS (c1) VIRTUAL,
113 PRIMARY KEY (id DESC),
114 UNIQUE KEY(v1)
115 ) Engine=InnoDB;
116 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
117 BEGIN;
118 UPDATE t1 SET c2=13 WHERE id = 3;
119 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
120 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
121 BEGIN;
122 UPDATE t1 SET c2=13 WHERE id=2;
123 UPDATE t1 SET c2=42 WHERE id=2;
124 BEGIN;
125 SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
126 1
127 1
128 COMMIT;
129 COMMIT;
130 ROLLBACK;
131 DROP TABLE t1;
132 # Scenario 4c
133 CREATE TABLE t1(
134 id INT NOT NULL,
135 c1 INT NOT NULL,
136 c2 INT NOT NULL,
137 v1 INT AS (c1) VIRTUAL,
138 PRIMARY KEY (id DESC),
139 UNIQUE KEY(v1)
140 ) Engine=InnoDB;
141 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
142 BEGIN;
143 UPDATE t1 SET c2=13 WHERE id = 3;
144 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
145 BEGIN;
146 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
147 UPDATE t1 SET c1=10 WHERE id=2;
148 UPDATE t1 SET c2=13 WHERE id=2;
149 BEGIN;
150 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
151 SELECT 1 FROM t1 WHERE v1=10 FOR SHARE;
152 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
153 COMMIT;
154 1
155 1
156 ROLLBACK;
157 DROP TABLE t1;
158 # Scenario 4d
159 CREATE TABLE t1(
160 id INT NOT NULL,
161 c1 INT NOT NULL,
162 c2 INT NOT NULL,
163 v1 INT AS (c1) VIRTUAL,
164 PRIMARY KEY (id DESC),
165 UNIQUE KEY(v1)
166 ) Engine=InnoDB;
167 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
168 BEGIN;
169 UPDATE t1 SET c2=13 WHERE id = 3;
170 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
171 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
172 BEGIN;
173 UPDATE t1 SET c1=10 WHERE id=2;
174 UPDATE t1 SET c2=13 WHERE id=2;
175 BEGIN;
176 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
177 SELECT 1 FROM t1 WHERE v1=10 FOR SHARE;
178 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
179 COMMIT;
180 1
181 1
182 ROLLBACK;
183 DROP TABLE t1;
184 # Scenario 4e
185 CREATE TABLE t1(
186 id INT NOT NULL,
187 c1 INT NOT NULL,
188 c2 INT NOT NULL,
189 v1 INT AS (c1) VIRTUAL,
190 PRIMARY KEY (id DESC),
191 UNIQUE KEY(v1)
192 ) Engine=InnoDB;
193 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
194 BEGIN;
195 UPDATE t1 SET c2=13 WHERE id = 3;
196 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
197 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
198 BEGIN;
199 UPDATE t1 SET c2=13 WHERE id=2;
200 BEGIN;
201 SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
202 1
203 1
204 COMMIT;
205 COMMIT;
206 ROLLBACK;
207 DROP TABLE t1;
208 # Scenario 1
209 CREATE TABLE t1(
210 id INT NOT NULL,
211 c1 INT NOT NULL,
212 c2 INT NOT NULL,
213 PRIMARY KEY (id DESC),
214 UNIQUE KEY(c1)
215 ) Engine=InnoDB;
216 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
217 BEGIN;
218 UPDATE t1 SET c2=13 WHERE id = 3;
219 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
220 BEGIN;
221 SELECT 1 FROM t1 WHERE c1=1 FOR SHARE;
222 1
223 1
224 BEGIN;
225 UPDATE t1 SET c2=13 WHERE id=1;
226 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
227 SELECT 1 FROM t1 WHERE c1=1 FOR UPDATE;;
228 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
229 ROLLBACK;
230 1
231 1
232 ROLLBACK;
233 ROLLBACK;
234 DROP TABLE t1;
235 # Scenario 2
236 CREATE TABLE t1(
237 id INT NOT NULL,
238 c1 INT NOT NULL,
239 c2 INT NOT NULL,
240 PRIMARY KEY (id DESC),
241 UNIQUE KEY(c1)
242 ) Engine=InnoDB;
243 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
244 BEGIN;
245 UPDATE t1 SET c2=13 WHERE id = 3;
246 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
247 UPDATE t1 SET c1=55 WHERE id=1;
248 BEGIN;
249 SELECT 1 FROM t1 WHERE c1=1 FOR SHARE;
250 1
251 BEGIN;
252 UPDATE t1 SET c2=13 WHERE id=1;
253 SELECT 1 FROM t1 WHERE c1=1 FOR UPDATE;
254 1
255 ROLLBACK;
256 ROLLBACK;
257 ROLLBACK;
258 DROP TABLE t1;
259 # Scenario 3
260 CREATE TABLE t1(
261 id INT NOT NULL,
262 c1 INT NOT NULL,
263 c2 INT NOT NULL,
264 PRIMARY KEY (id DESC),
265 UNIQUE KEY(c1)
266 ) Engine=InnoDB;
267 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
268 BEGIN;
269 UPDATE t1 SET c2=13 WHERE id = 3;
270 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
271 BEGIN;
272 DELETE FROM t1 WHERE id=1;
273 BEGIN;
274 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
275 SELECT 1 FROM t1 WHERE c1=1 FOR SHARE;
276 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
277 COMMIT;
278 1
279 ROLLBACK;
280 DROP TABLE t1;
281 # Scenario 4
282 # Scenario 4a
283 CREATE TABLE t1(
284 id INT NOT NULL,
285 c1 INT NOT NULL,
286 c2 INT NOT NULL,
287 PRIMARY KEY (id DESC),
288 UNIQUE KEY(c1)
289 ) Engine=InnoDB;
290 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
291 BEGIN;
292 UPDATE t1 SET c2=13 WHERE id = 3;
293 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
294 BEGIN;
295 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
296 UPDATE t1 SET c2=13 WHERE id=2;
297 BEGIN;
298 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
299 SELECT 1 FROM t1 WHERE c1=2 FOR SHARE;
300 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
301 COMMIT;
302 1
303 1
304 ROLLBACK;
305 DROP TABLE t1;
306 # Scenario 4b
307 CREATE TABLE t1(
308 id INT NOT NULL,
309 c1 INT NOT NULL,
310 c2 INT NOT NULL,
311 PRIMARY KEY (id DESC),
312 UNIQUE KEY(c1)
313 ) Engine=InnoDB;
314 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
315 BEGIN;
316 UPDATE t1 SET c2=13 WHERE id = 3;
317 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
318 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
319 BEGIN;
320 UPDATE t1 SET c2=13 WHERE id=2;
321 UPDATE t1 SET c2=42 WHERE id=2;
322 BEGIN;
323 SELECT 1 FROM t1 WHERE c1=2 FOR SHARE;
324 1
325 1
326 COMMIT;
327 COMMIT;
328 ROLLBACK;
329 DROP TABLE t1;
330 # Scenario 4c
331 CREATE TABLE t1(
332 id INT NOT NULL,
333 c1 INT NOT NULL,
334 c2 INT NOT NULL,
335 PRIMARY KEY (id DESC),
336 UNIQUE KEY(c1)
337 ) Engine=InnoDB;
338 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
339 BEGIN;
340 UPDATE t1 SET c2=13 WHERE id = 3;
341 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
342 BEGIN;
343 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
344 UPDATE t1 SET c1=10 WHERE id=2;
345 UPDATE t1 SET c2=13 WHERE id=2;
346 BEGIN;
347 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
348 SELECT 1 FROM t1 WHERE c1=10 FOR SHARE;
349 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
350 COMMIT;
351 1
352 1
353 ROLLBACK;
354 DROP TABLE t1;
355 # Scenario 4d
356 CREATE TABLE t1(
357 id INT NOT NULL,
358 c1 INT NOT NULL,
359 c2 INT NOT NULL,
360 PRIMARY KEY (id DESC),
361 UNIQUE KEY(c1)
362 ) Engine=InnoDB;
363 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
364 BEGIN;
365 UPDATE t1 SET c2=13 WHERE id = 3;
366 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
367 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
368 BEGIN;
369 UPDATE t1 SET c1=10 WHERE id=2;
370 UPDATE t1 SET c2=13 WHERE id=2;
371 BEGIN;
372 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
373 SELECT 1 FROM t1 WHERE c1=10 FOR SHARE;
374 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
375 COMMIT;
376 1
377 1
378 ROLLBACK;
379 DROP TABLE t1;
380 # Scenario 4e
381 CREATE TABLE t1(
382 id INT NOT NULL,
383 c1 INT NOT NULL,
384 c2 INT NOT NULL,
385 PRIMARY KEY (id DESC),
386 UNIQUE KEY(c1)
387 ) Engine=InnoDB;
388 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
389 BEGIN;
390 UPDATE t1 SET c2=13 WHERE id = 3;
391 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
392 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
393 BEGIN;
394 UPDATE t1 SET c2=13 WHERE id=2;
395 BEGIN;
396 SELECT 1 FROM t1 WHERE c1=2 FOR SHARE;
397 1
398 1
399 COMMIT;
400 COMMIT;
401 ROLLBACK;
402 DROP TABLE t1;
403 # Scenario 1
404 CREATE TABLE t1(
405 id INT NOT NULL,
406 c1 INT NOT NULL,
407 c2 INT NOT NULL,
408 c3 INT NOT NULL DEFAULT 1337,
409 v1 INT AS (c1) VIRTUAL,
410 PRIMARY KEY (id DESC),
411 UNIQUE KEY(v1),
412 KEY (c2)
413 ) Engine=InnoDB;
414 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
415 BEGIN;
416 UPDATE t1 SET c2=13 WHERE id = 3;
417 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
418 BEGIN;
419 SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
420 1
421 1
422 BEGIN;
423 UPDATE t1 SET c2=13 WHERE id=1;
424 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
425 SELECT 1 FROM t1 WHERE v1=1 FOR UPDATE;;
426 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
427 ROLLBACK;
428 1
429 1
430 ROLLBACK;
431 ROLLBACK;
432 DROP TABLE t1;
433 # Scenario 2
434 CREATE TABLE t1(
435 id INT NOT NULL,
436 c1 INT NOT NULL,
437 c2 INT NOT NULL,
438 c3 INT NOT NULL DEFAULT 1337,
439 v1 INT AS (c1) VIRTUAL,
440 PRIMARY KEY (id DESC),
441 UNIQUE KEY(v1),
442 KEY (c2)
443 ) Engine=InnoDB;
444 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
445 BEGIN;
446 UPDATE t1 SET c2=13 WHERE id = 3;
447 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
448 UPDATE t1 SET c1=55 WHERE id=1;
449 BEGIN;
450 SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
451 1
452 BEGIN;
453 UPDATE t1 SET c2=13 WHERE id=1;
454 SELECT 1 FROM t1 WHERE v1=1 FOR UPDATE;
455 1
456 ROLLBACK;
457 ROLLBACK;
458 ROLLBACK;
459 DROP TABLE t1;
460 # Scenario 2b
461 CREATE TABLE t1(
462 id INT NOT NULL,
463 c1 INT NOT NULL,
464 c2 INT NOT NULL,
465 c3 INT NOT NULL DEFAULT 1337,
466 v1 INT AS (c1) VIRTUAL,
467 PRIMARY KEY (id DESC),
468 UNIQUE KEY(v1),
469 KEY (c2)
470 ) Engine=InnoDB;
471 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
472 BEGIN;
473 UPDATE t1 SET c2=13 WHERE id = 3;
474 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
475 UPDATE t1 SET c1=55 WHERE id=1;
476 BEGIN;
477 SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
478 1
479 BEGIN;
480 UPDATE t1 SET c3=13 WHERE id=1;
481 UPDATE t1 SET c2=13 WHERE id=1;
482 SELECT 1 FROM t1 WHERE v1=1 FOR UPDATE;
483 1
484 ROLLBACK;
485 ROLLBACK;
486 ROLLBACK;
487 DROP TABLE t1;
488 # Scenario 3
489 CREATE TABLE t1(
490 id INT NOT NULL,
491 c1 INT NOT NULL,
492 c2 INT NOT NULL,
493 c3 INT NOT NULL DEFAULT 1337,
494 v1 INT AS (c1) VIRTUAL,
495 PRIMARY KEY (id DESC),
496 UNIQUE KEY(v1),
497 KEY (c2)
498 ) Engine=InnoDB;
499 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
500 BEGIN;
501 UPDATE t1 SET c2=13 WHERE id = 3;
502 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
503 BEGIN;
504 DELETE FROM t1 WHERE id=1;
505 BEGIN;
506 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
507 SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
508 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
509 COMMIT;
510 1
511 ROLLBACK;
512 DROP TABLE t1;
513 # Scenario 4
514 # Scenario 4a
515 CREATE TABLE t1(
516 id INT NOT NULL,
517 c1 INT NOT NULL,
518 c2 INT NOT NULL,
519 c3 INT NOT NULL DEFAULT 1337,
520 v1 INT AS (c1) VIRTUAL,
521 PRIMARY KEY (id DESC),
522 UNIQUE KEY(v1),
523 KEY (c2)
524 ) Engine=InnoDB;
525 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
526 BEGIN;
527 UPDATE t1 SET c2=13 WHERE id = 3;
528 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
529 BEGIN;
530 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
531 UPDATE t1 SET c2=13 WHERE id=2;
532 BEGIN;
533 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
534 SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
535 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
536 COMMIT;
537 1
538 1
539 ROLLBACK;
540 DROP TABLE t1;
541 # Scenario 4b
542 CREATE TABLE t1(
543 id INT NOT NULL,
544 c1 INT NOT NULL,
545 c2 INT NOT NULL,
546 c3 INT NOT NULL DEFAULT 1337,
547 v1 INT AS (c1) VIRTUAL,
548 PRIMARY KEY (id DESC),
549 UNIQUE KEY(v1),
550 KEY (c2)
551 ) Engine=InnoDB;
552 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
553 BEGIN;
554 UPDATE t1 SET c2=13 WHERE id = 3;
555 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
556 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
557 BEGIN;
558 UPDATE t1 SET c2=13 WHERE id=2;
559 UPDATE t1 SET c2=42 WHERE id=2;
560 BEGIN;
561 SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
562 1
563 1
564 COMMIT;
565 COMMIT;
566 ROLLBACK;
567 DROP TABLE t1;
568 # Scenario 4b-prime
569 CREATE TABLE t1(
570 id INT NOT NULL,
571 c1 INT NOT NULL,
572 c2 INT NOT NULL,
573 c3 INT NOT NULL DEFAULT 1337,
574 v1 INT AS (c1) VIRTUAL,
575 PRIMARY KEY (id DESC),
576 UNIQUE KEY(v1),
577 KEY (c2)
578 ) Engine=InnoDB;
579 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
580 BEGIN;
581 UPDATE t1 SET c2=13 WHERE id = 3;
582 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
583 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
584 BEGIN;
585 UPDATE t1 SET c3=13 WHERE id=2;
586 UPDATE t1 SET c2=42 WHERE id=2;
587 BEGIN;
588 SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
589 1
590 1
591 COMMIT;
592 COMMIT;
593 ROLLBACK;
594 DROP TABLE t1;
595 # Scenario 4c
596 CREATE TABLE t1(
597 id INT NOT NULL,
598 c1 INT NOT NULL,
599 c2 INT NOT NULL,
600 c3 INT NOT NULL DEFAULT 1337,
601 v1 INT AS (c1) VIRTUAL,
602 PRIMARY KEY (id DESC),
603 UNIQUE KEY(v1),
604 KEY (c2)
605 ) Engine=InnoDB;
606 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
607 BEGIN;
608 UPDATE t1 SET c2=13 WHERE id = 3;
609 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
610 BEGIN;
611 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
612 UPDATE t1 SET c1=10 WHERE id=2;
613 UPDATE t1 SET c2=13 WHERE id=2;
614 BEGIN;
615 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
616 SELECT 1 FROM t1 WHERE v1=10 FOR SHARE;
617 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
618 COMMIT;
619 1
620 1
621 ROLLBACK;
622 DROP TABLE t1;
623 # Scenario 4d
624 CREATE TABLE t1(
625 id INT NOT NULL,
626 c1 INT NOT NULL,
627 c2 INT NOT NULL,
628 c3 INT NOT NULL DEFAULT 1337,
629 v1 INT AS (c1) VIRTUAL,
630 PRIMARY KEY (id DESC),
631 UNIQUE KEY(v1),
632 KEY (c2)
633 ) Engine=InnoDB;
634 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
635 BEGIN;
636 UPDATE t1 SET c2=13 WHERE id = 3;
637 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
638 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
639 BEGIN;
640 UPDATE t1 SET c1=10 WHERE id=2;
641 UPDATE t1 SET c2=13 WHERE id=2;
642 BEGIN;
643 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
644 SELECT 1 FROM t1 WHERE v1=10 FOR SHARE;
645 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
646 COMMIT;
647 1
648 1
649 ROLLBACK;
650 DROP TABLE t1;
651 # Scenario 4e
652 CREATE TABLE t1(
653 id INT NOT NULL,
654 c1 INT NOT NULL,
655 c2 INT NOT NULL,
656 c3 INT NOT NULL DEFAULT 1337,
657 v1 INT AS (c1) VIRTUAL,
658 PRIMARY KEY (id DESC),
659 UNIQUE KEY(v1),
660 KEY (c2)
661 ) Engine=InnoDB;
662 INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
663 BEGIN;
664 UPDATE t1 SET c2=13 WHERE id = 3;
665 INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
666 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
667 BEGIN;
668 UPDATE t1 SET c2=13 WHERE id=2;
669 BEGIN;
670 SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
671 1
672 1
673 COMMIT;
674 COMMIT;
675 ROLLBACK;
676 DROP TABLE t1;
677 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;