summaryrefslogtreecommitdiff
path: root/mysql-test/main/innodb_mysql_lock2.test
blob: f319fe23568f0beae61e49fe1dc2f0adac0320c2 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
# This test covers behavior for InnoDB tables.
--source include/have_innodb.inc
# This test requires statement/mixed mode binary logging.
# Row-based mode puts weaker serializability requirements
# so weaker locks are acquired for it.
# Also in ROW mode LOCK_S row locks won't be acquired for DML
# and test for bug#51263 won't trigger execution path on which
# this bug was encountered.
--source include/have_binlog_format_mixed_or_statement.inc
# Original test case for bug#51263 needs partitioning.
--source include/have_partition.inc
# Save the initial number of concurrent sessions.
--source include/count_sessions.inc

--disable_query_log
CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
--enable_query_log

--echo #
--echo # Test how do we handle locking in various cases when
--echo # we read data from InnoDB tables.
--echo #
--echo # In fact by performing this test we check two things:
--echo # 1) That SQL-layer correctly determine type of thr_lock.c
--echo #    lock to be acquired/passed to InnoDB engine.
--echo # 2) That InnoDB engine correctly interprets this lock
--echo #    type and takes necessary row locks or does not
--echo #    take them if they are not necessary.
--echo #

--echo # This test makes sense only in REPEATABLE-READ mode as
--echo # in SERIALIZABLE mode all statements that read data take
--echo # shared lock on them to enforce its semantics.
select @@session.tx_isolation;

--echo # Prepare playground by creating tables, views,
--echo # routines and triggers used in tests.
connect (con1, localhost, root,,);
connection default;
--disable_warnings
drop table if exists t0, t1, t2, t3, t4, t5;
drop view if exists v1, v2;
drop procedure if exists p1;
drop procedure if exists p2;
drop function if exists f1;
drop function if exists f2;
drop function if exists f3;
drop function if exists f4;
drop function if exists f5;
drop function if exists f6;
drop function if exists f7;
drop function if exists f8;
drop function if exists f9;
drop function if exists f10;
drop function if exists f11;
drop function if exists f12;
drop function if exists f13;
drop function if exists f14;
drop function if exists f15;
--enable_warnings
create table t1 (i int primary key) engine=innodb;
insert into t1 values (1), (2), (3), (4), (5);
create table t2 (j int primary key) engine=innodb;
insert into t2 values (1), (2), (3), (4), (5);
create table t3 (k int primary key) engine=innodb;
insert into t3 values (1), (2), (3);
create table t4 (l int primary key) engine=innodb;
insert into t4 values (1);
create table t5 (l int primary key) engine=innodb;
insert into t5 values (1);
create view v1 as select i from t1;
create view v2 as select j from t2 where j in (select i from t1);
create procedure p1(k int) insert into t2 values (k);
delimiter |;
create function f1() returns int
begin
  declare j int;
  select i from t1 where i = 1 into j;
  return j;
end|
create function f2() returns int
begin
  declare k int;
  select i from t1 where i = 1 into k;
  insert into t2 values (k + 5);
  return 0;
end|
create function f3() returns int
begin
  return (select i from t1 where i = 3);
end|
create function f4() returns int
begin
  if (select i from t1 where i = 3) then
    return 1;
  else
    return 0;
  end if;
end|
create function f5() returns int
begin
  insert into t2 values ((select i from t1 where i = 1) + 5);
  return 0;
end|
create function f6() returns int
begin
  declare k int;
  select i from v1 where i = 1 into k;
  return k;
end|
create function f7() returns int
begin
  declare k int;
  select j from v2 where j = 1 into k;
  return k;
end|
create function f8() returns int
begin
  declare k int;
  select i from v1 where i = 1 into k;
  insert into t2 values (k+5);
  return k;
end|
create function f9() returns int
begin
  update v2 set j=j+10 where j=1;
  return 1;
end|
create function f10() returns int
begin
  return f1();
end|
create function f11() returns int
begin
  declare k int;
  set k= f1();
  insert into t2 values (k+5);
  return k;
end|
create function f12(p int) returns int
begin
  insert into t2 values (p);
  return p;
end|
create function f13(p int) returns int
begin
  return p;
end|
create procedure p2(inout p int)
begin
  select i from t1 where i = 1 into p;
