Debian Codegraph @

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

raw
1 --echo #
2 --echo # Bug #29508068 UNNECESSARY NEXT-KEY LOCK TAKEN
3 --echo #
4
5 --source include/have_debug_sync.inc
6 --source include/not_hypergraph.inc # Locks taken depend on the query plan chosen.
7
8 CREATE TABLE t1 (
9 id INT NOT NULL,
10 PRIMARY KEY (id ASC)
11 ) ENGINE=InnoDB;
12
13 CREATE TABLE t2 (
14 id INT NOT NULL,
15 PRIMARY KEY (id DESC)
16 ) ENGINE=InnoDB;
17
18 --let $TWO=2
19
20 SET @conditions = CONCAT(
21 '<=0 <1 <=1 <7 <=7 <=8 <9 <=9 <=10 ',
22 '>=10 >9 >=9 >3 >=3 >=2 >1 >=1 >0'
23 );
24 let $conditions_cnt = `
25 SELECT 1 + LENGTH(@conditions) - LENGTH(REPLACE(@conditions, ' ',''))
26 `;
27
28 --let $t=1
29 while($t <= 2)
30 {
31
32 --eval INSERT INTO t$t VALUES (1), (3), (4), (5), (6), (7), (9)
33
34 --eval ANALYZE TABLE t$t
35
36 --let $c=1
37 while($c <= $conditions_cnt)
38 {
39 let condition = `
40 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@conditions,' ',$c),' ',-1)
41 `;
42
43 --let $desc=0
44 while($desc <= 1)
45 {
46 if($desc == 0)
47 {
48 --let $ord=ASC
49 }
50 if($desc == 1)
51 {
52 --let $ord=DESC
53 }
54 BEGIN;
55
56 eval
57 SELECT *
58 FROM t$t FORCE INDEX (PRIMARY)
59 WHERE id $condition
60 ORDER BY id $ord
61 FOR UPDATE;
62
63 --sorted_result
64 eval
65 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
66 FROM performance_schema.data_locks
67 WHERE object_name = 't$t';
68
69 ROLLBACK;
70
71 --inc $desc
72 }
73
74 --inc $c
75 }
76
77 --eval DROP TABLE t$t
78 --inc $t
79 }
80
81 CREATE TABLE t1 (
82 id1 INT NOT NULL,
83 id2 INT NOT NULL,
84 PRIMARY KEY (id1 ASC, id2 ASC)
85 ) ENGINE=InnoDB;
86
87 CREATE TABLE t2 (
88 id1 INT NOT NULL,
89 id2 INT NOT NULL,
90 PRIMARY KEY (id1 ASC, id2 DESC)
91 ) ENGINE=InnoDB;
92
93 CREATE TABLE t3 (
94 id1 INT NOT NULL,
95 id2 INT NOT NULL,
96 PRIMARY KEY (id1 DESC, id2 ASC)
97 ) ENGINE=InnoDB;
98
99 CREATE TABLE t4 (
100 id1 INT NOT NULL,
101 id2 INT NOT NULL,
102 PRIMARY KEY (id1 DESC, id2 DESC)
103 ) ENGINE=InnoDB;
104
105 SET @conditions = CONCAT(
106 '<0 <1 <3 <=3 <=4 <5 <=5 ',
107 '>6 >5 >3 >=3 >=2 >1 >=1'
108 );
109 let $conditions_cnt = `
110 SELECT 1 + LENGTH(@conditions) - LENGTH(REPLACE(@conditions, ' ',''))
111 `;
112
113 --let $t = 1
114 while($t <= 4)
115 {
116 eval
117 INSERT INTO t$t (id1,id2) VALUES
118 (1,1),(1,3),(1,5),(3,1),(3,3),(3,5),(5,1),(5,3),(5,5);
119
120 --eval ANALYZE TABLE t$t
121
122 --let $c = 1
123 while($c <= $conditions_cnt)
124 {
125 let condition = `
126 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@conditions,' ',$c),' ',-1)
127 `;
128 --let $desc=0
129 while($desc <= 1)
130 {
131 if($desc == 0)
132 {
133 --let $ord=ASC
134 }
135 if($desc == 1)
136 {
137 --let $ord=DESC
138 }
139
140 --let $field = 1
141 while($field <= 2)
142 {
143 if($field == 1){
144 --let $predicate=id1 $condition
145 --let $order_column=id1
146 }
147 if($field == 2){
148 --let $predicate=id1=3 AND id2 $condition
149 --let $order_column=id2
150 }
151 BEGIN;
152
153 eval
154 SELECT *
155 FROM t$t FORCE INDEX (PRIMARY)
156 WHERE $predicate
157 ORDER BY $order_column $ord
158 FOR UPDATE;
159
160 --sorted_result
161 eval
162 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
163 FROM performance_schema.data_locks
164 WHERE object_name = 't$t';
165
166 ROLLBACK;
167
168 --inc $field
169 }
170 --inc $desc
171 }
172
173 --inc $c
174 }
175
176 --eval DROP TABLE t$t
177 --inc $t
178 }
179
180 CREATE TABLE t1 (
181 id VARCHAR(100) NOT NULL,
182 PRIMARY KEY (id(1) ASC)
183 ) ENGINE=InnoDB;
184
185 CREATE TABLE t2 (
186 id VARCHAR(100) NOT NULL,
187 PRIMARY KEY (id(1) DESC)
188 ) ENGINE=InnoDB;
189
190 SET @conditions = CONCAT(
191 '<="c" <="d" <"e" <"ee" <="ee" <="ec" <="ef" ',
192 '>="g" >="f" >"e" >"ee" >="ee" >="ef" >="ec"'
193 );
194 let $conditions_cnt = `
195 SELECT 1 + LENGTH(@conditions) - LENGTH(REPLACE(@conditions, ' ',''))
196 `;
197
198 --let $t = 1
199 while($t <= 2)
200 {
201
202 eval
203 INSERT INTO t$t
204 VALUES ("aa"), ("bb"), ("cc"), ("ee"), ("gg"), ("hh"), ("ii");
205
206 --eval ANALYZE TABLE t$t
207
208 --let $c = 1
209 while($c <= $conditions_cnt)
210 {
211 let condition = `
212 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@conditions,' ',$c),' ',-1)
213 `;
214
215 --let $desc=0
216 while($desc <= 1)
217 {
218 if($desc == 0)
219 {
220 --let $ord=ASC
221 }
222 if($desc == 1)
223 {
224 --let $ord=DESC
225 }
226 BEGIN;
227
228 eval
229 SELECT *
230 FROM t$t FORCE INDEX (PRIMARY)
231 WHERE id $condition
232 ORDER BY id $ord
233 FOR UPDATE;
234
235 --sorted_result
236 eval
237 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
238 FROM performance_schema.data_locks
239 WHERE object_name = 't$t';
240
241 ROLLBACK;
242
243 --inc $desc
244 }
245
246 --inc $c
247 }
248
249 --eval DROP TABLE t$t
250 --inc $t
251 }
252
253 CREATE TABLE t1 (
254 id VARCHAR(100) NOT NULL,
255 PRIMARY KEY (id(2) ASC)
256 ) ENGINE=InnoDB;
257
258 CREATE TABLE t2 (
259 id VARCHAR(100) NOT NULL,
260 PRIMARY KEY (id(2) DESC)
261 ) ENGINE=InnoDB;
262
263 SET @conditions = CONCAT(
264 '<="c" <="d" <"e" <"ee" <="ee" <="ec" <="ef" ',
265 '>="g" >="f" >"e" >"ee" >="ee" >="ef" >="ec"'
266 );
267 let $conditions_cnt = `
268 SELECT 1 + LENGTH(@conditions) - LENGTH(REPLACE(@conditions, ' ',''))
269 `;
270
271 --let $t = 1
272 while($t <= 2)
273 {
274
275 eval
276 INSERT INTO t$t
277 VALUES ("aaa"), ("bbb"), ("ccc"), ("eee"), ("ggg"), ("hhh"), ("iii");
278
279 --eval ANALYZE TABLE t$t
280
281 --let $c = 1
282 while($c <= $conditions_cnt)
283 {
284 let condition = `
285 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@conditions,' ',$c),' ',-1)
286 `;
287
288 --let $desc=0
289 while($desc <= 1)
290 {
291 if($desc == 0)
292 {
293 --let $ord=ASC
294 }
295 if($desc == 1)
296 {
297 --let $ord=DESC
298 }
299 BEGIN;
300
301 eval
302 SELECT *
303 FROM t$t FORCE INDEX (PRIMARY)
304 WHERE id $condition
305 ORDER BY id $ord
306 FOR UPDATE;
307
308 --sorted_result
309 eval
310 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
311 FROM performance_schema.data_locks
312 WHERE object_name = 't$t';
313
314 ROLLBACK;
315
316 --inc $desc
317 }
318
319 --inc $c
320 }
321
322 --eval DROP TABLE t$t
323 --inc $t
324 }
325
326 # In this scenario, we test what happens if the last row in the range is locked
327 # by another transaction. In particular con1 locks row with id=7, and con2 tries
328 # to lock rows with id<=7. This scenario is interesting, because when con2 goes
329 # to waiting state AFTER it has already "seen" the end of the range, and when it
330 # "wakes up" (after con1 releases the lock) it tries to read the row again.
331 # A faulty implementation might fail on assert, not read the row, or fail to
332 # lock it properly on the second attempt.
333
334 CREATE TABLE t1 (
335 id INT NOT NULL,
336 PRIMARY KEY (id ASC)
337 ) ENGINE=InnoDB;
338
339 INSERT INTO t1 VALUES (1), (3), (4), (5), (6), (7), (9);
340 ANALYZE TABLE t1;
341 --connect (con1,localhost,root,,)
342 --connect (con2,localhost,root,,)
343
344 --connection con1
345 BEGIN;
346 SELECT * FROM t1 WHERE id=7 FOR UPDATE;
347
348 --connection con2
349 BEGIN;
350 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL con2_will_wait';
351 --send SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE id<=7 FOR UPDATE;
352
353 --connection con1
354 SET DEBUG_SYNC='now WAIT_FOR con2_will_wait';
355 ROLLBACK;
356
357 --connection con2
358 --reap
359
360 --connection default
361 --disconnect con1
362 --disconnect con2
363
364 DROP TABLE t1;
365
366 # In this scenario, we test what happens if the last row in the range is delete-
367 # -marked
368
369 CREATE TABLE t1 (
370 id INT NOT NULL,
371 PRIMARY KEY (id ASC)
372 ) ENGINE=InnoDB;
373
374 INSERT INTO t1 VALUES (1), (3), (4), (5), (6), (7), (9);
375 ANALYZE TABLE t1;
376 SET GLOBAL innodb_purge_stop_now = ON;
377 DELETE FROM t1 WHERE id=7;
378
379
380 BEGIN;
381 SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE id<=7 FOR UPDATE;
382
383 --sorted_result
384 SELECT index_name, lock_type, lock_mode, lock_status, lock_data
385 FROM performance_schema.data_locks
386 WHERE object_name='t1';
387
388 ROLLBACK;
389 SET GLOBAL innodb_purge_run_now = ON;
390
391 DROP TABLE t1;
392
393 # In this scenario, we test what happens if the last row in the range is locked
394 # by another transaction and is delete-marked.
395 # In particular con1 locks row with id=7, and con2 tries
396 # to lock rows with id<=7. This scenario is interesting, because when con2 goes
397 # to waiting state AFTER it has already "seen" the end of the range, and when it
398 # "wakes up" (after con1 releases the lock) it tries to read the row again.
399 # A faulty implementation might fail on assert, not read the row, or fail to
400 # lock it properly on the second attempt.
401
402 CREATE TABLE t1 (
403 id INT NOT NULL,
404 PRIMARY KEY (id ASC)
405 ) ENGINE=InnoDB;
406
407 INSERT INTO t1 VALUES (1), (3), (4), (5), (6), (7), (9);
408 ANALYZE TABLE t1;
409 SET GLOBAL innodb_purge_stop_now = ON;
410 DELETE FROM t1 WHERE id=7;
411
412 --connect (con1,localhost,root,,)
413 --connect (con2,localhost,root,,)
414
415 --connection con1
416 BEGIN;
417 SELECT * FROM t1 WHERE id=7 FOR UPDATE;
418
419 --connection con2
420 BEGIN;
421 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL con2_will_wait';
422 --send SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE id<=7 FOR UPDATE;
423
424 --connection con1
425 SET DEBUG_SYNC='now WAIT_FOR con2_will_wait';
426 ROLLBACK;
427
428 --connection con2
429 --reap
430
431 --connection default
432 --disconnect con1
433 --disconnect con2
434
435 SET GLOBAL innodb_purge_run_now = ON;
436 DROP TABLE t1;
437
438 # In this scenario, we test what happens if the last row in the range is locked
439 # by another transaction and is delete-marked, and the other transaction
440 # undeletes this row, and then rolls back.
441 # In particular con1 locks row with id=7, and con2 tries
442 # to lock rows with id<=7. This scenario is interesting, because when con2 goes
443 # to waiting state AFTER it has already "seen" the end of the range, and when it
444 # "wakes up" (after con1 releases the lock) it tries to read the row again.
445 # A faulty implementation might fail on assert, not read the row, or fail to
446 # lock it properly on the second attempt.
447
448 CREATE TABLE t1 (
449 id INT NOT NULL,
450 PRIMARY KEY (id ASC)
451 ) ENGINE=InnoDB;
452
453 INSERT INTO t1 VALUES (1), (3), (4), (5), (6), (7), (9);
454 ANALYZE TABLE t1;
455 SET GLOBAL innodb_purge_stop_now = ON;
456 DELETE FROM t1 WHERE id=7;
457
458 --connect (con1,localhost,root,,)
459 --connect (con2,localhost,root,,)
460
461 --connection con1
462 BEGIN;
463 INSERT INTO t1 VALUES (7);
464
465 --connection con2
466 BEGIN;
467 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL con2_will_wait';
468 --send SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE id<=7 FOR UPDATE;
469
470 --connection con1
471 SET DEBUG_SYNC='now WAIT_FOR con2_will_wait';
472 ROLLBACK;
473
474 --connection con2
475 --reap
476
477 --connection default
478 --disconnect con1
479 --disconnect con2
480
481 SET GLOBAL innodb_purge_run_now = ON;
482 DROP TABLE t1;
483
484 # In this scenario, we test what happens if the last row in the range is locked
485 # by another transaction and is delete-marked, and the other transaction
486 # undeletes this row.
487 # In particular con1 locks row with id=7, and con2 tries
488 # to lock rows with id<=7. This scenario is interesting, because when con2 goes
489 # to waiting state AFTER it has already "seen" the end of the range, and when it
490 # "wakes up" (after con1 releases the lock) it tries to read the row again.
491 # A faulty implementation might fail on assert, not read the row, or fail to
492 # lock it properly on the second attempt.
493
494 CREATE TABLE t1 (
495 id INT NOT NULL,
496 PRIMARY KEY (id ASC)
497 ) ENGINE=InnoDB;
498
499 INSERT INTO t1 VALUES (1), (3), (4), (5), (6), (7), (9);
500 ANALYZE TABLE t1;
501 SET GLOBAL innodb_purge_stop_now = ON;
502 DELETE FROM t1 WHERE id=7;
503
504 --connect (con1,localhost,root,,)
505 --connect (con2,localhost,root,,)
506
507 --connection con1
508 BEGIN;
509 INSERT INTO t1 VALUES (7);
510
511 --connection con2
512 BEGIN;
513 SET DEBUG_SYNC='lock_wait_will_wait SIGNAL con2_will_wait';
514 --send SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE id<=7 FOR UPDATE;
515
516 --connection con1
517 SET DEBUG_SYNC='now WAIT_FOR con2_will_wait';
518 COMMIT;
519
520 --connection con2
521 --reap
522
523 --connection default
524 --disconnect con1
525 --disconnect con2
526
527 SET GLOBAL innodb_purge_run_now = ON;
528 DROP TABLE t1;
529
530 # In this scenario, we test what happens when semi-consistent read is involved.
531 # In particular con1 locks row with id=6 while deleting it, and con2 tries
532 # to lock rows with id<=6 while updating them using semi-consistent read.
533 # This scenario is interesting, because when con2 notices it would have to
534 # wait, it avoids creating a waiting lock, and reports the old (non-deleted)
535 # version of the row to the higher layer, which then retries the read, this time
536 # with proper locking enabled - so it is interesting what would happen if con1
537 # have had commited the delete meanwhile.
538
539 CREATE TABLE t1 (
540 id INT NOT NULL,
541 val INT,
542 PRIMARY KEY (id ASC)
543 ) ENGINE=InnoDB;
544
545 INSERT INTO t1 (id,val) VALUES (1,1),(2,2) ,(3,3), (4,4), (5,5), (6,6), (9,9);
546
547 --connect (con1,localhost,root,,)
548 --connect (con2,localhost,root,,)
549
550 --connection con1
551 BEGIN;
552 DELETE FROM t1 WHERE id=6;
553
554 --connection con2
555 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
556 BEGIN;
557 SET DEBUG_SYNC='
558 semi_consistent_read_would_wait
559 SIGNAL con2_would_wait
560 WAIT_FOR con2_can_peek';
561 SET DEBUG_SYNC='
562 row_search_for_mysql_before_return
563 SIGNAL con2_returns_row
564 WAIT_FOR con2_can_return_row
565 EXECUTE 6';
566 --send UPDATE t1 SET val=13 WHERE id<=6
567
568 --connection con1
569
570 --let i=1
571 while($i < 6)
572 {
573 --echo Expecting row number $i
574 SET DEBUG_SYNC='now WAIT_FOR con2_returns_row';
575 SET DEBUG_SYNC='now SIGNAL con2_can_return_row';
576 --inc $i
577 }
578
579 SET DEBUG_SYNC='now WAIT_FOR con2_would_wait';
580 --sorted_result
581 SELECT index_name, lock_type, lock_mode, lock_status, lock_data
582 FROM performance_schema.data_locks
583 WHERE object_name='t1';
584 SET DEBUG_SYNC='now SIGNAL con2_can_peek';
585 SET DEBUG_SYNC='now WAIT_FOR con2_returns_row';
586 --sorted_result
587 SELECT index_name, lock_type, lock_mode, lock_status, lock_data
588 FROM performance_schema.data_locks
589 WHERE object_name='t1';
590 COMMIT;
591
592 # Wait for purge to delete the delete-marked record
593 --source include/wait_innodb_all_purged.inc
594
595 --sorted_result
596 SELECT index_name, lock_type, lock_mode, lock_status, lock_data
597 FROM performance_schema.data_locks
598 WHERE object_name='t1';
599 SET DEBUG_SYNC='now SIGNAL con2_can_return_row';
600
601
602 --connection con2
603 --reap
604 --sorted_result
605 SELECT index_name, lock_type, lock_mode, lock_status, lock_data
606 FROM performance_schema.data_locks
607 WHERE object_name='t1';
608 ROLLBACK;
609
610 --connection default
611 --disconnect con1
612 --disconnect con2
613
614 DROP TABLE t1;
615 --echo #
616 --echo # Bug #30112238 - [INNODB] ASSERTION FAILURE: ROW0SEL.CC.*DIRECTION != 0
617 --echo #
618
619 CREATE TABLE t1 (
620 id INT PRIMARY KEY
621 );
622
623 INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9);
624 ANALYZE TABLE t1;
625
626
627 --connect (view_keeper,localhost,root,,)
628 BEGIN;
629 SELECT * FROM t1;
630
631 --connection default
632
633 DELETE FROM t1 WHERE id<=5;
634
635 BEGIN;
636 --sorted_result
637 SELECT index_name, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks WHERE object_name='t1';
638 SELECT id FROM t1 FORCE INDEX (PRIMARY) WHERE id<=5 FOR UPDATE;
639 --sorted_result
640 SELECT index_name, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks WHERE object_name='t1';
641 COMMIT;
642
643 --disconnect view_keeper
644
645 DROP TABLE t1;