Debian Codegraph @

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

raw
1 --source include/have_debug_sync.inc
2
3 --echo # Bug #27491839 INNODB: ASSERTION FAILURE:
4 --echo # LOCK0LOCK.CC:NNN:!LOCK_REC_OTHER_TRX_HOLDS_EXPL( LOCK
5
6 # Save the original settings, to be restored at the end of test
7 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
8
9 # Make sure that transactions will not finish prematurely
10 SET @@global.innodb_lock_wait_timeout = 100000;
11
12 --echo # Scenario 1
13
14 --source suite/innodb/include/prepare_secondary_index_on_virtual.inc
15
16 # This scenario reproduces the original problem, which was that a transaction
17 # was considered to hold implicit lock on a secondary index entry, even if
18 # it has not modified any column affecting this secondary index.
19 # 1. C1 obtains S lock on secondary index only for id=1
20 # 2. C2 modifies row with id=1, by changing c2, which does not affect
21 # secondary index, and thus it should not be considered to hold an
22 # implicit lock on secondary index. Also, this does not require C2
23 # to pay any attention to C1's lock on secondary index.
24 # 3. C1 tries to get X lock on the same row, which as one of the steps
25 # checks if C2 has implicit lock (and the bug causes it to believe
26 # that C2 holds it) and if so, then if there is some trx holding an
27 # explicit lock (and it turns out that C1 already has the S lock).
28
29 --connect (C1, localhost, root,,)
30 BEGIN;
31 SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
32
33 --connect (C2, localhost, root,,)
34 BEGIN;
35 UPDATE t1 SET c2=13 WHERE id=1;
36
37 --connection C1
38 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
39 --send SELECT 1 FROM t1 WHERE v1=1 FOR UPDATE;
40
41 --connection C2
42 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
43 ROLLBACK;
44
45 --connection C1
46 --reap
47 ROLLBACK;
48
49 --connection default
50 --disconnect C1
51 --disconnect C2
52
53 --source suite/innodb/include/cleanup_secondary_index.inc
54
55
56 --echo # Scenario 2
57
58 --source suite/innodb/include/prepare_secondary_index_on_virtual.inc
59
60 # This scenario is dual to Scenario 1 in some sense, as it deals with delete
61 # marked entries in secondary index.
62 # It is similar to Scenario 1 in that a bad implementation could wrongly
63 # conclude that modifying an unrelated field makes the transaction an implicit
64 # lock owner.
65 # This scenario proves that the bug in the original code was not simply in
66 # negating the result of row_vers_non_vc_index_entry_match.
67 # 1. `default` modifies row with id=1, by setting c1 to 55,
68 # so that that entry with v1=1 becomes delete marked
69 # 2. C1 obtains S lock on secondary index only for v1=1
70 # 3. C2 modifies row with id=1, by changing c2, which does not affect
71 # secondary index, and thus it should not be considered to hold an
72 # implicit lock on secondary index. Also, this does not require C2
73 # to pay any attention to C1's lock on secondary index.
74 # 4. C1 tries to get X lock on the same row, which as one of the steps
75 # checks if C2 has implicit lock (and the bug causes it to believe
76 # that C2 holds it) and if so, then if there is some trx holding an
77 # explicit lock (and it turns out that C1 already has the S lock).
78
79 UPDATE t1 SET c1=55 WHERE id=1;
80
81 --connect (C1, localhost, root,,)
82 BEGIN;
83 SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
84
85 --connect (C2, localhost, root,,)
86 BEGIN;
87 UPDATE t1 SET c2=13 WHERE id=1;
88
89 --connection C1
90 SELECT 1 FROM t1 WHERE v1=1 FOR UPDATE;
91 ROLLBACK;
92
93 --connection C2
94 ROLLBACK;
95
96 --connection default
97 --disconnect C1
98 --disconnect C2
99
100 --source suite/innodb/include/cleanup_secondary_index.inc
101
102 --echo # Scenario 3
103
104 --source suite/innodb/include/prepare_secondary_index_on_virtual.inc
105
106 # This is not a very difficult scenario, but one needed to verify that a
107 # DELETE operation is logged to undo log without UPD_NODE_NO_ORD_CHANGE flag
108 # and thus virtual column fields can be retrieved from undo log.
109 # This is one of the core assumptions for the fix to this bug.
110 # 1. C1 DELETEs record with id=1, and thus should hold implicit lock
111 # on secondary index for v1=1.
112 # 2. C2 performs a SELECT 1 FROM t1 WHERE v1=1 FOR SHARE, and should wait
113 # 3. C1 commits
114 # 4. C2 should see empty result
115
116 --connect (C1, localhost, root,,)
117 BEGIN;
118 DELETE FROM t1 WHERE id=1;
119
120 --connect (C2, localhost, root,,)
121 BEGIN;
122 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
123 --send SELECT 1 FROM t1 WHERE v1=1 FOR SHARE
124
125 --connection C1
126 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
127 COMMIT;
128
129 --connection C2
130 --reap
131
132 --connection default
133 --disconnect C1
134 --disconnect C2
135
136 --source suite/innodb/include/cleanup_secondary_index.inc
137
138 --echo # Scenario 4
139
140 # In this scenario we check the last possible path for missing virtual
141 # columns information: the one where secondary row is not delete marked,
142 # and previous version of primary index row is also not delete marked in
143 # which case we should withold with deciding if there is an implicit lock
144 # until we check trx_id.
145 # There are many subscenarios.
146
147 --echo # Scenario 4a
148
149 --source suite/innodb/include/prepare_secondary_index_on_virtual.inc
150
151 # In 4a we have a case where the previous version is made by active trx,
152 # and there are no older versions, because it started with INSERT.
153 # In such case it should own implicit lock.
154 # 1. C1 INSERTS new row with id=2 v1=2
155 # 2. C1 UPDATES unrelated column c2, so that previous_version is INSERT
156 # 3. C2 does SELECT 1 FROM t1 WHERE v1=2 FOR SHARE and has to wait
157 # 4. C1 commits
158 # 5. C2 sees the new row
159
160 --connect (C1, localhost, root,,)
161 BEGIN;
162 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
163 UPDATE t1 SET c2=13 WHERE id=2;
164
165 --connect (C2, localhost, root,,)
166 BEGIN;
167 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
168 --send SELECT 1 FROM t1 WHERE v1=2 FOR SHARE
169
170 --connection C1
171 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
172 COMMIT;
173
174 --connection C2
175 --reap
176
177
178 --connection default
179 --disconnect C1
180 --disconnect C2
181
182 --source suite/innodb/include/cleanup_secondary_index.inc
183
184 --echo # Scenario 4b
185
186 --source suite/innodb/include/prepare_secondary_index_on_virtual.inc
187
188 # In 4b, again the previous version is made by active trx, and there exists
189 # an even older version, which was not made by active trx and also matches
190 # so in this case there is no implicit lock.
191 # 1. default INSERTs id=1,v1=2
192 # 2. C1 UPDATEs unrelated field c2
193 # 3. C1 UPDATEs unrelated field c2 again
194 # 4. C2 does SELECT 1 FROM t1 WHERE v1=2 FOR SHARE and does not have to wait
195 # 5. C2 COMMITS
196 # 6. C1 COMMITS
197
198 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
199
200 --connect (C1, localhost, root,,)
201 BEGIN;
202 UPDATE t1 SET c2=13 WHERE id=2;
203 UPDATE t1 SET c2=42 WHERE id=2;
204
205 --connect (C2, localhost, root,,)
206 BEGIN;
207 SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
208 COMMIT;
209
210 --connection C1
211 COMMIT;
212
213 --connection default
214 --disconnect C1
215 --disconnect C2
216
217 --source suite/innodb/include/cleanup_secondary_index.inc
218
219 --echo # Scenario 4c
220
221 --source suite/innodb/include/prepare_secondary_index_on_virtual.inc
222
223 # In 4c, the previous version is made by active trx, there is an older version,
224 # also made by trx_id but does not match the secondary index, thus
225 # there is an implicit lock
226 # 1. C1 INSERTs id=2 v1=2
227 # 2. C1 UPDATEs c1 (and thus v1) to 10 for id=2
228 # 3. C1 UPDATEs unrelated field c2
229 # 4. C2 does SELECT 1 FROM t1 WHERE v1=10 FOR SHARE and has to wait
230 # 5. C1 COMMITS
231 # 6. C2 sees the new row
232
233 --connect (C1, localhost, root,,)
234 BEGIN;
235 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
236 UPDATE t1 SET c1=10 WHERE id=2;
237 UPDATE t1 SET c2=13 WHERE id=2;
238
239 --connect (C2, localhost, root,,)
240 BEGIN;
241 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
242 --send SELECT 1 FROM t1 WHERE v1=10 FOR SHARE
243
244 --connection C1
245 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
246 COMMIT;
247
248 --connection C2
249 --reap
250
251 --connection default
252 --disconnect C1
253 --disconnect C2
254
255 --source suite/innodb/include/cleanup_secondary_index.inc
256
257 --echo # Scenario 4d
258
259 --source suite/innodb/include/prepare_secondary_index_on_virtual.inc
260
261 # In 4d, the previous version is made by active trx, there is an older version,
262 # which was not made by trx_id, and does not match secondary index, thus
263 # there is an implicit lock.
264 # 1. default INSERTs id=2 v1=2
265 # 2. C1 UPDATEs c1 (and thus v1) to 10 for id=2
266 # 3. C1 UPDATEs unrelated field c2
267 # 4. C2 does SELECT 1 FROM t1 WHERE v1=10 FOR SHARE and has to wait
268 # 5. C1 COMMITS
269 # 6. C2 sees the new row
270
271 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
272
273 --connect (C1, localhost, root,,)
274 BEGIN;
275 UPDATE t1 SET c1=10 WHERE id=2;
276 UPDATE t1 SET c2=13 WHERE id=2;
277
278 --connect (C2, localhost, root,,)
279 BEGIN;
280 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
281 --send SELECT 1 FROM t1 WHERE v1=10 FOR SHARE
282
283 --connection C1
284 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
285 COMMIT;
286
287 --connection C2
288 --reap
289
290 --connection default
291 --disconnect C1
292 --disconnect C2
293
294 --source suite/innodb/include/cleanup_secondary_index.inc
295
296 --echo # Scenario 4e
297
298 --source suite/innodb/include/prepare_secondary_index_on_virtual.inc
299
300 # In 4e, the previous version is not made by trx_id, so we conclude, that
301 # there is no implicit lock
302 # 1. default INSERTs id=2,v1=2
303 # 2. C1 UPDATEs unrelated field c2
304 # 4. C2 does SELECT 1 FROM t1 WHERE v1=2 FOR SHARE and does not have to wait
305 # 5. C2 COMMITS
306 # 6. C1 COMMITS
307
308 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
309
310 --connect (C1, localhost, root,,)
311 BEGIN;
312 UPDATE t1 SET c2=13 WHERE id=2;
313
314 --connect (C2, localhost, root,,)
315 BEGIN;
316 SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
317 COMMIT;
318
319 --connection C1
320 COMMIT;
321
322 --connection default
323 --disconnect C1
324 --disconnect C2
325
326 --source suite/innodb/include/cleanup_secondary_index.inc
327
328
329
330 #
331 # We will now run through same scenarios as above, but this time we will not use
332 # any virtual columns: we will use c1 in place of v1, just to see if the code
333 # which handles regular secondary columns has same observable behavior as the
334 # code which handles virtual columns
335 #
336
337
338
339 --echo # Scenario 1
340
341 --source suite/innodb/include/prepare_secondary_index.inc
342
343 # This scenario reproduces the original problem, which was that a transaction
344 # was considered to hold implicit lock on a secondary index entry, even if
345 # it has not modified any column affecting this secondary index.
346 # 1. C1 obtains S lock on secondary index only for id=1
347 # 2. C2 modifies row with id=1, by changing c2, which does not affect
348 # secondary index, and thus it should not be considered to hold an
349 # implicit lock on secondary index. Also, this does not require C2
350 # to pay any attention to C1's lock on secondary index.
351 # 3. C1 tries to get X lock on the same row, which as one of the steps
352 # checks if C2 has implicit lock (and the bug causes it to believe
353 # that C2 holds it) and if so, then if there is some trx holding an
354 # explicit lock (and it turns out that C1 already has the S lock).
355
356 --connect (C1, localhost, root,,)
357 BEGIN;
358 SELECT 1 FROM t1 WHERE c1=1 FOR SHARE;
359
360 --connect (C2, localhost, root,,)
361 BEGIN;
362 UPDATE t1 SET c2=13 WHERE id=1;
363
364 --connection C1
365 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
366 --send SELECT 1 FROM t1 WHERE c1=1 FOR UPDATE;
367
368 --connection C2
369 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
370 ROLLBACK;
371
372 --connection C1
373 --reap
374 ROLLBACK;
375
376 --connection default
377 --disconnect C1
378 --disconnect C2
379
380 --source suite/innodb/include/cleanup_secondary_index.inc
381
382
383 --echo # Scenario 2
384
385 --source suite/innodb/include/prepare_secondary_index.inc
386
387 # This scenario is dual to Scenario 1 in some sense, as it deals with delete
388 # marked entries in secondary index.
389 # It is similar to Scenario 1 in that a bad implementation could wrongly
390 # conclude that modifying an unrelated field makes the transaction an implicit
391 # lock owner.
392 # This scenario proves that the bug in the original code was not simply in
393 # negating the result of row_vers_non_vc_index_entry_match.
394 # 1. `default` modifies row with id=1, by setting c1 to 55,
395 # so that that entry with c1=1 becomes delete marked
396 # 2. C1 obtains S lock on secondary index only for c1=1
397 # 3. C2 modifies row with id=1, by changing c2, which does not affect
398 # secondary index, and thus it should not be considered to hold an
399 # implicit lock on secondary index. Also, this does not require C2
400 # to pay any attention to C1's lock on secondary index.
401 # 4. C1 tries to get X lock on the same row, which as one of the steps
402 # checks if C2 has implicit lock (and the bug causes it to believe
403 # that C2 holds it) and if so, then if there is some trx holding an
404 # explicit lock (and it turns out that C1 already has the S lock).
405
406 UPDATE t1 SET c1=55 WHERE id=1;
407
408 --connect (C1, localhost, root,,)
409 BEGIN;
410 SELECT 1 FROM t1 WHERE c1=1 FOR SHARE;
411
412 --connect (C2, localhost, root,,)
413 BEGIN;
414 UPDATE t1 SET c2=13 WHERE id=1;
415
416 --connection C1
417 SELECT 1 FROM t1 WHERE c1=1 FOR UPDATE;
418 ROLLBACK;
419
420 --connection C2
421 ROLLBACK;
422
423 --connection default
424 --disconnect C1
425 --disconnect C2
426
427 --source suite/innodb/include/cleanup_secondary_index.inc
428
429 --echo # Scenario 3
430
431 --source suite/innodb/include/prepare_secondary_index.inc
432
433 # This is not a very difficult scenario, and it makes more sense
434 # for virtual colum version of this test which can be found above.
435 # 1. C1 DELETEs record with id=1, and thus should hold implicit lock
436 # on secondary index for c1=1.
437 # 2. C2 performs a SELECT 1 FROM t1 WHERE c1=1 FOR SHARE, and should wait
438 # 3. C1 commits
439 # 4. C2 should see empty result
440
441 --connect (C1, localhost, root,,)
442 BEGIN;
443 DELETE FROM t1 WHERE id=1;
444
445 --connect (C2, localhost, root,,)
446 BEGIN;
447 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
448 --send SELECT 1 FROM t1 WHERE c1=1 FOR SHARE
449
450 --connection C1
451 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
452 COMMIT;
453
454 --connection C2
455 --reap
456
457 --connection default
458 --disconnect C1
459 --disconnect C2
460
461 --source suite/innodb/include/cleanup_secondary_index.inc
462
463 --echo # Scenario 4
464
465 # In this scenario we check the last possible path - the one where secondary
466 # row is not delete marked, and previous version of primary index row is also
467 # not delete marked in which case we should withold with deciding if there is an
468 # implicit lock until we check trx_id.
469 # There are many subscenarios.
470
471 --echo # Scenario 4a
472
473 --source suite/innodb/include/prepare_secondary_index.inc
474
475 # In 4a we have a case where the previous version is made by active trx,
476 # and there are no older versions, because it started with INSERT.
477 # In such case it should own implicit lock.
478 # 1. C1 INSERTS new row with id=2 c1=2
479 # 2. C1 UPDATES unrelated column c2, so that previous_version is INSERT
480 # 3. C2 does SELECT 1 FROM t1 WHERE c1=2 FOR SHARE and has to wait
481 # 4. C1 commits
482 # 5. C2 sees the new row
483
484 --connect (C1, localhost, root,,)
485 BEGIN;
486 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
487 UPDATE t1 SET c2=13 WHERE id=2;
488
489 --connect (C2, localhost, root,,)
490 BEGIN;
491 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
492 --send SELECT 1 FROM t1 WHERE c1=2 FOR SHARE
493
494 --connection C1
495 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
496 COMMIT;
497
498 --connection C2
499 --reap
500
501
502 --connection default
503 --disconnect C1
504 --disconnect C2
505
506 --source suite/innodb/include/cleanup_secondary_index.inc
507
508 --echo # Scenario 4b
509
510 --source suite/innodb/include/prepare_secondary_index.inc
511
512 # In 4b, again the previous version is made by active trx, and there exists
513 # an even older version, which was not made by active trx and also matches
514 # so in this case there is no implicit lock.
515 # 1. default INSERTs id=1,c1=2
516 # 2. C1 UPDATEs unrelated field c2
517 # 3. C1 UPDATEs unrelated field c2 again
518 # 4. C2 does SELECT 1 FROM t1 WHERE c1=2 FOR SHARE and does not have to wait
519 # 5. C2 COMMITS
520 # 6. C1 COMMITS
521
522 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
523
524 --connect (C1, localhost, root,,)
525 BEGIN;
526 UPDATE t1 SET c2=13 WHERE id=2;
527 UPDATE t1 SET c2=42 WHERE id=2;
528
529 --connect (C2, localhost, root,,)
530 BEGIN;
531 SELECT 1 FROM t1 WHERE c1=2 FOR SHARE;
532 COMMIT;
533
534 --connection C1
535 COMMIT;
536
537 --connection default
538 --disconnect C1
539 --disconnect C2
540
541 --source suite/innodb/include/cleanup_secondary_index.inc
542
543 --echo # Scenario 4c
544
545 --source suite/innodb/include/prepare_secondary_index.inc
546
547 # In 4c, the previous version is made by active trx, there is an older version,
548 # also made by trx_id but does not match the secondary index, thus
549 # there is an implicit lock
550 # 1. C1 INSERTs id=2 c1=2
551 # 2. C1 UPDATEs c1 to 10 for id=2
552 # 3. C1 UPDATEs unrelated field c2
553 # 4. C2 does SELECT 1 FROM t1 WHERE c1=10 FOR SHARE and has to wait
554 # 5. C1 COMMITS
555 # 6. C2 sees the new row
556
557 --connect (C1, localhost, root,,)
558 BEGIN;
559 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
560 UPDATE t1 SET c1=10 WHERE id=2;
561 UPDATE t1 SET c2=13 WHERE id=2;
562
563 --connect (C2, localhost, root,,)
564 BEGIN;
565 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
566 --send SELECT 1 FROM t1 WHERE c1=10 FOR SHARE
567
568 --connection C1
569 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
570 COMMIT;
571
572 --connection C2
573 --reap
574
575 --connection default
576 --disconnect C1
577 --disconnect C2
578
579 --source suite/innodb/include/cleanup_secondary_index.inc
580
581 --echo # Scenario 4d
582
583 --source suite/innodb/include/prepare_secondary_index.inc
584
585 # In 4d, the previous version is made by active trx, there is an older version,
586 # which was not made by trx_id, and does not match secondary index, thus
587 # there is an implicit lock.
588 # 1. default INSERTs id=2 c1=2
589 # 2. C1 UPDATEs c1 to 10 for id=2
590 # 3. C1 UPDATEs unrelated field c2
591 # 4. C2 does SELECT 1 FROM t1 WHERE c1=10 FOR SHARE and has to wait
592 # 5. C1 COMMITS
593 # 6. C2 sees the new row
594
595 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
596
597 --connect (C1, localhost, root,,)
598 BEGIN;
599 UPDATE t1 SET c1=10 WHERE id=2;
600 UPDATE t1 SET c2=13 WHERE id=2;
601
602 --connect (C2, localhost, root,,)
603 BEGIN;
604 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
605 --send SELECT 1 FROM t1 WHERE c1=10 FOR SHARE
606
607 --connection C1
608 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
609 COMMIT;
610
611 --connection C2
612 --reap
613
614 --connection default
615 --disconnect C1
616 --disconnect C2
617
618 --source suite/innodb/include/cleanup_secondary_index.inc
619
620 --echo # Scenario 4e
621
622 --source suite/innodb/include/prepare_secondary_index.inc
623
624 # In 4e, the previous version is not made by trx_id, so we conclude, that
625 # there is no implicit lock
626 # 1. default INSERTs id=2,c1=2
627 # 2. C1 UPDATEs unrelated field c2
628 # 4. C2 does SELECT 1 FROM t1 WHERE c1=2 FOR SHARE and does not have to wait
629 # 5. C2 COMMITS
630 # 6. C1 COMMITS
631
632 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
633
634 --connect (C1, localhost, root,,)
635 BEGIN;
636 UPDATE t1 SET c2=13 WHERE id=2;
637
638 --connect (C2, localhost, root,,)
639 BEGIN;
640 SELECT 1 FROM t1 WHERE c1=2 FOR SHARE;
641 COMMIT;
642
643 --connection C1
644 COMMIT;
645
646 --connection default
647 --disconnect C1
648 --disconnect C2
649
650 --source suite/innodb/include/cleanup_secondary_index.inc
651
652
653
654 #
655 # We go through all scenarios one more time, but this time, there exists a
656 # separate secondary index on c2, so changes to c2 cause undo logging of columns
657 # - in particular virtual columns.
658 # As we have separate cases in code for the lack of virtual columns information
659 # in the undo log, this is an interesting case.
660 # In case of scenario 4b, we now have two separate versions of it,
661 # where 4b' is the same as 4b except that one of the updates changes column
662 # c3, which does not take part in any secondary index at all.
663 #
664
665 --echo # Scenario 1
666
667 --source suite/innodb/include/prepare_secondary_indexs_on_virtual_and_normal.inc
668
669 # This scenario reproduces the original problem, which was that a transaction
670 # was considered to hold implicit lock on a secondary index entry, even if
671 # it has not modified any column affecting this secondary index.
672 # 1. C1 obtains S lock on secondary index only for id=1
673 # 2. C2 modifies row with id=1, by changing c2, which does not affect
674 # secondary index, and thus it should not be considered to hold an
675 # implicit lock on secondary index. Also, this does not require C2
676 # to pay any attention to C1's lock on secondary index.
677 # 3. C1 tries to get X lock on the same row, which as one of the steps
678 # checks if C2 has implicit lock (and the bug causes it to believe
679 # that C2 holds it) and if so, then if there is some trx holding an
680 # explicit lock (and it turns out that C1 already has the S lock).
681
682 --connect (C1, localhost, root,,)
683 BEGIN;
684 SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
685
686 --connect (C2, localhost, root,,)
687 BEGIN;
688 UPDATE t1 SET c2=13 WHERE id=1;
689
690 --connection C1
691 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
692 --send SELECT 1 FROM t1 WHERE v1=1 FOR UPDATE;
693
694 --connection C2
695 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
696 ROLLBACK;
697
698 --connection C1
699 --reap
700 ROLLBACK;
701
702 --connection default
703 --disconnect C1
704 --disconnect C2
705
706 --source suite/innodb/include/cleanup_secondary_index.inc
707
708
709 --echo # Scenario 2
710
711 --source suite/innodb/include/prepare_secondary_indexs_on_virtual_and_normal.inc
712
713 # This scenario is dual to Scenario 1 in some sense, as it deals with delete
714 # marked entries in secondary index.
715 # It is similar to Scenario 1 in that a bad implementation could wrongly
716 # conclude that modifying an unrelated field makes the transaction an implicit
717 # lock owner.
718 # This scenario proves that the bug in the original code was not simply in
719 # negating the result of row_vers_non_vc_index_entry_match.
720 # 1. `default` modifies row with id=1, by setting c1 to 55,
721 # so that that entry with v1=1 becomes delete marked
722 # 2. C1 obtains S lock on secondary index only for v1=1
723 # 3. C2 modifies row with id=1, by changing c2, which does not affect
724 # secondary index, and thus it should not be considered to hold an
725 # implicit lock on secondary index. Also, this does not require C2
726 # to pay any attention to C1's lock on secondary index.
727 # 4. C1 tries to get X lock on the same row, which as one of the steps
728 # checks if C2 has implicit lock (and the bug causes it to believe
729 # that C2 holds it) and if so, then if there is some trx holding an
730 # explicit lock (and it turns out that C1 already has the S lock).
731
732 UPDATE t1 SET c1=55 WHERE id=1;
733
734 --connect (C1, localhost, root,,)
735 BEGIN;
736 SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
737
738 --connect (C2, localhost, root,,)
739 BEGIN;
740 UPDATE t1 SET c2=13 WHERE id=1;
741
742 --connection C1
743 SELECT 1 FROM t1 WHERE v1=1 FOR UPDATE;
744 ROLLBACK;
745
746 --connection C2
747 ROLLBACK;
748
749 --connection default
750 --disconnect C1
751 --disconnect C2
752
753 --source suite/innodb/include/cleanup_secondary_index.inc
754
755 --echo # Scenario 2b
756
757 --source suite/innodb/include/prepare_secondary_indexs_on_virtual_and_normal.inc
758
759 # This scenario is similar to 2, but we perform not one, but two UPDATES
760 # in C2, one of them on a totally unrelated column c3, just to check if the
761 # algorithm correctly handles missing vrow information
762
763 UPDATE t1 SET c1=55 WHERE id=1;
764
765 --connect (C1, localhost, root,,)
766 BEGIN;
767 SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
768
769 --connect (C2, localhost, root,,)
770 BEGIN;
771 UPDATE t1 SET c3=13 WHERE id=1;
772 UPDATE t1 SET c2=13 WHERE id=1;
773
774 --connection C1
775 SELECT 1 FROM t1 WHERE v1=1 FOR UPDATE;
776 ROLLBACK;
777
778 --connection C2
779 ROLLBACK;
780
781 --connection default
782 --disconnect C1
783 --disconnect C2
784
785 --source suite/innodb/include/cleanup_secondary_index.inc
786
787 --echo # Scenario 3
788
789 --source suite/innodb/include/prepare_secondary_indexs_on_virtual_and_normal.inc
790
791 # This is not a very difficult scenario, but one needed to verify that a
792 # DELETE operation is logged to undo log without UPD_NODE_NO_ORD_CHANGE flag
793 # and thus virtual column fields can be retrieved from undo log.
794 # This is one of the core assumptions for the fix to this bug.
795 # 1. C1 DELETEs record with id=1, and thus should hold implicit lock
796 # on secondary index for v1=1.
797 # 2. C2 performs a SELECT 1 FROM t1 WHERE v1=1 FOR SHARE, and should wait
798 # 3. C1 commits
799 # 4. C2 should see empty result
800
801 --connect (C1, localhost, root,,)
802 BEGIN;
803 DELETE FROM t1 WHERE id=1;
804
805 --connect (C2, localhost, root,,)
806 BEGIN;
807 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
808 --send SELECT 1 FROM t1 WHERE v1=1 FOR SHARE
809
810 --connection C1
811 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
812 COMMIT;
813
814 --connection C2
815 --reap
816
817 --connection default
818 --disconnect C1
819 --disconnect C2
820
821 --source suite/innodb/include/cleanup_secondary_index.inc
822
823 --echo # Scenario 4
824
825 # In this scenario we check the last possible path for missing virtual
826 # columns information: the one where secondary row is not delete marked,
827 # and previous version of primary index row is also not delete marked in
828 # which case we should withold with deciding if there is an implicit lock
829 # until we check trx_id.
830 # There are many subscenarios.
831
832 --echo # Scenario 4a
833
834 --source suite/innodb/include/prepare_secondary_indexs_on_virtual_and_normal.inc
835
836 # In 4a we have a case where the previous version is made by active trx,
837 # and there are no older versions, because it started with INSERT.
838 # In such case it should own implicit lock.
839 # 1. C1 INSERTS new row with id=2 v1=2
840 # 2. C1 UPDATES unrelated column c2, so that previous_version is INSERT
841 # 3. C2 does SELECT 1 FROM t1 WHERE v1=2 FOR SHARE and has to wait
842 # 4. C1 commits
843 # 5. C2 sees the new row
844
845 --connect (C1, localhost, root,,)
846 BEGIN;
847 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
848 UPDATE t1 SET c2=13 WHERE id=2;
849
850 --connect (C2, localhost, root,,)
851 BEGIN;
852 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
853 --send SELECT 1 FROM t1 WHERE v1=2 FOR SHARE
854
855 --connection C1
856 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
857 COMMIT;
858
859 --connection C2
860 --reap
861
862
863 --connection default
864 --disconnect C1
865 --disconnect C2
866
867 --source suite/innodb/include/cleanup_secondary_index.inc
868
869 --echo # Scenario 4b
870
871 --source suite/innodb/include/prepare_secondary_indexs_on_virtual_and_normal.inc
872
873 # In 4b, again the previous version is made by active trx, and there exists
874 # an even older version, which was not made by active trx and also matches
875 # so in this case there is no implicit lock.
876 # 1. default INSERTs id=1,v1=2
877 # 2. C1 UPDATEs unrelated field c2
878 # 3. C1 UPDATEs unrelated field c2
879 # 4. C2 does SELECT 1 FROM t1 WHERE v1=2 FOR SHARE and does not have to wait
880 # 5. C2 COMMITS
881 # 6. C1 COMMITS
882
883 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
884
885 --connect (C1, localhost, root,,)
886 BEGIN;
887 UPDATE t1 SET c2=13 WHERE id=2;
888 UPDATE t1 SET c2=42 WHERE id=2;
889
890 --connect (C2, localhost, root,,)
891 BEGIN;
892 SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
893 COMMIT;
894
895 --connection C1
896 COMMIT;
897
898 --connection default
899 --disconnect C1
900 --disconnect C2
901
902 --source suite/innodb/include/cleanup_secondary_index.inc
903
904 --echo # Scenario 4b-prime
905
906 --source suite/innodb/include/prepare_secondary_indexs_on_virtual_and_normal.inc
907
908 # In 4b, again the previous version is made by active trx, and there exists
909 # an even older version, which was not made by active trx and also matches
910 # so in this case there is no implicit lock.
911 # 1. default INSERTs id=1,v1=2
912 # 2. C1 UPDATEs unrelated field c3
913 # 3. C1 UPDATEs unrelated field c2
914 # 4. C2 does SELECT 1 FROM t1 WHERE v1=2 FOR SHARE and does not have to wait
915 # 5. C2 COMMITS
916 # 6. C1 COMMITS
917
918 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
919
920 --connect (C1, localhost, root,,)
921 BEGIN;
922 UPDATE t1 SET c3=13 WHERE id=2;
923 UPDATE t1 SET c2=42 WHERE id=2;
924
925 --connect (C2, localhost, root,,)
926 BEGIN;
927 SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
928 COMMIT;
929
930 --connection C1
931 COMMIT;
932
933 --connection default
934 --disconnect C1
935 --disconnect C2
936
937 --source suite/innodb/include/cleanup_secondary_index.inc
938
939 --echo # Scenario 4c
940
941 --source suite/innodb/include/prepare_secondary_indexs_on_virtual_and_normal.inc
942
943 # In 4c, the previous version is made by active trx, there is an older version,
944 # also made by trx_id but does not match the secondary index, thus
945 # there is an implicit lock
946 # 1. C1 INSERTs id=2 v1=2
947 # 2. C1 UPDATEs c1 (and thus v1) to 10 for id=2
948 # 3. C1 UPDATEs unrelated field c2
949 # 4. C2 does SELECT 1 FROM t1 WHERE v1=10 FOR SHARE and has to wait
950 # 5. C1 COMMITS
951 # 6. C2 sees the new row
952
953 --connect (C1, localhost, root,,)
954 BEGIN;
955 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
956 UPDATE t1 SET c1=10 WHERE id=2;
957 UPDATE t1 SET c2=13 WHERE id=2;
958
959 --connect (C2, localhost, root,,)
960 BEGIN;
961 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
962 --send SELECT 1 FROM t1 WHERE v1=10 FOR SHARE
963
964 --connection C1
965 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
966 COMMIT;
967
968 --connection C2
969 --reap
970
971 --connection default
972 --disconnect C1
973 --disconnect C2
974
975 --source suite/innodb/include/cleanup_secondary_index.inc
976
977 --echo # Scenario 4d
978
979 --source suite/innodb/include/prepare_secondary_indexs_on_virtual_and_normal.inc
980
981 # In 4d, the previous version is made by active trx, there is an older version,
982 # which was not made by trx_id, and does not match secondary index, thus
983 # there is an implicit lock.
984 # 1. default INSERTs id=2 v1=2
985 # 2. C1 UPDATEs c1 (and thus v1) to 10 for id=2
986 # 3. C1 UPDATEs unrelated field c2
987 # 4. C2 does SELECT 1 FROM t1 WHERE v1=10 FOR SHARE and has to wait
988 # 5. C1 COMMITS
989 # 6. C2 sees the new row
990
991 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
992
993 --connect (C1, localhost, root,,)
994 BEGIN;
995 UPDATE t1 SET c1=10 WHERE id=2;
996 UPDATE t1 SET c2=13 WHERE id=2;
997
998 --connect (C2, localhost, root,,)
999 BEGIN;
1000 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
1001 --send SELECT 1 FROM t1 WHERE v1=10 FOR SHARE
1002
1003 --connection C1
1004 SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
1005 COMMIT;
1006
1007 --connection C2
1008 --reap
1009
1010 --connection default
1011 --disconnect C1
1012 --disconnect C2
1013
1014 --source suite/innodb/include/cleanup_secondary_index.inc
1015
1016 --echo # Scenario 4e
1017
1018 --source suite/innodb/include/prepare_secondary_indexs_on_virtual_and_normal.inc
1019
1020 # In 4e, the previous version is not made by trx_id, so we conclude, that
1021 # there is no implicit lock
1022 # 1. default INSERTs id=2,v1=2
1023 # 2. C1 UPDATEs unrelated field c2
1024 # 4. C2 does SELECT 1 FROM t1 WHERE v1=2 FOR SHARE and does not have to wait
1025 # 5. C2 COMMITS
1026 # 6. C1 COMMITS
1027
1028 INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
1029
1030 --connect (C1, localhost, root,,)
1031 BEGIN;
1032 UPDATE t1 SET c2=13 WHERE id=2;
1033
1034 --connect (C2, localhost, root,,)
1035 BEGIN;
1036 SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
1037 COMMIT;
1038
1039 --connection C1
1040 COMMIT;
1041
1042 --connection default
1043 --disconnect C1
1044 --disconnect C2
1045
1046 --source suite/innodb/include/cleanup_secondary_index.inc
1047
1048
1049
1050 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;