end|
create function f14() returns int
begin
  declare k int;
  call p2(k);
  insert into t2 values (k+5);
  return k;
end|
create function f15() returns int
begin
  declare k int;
  call p2(k);
  return k;
end|
create trigger t4_bi before insert on t4 for each row
begin
  declare k int;
  select i from t1 where i=1 into k;
  set new.l= k+1;
end|
create trigger t4_bu before update on t4 for each row
begin
  if (select i from t1 where i=1) then
    set new.l= 2;
  end if;
end|
create trigger t4_bd before delete on t4 for each row
begin
  if !(select i from v1 where i=1) then
    signal sqlstate '45000';
  end if;
end|
create trigger t5_bi before insert on t5 for each row
begin
  set new.l= f1()+1;
end|
create trigger t5_bu before update on t5 for each row
begin
  declare j int;
  call p2(j);
  set new.l= j + 1;
end|
delimiter ;|

--echo #
--echo # Set common variables to be used by scripts called below.
--echo #
let $con_aux= con1; 
let $table= t1;


--echo #
--echo # 1. Statements that read tables and do not use subqueries.
--echo #

--echo #
--echo # 1.1 Simple SELECT statement.
--echo #
--echo # No locks are necessary as this statement won't be written
--echo # to the binary log and InnoDB supports snapshots.
let $statement= select * from t1;
--source include/check_no_row_lock.inc

--echo #
--echo # 1.2 Multi-UPDATE statement.
--echo #
--echo # Has to take shared locks on rows in the table being read as this
--echo # statement will be written to the binary log and therefore should
--echo # be serialized with concurrent statements.
let $statement= update t2, t1 set j= j - 1 where i = j;
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 1.3 Multi-DELETE statement.
--echo #
--echo # The above is true for this statement as well.
let $statement= delete t2 from t1, t2 where i = j;
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 1.4 DESCRIBE statement.
--echo #
--echo # This statement does not really read data from the
--echo # target table and thus does not take any lock on it.
--echo # We check this for completeness of coverage.
let $statement= describe t1;
--source include/check_no_row_lock.inc

--echo #
--echo # 1.5 SHOW statements.
--echo # 
--echo # The above is true for SHOW statements as well.
let $statement= show create table t1;
--source include/check_no_row_lock.inc
let $statement= show keys from t1;
--source include/check_no_row_lock.inc


--echo #
--echo # 2. Statements which read tables through subqueries.
--echo #

--echo #
--echo # 2.1 CALL with a subquery.
--echo # 
--echo # A strong lock is not necessary as this statement is not
--echo # written to the binary log as a whole (it is written
--echo # statement-by-statement) and thanks to MVCC we can always get
--echo # versions of rows prior to the update that has locked them.
--echo # But in practice InnoDB does locking reads for all statements
--echo # other than SELECT (unless it is a READ-COMITTED mode or
--echo # innodb_locks_unsafe_for_binlog is ON).
let $statement= call p1((select i + 5 from t1 where i = 1));
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 2.2 CREATE TABLE with a subquery.
--echo #
--echo # Has to take shared locks on rows in the table being read as
--echo # this statement is written to the binary log and therefore
--echo # should be serialized with concurrent statements.
let $statement= create table t0 engine=innodb select * from t1;
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc
drop table t0;
let $statement= create table t0 engine=innodb select j from t2 where j in (select i from t1);
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc
drop table t0;

--echo #
--echo # 2.3 DELETE with a subquery.
--echo #
--echo # The above is true for this statement as well.
let $statement= delete from t2 where j in (select i from t1);
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 2.4 MULTI-DELETE with a subquery.
--echo #
--echo # Same is true for this statement as well.
let $statement= delete t2 from t3, t2 where k = j and j in (select i from t1);
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 2.5 DO with a subquery.
--echo #
--echo # In theory should not take row locks as it is not logged.
--echo # In practice InnoDB takes shared row locks.
let $statement= do (select i from t1 where i = 1);
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 2.6 INSERT with a subquery.
--echo #
--echo # Has to take shared locks on rows in the table being read as
--echo # this statement is written to the binary log and therefore
--echo # should be serialized with concurrent statements.
let $statement= insert into t2 select i+5 from t1;
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc
let $statement= insert into t2 values ((select i+5 from t1 where i = 4));
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 2.7 LOAD DATA with a subquery.
--echo # 
--echo # The above is true for this statement as well.
let $statement= load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, @b) set j= @b + (select i from t1 where i = 1);
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 2.8 REPLACE with a subquery.
--echo # 
--echo # Same is true for this statement as well.
let $statement= replace into t2 select i+5 from t1;
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc
let $statement= replace into t2 values ((select i+5 from t1 where i = 4));
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 2.9 SELECT with a subquery.
--echo #
--echo # Locks are not necessary as this statement is not written
--echo # to the binary log and thanks to MVCC we can always get
--echo # versions of rows prior to the update that has locked them.
--echo #
--echo # Also serves as a test case for bug #46947 "Embedded SELECT
--echo # without FOR UPDATE is causing a lock".
let $statement= select * from t2 where j in (select i from t1);
--source include/check_no_row_lock.inc

