Debian Codegraph @

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

raw
1 --source include/have_ndb.inc
2
3 use ndbinfo;
4
5 #
6 # WL 7656 locks_per_fragment view
7 #
8
9 # Note that we create the test table partitioned over lots of fragments.
10 # That is a workaround for row partitioning being affected by platform
11 # endianness.
12 # By having many partitions, there will be max a single row stored in each
13 # fragment. Thus avoiding platform dependencies on how many locks being
14 # reported before intentionally being blocked on lock conflicts where we
15 # test for such.
16 #
17 create table test.t1 (a int primary key, b int, c int, unique(b), key(c))
18 engine=ndb
19 partition by key(a) partitions 64;
20
21 create view frag_locks as
22 select fq_name = 'test/def/t1' as main_tab,
23 type,
24 sum(ex_req) as ex_reqs,
25 sum(ex_imm_ok) as ex_imm_oks,
26 sum(ex_wait_ok) as ex_wait_oks,
27 sum(ex_wait_fail) as ex_wait_fails,
28 sum(sh_req) as sh_reqs,
29 sum(sh_imm_ok) as sh_imm_oks,
30 sum(sh_wait_ok) as sh_wait_oks,
31 sum(sh_wait_fail) as sh_wait_fails
32 from locks_per_fragment
33 where
34 fq_name = 'test/def/t1' or
35 parent_fq_name = 'test/def/t1'
36 group by
37 main_tab, type;
38
39 --echo No locks taken
40 --sorted_result
41 select * from frag_locks;
42
43 create table baseline as select * from frag_locks;
44 create table current like baseline;
45
46 create view baseline_diff as
47 select c.ex_reqs - b.ex_reqs as ex_reqs,
48 c.ex_imm_oks - b.ex_imm_oks as ex_imm_oks,
49 c.ex_wait_oks - b.ex_wait_oks as ex_wait_oks,
50 c.ex_wait_fails - b.ex_wait_fails as ex_wait_fails,
51 c.sh_reqs - b.sh_reqs as sh_reqs,
52 c.sh_imm_oks - b.sh_imm_oks as sh_imm_oks,
53 c.sh_wait_oks - b.sh_wait_oks as sh_wait_oks,
54 c.sh_wait_fails - b.sh_wait_fails as sh_wait_fail,
55 c.type,
56 c.main_tab
57 from baseline b join current c on
58 b.type = c.type and
59 b.main_tab = c.main_tab
60 order by main_tab desc;
61
62 --echo Start testing
63 --echo Insert some data
64 insert into test.t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5), (6,6,6), (7,7,7), (8,8,8);
65 --echo Expect :
66 --echo 1. NoOfReplicas(2) * NumRows * Exclusive row locks immediately claimed on user table
67 --echo 2. NoOfReplicas(2) * NumRows * Exclusive row locks immediately claimed on unique index table
68 --source ndbinfo_show_locks_delta.inc
69
70 --echo Read committed by PK
71 select * from test.t1 where a=7;
72 --echo Expect :
73 --echo 1. No increments
74 --source ndbinfo_show_locks_delta.inc
75 --echo ****************************************************
76
77 --echo Read shared by PK
78 select * from test.t1 where a=7 lock in share mode;
79 --echo Expect :
80 --echo 1. 1 sh lock on TAB
81 --echo 2. No effect on UI
82 --source ndbinfo_show_locks_delta.inc
83 --echo ****************************************************
84
85 --echo Read exclusive by PK
86 select * from test.t1 where a=7 for update;
87 --echo Expect :
88 --echo 1. 1 ex lock on TAB
89 --echo 2. No effect on UI
90 --source ndbinfo_show_locks_delta.inc
91 --echo ****************************************************
92
93 --echo Update by PK
94 update test.t1 set c=50 where a=5;
95 --echo Expect :
96 --echo 1. 2 * 2 ex locks on TAB
97 --echo 2. No effect on UI
98 --source ndbinfo_show_locks_delta.inc
99 --echo ****************************************************
100
101 --echo Update by PK, UK affected
102 update test.t1 set b=50 where a=5;
103 --echo Expect :
104 --echo 1. 2 * 1 ex on TAB
105 --echo Extra tab read here?
106 --echo 2. 2 * 2 * ex on UI table (Delete old row, Insert new)
107 --source ndbinfo_show_locks_delta.inc
108 --echo ****************************************************
109
110 --echo Delete by PK
111 delete from test.t1 where a=5;
112 --echo Expect :
113 --echo 1. 2 * 1 * ex on TAB
114 --echo 2. 2 * 1 * ex on UI
115 --source ndbinfo_show_locks_delta.inc
116 --echo ****************************************************
117
118 --echo Read committed by UK
119 select * from test.t1 where b=3;
120 --echo Expect:
121 --echo 1. 1 * SH on TAB (upgrade)
122 --echo 2. 1 * SH on UI (upgrade)
123 --source ndbinfo_show_locks_delta.inc
124 --echo ****************************************************
125
126 --echo Read shared by UK
127 select * from test.t1 where b=3 lock in share mode;
128 --echo Expect:
129 --echo 1. 1 * SH on TAB
130 --echo 2. 1 * SH on TAB
131 --source ndbinfo_show_locks_delta.inc
132 --echo ****************************************************
133
134 --echo Read exclusive by UK
135 select * from test.t1 where b=3 for update;
136 --echo Expect:
137 --echo 1. 1 * EX on TAB
138 --echo 2. 1 * EX on UI
139 --source ndbinfo_show_locks_delta.inc
140 --echo ****************************************************
141
142 --echo Update by UK, not affecting UK value
143 update test.t1 set c=30 where b=3;
144 --echo Expect:
145 --echo 1. 2 * 1 EX on TAB
146 --echo 2. 1 * EX on UI
147 --source ndbinfo_show_locks_delta.inc
148 --echo ****************************************************
149
150 --echo Update by UK, affecting UK value
151 update test.t1 set b=30 where b=3;
152 --echo Expect:
153 --echo 1. 1 + 2 * 1 EX on TAB
154 --echo 2. 1 + 2 * 2 EX on UI
155 --echo Due to : Ex read by UK -> Ex read of PK ->
156 --echo Update T by PK (*NR) ->
157 --echo Delete + Insert of UI by PK (*NR)
158 --source ndbinfo_show_locks_delta.inc
159 --echo ****************************************************
160
161 --echo Delete by UK
162 delete from test.t1 where b=30;
163 --echo Expect:
164 --echo 1. 1 + 2 * 1 EX on TAB
165 --echo 2. 1 + 2 * 1 EX on UI
166 --source ndbinfo_show_locks_delta.inc
167 --echo ****************************************************
168
169 --echo OI scan (Primary) with no locks
170 select * from test.t1 order by a;
171 --echo Expect : No locking
172 --source ndbinfo_show_locks_delta.inc
173 --echo ****************************************************
174
175 --echo OI scan (Primary) with shared lock, held on no rows
176 select * from test.t1 where a%1234 = 1233 lock in share mode;
177 --echo Expect : Lock taken on each row, but not held
178 --source ndbinfo_show_locks_delta.inc
179 --echo ****************************************************
180
181 --echo OI scan (Primary) with shared lock, held on 1 row
182 select * from test.t1 where a%1234 = 8 lock in share mode;
183 --echo Expect : Lock taken on each row, one held via takeover
184 --echo x+1
185 --source ndbinfo_show_locks_delta.inc
186 --echo ****************************************************
187
188 --echo OI scan (Primary) with shared lock, held on all rows
189 select * from test.t1 where a%1234 > 0 order by a lock in share mode;
190 --echo Expect : Lock taken on each row, all held via takeover
191 --echo 2x
192 --source ndbinfo_show_locks_delta.inc
193 --echo ****************************************************
194
195 --echo OI scan (Primary) with exclusive lock, held on no rows
196 select * from test.t1 where a%1234 = 1233 for update;
197 --echo Expect : Lock taken on each row, but not held
198 --source ndbinfo_show_locks_delta.inc
199 --echo ****************************************************
200
201 --echo OI scan (Primary) with exclusive lock, held on 1 row
202 select * from test.t1 where a%1234 = 8 for update;
203 --echo Expect : Lock taken on each row, one held via takeover
204 --echo x+1
205 --source ndbinfo_show_locks_delta.inc
206 --echo ****************************************************
207
208 --echo OI scan (Primary) with exclusive lock, held on all rows
209 select * from test.t1 where a%1234 > 0 order by a for update;
210 --echo Expect : Lock taken on each row, all held via takeover
211 --echo 2x
212 --source ndbinfo_show_locks_delta.inc
213 --echo ****************************************************
214
215 --echo Multiple locks from a single transaction on one row
216 begin;
217 select * from test.t1 where a=1 lock in share mode;
218 select * from test.t1 where a=1 lock in share mode;
219 select * from test.t1 where a=1 lock in share mode;
220 --echo Upgrade
221 select * from test.t1 where a=1 for update;
222 select * from test.t1 where a=1 for update;
223 select * from test.t1 where a=1 for update;
224 commit;
225 --echo Expect : 3 * Ex lock + 3 * Sh lock
226 --source ndbinfo_show_locks_delta.inc
227 --echo ****************************************************
228
229 --echo ****************
230 --echo Show lock Delays
231 --echo ****************
232
233 connect (con1,localhost,root,,);
234 connect (con2,localhost,root,,);
235
236 --echo SH <- EX PK
237 --connection con1
238 --echo Take and hold shared lock on a row
239 begin;
240 select * from test.t1 where a=1 lock in share mode;
241
242 --connection default
243 --source ndbinfo_show_locks_delta.inc
244 --source ndbinfo_init_frag_lock_count.inc
245 --source ndb_disable_trans_timeout.inc
246
247 --connection con2
248 --echo Attempt to get an exclusive lock from a separate
249 --echo transaction, PK read
250 --send select * from test.t1 where a=1 for update;
251
252 --connection default
253 --echo Give request a chance to be registered and wait
254 --source ndbinfo_wait_frag_lock_count.inc
255
256 --connection con1
257 --echo Rollback lock holder, allowing con2 to get lock
258 rollback;
259
260 --connection con2
261 --reap
262
263 --connection default
264 --echo Expect :
265 --echo Exclusive lock successfully claimed after some delay
266 --source ndbinfo_show_locks_delta.inc
267 --echo ****************************************************
268
269 --echo SH <- EX SCAN
270 --connection con1
271 --echo Take and hold shared lock on a row
272 begin;
273 select * from test.t1 where a=1 lock in share mode;
274
275 --connection default
276 --source ndbinfo_show_locks_delta.inc
277 --source ndbinfo_init_frag_lock_count.inc
278
279 --connection con2
280 --echo Attempt to get an exclusive lock from a separate
281 --echo transaction, OI scan
282 --send select * from test.t1 where a % 1245 = 1 for update;
283
284 --connection default
285 --echo Give request a chance to be registered and wait
286 --source ndbinfo_wait_frag_lock_count.inc
287
288 --connection con1
289 --echo Rollback lock holder, allowing con2 to get lock
290 rollback;
291
292 --connection con2
293 --reap
294
295 --connection default
296 --echo Expect :
297 --echo x - 1 EX locks taken immediately
298 --echo 1 EX lock taken after wait
299 --echo 1 EX lock taken immediately (Takeover)
300 --source ndbinfo_show_locks_delta.inc
301 --echo ****************************************************
302
303 --echo EX <- SH UI
304 --connection con1
305 --echo Take and hold exclusive lock on a row
306 begin;
307 select * from test.t1 where a=1 for update;
308
309 --connection default
310 --source ndbinfo_show_locks_delta.inc
311 --source ndbinfo_init_frag_lock_count.inc
312
313 --connection con2
314 --echo Attempt to get a shared lock from a separate
315 --echo transaction, UK read
316 --send select * from test.t1 where b=1 lock in share mode;
317
318 --connection default
319 --echo Give request a chance to be registered and wait
320 --source ndbinfo_wait_frag_lock_count.inc
321
322 --connection con1
323 --echo Rollback lock holder, allowing con2 to get lock
324 rollback;
325
326 --connection con2
327 --reap
328
329 --connection default
330 --echo Expect :
331 --echo Shared lock immediately claimed on UI
332 --echo Shared lock successfully claimed on TAB after some delay
333 --source ndbinfo_show_locks_delta.inc
334 --echo ****************************************************
335
336 --echo SH <- EX UI
337 --connection con1
338 --echo Take and hold shared lock on a row
339 begin;
340 select * from test.t1 where a=1 lock in share mode;
341
342 --connection default
343 --source ndbinfo_show_locks_delta.inc
344 --source ndbinfo_init_frag_lock_count.inc
345
346 --connection con2
347 --echo Attempt to get an exclusive lock from a separate
348 --echo transaction, UK read
349 --send select * from test.t1 where b = 1 for update;
350
351 --connection default
352 --echo Give request a chance to be registered and wait
353 --source ndbinfo_wait_frag_lock_count.inc
354
355 --connection con1
356 --echo Rollback lock holder, allowing con2 to get lock
357 rollback;
358
359 --connection con2
360 --reap
361
362 --connection default
363 --echo Expect :
364 --echo Ex lock immediately claimed on UI
365 --echo Ex lock successfully claimed on TAB after some delay
366 --source ndbinfo_show_locks_delta.inc
367 --echo ****************************************************
368
369 --echo EX <- SH PK
370 --connection con1
371 --echo Take and hold exclusive lock on a row
372 begin;
373 select * from test.t1 where a=1 for update;
374
375 --connection default
376 --source ndbinfo_show_locks_delta.inc
377 --source ndbinfo_init_frag_lock_count.inc
378
379 --connection con2
380 --echo Attempt to get a shared lock from a separate
381 --echo transaction, PK read
382 --send select * from test.t1 where a=1 lock in share mode;
383
384 --connection default
385 --echo Give request a chance to be registered and wait
386 --source ndbinfo_wait_frag_lock_count.inc
387
388 --connection con1
389 --echo Rollback lock holder, allowing con2 to get lock
390 rollback;
391
392 --connection con2
393 --reap
394
395 --connection default
396 --echo Expect :
397 --echo Shared lock successfully claimed after some delay
398 --source ndbinfo_show_locks_delta.inc
399 --echo ****************************************************
400
401 --echo EX <- SH SCAN
402 --connection con1
403 --echo Take and hold exclusive lock on a row
404 begin;
405 select * from test.t1 where a=1 for update;
406
407 --connection default
408 --source ndbinfo_show_locks_delta.inc
409 --source ndbinfo_init_frag_lock_count.inc
410
411 --connection con2
412 --echo Attempt to get a shared lock from a separate
413 --echo transaction, OI scan
414 --send select * from test.t1 where a % 1245 = 1 lock in share mode;
415
416 --connection default
417 --echo Give request a chance to be registered and wait
418 --source ndbinfo_wait_frag_lock_count.inc
419
420 --connection con1
421 --echo Rollback lock holder, allowing con2 to get lock
422 rollback;
423
424 --connection con2
425 --reap
426
427 --connection default
428 --echo Expect :
429 --echo x - 1 SH locks taken immediately
430 --echo 1 SH lock taken after wait
431 --echo 1 SH lock taken immediately (Takeover)
432 --source ndbinfo_show_locks_delta.inc
433 --echo ****************************************************
434
435 --source ndb_enable_trans_timeout.inc
436
437 --echo *******************
438 --echo Lock claim failures
439 --echo *******************
440
441 --connection con1
442 --echo Take and hold shared lock on a row
443 begin;
444 select * from test.t1 where a=1 lock in share mode;
445
446 --connection default
447 --source ndbinfo_show_locks_delta.inc
448
449 --echo SH <- EX PK
450 --connection con2
451 --echo Attempt to get an exclusive lock from a separate
452 --echo transaction, PK read
453 --error ER_LOCK_WAIT_TIMEOUT
454 select * from test.t1 where a=1 for update;
455
456 --connection default
457 --echo Expect :
458 --echo Exclusive lock claim failed after some delay
459 --source ndbinfo_show_locks_delta.inc
460 --echo ****************************************************
461
462 --echo SH <- EX UK
463 --connection con2
464 --echo Attempt to get an exclusive lock from a separate
465 --echo transaction, UK read
466 --error ER_LOCK_WAIT_TIMEOUT
467 select * from test.t1 where b=1 for update;
468
469 --connection default
470 --echo Expect :
471 --echo EX immediate on unique key
472 --echo EX failed on TAB
473 --source ndbinfo_show_locks_delta.inc
474 --echo ****************************************************
475
476 --echo SH <- EX OI Scan
477 --connection con2
478 --echo Attempt to get an exclusive lock from a separate
479 --echo transaction, OI scan
480 --error ER_LOCK_WAIT_TIMEOUT
481 select * from test.t1 where a % 1234 =1 for update;
482
483 --connection default
484 --echo Expect :
485 --echo x -1 EX immediate on TAB
486 --echo 1 EX failed on TAB
487 --source ndbinfo_show_locks_delta.inc
488 --echo ****************************************************
489
490 --connection con1
491 --echo Escalate 'holding' lock to EX
492 select * from test.t1 where a=1 for update;
493 --connection default
494 --source ndbinfo_show_locks_delta.inc
495
496 --echo EX <- SH PK
497 --connection con2
498 --echo Attempt to get a shared lock from a separate
499 --echo transaction, PK read
500 --error ER_LOCK_WAIT_TIMEOUT
501 select * from test.t1 where a=1 lock in share mode;
502
503 --connection default
504 --echo Expect :
505 --echo Shared lock claim failed after some delay
506 --source ndbinfo_show_locks_delta.inc
507 --echo ****************************************************
508
509 --echo EX <- SH UK
510 --connection con2
511 --echo Attempt to get a shared lock from a separate
512 --echo transaction, UK read
513 --error ER_LOCK_WAIT_TIMEOUT
514 select * from test.t1 where b=1 lock in share mode;
515
516 --connection default
517 --echo Expect :
518 --echo SH immediate on unique key
519 --echo SH failed on TAB
520 --source ndbinfo_show_locks_delta.inc
521 --echo ****************************************************
522
523 --echo EX <- SH OI Scan
524 --connection con2
525 --echo Attempt to get a shared lock from a separate
526 --echo transaction, OI scan
527 --error ER_LOCK_WAIT_TIMEOUT
528 select * from test.t1 where a % 1234 =1 lock in share mode;
529
530 --connection default
531 --echo Expect :
532 --echo x -1 SH immediate on TAB
533 --echo 1 SH failed on TAB
534 --source ndbinfo_show_locks_delta.inc
535 --echo ****************************************************
536
537 --connection con1
538 --echo Escalate 'holding' lock to EX on UI too
539 select * from test.t1 where b=1 for update;
540 --connection default
541 --source ndbinfo_show_locks_delta.inc
542
543 --echo EX UK <- SH UK
544 --connection con2
545 --echo Attempt to get a shared lock from a separate
546 --echo transaction, UK read
547 --error ER_LOCK_WAIT_TIMEOUT
548 select * from test.t1 where b=1 lock in share mode;
549
550 --connection default
551 --echo Expect :
552 --echo SH failed on UK
553 --source ndbinfo_show_locks_delta.inc
554 --echo ****************************************************
555
556 --echo EX UK <- EX UK
557 --connection con2
558 --echo Attempt to get an exclusive lock from a separate
559 --echo transaction, UK read
560 --error ER_LOCK_WAIT_TIMEOUT
561 select * from test.t1 where b=1 for update;
562
563 --connection default
564 --echo Expect :
565 --echo EX failed on UK
566 --source ndbinfo_show_locks_delta.inc
567 --echo ****************************************************
568
569 --connection con1
570 rollback;
571 --connection default
572
573 --echo *******************
574 --echo Row existence cases
575 --echo *******************
576
577 --echo Show table content
578 select * from test.t1 order by a;
579
580 --echo SH PK read of non existing row
581 select * from test.t1 where a=1234 lock in share mode;
582 --echo Expect : Nothing
583 --source ndbinfo_show_locks_delta.inc
584 --echo ****************************************************
585
586 --echo EX PK read of non existing row
587 select * from test.t1 where a=1234 for update;
588 --echo Expect : Nothing
589 --source ndbinfo_show_locks_delta.inc
590 --echo ****************************************************
591
592 --echo Update of non existing row
593 update test.t1 set c=1234 where a=1234;
594 --echo Expect : Nothing
595 --source ndbinfo_show_locks_delta.inc
596 --echo ****************************************************
597
598 --echo Delete of non existing row
599 delete from test.t1 where a=1234;
600 --echo Expect : Nothing
601 --source ndbinfo_show_locks_delta.inc
602 --echo ****************************************************
603
604 --echo Insert of existing row
605 --error ER_DUP_ENTRY
606 insert into test.t1 values (1,1,1);
607 --echo Expect : Nothing
608 --source ndbinfo_show_locks_delta.inc
609 --echo ****************************************************
610
611
612 --echo *************
613 --echo Special cases
614 --echo *************
615
616 --echo Existing row is deleted, other transaction waiting
617 --echo for lock...
618
619 --source ndb_disable_trans_timeout.inc
620
621 --connection con1
622 --echo Prepare a delete on a row
623 begin;
624 delete from test.t1 where a=1;
625
626 --connection default
627 --source ndbinfo_show_locks_delta.inc
628 --source ndbinfo_init_frag_lock_count.inc
629
630 --connection con2
631 --echo Attempt to read the row by PK
632 --send select * from test.t1 where a =1 lock in share mode;
633
634 --connection default
635 --echo Give request a chance to be registered and wait
636 --source ndbinfo_wait_frag_lock_count.inc
637
638 --connection con1
639 --echo Commit the delete
640 commit;
641
642 --connection con2
643 # No error
644 --reap
645
646 --connection default
647 --echo Expect 1 SH lock on TAB, after delay
648 --source ndbinfo_show_locks_delta.inc
649 --echo ****************************************************
650
651 insert into test.t1 values (1,1,1);
652
653 --connection con1
654 --echo Prepare a delete on a row
655 begin;
656 delete from test.t1 where a=1;
657
658 --connection default
659 --source ndbinfo_show_locks_delta.inc
660 --source ndbinfo_init_frag_lock_count.inc
661
662 --connection con2
663 --echo Attempt to read the row by UK
664 --send select * from test.t1 where b=1 lock in share mode;
665
666 --connection default
667 --echo Give request a chance to be registered and wait
668 --source ndbinfo_wait_frag_lock_count.inc
669
670 --connection con1
671 --echo Commit the delete
672 commit;
673
674 --connection con2
675 # No error
676 --reap
677
678 --connection default
679 --echo Expect 1 SH lock on Unique, after delay
680 --source ndbinfo_show_locks_delta.inc
681 --echo ****************************************************
682
683 insert into test.t1 values (1,1,1);
684
685 --connection con1
686 --echo Prepare a delete on a row
687 begin;
688 delete from test.t1 where a=1;
689
690 --connection default
691 --source ndbinfo_show_locks_delta.inc
692 --source ndbinfo_init_frag_lock_count.inc
693
694 --connection con2
695 --echo Attempt to read the row by OI scan
696 --send select * from test.t1 where a % 1234 = 1 lock in share mode;
697
698 --connection default
699 --echo Give request a chance to be registered and wait
700 --source ndbinfo_wait_frag_lock_count.inc
701
702 --connection con1
703 --echo Commit the delete
704 commit;
705
706 --connection con2
707 # No error
708 --reap
709
710 --connection default
711 --echo Expect :
712 --echo x - 1 SH locks immediately claimed
713 --echo 1 SH lock claimed after wait
714 --source ndbinfo_show_locks_delta.inc
715 --echo ****************************************************
716
717 insert into test.t1 values (1,1,1);
718
719 --connection con1
720 --echo Prepare a delete on a row
721 begin;
722 delete from test.t1 where a=1;
723
724 --connection default
725 --source ndbinfo_show_locks_delta.inc
726 --source ndbinfo_init_frag_lock_count.inc
727
728 --connection con2
729 --echo Attempt to insert a similar row
730 --send insert into test.t1 values (1,1,1);
731
732 --connection default
733 --echo Give request a chance to be registered and wait
734 --source ndbinfo_wait_frag_lock_count.inc
735
736 --connection con1
737 --echo Commit the delete
738 commit;
739
740 --connection con2
741 # No error
742 --reap
743
744 --connection default
745 --echo Expect same as row never existed :
746 --echo 1 EX locks on TAB ok after wait (Insert, primary)
747 --echo 1 EX lock on TAB ok immediate (replica)
748 --echo or wait @ replica
749 --echo 2 EX locks on UI immediate (Insert)
750 delete from current; insert into current select * from frag_locks;
751 --echo expect 2 ex_reqs, either immediate or wait
752 select ex_reqs,
753 (ex_imm_oks + ex_wait_oks),
754 ex_wait_fails,
755 sh_reqs, sh_imm_oks, sh_wait_oks, sh_wait_fail,
756 main_tab, type from baseline_diff order by main_tab desc;
757
758 delete from baseline; insert into baseline select * from current;
759 --echo ****************************************************
760
761 # insert into test.t1 values (1,1,1);
762
763 --connection con1
764 --echo Prepare a delete on a row
765 begin;
766 delete from test.t1 where a=1;
767
768 --connection default
769 --source ndbinfo_show_locks_delta.inc
770 --source ndbinfo_init_frag_lock_count.inc
771
772 --connection con2
773 --echo Attempt to delete the row from another transaction
774 --send delete from test.t1 where a=1;
775
776 --connection default
777 --echo Give request a chance to be registered and wait
778 --source ndbinfo_wait_frag_lock_count.inc
779
780 --connection con1
781 --echo Commit the first delete
782 commit;
783
784 --connection con2
785 # No error, no such row.
786 --reap
787
788 --connection default
789 --echo Expect :
790 --echo 1 EX lock wait ok
791 --source ndbinfo_show_locks_delta.inc
792 --echo ****************************************************
793
794 --connection con1
795 --echo Prepare to insert a new row
796 begin;
797 insert into test.t1 values (27, 27, 27);
798
799 --connection default
800 --source ndbinfo_show_locks_delta.inc
801 --source ndbinfo_init_frag_lock_count.inc
802
803 --connection con2
804 --echo Attempt to read the row from another transaction
805 --echo (PK SH)
806 --send select * from test.t1 where a=27 lock in share mode;
807
808 --connection default
809 --echo Give request a chance to be registered and wait
810 --source ndbinfo_wait_frag_lock_count.inc
811
812 --connection con1
813 --echo Abort the insert
814 rollback;
815
816 --connection con2
817 # No error, no such row.
818 --reap
819
820 --connection default
821 --echo Expect :
822 --echo 1 SH lock wait ok
823 --source ndbinfo_show_locks_delta.inc
824 --echo ****************************************************
825
826 --connection con1
827 --echo Prepare to insert a new row
828 begin;
829 insert into test.t1 values (27, 27, 27);
830
831 --connection default
832 --source ndbinfo_show_locks_delta.inc
833 --source ndbinfo_init_frag_lock_count.inc
834
835 --connection con2
836 --echo Attempt to insert the same row from another transaction
837 --echo (PK SH)
838 --send insert into test.t1 values (27, 27, 27);
839
840 --connection default
841 --echo Give request a chance to be registered and wait
842 --source ndbinfo_wait_frag_lock_count.inc
843
844 --connection con1
845 --echo Abort the first insert
846 rollback;
847
848 --connection con2
849 # No error
850 --reap
851
852 --connection default
853 --echo Expect :
854 --echo 1 EX lock wait ok on TAB (prim)
855 --echo 1 EX lock immed on TAB (backup)
856 --echo 2 EX lock immed ok on UI
857 --source ndbinfo_show_locks_delta.inc
858 --echo ****************************************************
859
860 --source ndb_enable_trans_timeout.inc
861
862 --echo ****************************************************
863 --echo Show elapsed time added to totals
864 --echo ****************************************************
865 --echo Timeout case
866
867 let $elapsed_ok_before = query_get_value(select sum(wait_ok_millis) as m from ndbinfo.locks_per_fragment where fq_name='test/def/t1', m, 1);
868 let $elapsed_fail_before = query_get_value(select sum(wait_fail_millis) as m from ndbinfo.locks_per_fragment where fq_name='test/def/t1', m, 1);
869
870 --connection con1
871 begin;
872 select * from test.t1 where a=2 lock in share mode;
873
874 --connection con2
875 begin;
876 --error ER_LOCK_WAIT_TIMEOUT
877 select * from test.t1 where a=2 for update;
878 rollback;
879
880 --connection con1
881 rollback;
882
883 --connection default
884 --echo Show no extra 'ok' time, and some extra 'fail' time
885 let $elapsed_ok_after = query_get_value(select sum(wait_ok_millis) as m from ndbinfo.locks_per_fragment where fq_name='test/def/t1', m, 1);
886 let $elapsed_fail_after = query_get_value(select sum(wait_fail_millis) as m from ndbinfo.locks_per_fragment where fq_name='test/def/t1', m, 1);
887
888 --disable_query_log
889 --echo Expect 0 ok_millis, > 0 fail_millis
890 eval select ($elapsed_ok_after - $elapsed_ok_before) > 0 as ok_millis,
891 ($elapsed_fail_after - $elapsed_fail_before) > 0 as fail_millis;
892 --enable_query_log
893
894 --echo ****************************************************
895 --echo Ok wait case
896 --echo ****************************************************
897
898 --source ndb_disable_trans_timeout.inc
899
900 --echo Expect > 0 ok_millis, 0 fail_millis
901 --disable_query_log
902 --disable_result_log
903 let $success=0;
904 while (!$success)
905 {
906 --connection default
907 let $elapsed_ok_before = query_get_value(select sum(wait_ok_millis) as m from ndbinfo.locks_per_fragment where fq_name='test/def/t1', m, 1);
908 let $elapsed_fail_before = query_get_value(select sum(wait_fail_millis) as m from ndbinfo.locks_per_fragment where fq_name='test/def/t1', m, 1);
909
910 --connection con1
911 #--echo Take lock in transaction 1
912 begin;
913 select * from test.t1 where a=2 lock in share mode;
914
915 --connection con2
916 #--echo Attempt to take in transaction 2
917 begin;
918 --send select * from test.t1 where a=2 for update;
919
920 --connection default
921 # Sleep here is intended to be enough for some lock wait time
922 # to be measured
923 --sleep 2
924 --connection con1
925 rollback;
926 --connection con2
927 --reap
928 rollback;
929
930 --connection default
931 let $elapsed_ok_after = query_get_value(select sum(wait_ok_millis) as m from ndbinfo.locks_per_fragment where fq_name='test/def/t1', m, 1);
932 let $elapsed_fail_after = query_get_value(select sum(wait_fail_millis) as m from ndbinfo.locks_per_fragment where fq_name='test/def/t1', m, 1);
933
934 # --echo Expect 0 ok_millis, > 0 fail_millis
935 eval select ($elapsed_ok_after - $elapsed_ok_before) > 0 as ok_millis into @ok_millis;
936 eval select ($elapsed_fail_after - $elapsed_fail_before) > 0 as fail_millis into @fail_millis;
937 eval select @ok_millis as ok_millis, @fail_millis as fail_millis;
938 let $success = query_get_value(select (@ok_millis > 0) AND (@fail_millis=0) as ok, ok, 1);
939 }
940 --enable_result_log
941 # Show result
942 eval select ($elapsed_ok_after - $elapsed_ok_before) > 0 as ok_millis into @ok_millis;
943 eval select ($elapsed_fail_after - $elapsed_fail_before) > 0 as fail_millis into @fail_millis;
944 eval select @ok_millis as ok_millis, @fail_millis as fail_millis;
945 --enable_query_log
946
947 --source ndb_enable_trans_timeout.inc
948
949 drop table test.t1;
950 delete from current;
951 delete from baseline;
952
953 --echo *****
954 --echo Blobs
955 --echo *****
956
957 create table test.t1 (a int primary key, b text, c int) engine=ndb;
958
959 insert into baseline select * from frag_locks;
960
961 insert into test.t1 values (1,repeat('BJC', 300), 1);
962
963 --echo Show insert lock footprint across main and
964 --echo parts tables :
965 --echo - Blob uses extra ops on main table row
966 --echo to set length etc...
967 --source ndbinfo_show_locks_delta.inc
968 --echo ****************************************************
969
970
971 --echo Show Blob read by PK Committed read
972 select a, length(b) from test.t1 where a=1;
973 --echo Expect SH on main table (Upgrade)
974 --echo + SH on parts table (SimpleRead)
975 --source ndbinfo_show_locks_delta.inc
976 --echo ****************************************************
977
978 --echo Show Blob read by PK Shared
979 select a, length(b) from test.t1 where a=1 lock in share mode;
980 --echo Expect SH on main table
981 --echo + SH on parts table (SimpleRead)
982 --source ndbinfo_show_locks_delta.inc
983 --echo ****************************************************
984
985 --echo Show Blob read by PK Exclusive
986 select a, length(b) from test.t1 where a=1 for update;
987 --echo Expect EX on main table
988 --echo + SH on parts table (SimpleRead)
989 --source ndbinfo_show_locks_delta.inc
990 --echo ****************************************************
991
992 --echo Show Blob read by scan Committed read, no rows returned
993 select a, length(b) from test.t1 where c != 1;
994 --echo Expect SH on main table
995 --echo + Nothing on parts table
996 --source ndbinfo_show_locks_delta.inc
997 --echo ****************************************************
998
999 --echo Show Blob read by scan Committed read, 1 row returned
1000 select a, length(b) from test.t1 where c % 1234 = 1;
1001 --echo Expect SH on main table
1002 --echo + SH on parts table (SimpleRead)
1003 --source ndbinfo_show_locks_delta.inc
1004 --echo ****************************************************
1005
1006 --echo Show Blob read by scan shared, 1 row returned
1007 select a, length(b) from test.t1 where c % 1234 = 1 lock in share mode;
1008 --echo Expect 2* SH on main table
1009 --echo + SH on parts table (SimpleRead)
1010 --source ndbinfo_show_locks_delta.inc
1011 --echo ****************************************************
1012
1013 --echo Show Blob read by scan exlusive, 1 row returned
1014 select a, length(b) from test.t1 where c % 1234 = 1 for update;
1015 --echo Expect 2* EX on main table
1016 --echo + SH on parts table (SimpleRead)
1017 --source ndbinfo_show_locks_delta.inc
1018 --echo ****************************************************
1019
1020 drop view baseline_diff;
1021 drop table current;
1022 drop table baseline;
1023 drop view frag_locks;
1024
1025 drop table test.t1;
1026 --remove_file $NDB_TOOLS_OUTPUT