Debian Codegraph @

mysql-test/suite/ndb/r/ndbinfo_cluster_locks.result

raw
1 use ndbinfo;
2 Create a view which should be deterministic
3 and exclude runtime specifics
4 create view ndbinfo.ordered_locks as
5 select mode, state, detail, op,
6 if(ifnull(waiting_for, 0xffffff00) = 0xffffff00, 0, 1) as waiting
7 from ndbinfo.cluster_locks
8 order by
9 node_id, block_instance, tableid, fragmentid, rowid, transid;
10 create table test.t1 (a int primary key, b int, c int, key(c)) engine=ndb;
11 insert into test.t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
12 Show no locks held by an open transaction + committed reads
13 of various types
14 Show no locks to begin with
15 select * from ndbinfo.ordered_locks;
16 mode state detail op waiting
17 -------------
18 CommittedRead
19 -------------
20 PK read, CR
21 begin;
22 select * from test.t1 where a=1;
23 a b c
24 1 1 1
25 Should be no locks held.
26 select * from ndbinfo.ordered_locks;
27 mode state detail op waiting
28 rollback;
29 ACC scan, CR
30 begin;
31 select * from test.t1 where b=4;
32 a b c
33 4 4 4
34 Should be no locks held.
35 select * from ndbinfo.ordered_locks;
36 mode state detail op waiting
37 rollback;
38 TUX scan, CR
39 begin;
40 select * from test.t1 where c > 4;
41 a b c
42 5 5 5
43 Should be no locks held.
44 select * from ndbinfo.ordered_locks;
45 mode state detail op waiting
46 rollback;
47 ----------
48 SharedRead
49 ----------
50 PK read, Shared
51 begin;
52 select * from test.t1 where a=1 lock in share mode;
53 a b c
54 1 1 1
55 One lock on primary
56 select * from ndbinfo.ordered_locks;
57 mode state detail op waiting
58 S H * READ 0
59 rollback;
60 ACC scan, Shared
61 begin;
62 select * from test.t1 where b=4 lock in share mode;
63 a b c
64 4 4 4
65 One taken-over lock on primary
66 select * from ndbinfo.ordered_locks;
67 mode state detail op waiting
68 S H * READ 0
69 rollback;
70 TUX scan, Shared
71 begin;
72 select * from test.t1 where c > 4 lock in share mode;
73 a b c
74 5 5 5
75 One taken-over lock on primary
76 select * from ndbinfo.ordered_locks;
77 mode state detail op waiting
78 S H * READ 0
79 rollback;
80 Multiple rows
81 PK read, Shared
82 begin;
83 select * from test.t1 where a=1 lock in share mode;
84 a b c
85 1 1 1
86 select * from test.t1 where a=2 lock in share mode;
87 a b c
88 2 2 2
89 select * from test.t1 where a=3 lock in share mode;
90 a b c
91 3 3 3
92 select * from test.t1 where a=4 lock in share mode;
93 a b c
94 4 4 4
95 select * from test.t1 where a=5 lock in share mode;
96 a b c
97 5 5 5
98 Expect 5 row locks on primaries
99 select * from ndbinfo.ordered_locks;
100 mode state detail op waiting
101 S H * READ 0
102 S H * READ 0
103 S H * READ 0
104 S H * READ 0
105 S H * READ 0
106 rollback;
107 ACC scan, Shared
108 begin;
109 select * from test.t1 lock in share mode;
110 Expect 5 row locks on primaries
111 select * from ndbinfo.ordered_locks;
112 mode state detail op waiting
113 S H * READ 0
114 S H * READ 0
115 S H * READ 0
116 S H * READ 0
117 S H * READ 0
118 rollback;
119 TUX scan, Shared
120 begin;
121 select * from test.t1 lock in share mode;
122 Expect 5 row locks on primaries
123 select * from ndbinfo.ordered_locks;
124 mode state detail op waiting
125 S H * READ 0
126 S H * READ 0
127 S H * READ 0
128 S H * READ 0
129 S H * READ 0
130 rollback;
131 --------------
132 Exclusive Read
133 --------------
134 PK read, Exclusive
135 begin;
136 select * from test.t1 where a=1 for update;
137 a b c
138 1 1 1
139 One lock on primary
140 select * from ndbinfo.ordered_locks;
141 mode state detail op waiting
142 X H * READ 0
143 rollback;
144 ACC scan, Exclusive
145 begin;
146 select * from test.t1 where b=4 for update;
147 a b c
148 4 4 4
149 One taken-over lock on primary
150 select * from ndbinfo.ordered_locks;
151 mode state detail op waiting
152 X H * READ 0
153 rollback;
154 TUX scan, Exclusive
155 begin;
156 select * from test.t1 where c > 4 for update;
157 a b c
158 5 5 5
159 One taken-over lock on primary
160 select * from ndbinfo.ordered_locks;
161 mode state detail op waiting
162 X H * READ 0
163 rollback;
164 Multiple rows
165 PK read, Exclusive
166 begin;
167 select * from test.t1 where a=1 for update;
168 a b c
169 1 1 1
170 select * from test.t1 where a=2 for update;
171 a b c
172 2 2 2
173 select * from test.t1 where a=3 for update;
174 a b c
175 3 3 3
176 select * from test.t1 where a=4 for update;
177 a b c
178 4 4 4
179 select * from test.t1 where a=5 for update;
180 a b c
181 5 5 5
182 Expect 5 row locks on primaries
183 select * from ndbinfo.ordered_locks;
184 mode state detail op waiting
185 X H * READ 0
186 X H * READ 0
187 X H * READ 0
188 X H * READ 0
189 X H * READ 0
190 rollback;
191 ACC scan, Exclusive
192 begin;
193 select * from test.t1 for update;
194 Expect 5 row locks on primaries
195 select * from ndbinfo.ordered_locks;
196 mode state detail op waiting
197 X H * READ 0
198 X H * READ 0
199 X H * READ 0
200 X H * READ 0
201 X H * READ 0
202 rollback;
203 TUX scan, Exclusive
204 begin;
205 select * from test.t1 for update;
206 Expect 5 row locks on primaries
207 select * from ndbinfo.ordered_locks;
208 mode state detail op waiting
209 X H * READ 0
210 X H * READ 0
211 X H * READ 0
212 X H * READ 0
213 X H * READ 0
214 rollback;
215 -----------
216 DML (I/U/D)
217 -----------
218 Insert
219 begin;
220 insert into test.t1 values (6,6,6);
221 Expect 1 lock per replica
222 select * from ndbinfo.ordered_locks;
223 mode state detail op waiting
224 X H * INSERT 0
225 X H * INSERT 0
226 rollback;
227 Update by PK
228 begin;
229 update test.t1 set b=50 where a=5;
230 Expect 1 lock per replica
231 select * from ndbinfo.ordered_locks;
232 mode state detail op waiting
233 X H * UPDATE 0
234 X H * UPDATE 0
235 rollback;
236 Update by ACC scan
237 begin;
238 update test.t1 set c=123 where b > 0;
239 Expect 5 locks per replica
240 select * from ndbinfo.ordered_locks;
241 mode state detail op waiting
242 X H * UPDATE 0
243 X H * UPDATE 0
244 X H * UPDATE 0
245 X H * UPDATE 0
246 X H * UPDATE 0
247 X H * UPDATE 0
248 X H * UPDATE 0
249 X H * UPDATE 0
250 X H * UPDATE 0
251 X H * UPDATE 0
252 rollback;
253 Update by TUX scan
254 begin;
255 update test.t1 set b=123 where c > 0;
256 Expect 5 locks per replica
257 select * from ndbinfo.ordered_locks;
258 mode state detail op waiting
259 X H * UPDATE 0
260 X H * UPDATE 0
261 X H * UPDATE 0
262 X H * UPDATE 0
263 X H * UPDATE 0
264 X H * UPDATE 0
265 X H * UPDATE 0
266 X H * UPDATE 0
267 X H * UPDATE 0
268 X H * UPDATE 0
269 rollback;
270 Delete by PK
271 begin;
272 delete from test.t1 where a=3;
273 Expect 1 lock per replica
274 select * from ndbinfo.ordered_locks;
275 mode state detail op waiting
276 X H * DELETE 0
277 X H * DELETE 0
278 rollback;
279 Delete by ACC scan
280 begin;
281 delete from test.t1 where b > 0;
282 Expect 5 locks per replica
283 select * from ndbinfo.ordered_locks;
284 mode state detail op waiting
285 X H * DELETE 0
286 X H * DELETE 0
287 X H * DELETE 0
288 X H * DELETE 0
289 X H * DELETE 0
290 X H * DELETE 0
291 X H * DELETE 0
292 X H * DELETE 0
293 X H * DELETE 0
294 X H * DELETE 0
295 rollback;
296 Update by TUX scan
297 begin;
298 delete from test.t1 where c > 0;
299 Expect 5 locks per replica
300 select * from ndbinfo.ordered_locks;
301 mode state detail op waiting
302 X H * DELETE 0
303 X H * DELETE 0
304 X H * DELETE 0
305 X H * DELETE 0
306 X H * DELETE 0
307 X H * DELETE 0
308 X H * DELETE 0
309 X H * DELETE 0
310 X H * DELETE 0
311 X H * DELETE 0
312 rollback;
313 ----------------
314 Multi op per row
315 ----------------
316 Shared PK read
317 begin;
318 select * from test.t1 where a=2 lock in share mode;
319 a b c
320 2 2 2
321 select * from test.t1 where a=2 lock in share mode;
322 a b c
323 2 2 2
324 select * from test.t1 where a=2 lock in share mode;
325 a b c
326 2 2 2
327 Expect 3 locks held on primary, only one is holder
328 select * from ndbinfo.ordered_locks order by detail, op;
329 mode state detail op waiting
330 S H READ 0
331 S H READ 0
332 S H * READ 0
333 rollback;
334 Shared and Exclusive PK read
335 begin;
336 select * from test.t1 where a=2 lock in share mode;
337 a b c
338 2 2 2
339 select * from test.t1 where a=2 for update;
340 a b c
341 2 2 2
342 select * from test.t1 where a=2 lock in share mode;
343 a b c
344 2 2 2
345 Expect 3 locks held on primary, only one is holder
346 Holder is Shared, others are Xclusive
347 select * from ndbinfo.ordered_locks order by detail, op;
348 mode state detail op waiting
349 X H READ 0
350 X H READ 0
351 S H * READ 0
352 rollback;
353 Multi Exclusive PK read
354 begin;
355 select * from test.t1 where a=2 for update;
356 a b c
357 2 2 2
358 select * from test.t1 where a=2 for update;
359 a b c
360 2 2 2
361 select * from test.t1 where a=2 for update;
362 a b c
363 2 2 2
364 Expect 3 locks held on primary, only one is holder
365 select * from ndbinfo.ordered_locks order by detail, op;
366 mode state detail op waiting
367 X H READ 0
368 X H READ 0
369 X H * READ 0
370 rollback;
371 Multi scan shared
372 begin;
373 select * from test.t1 where a > 4 lock in share mode;
374 select * from test.t1 where a > 4 lock in share mode;
375 select * from test.t1 where a > 4 lock in share mode;
376 Expect 3 locks held on 1 row, only one is holder
377 select * from ndbinfo.ordered_locks order by detail, op;
378 mode state detail op waiting
379 S H READ 0
380 S H READ 0
381 S H * READ 0
382 rollback;
383 Multi scan mixed
384 begin;
385 select * from test.t1 where a > 4 lock in share mode;
386 select * from test.t1 where a > 4 for update;
387 select * from test.t1 where a > 4 lock in share mode;
388 Expect 3 locks held on 1 row, only one is holder
389 Holder is shared
390 select * from ndbinfo.ordered_locks order by detail, op;
391 mode state detail op waiting
392 X H READ 0
393 X H READ 0
394 S H * READ 0
395 rollback;
396 Multi scan exclusive
397 begin;
398 select * from test.t1 where a > 4 for update;
399 select * from test.t1 where a > 4 for update;
400 select * from test.t1 where a > 4 for update;
401 Expect 3 locks held on 1 row, only one is holder
402 select * from ndbinfo.ordered_locks order by detail, op;
403 mode state detail op waiting
404 X H READ 0
405 X H READ 0
406 X H * READ 0
407 rollback;
408 ReadSh-Update PK
409 begin;
410 select * from test.t1 where a=2 lock in share mode;
411 update test.t1 set b=20 where a=2;
412 Expect 1 Primary S Read + 1 per replica EX Update
413 S is holder on primary replica, EX on Backup
414 select * from ndbinfo.ordered_locks order by detail, op;
415 mode state detail op waiting
416 X H UPDATE 0
417 S H * READ 0
418 X H * UPDATE 0
419 rollback;
420 ReadEx-Update PK
421 begin;
422 select * from test.t1 where a=2 for update;
423 update test.t1 set b=20 where a=2;
424 Expect 1 Primary EX Read + 1 per replica EX Update
425 select * from ndbinfo.ordered_locks order by detail, op;
426 mode state detail op waiting
427 X H UPDATE 0
428 X H * READ 0
429 X H * UPDATE 0
430 rollback;
431 ReadSh-Delete PK
432 begin;
433 select * from test.t1 where a=2 lock in share mode;
434 delete from test.t1 where a=2;
435 Expect 1 Primary S Read + 1 per replica EX Delete
436 S is holder on primary replica, EX on Backup
437 select * from ndbinfo.ordered_locks order by detail, op;
438 mode state detail op waiting
439 X H DELETE 0
440 X H * DELETE 0
441 S H * READ 0
442 rollback;
443 ReadEx-Update PK
444 begin;
445 select * from test.t1 where a=2 for update;
446 delete from test.t1 where a=2;
447 Expect 1 Primary EX Read + 1 per replica EX Delete
448 select * from ndbinfo.ordered_locks order by detail, op;
449 mode state detail op waiting
450 X H DELETE 0
451 X H * DELETE 0
452 X H * READ 0
453 rollback;
454 Insert-Update PK
455 begin;
456 insert into test.t1 values (6,6,6);
457 update test.t1 set b=60 where a=6;
458 Expect 2 locks per replica, held by Insert
459 select * from ndbinfo.ordered_locks order by detail, op;
460 mode state detail op waiting
461 X H UPDATE 0
462 X H UPDATE 0
463 X H * INSERT 0
464 X H * INSERT 0
465 rollback;
466 Insert-Delete PK
467 begin;
468 insert into test.t1 values (6,6,6);
469 delete from test.t1 where a=6;
470 Expect 2 locks per replica, held by Insert
471 select * from ndbinfo.ordered_locks order by detail, op;
472 mode state detail op waiting
473 X H DELETE 0
474 X H DELETE 0
475 X H * INSERT 0
476 X H * INSERT 0
477 rollback;
478 Update-Update PK
479 begin;
480 update test.t1 set b=45 where a=3;
481 update test.t1 set b=46 where a=3;
482 Expect 2 locks per replica
483 select * from ndbinfo.ordered_locks order by detail, op;
484 mode state detail op waiting
485 X H UPDATE 0
486 X H UPDATE 0
487 X H * UPDATE 0
488 X H * UPDATE 0
489 rollback;
490 Update-Delete PK
491 begin;
492 update test.t1 set b=45 where a=3;
493 delete from test.t1 where a=3;
494 Expect 2 locks per replica held by Update
495 select * from ndbinfo.ordered_locks order by detail, op;
496 mode state detail op waiting
497 X H DELETE 0
498 X H DELETE 0
499 X H * UPDATE 0
500 X H * UPDATE 0
501 rollback;
502 Delete-Insert PK
503 begin;
504 delete from test.t1 where a=4;
505 insert into test.t1 values (4,44,44);
506 Expect 2 locks per replica held by Delete
507 select * from ndbinfo.ordered_locks order by detail, op;
508 mode state detail op waiting
509 X H INSERT 0
510 X H INSERT 0
511 X H * DELETE 0
512 X H * DELETE 0
513 rollback;
514 Delete-Insert-Delete PK
515 begin;
516 delete from test.t1 where a=4;
517 insert into test.t1 values (4,44,44);
518 delete from test.t1 where a=4;
519 Expect 3 locks per replica held by Delete
520 select * from ndbinfo.ordered_locks order by detail, op;
521 mode state detail op waiting
522 X H DELETE 0
523 X H DELETE 0
524 X H INSERT 0
525 X H INSERT 0
526 X H * DELETE 0
527 X H * DELETE 0
528 rollback;
529 Insert-Delete-Insert PK
530 begin;
531 insert into test.t1 values (6,66,66);
532 delete from test.t1 where a=6;
533 insert into test.t1 values (6,7,8);
534 Expect 3 locks per replica, held by Insert
535 select * from ndbinfo.ordered_locks order by detail, op;
536 mode state detail op waiting
537 X H DELETE 0
538 X H DELETE 0
539 X H INSERT 0
540 X H INSERT 0
541 X H * INSERT 0
542 X H * INSERT 0
543 rollback;
544 -----------------
545 Multi-transaction
546 -----------------
547 Shared reads PK
548 begin;
549 select * from test.t1 where a=1 lock in share mode;
550 a b c
551 1 1 1
552 begin;
553 select * from test.t1 where a=1 lock in share mode;
554 a b c
555 1 1 1
556 begin;
557 select * from test.t1 where a=1 lock in share mode;
558 a b c
559 1 1 1
560 Expect 3 row locks taken, one is owner
561 select * from ndbinfo.ordered_locks order by detail, op;
562 mode state detail op waiting
563 S H READ 0
564 S H READ 0
565 S H * READ 0
566 Expect 3 transactions
567 select count(distinct transid) from ndbinfo.cluster_locks;
568 count(distinct transid)
569 3
570 rollback;
571 rollback;
572 rollback;
573 **********
574 Contention
575 **********
576 PK SH vs PK EX
577 begin;
578 select * from test.t1 where a=1 lock in share mode;
579 a b c
580 1 1 1
581 begin;
582 select * from test.t1 where a=1 for update;;
583 Expect : X waiting for SH lock
584 2 transactions
585 select * from ndbinfo.ordered_locks order by detail, op;
586 mode state detail op waiting
587 X W READ 1
588 S H * READ 0
589 select count(distinct transid) from ndbinfo.cluster_locks;
590 count(distinct transid)
591 2
592 rollback;
593 a b c
594 1 1 1
595 rollback;
596 PK EX vs PK SH
597 begin;
598 select * from test.t1 where a=1 for update;
599 a b c
600 1 1 1
601 begin;
602 select * from test.t1 where a=1 lock in share mode;;
603 Expect : SH waiting for X lock
604 2 transactions
605 select * from ndbinfo.ordered_locks order by detail, op;
606 mode state detail op waiting
607 S W READ 1
608 X H * READ 0
609 select count(distinct transid) from ndbinfo.cluster_locks;
610 count(distinct transid)
611 2
612 rollback;
613 a b c
614 1 1 1
615 rollback;
616 PK EX vs PK EX
617 begin;
618 select * from test.t1 where a=1 for update;
619 a b c
620 1 1 1
621 begin;
622 select * from test.t1 where a=1 for update;;
623 Expect : X waiting for X lock
624 2 transactions
625 select * from ndbinfo.ordered_locks order by detail, op;
626 mode state detail op waiting
627 X W READ 1
628 X H * READ 0
629 select count(distinct transid) from ndbinfo.cluster_locks;
630 count(distinct transid)
631 2
632 rollback;
633 a b c
634 1 1 1
635 rollback;
636 Insert vs PK SH read
637 begin;
638 insert into test.t1 values (7,7,7);
639 begin;
640 select * from test.t1 where a=7 lock in share mode;;
641 Expect : S waiting for X lock
642 2 transactions
643 select * from ndbinfo.ordered_locks order by detail, op;
644 mode state detail op waiting
645 S W READ 1
646 X H * INSERT 0
647 X H * INSERT 0
648 select count(distinct transid) from ndbinfo.cluster_locks;
649 count(distinct transid)
650 2
651 rollback;
652 a b c
653 rollback;
654 Update vs PK SH read
655 begin;
656 update test.t1 set b=55 where a=5;
657 begin;
658 select * from test.t1 where a=5 lock in share mode;;
659 Expect : S waiting for X lock
660 2 transactions
661 select * from ndbinfo.ordered_locks order by detail, op;
662 mode state detail op waiting
663 S W READ 1
664 X H * UPDATE 0
665 X H * UPDATE 0
666 select count(distinct transid) from ndbinfo.cluster_locks;
667 count(distinct transid)
668 2
669 rollback;
670 a b c
671 5 5 5
672 rollback;
673 PK SH read vs Update
674 begin;
675 select * from test.t1 where a=5 lock in share mode;
676 a b c
677 5 5 5
678 begin;
679 update test.t1 set b=55 where a=5;;
680 Expect : X waiting for S lock
681 2 transactions
682 select * from ndbinfo.ordered_locks order by detail, op;
683 mode state detail op waiting
684 X W UPDATE 1
685 S H * READ 0
686 select count(distinct transid) from ndbinfo.cluster_locks;
687 count(distinct transid)
688 2
689 rollback;
690 rollback;
691 Delete vs PK SH read
692 begin;
693 delete from test.t1 where a=5;
694 begin;
695 select * from test.t1 where a=5 lock in share mode;;
696 Expect : S waiting for X lock
697 2 transactions
698 select * from ndbinfo.ordered_locks order by detail, op;
699 mode state detail op waiting
700 S W READ 1
701 X H * DELETE 0
702 X H * DELETE 0
703 select count(distinct transid) from ndbinfo.cluster_locks;
704 count(distinct transid)
705 2
706 rollback;
707 a b c
708 5 5 5
709 rollback;
710 PK SH read vs Delete
711 begin;
712 select * from test.t1 where a=5 lock in share mode;
713 a b c
714 5 5 5
715 begin;
716 delete from test.t1 where a=5;;
717 Expect : X waiting for S lock
718 2 transactions
719 select * from ndbinfo.ordered_locks order by detail, op;
720 mode state detail op waiting
721 X W DELETE 1
722 S H * READ 0
723 select count(distinct transid) from ndbinfo.cluster_locks;
724 count(distinct transid)
725 2
726 rollback;
727 rollback;
728 PK SH read vs EX scan
729 begin;
730 select * from test.t1 where a=5 lock in share mode;
731 a b c
732 5 5 5
733 begin;
734 select * from test.t1 order by a for update;;
735 Expect : 5-1 X locks from scan
736 1 X lock waiting from scan
737 1 S lock held by read
738 2 transactions
739 select mode, state, detail, waiting from ndbinfo.ordered_locks order by detail, mode, op;
740 mode state detail waiting
741 X W 1
742 S H * 0
743 X H * 0
744 X H * 0
745 X H * 0
746 X H * 0
747 select count(distinct transid) from ndbinfo.cluster_locks;
748 count(distinct transid)
749 2
750 rollback;
751 a b c
752 1 1 1
753 2 2 2
754 3 3 3
755 4 4 4
756 5 5 5
757 rollback;
758 PK EX read vs SH scan
759 begin;
760 select * from test.t1 where a=5 for update;
761 a b c
762 5 5 5
763 begin;
764 select * from test.t1 order by a lock in share mode;;
765 Expect : 5-1 S locks from scan
766 1 S lock waiting from scan
767 1 X lock held by read
768 2 transactions
769 select mode, state, detail, waiting from ndbinfo.ordered_locks order by detail, mode, op;
770 mode state detail waiting
771 S W 1
772 S H * 0
773 S H * 0
774 S H * 0
775 S H * 0
776 X H * 0
777 select count(distinct transid) from ndbinfo.cluster_locks;
778 count(distinct transid)
779 2
780 rollback;
781 a b c
782 1 1 1
783 2 2 2
784 3 3 3
785 4 4 4
786 5 5 5
787 rollback;
788 SH read, SH read, SH upgrade (blocked)
789 begin;
790 select * from test.t1 where a=5 lock in share mode;
791 a b c
792 5 5 5
793 begin;
794 select * from test.t1 where a=5 lock in share mode;
795 a b c
796 5 5 5
797 Upgrade attempt will block as other trans has
798 shared lock on row
799 select * from test.t1 where a=5 for update;;
800 2 shared locks on row
801 1 ex read waiting
802 2 transactions
803 select * from ndbinfo.ordered_locks order by detail, mode, op;
804 mode state detail op waiting
805 S H READ 0
806 X W READ 1
807 S H * READ 0
808 select count(distinct transid) from ndbinfo.cluster_locks;
809 count(distinct transid)
810 2
811 rollback;
812 a b c
813 5 5 5
814 rollback;
815 drop table test.t1;
816 ***********
817 Blob tables
818 ***********
819 create table test.t1 (a int primary key, b int, c text) engine=ndb;
820 insert into test.t1 values (1, 1, repeat('SSG',300));
821 Show PK CR leaves no locks
822 begin;
823 select a,b,length(c) from test.t1 where a=1;
824 a b length(c)
825 1 1 900
826 select * from ndbinfo.ordered_locks order by detail, mode, op;
827 mode state detail op waiting
828 rollback;
829 Show PK SH lock
830 begin;
831 select a,b,length(c) from test.t1 where a=1 lock in share mode;
832 a b length(c)
833 1 1 900
834 Expect Shared lock on main table row only
835 select * from ndbinfo.ordered_locks order by detail, mode, op;
836 mode state detail op waiting
837 S H * READ 0
838 rollback;
839 Show PK EX lock
840 begin;
841 select a,b,length(c) from test.t1 where a=1 for update;
842 a b length(c)
843 1 1 900
844 Expect Exclusive lock on main table row only
845 select * from ndbinfo.ordered_locks order by detail, mode, op;
846 mode state detail op waiting
847 X H * READ 0
848 rollback;
849 Show insert locks
850 begin;
851 insert into test.t1 values (2,2,repeat('TSG', 300));
852 Expect Exclusive locks on all replicas of main + part row
853 select * from ndbinfo.ordered_locks order by detail, mode, op;
854 mode state detail op waiting
855 X H UPDATE 0
856 X H UPDATE 0
857 X H * INSERT 0
858 X H * INSERT 0
859 X H * INSERT 0
860 X H * INSERT 0
861 rollback;
862 Show update locks
863 begin;
864 update test.t1 set c=repeat('FSG', 300) where a=1;
865 Expect Exclusive locks on all replicas of main + part row
866 Various : Reads, Updates on main, Updates on parts
867 Part(s) may have delete + insert rather than update
868 select * from ndbinfo.ordered_locks order by detail,op;
869 mode state detail op waiting
870 X H READ 0
871 X H UPDATE 0
872 X H UPDATE 0
873 X H UPDATE 0
874 X H * READ 0
875 X H * UPDATE 0
876 X H * UPDATE 0
877 X H * UPDATE 0
878 rollback;
879 Show delete locks
880 begin;
881 delete from test.t1 where a=1;
882 Expect Exclusive locks on all replicas of main + part row
883 select * from ndbinfo.ordered_locks order by detail,op;
884 mode state detail op waiting
885 X H DELETE 0
886 X H READ 0
887 X H * DELETE 0
888 X H * DELETE 0
889 X H * DELETE 0
890 X H * READ 0
891 rollback;
892 Show CR takes a lock transiently
893 Take exclusive lock
894 begin;
895 select a,b,length(c) from test.t1 where a=1 for update;
896 a b length(c)
897 1 1 900
898 select a,b,length(c) from test.t1 where a=1;;
899 Expect Shared waiting for Exclusive on main table row.
900 select * from ndbinfo.ordered_locks order by detail,op;
901 mode state detail op waiting
902 S W READ 1
903 X H * READ 0
904 select count(distinct transid) from ndbinfo.cluster_locks;
905 count(distinct transid)
906 2
907 rollback;
908 a b length(c)
909 1 1 900
910 rollback;
911 drop table test.t1;
912 ***********
913 Unique keys
914 ***********
915 create table test.t1 (a int primary key, b int, c int, unique(c)) engine=ndb;
916 insert into test.t1 values (5,5,5);
917 Show UK CR takes shared locks
918 begin;
919 select * from test.t1 where c=5;
920 a b c
921 5 5 5
922 select * from ndbinfo.ordered_locks order by detail,op;
923 mode state detail op waiting
924 S H * READ 0
925 S H * READ 0
926 select count(distinct tableid) from ndbinfo.cluster_locks;
927 count(distinct tableid)
928 2
929 rollback;
930 Show UK EX read
931 begin;
932 select * from test.t1 where c=5 for update;
933 a b c
934 5 5 5
935 select * from ndbinfo.ordered_locks order by detail,op;
936 mode state detail op waiting
937 X H * READ 0
938 X H * READ 0
939 select count(distinct tableid) from ndbinfo.cluster_locks;
940 count(distinct tableid)
941 2
942 rollback;
943 Show UK update, not of UK column
944 begin;
945 update test.t1 set b=55 where c=5;
946 select * from ndbinfo.ordered_locks order by detail,op;
947 mode state detail op waiting
948 X H * READ 0
949 X H * UPDATE 0
950 X H * UPDATE 0
951 select count(distinct tableid) from ndbinfo.cluster_locks;
952 count(distinct tableid)
953 2
954 rollback;
955 Show UK update, of UK column
956 begin;
957 update test.t1 set c=55 where c=5;
958 Read by UK @ primary
959 Read @ UK
960 Update of main @ both
961 Delete of old UK @ both
962 Insert of new UK @ both
963 select * from ndbinfo.ordered_locks order by detail,op;
964 mode state detail op waiting
965 X H DELETE 0
966 X H UPDATE 0
967 X H * DELETE 0
968 X H * INSERT 0
969 X H * INSERT 0
970 X H * READ 0
971 X H * READ 0
972 X H * UPDATE 0
973 select count(distinct tableid) from ndbinfo.cluster_locks;
974 count(distinct tableid)
975 2
976 rollback;
977 Show UK delete
978 begin;
979 delete from test.t1 where c=5;
980 select * from ndbinfo.ordered_locks order by detail,op;
981 mode state detail op waiting
982 X H DELETE 0
983 X H * DELETE 0
984 X H * DELETE 0
985 X H * DELETE 0
986 X H * READ 0
987 select count(distinct tableid) from ndbinfo.cluster_locks;
988 count(distinct tableid)
989 2
990 rollback;
991 Show PK update vs UK update
992 begin;
993 update test.t1 set b=400 where a=5;
994 begin;
995 update test.t1 set b=444 where c=5;;
996 Expect PK update holding lock@each replica
997 Expect UK read holding lock on UK table + waiting for
998 lock on UK table primary
999 select * from ndbinfo.ordered_locks order by detail,op;
1000 mode state detail op waiting
1001 X W UPDATE 1
1002 X H * READ 0
1003 X H * UPDATE 0
1004 X H * UPDATE 0
1005 select count(distinct transid) from ndbinfo.cluster_locks;
1006 count(distinct transid)
1007 2
1008 select count(distinct tableid) from ndbinfo.cluster_locks;
1009 count(distinct tableid)
1010 2
1011 rollback;
1012 rollback;
1013 drop table test.t1;
1014 ********
1015 Deadlock
1016 ********
1017 create table test.t1 (a int primary key, b int) engine=ndb;
1018 insert into test.t1 values (1,1), (2,2);
1019 begin;
1020 select * from test.t1 where a=1 for update;
1021 a b
1022 1 1
1023 begin;
1024 select * from test.t1 where a=2 for update;
1025 a b
1026 2 2
1027 select * from test.t1 where a=2 for update;;
1028 select * from test.t1 where a=1 for update;;
1029 Show using cluster_locks to find the deadlock
1030
1031 create view transaction_dependencies as
1032 select a.transid as waiting_transid, b.transid as holding_transid
1033 from ndbinfo.cluster_locks a join
1034 ndbinfo.cluster_locks b
1035 on a.node_id = b.node_id and
1036 a.block_instance = b.block_instance and
1037 a.waiting_for = b.lock_num;
1038 select * from transaction_dependencies;
1039 create view second_level_dependencies as
1040 select a.waiting_transid as waiting_transid,
1041 b.holding_transid as holding_transid,
1042 a.holding_transid as other
1043 from transaction_dependencies a join transaction_dependencies b
1044 on a.holding_transid = b.waiting_transid;
1045 select * from second_level_dependencies;
1046 create view 2_way_deadlocks_found as
1047 select *
1048 from second_level_dependencies
1049 where waiting_transid = holding_transid;
1050 select * from 2_way_deadlocks_found;
1051 create view unique_2_way_deadlocks as
1052 select distinct
1053 if (waiting_transid < other,
1054 waiting_transid,
1055 other),
1056 if (waiting_transid < other,
1057 other,
1058 waiting_transid)
1059 from 2_way_deadlocks_found;
1060 select * from unique_2_way_deadlocks;
1061 select count(1)
1062 from unique_2_way_deadlocks;
1063 count(1)
1064 1
1065 drop view unique_2_way_deadlocks;
1066 drop view 2_way_deadlocks_found;
1067 drop view second_level_dependencies;
1068 drop view transaction_dependencies;
1069 rollback;
1070 rollback;
1071 drop table test.t1;
1072 drop view ndbinfo.ordered_locks;