--echo #
--echo # 2.10 SET with a subquery.
--echo #
--echo # In theory should not require locking as it is not written
--echo # to the binary log. In practice InnoDB acquires shared row
--echo # locks.
let $statement= set @a:= (select i from t1 where i = 1);
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 2.11 SHOW with a subquery.
--echo # 
--echo # Similarly to the previous case, in theory should not require locking
--echo # as it is not written to the binary log. In practice InnoDB
--echo # acquires shared row locks.
let $statement= show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1);
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc
let $statement= show columns from t2 where (select i from t1 where i = 1);
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 2.12 UPDATE with a subquery.
--echo #
--echo # Has to take shared locks on rows in the table being read as
--echo # this statement is written to the binary log and therefore
--echo # should be serialized with concurrent statements.
let $statement= update t2 set j= j-10 where j in (select i from t1);
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 2.13 MULTI-UPDATE with a subquery.
--echo #
--echo # Same is true for this statement as well.
let $statement= update t2, t3 set j= j -10 where j=k and j in (select i from t1);
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc


--echo #
--echo # 3. Statements which read tables through a view.
--echo #

--echo #
--echo # 3.1 SELECT statement which uses some table through a view.
--echo #
--echo # Since this statement is not written to the binary log
--echo # and old version of rows are accessible thanks to MVCC,
--echo # no locking is necessary.
let $statement= select * from v1;
--source include/check_no_row_lock.inc
let $statement= select * from v2;
--source include/check_no_row_lock.inc
let $statement= select * from t2 where j in (select i from v1);
--source include/check_no_row_lock.inc
let $statement= select * from t3 where k in (select j from v2);
--source include/check_no_row_lock.inc

--echo #
--echo # 3.2 Statements which modify a table and use views.
--echo #
--echo # Since such statements are going to be written to the binary
--echo # log they need to be serialized against concurrent statements
--echo # and therefore should take shared row locks on data read.
let $statement= update t2 set j= j-10 where j in (select i from v1);
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc
let $statement= update t3 set k= k-10 where k in (select j from v2);
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc
let $statement= update t2, v1 set j= j-10 where j = i;
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc
let $statement= update v2 set j= j-10 where j = 3;
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc


--echo #
--echo # 4. Statements which read tables through stored functions.
--echo #

--echo #
--echo # 4.1 SELECT/SET with a stored function which does not 
--echo #     modify data and uses SELECT in its turn.
--echo #
--echo # There is no need to take row locks on the table
--echo # being selected from in SF as the call to such function
--echo # won't get into the binary log.
--echo #
--echo # However in practice innodb takes strong lock on tables
--echo # being selected from within SF, when SF is called from
--echo # non SELECT statements like 'set' statement below.
let $statement= select f1();
let $wait_statement= select i from t1 where i = 1 into j;
--source include/check_no_row_lock.inc
let $statement= set @a:= f1();
let $wait_statement= select i from t1 where i = 1 into j;
--source include/check_shared_row_lock.inc

--echo #
--echo # 4.2 INSERT (or other statement which modifies data) with
--echo #     a stored function which does not modify data and uses
--echo #     SELECT.
--echo #
--echo # Since such statement is written to the binary log it should
--echo # be serialized with concurrent statements affecting the data
--echo # it uses. Therefore it should take row locks on the data
--echo # it reads.
let $statement= insert into t2 values (f1() + 5);
let $wait_statement= select i from t1 where i = 1 into j;
--source include/check_shared_row_lock.inc

