Debian Codegraph @

mysql-test/suite/ndb/t/ndbinfo_cluster_locks.test

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