--echo #
--echo # 4.3 SELECT/SET with a stored function which
--echo #     reads and modifies data.
--echo #
--echo # Since a call to such function is written to the binary log,
--echo # it should be serialized with concurrent statements affecting
--echo # the data it uses. Hence, row locks on the data read
--echo # should be taken.
let $statement= select f2();
let $wait_statement= select i from t1 where i = 1 into k;
--source include/check_shared_row_lock.inc
let $statement= set @a:= f2();
let $wait_statement= select i from t1 where i = 1 into k;
--source include/check_shared_row_lock.inc

--echo #
--echo # 4.4. SELECT/SET with a stored function which does not
--echo #      modify data and reads a table through subselect
--echo #      in a control construct.
--echo #
--echo # Call to this function won't get to the
--echo # binary log and thus no locking is needed.
--echo #
--echo # However in practice innodb takes strong lock on tables
--echo # being selected from within SF, when SF is called from
--echo # non SELECT statements like 'set' statement below.
let $statement= select f3();
let $wait_statement= $statement;
--source include/check_no_row_lock.inc
let $statement= set @a:= f3();
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc
let $statement= select f4();
let $wait_statement= $statement;
--source include/check_no_row_lock.inc
let $statement= set @a:= f4();
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 4.5. INSERT (or other statement which modifies data) with
--echo #      a stored function which does not modify data and reads
--echo #      the table through a subselect in one of its control
--echo #      constructs.
--echo #
--echo # Since such statement is written to the binary log it should
--echo # be serialized with concurrent statements affecting data it
--echo # uses. Therefore it should take row locks on the data
--echo # it reads.
let $statement= insert into t2 values (f3() + 5);
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc
let $statement= insert into t2 values (f4() + 6);
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 4.6 SELECT/SET which uses a stored function with
--echo #      DML which reads a table via a subquery.
--echo #
--echo # Since call to such function is written to the binary log
--echo # it should be serialized with concurrent statements.
--echo # Hence reads should take row locks.
let $statement= select f5();
let $wait_statement= insert into t2 values ((select i from t1 where i = 1) + 5);
--source include/check_shared_row_lock.inc
let $statement= set @a:= f5();
let $wait_statement= insert into t2 values ((select i from t1 where i = 1) + 5);
--source include/check_shared_row_lock.inc

--echo #
--echo # 4.7 SELECT/SET which uses a stored function which
--echo #     doesn't modify data and reads tables through
--echo #     a view.
--echo #
--echo # Calls to such functions won't get into
--echo # the binary log and thus don't need row locks.
--echo #
--echo # However in practice innodb takes strong lock on tables
--echo # being selected from within SF, when SF is called from
--echo # non SELECT statements like 'set' statement below.
let $statement= select f6();
let $wait_statement= select i from v1 where i = 1 into k;
--source include/check_no_row_lock.inc
let $statement= set @a:= f6();
let $wait_statement= select i from v1 where i = 1 into k;
--source include/check_shared_row_lock.inc
let $statement= select f7();
let $wait_statement= select j from v2 where j = 1 into k;
--source include/check_no_row_lock.inc
let $statement= set @a:= f7();
let $wait_statement= select j from v2 where j = 1 into k;
--source include/check_shared_row_lock.inc

--echo #
--echo # 4.8 INSERT which uses stored function which
--echo #     doesn't modify data and reads a table
--echo #     through a view.
--echo #
--echo # Since such statement is written to the binary log and
--echo # should be serialized with concurrent statements affecting
--echo # the data it uses. Therefore it should take row locks on
--echo # the rows it reads.
let $statement= insert into t3 values (f6() + 5);
let $wait_statement= select i from v1 where i = 1 into k;
--source include/check_shared_row_lock.inc
let $statement= insert into t3 values (f7() + 5);
let $wait_statement= select j from v2 where j = 1 into k;
--source include/check_shared_row_lock.inc


--echo #
--echo # 4.9 SELECT which uses a stored function which
--echo #     modifies data and reads tables through a view.
--echo #
--echo # Since a call to such function is written to the binary log
--echo # it should be serialized with concurrent statements.
--echo # Hence, reads should take row locks.
let $statement= select f8();
let $wait_statement= select i from v1 where i = 1 into k;
--source include/check_shared_row_lock.inc
let $statement= select f9();
let $wait_statement= update v2 set j=j+10 where j=1;
--source include/check_shared_row_lock.inc

--echo #
--echo # 4.10 SELECT which uses stored function which doesn't modify
--echo #      data and reads a table indirectly, by calling another
--echo #      function.
--echo #
--echo # Calls to such functions won't get into the binary
--echo # log and thus don't need to acquire row locks.
let $statement= select f10();
let $wait_statement= select i from t1 where i = 1 into j;
--source include/check_no_row_lock.inc

--echo #
--echo # 4.11 INSERT which uses a stored function which doesn't modify
--echo #      data and reads a table indirectly, by calling another
--echo #      function. 
--echo #
--echo # Since such statement is written to the binary log, it should
--echo # be serialized with concurrent statements affecting the data it
--echo # uses. Therefore it should take row locks on data it reads.
let $statement= insert into t2 values (f10() + 5);
let $wait_statement= select i from t1 where i = 1 into j;
--source include/check_shared_row_lock.inc

--echo #
--echo # 4.12 SELECT which uses a stored function which modifies
--echo #      data and reads a table indirectly, by calling another
--echo #      function. 
--echo #
--echo # Since a call to such function is written to the binary log
--echo # it should be serialized from concurrent statements.
--echo # Hence, reads should take row locks.
let $statement= select f11();
let $wait_statement= select i from t1 where i = 1 into j;
--source include/check_shared_row_lock.inc

--echo #
--echo # 4.13 SELECT that reads a table through a subquery passed
--echo #      as a parameter to a stored function which modifies
--echo #      data.
--echo #
--echo # Even though a call to this function is written to the
--echo # binary log, values of its parameters are written as literals.
--echo # So there is no need to acquire row locks on rows used in 
--echo # the subquery.
let $statement= select f12((select i+10 from t1 where i=1));
--source include/check_no_row_lock.inc

--echo #
--echo # 4.14 INSERT that reads a table via a subquery passed
--echo #      as a parameter to a stored function which doesn't
--echo #      modify data.
--echo #
--echo # Since this statement is written to the binary log it should
--echo # be serialized with concurrent statements affecting the data it
--echo # uses. Therefore it should take row locks on the data it reads.
let $statement= insert into t2 values (f13((select i+10 from t1 where i=1)));
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc


--echo #
--echo # 5. Statements that read tables through stored procedures.
--echo #

--echo #
--echo # 5.1 CALL statement which reads a table via SELECT.
--echo #
--echo # Since neither this statement nor its components are
--echo # written to the binary log, there is no need to take
--echo # row locks on the data it reads.
let $statement= call p2(@a);
--source include/check_no_row_lock.inc

--echo #
--echo # 5.2 Function that modifies data and uses CALL, 
--echo #     which reads a table through SELECT.
--echo #
--echo # Since a call to such function is written to the binary
--echo # log, it should be serialized with concurrent statements.
--echo # Hence, in this case reads should take row locks on data.
let $statement= select f14();
let $wait_statement= select i from t1 where i = 1 into p;
--source include/check_shared_row_lock.inc

--echo #
--echo # 5.3 SELECT that calls a function that doesn't modify data and
--echo #     uses a CALL statement that reads a table via SELECT.
--echo #
--echo # Calls to such functions won't get into the binary
--echo # log and thus don't need to acquire row locks.
let $statement= select f15();
let $wait_statement= select i from t1 where i = 1 into p;
--source include/check_no_row_lock.inc

--echo #
--echo # 5.4 INSERT which calls function which doesn't modify data and
--echo #     uses CALL statement which reads table through SELECT.
--echo #
--echo # Since such statement is written to the binary log it should
--echo # be serialized with concurrent statements affecting data it
--echo # uses. Therefore it should take row locks on data it reads.
let $statement= insert into t2 values (f15()+5);
let $wait_statement= select i from t1 where i = 1 into p;
--source include/check_shared_row_lock.inc


--echo #
--echo # 6. Statements that use triggers.
--echo #

--echo #
--echo # 6.1 Statement invoking a trigger that reads table via SELECT.
--echo #
--echo # Since this statement is written to the binary log it should
--echo # be serialized with concurrent statements affecting the data
--echo # it uses. Therefore, it should take row locks on the data
--echo # it reads.
let $statement= insert into t4 values (2);
let $wait_statement= select i from t1 where i=1 into k;
--source include/check_shared_row_lock.inc

--echo #
--echo # 6.2 Statement invoking a trigger that reads table through
--echo #     a subquery in a control construct.
--echo #
--echo # The above is true for this statement as well.
let $statement= update t4 set l= 2 where l = 1;
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 6.3 Statement invoking a trigger that reads a table through
--echo #     a view.
--echo #
--echo # And for this statement.
let $statement= delete from t4 where l = 1;
let $wait_statement= $statement;
--source include/check_shared_row_lock.inc

--echo #
--echo # 6.4 Statement invoking a trigger that reads a table through
--echo #     a stored function.
--echo #
--echo # And for this statement.
let $statement= insert into t5 values (2);
let $wait_statement= select i from t1 where i = 1 into j;
--source include/check_shared_row_lock.inc

--echo #
--echo # 6.5 Statement invoking a trigger that reads a table through
--echo #     stored procedure.
--echo #
--echo # And for this statement.
let $statement= update t5 set l= 2 where l = 1;
let $wait_statement= select i from t1 where i = 1 into p;
--source include/check_shared_row_lock.inc

--echo # Clean-up.
drop function f1;
drop function f2;
drop function f3;
drop function f4;
drop function f5;
drop function f6;
drop function f7;
drop function f8;
drop function f9;
drop function f10;
drop function f11;
drop function f12;
drop function f13;
drop function f14;
drop function f15;
drop view v1, v2;
drop procedure p1;
drop procedure p2;
drop table t1, t2, t3, t4, t5;
disconnect con1;


--echo #
--echo # Test for bug#51263 "Deadlock between transactional SELECT
--echo # and ALTER TABLE ... REBUILD PARTITION".
--echo #
connect (con1,localhost,root,,test,,);
connection default;
--disable_warnings
drop table if exists t1, t2;
--enable_warnings
create table t1 (i int auto_increment not null primary key) engine=innodb;
create table t2 (i int) engine=innodb;
insert into t1 values (1), (2), (3), (4), (5);

begin;
--echo # Acquire SR metadata lock on t1 and LOCK_S row-locks on its rows.
insert into t2 select count(*) from t1;

connection con1;
--echo # Sending:
--send alter table t1 add column j int

connection default;
--echo # Wait until ALTER is blocked because it tries to upgrade SNW
--echo # metadata lock to X lock.
--echo # It should not be blocked during copying data to new version of
--echo # table as it acquires LOCK_S locks on rows of old version, which
--echo # are compatible with locks acquired by connection 'con1'.
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table metadata lock" and
        info = "alter table t1 add column j int";
--source include/wait_condition.inc

--echo # The below statement will deadlock because it will try to acquire
--echo # SW lock on t1, which will conflict with ALTER's SNW lock. And
--echo # ALTER will be waiting for this connection to release its SR lock.
--echo # This deadlock should be detected by an MDL subsystem and this
--echo # statement should be aborted with an appropriate error.
--error ER_LOCK_DEADLOCK
insert into t1 values (6);
--echo # Unblock ALTER TABLE.
commit;

connection con1;
--echo # Reaping ALTER TABLE.
--reap

connection default;

--echo #
--echo # Now test for scenario in which bug was reported originally.
--echo #
drop tables t1, t2;
create table t1 (i int auto_increment not null primary key) engine=innodb
  partition by hash (i) partitions 4;
create table t2 (i int) engine=innodb;
insert into t1 values (1), (2), (3), (4), (5);

begin;
--echo # Acquire SR metadata lock on t1.
select * from t1;

connection con1;
--echo # Sending:
--send alter table t1 rebuild partition p0

connection default;
--echo # Wait until ALTER is blocked because of active SR lock.
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table metadata lock" and
        info = "alter table t1 rebuild partition p0";
--source include/wait_condition.inc

--echo # The below statement should succeed as transaction
--echo # has SR metadata lock on t1 and only going to read
--echo # rows from it.
insert into t2 select count(*) from t1;
--echo # Unblock ALTER TABLE.
commit;

connection con1;
--echo # Reaping ALTER TABLE.
--reap

connection default;
disconnect con1;
--echo # Clean-up.
drop tables t1, t2;


# Check that all connections opened by test cases in this file are really
# gone so execution of other tests won't be affected by their presence.
--source include/wait_until_count_sessions.inc