1# This test covers behavior for InnoDB tables. 2--source include/have_innodb.inc 3# This test requires statement/mixed mode binary logging. 4# Row-based mode puts weaker serializability requirements 5# so weaker locks are acquired for it. 6# Also in ROW mode LOCK_S row locks won't be acquired for DML 7# and test for bug#51263 won't trigger execution path on which 8# this bug was encountered. 9--source include/have_binlog_format_mixed_or_statement.inc 10# Original test case for bug#51263 needs partitioning. 11--source include/have_partition.inc 12# Save the initial number of concurrent sessions. 13--source include/count_sessions.inc 14 15--disable_query_log 16CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); 17--enable_query_log 18 19--echo # 20--echo # Test how do we handle locking in various cases when 21--echo # we read data from InnoDB tables. 22--echo # 23--echo # In fact by performing this test we check two things: 24--echo # 1) That SQL-layer correctly determine type of thr_lock.c 25--echo # lock to be acquired/passed to InnoDB engine. 26--echo # 2) That InnoDB engine correctly interprets this lock 27--echo # type and takes necessary row locks or does not 28--echo # take them if they are not necessary. 29--echo # 30 31--echo # This test makes sense only in REPEATABLE-READ mode as 32--echo # in SERIALIZABLE mode all statements that read data take 33--echo # shared lock on them to enforce its semantics. 34select @@session.tx_isolation; 35 36--echo # Prepare playground by creating tables, views, 37--echo # routines and triggers used in tests. 38connect (con1, localhost, root,,); 39connection default; 40--disable_warnings 41drop table if exists t0, t1, t2, t3, t4, t5; 42drop view if exists v1, v2; 43drop procedure if exists p1; 44drop procedure if exists p2; 45drop function if exists f1; 46drop function if exists f2; 47drop function if exists f3; 48drop function if exists f4; 49drop function if exists f5; 50drop function if exists f6; 51drop function if exists f7; 52drop function if exists f8; 53drop function if exists f9; 54drop function if exists f10; 55drop function if exists f11; 56drop function if exists f12; 57drop function if exists f13; 58drop function if exists f14; 59drop function if exists f15; 60--enable_warnings 61create table t1 (i int primary key) engine=innodb; 62insert into t1 values (1), (2), (3), (4), (5); 63create table t2 (j int primary key) engine=innodb; 64insert into t2 values (1), (2), (3), (4), (5); 65create table t3 (k int primary key) engine=innodb; 66insert into t3 values (1), (2), (3); 67create table t4 (l int primary key) engine=innodb; 68insert into t4 values (1); 69create table t5 (l int primary key) engine=innodb; 70insert into t5 values (1); 71create view v1 as select i from t1; 72create view v2 as select j from t2 where j in (select i from t1); 73create procedure p1(k int) insert into t2 values (k); 74delimiter |; 75create function f1() returns int 76begin 77 declare j int; 78 select i from t1 where i = 1 into j; 79 return j; 80end| 81create function f2() returns int 82begin 83 declare k int; 84 select i from t1 where i = 1 into k; 85 insert into t2 values (k + 5); 86 return 0; 87end| 88create function f3() returns int 89begin 90 return (select i from t1 where i = 3); 91end| 92create function f4() returns int 93begin 94 if (select i from t1 where i = 3) then 95 return 1; 96 else 97 return 0; 98 end if; 99end| 100create function f5() returns int 101begin 102 insert into t2 values ((select i from t1 where i = 1) + 5); 103 return 0; 104end| 105create function f6() returns int 106begin 107 declare k int; 108 select i from v1 where i = 1 into k; 109 return k; 110end| 111create function f7() returns int 112begin 113 declare k int; 114 select j from v2 where j = 1 into k; 115 return k; 116end| 117create function f8() returns int 118begin 119 declare k int; 120 select i from v1 where i = 1 into k; 121 insert into t2 values (k+5); 122 return k; 123end| 124create function f9() returns int 125begin 126 update v2 set j=j+10 where j=1; 127 return 1; 128end| 129create function f10() returns int 130begin 131 return f1(); 132end| 133create function f11() returns int 134begin 135 declare k int; 136 set k= f1(); 137 insert into t2 values (k+5); 138 return k; 139end| 140create function f12(p int) returns int 141begin 142 insert into t2 values (p); 143 return p; 144end| 145create function f13(p int) returns int 146begin 147 return p; 148end| 149create procedure p2(inout p int) 150begin 151 select i from t1 where i = 1 into p; 152end| 153create function f14() returns int 154begin 155 declare k int; 156 call p2(k); 157 insert into t2 values (k+5); 158 return k; 159end| 160create function f15() returns int 161begin 162 declare k int; 163 call p2(k); 164 return k; 165end| 166create trigger t4_bi before insert on t4 for each row 167begin 168 declare k int; 169 select i from t1 where i=1 into k; 170 set new.l= k+1; 171end| 172create trigger t4_bu before update on t4 for each row 173begin 174 if (select i from t1 where i=1) then 175 set new.l= 2; 176 end if; 177end| 178create trigger t4_bd before delete on t4 for each row 179begin 180 if !(select i from v1 where i=1) then 181 signal sqlstate '45000'; 182 end if; 183end| 184create trigger t5_bi before insert on t5 for each row 185begin 186 set new.l= f1()+1; 187end| 188create trigger t5_bu before update on t5 for each row 189begin 190 declare j int; 191 call p2(j); 192 set new.l= j + 1; 193end| 194delimiter ;| 195 196--echo # 197--echo # Set common variables to be used by scripts called below. 198--echo # 199let $con_aux= con1; 200let $table= t1; 201 202 203--echo # 204--echo # 1. Statements that read tables and do not use subqueries. 205--echo # 206 207--echo # 208--echo # 1.1 Simple SELECT statement. 209--echo # 210--echo # No locks are necessary as this statement won't be written 211--echo # to the binary log and InnoDB supports snapshots. 212let $statement= select * from t1; 213--source include/check_no_row_lock.inc 214 215--echo # 216--echo # 1.2 Multi-UPDATE statement. 217--echo # 218--echo # Has to take shared locks on rows in the table being read as this 219--echo # statement will be written to the binary log and therefore should 220--echo # be serialized with concurrent statements. 221let $statement= update t2, t1 set j= j - 1 where i = j; 222let $wait_statement= $statement; 223--source include/check_shared_row_lock.inc 224 225--echo # 226--echo # 1.3 Multi-DELETE statement. 227--echo # 228--echo # The above is true for this statement as well. 229let $statement= delete t2 from t1, t2 where i = j; 230let $wait_statement= $statement; 231--source include/check_shared_row_lock.inc 232 233--echo # 234--echo # 1.4 DESCRIBE statement. 235--echo # 236--echo # This statement does not really read data from the 237--echo # target table and thus does not take any lock on it. 238--echo # We check this for completeness of coverage. 239let $statement= describe t1; 240--source include/check_no_row_lock.inc 241 242--echo # 243--echo # 1.5 SHOW statements. 244--echo # 245--echo # The above is true for SHOW statements as well. 246let $statement= show create table t1; 247--source include/check_no_row_lock.inc 248let $statement= show keys from t1; 249--source include/check_no_row_lock.inc 250 251 252--echo # 253--echo # 2. Statements which read tables through subqueries. 254--echo # 255 256--echo # 257--echo # 2.1 CALL with a subquery. 258--echo # 259--echo # A strong lock is not necessary as this statement is not 260--echo # written to the binary log as a whole (it is written 261--echo # statement-by-statement) and thanks to MVCC we can always get 262--echo # versions of rows prior to the update that has locked them. 263--echo # But in practice InnoDB does locking reads for all statements 264--echo # other than SELECT (unless READ UNCOMMITTED or READ COMMITTED). 265let $statement= call p1((select i + 5 from t1 where i = 1)); 266let $wait_statement= $statement; 267--source include/check_shared_row_lock.inc 268 269--echo # 270--echo # 2.2 CREATE TABLE with a subquery. 271--echo # 272--echo # Has to take shared locks on rows in the table being read as 273--echo # this statement is written to the binary log and therefore 274--echo # should be serialized with concurrent statements. 275let $statement= create table t0 engine=innodb select * from t1; 276let $wait_statement= $statement; 277--source include/check_shared_row_lock.inc 278drop table t0; 279let $statement= create table t0 engine=innodb select j from t2 where j in (select i from t1); 280let $wait_statement= $statement; 281--source include/check_shared_row_lock.inc 282drop table t0; 283 284--echo # 285--echo # 2.3 DELETE with a subquery. 286--echo # 287--echo # The above is true for this statement as well. 288let $statement= delete from t2 where j in (select i from t1); 289let $wait_statement= $statement; 290--source include/check_shared_row_lock.inc 291 292--echo # 293--echo # 2.4 MULTI-DELETE with a subquery. 294--echo # 295--echo # Same is true for this statement as well. 296let $statement= delete t2 from t3, t2 where k = j and j in (select i from t1); 297let $wait_statement= $statement; 298--source include/check_shared_row_lock.inc 299 300--echo # 301--echo # 2.5 DO with a subquery. 302--echo # 303--echo # In theory should not take row locks as it is not logged. 304--echo # In practice InnoDB takes shared row locks. 305let $statement= do (select i from t1 where i = 1); 306let $wait_statement= $statement; 307--source include/check_shared_row_lock.inc 308 309--echo # 310--echo # 2.6 INSERT with a subquery. 311--echo # 312--echo # Has to take shared locks on rows in the table being read as 313--echo # this statement is written to the binary log and therefore 314--echo # should be serialized with concurrent statements. 315let $statement= insert into t2 select i+5 from t1; 316let $wait_statement= $statement; 317--source include/check_shared_row_lock.inc 318let $statement= insert into t2 values ((select i+5 from t1 where i = 4)); 319let $wait_statement= $statement; 320--source include/check_shared_row_lock.inc 321 322--echo # 323--echo # 2.7 LOAD DATA with a subquery. 324--echo # 325--echo # The above is true for this statement as well. 326let $statement= load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, @b) set j= @b + (select i from t1 where i = 1); 327let $wait_statement= $statement; 328--source include/check_shared_row_lock.inc 329 330--echo # 331--echo # 2.8 REPLACE with a subquery. 332--echo # 333--echo # Same is true for this statement as well. 334let $statement= replace into t2 select i+5 from t1; 335let $wait_statement= $statement; 336--source include/check_shared_row_lock.inc 337let $statement= replace into t2 values ((select i+5 from t1 where i = 4)); 338let $wait_statement= $statement; 339--source include/check_shared_row_lock.inc 340 341--echo # 342--echo # 2.9 SELECT with a subquery. 343--echo # 344--echo # Locks are not necessary as this statement is not written 345--echo # to the binary log and thanks to MVCC we can always get 346--echo # versions of rows prior to the update that has locked them. 347--echo # 348--echo # Also serves as a test case for bug #46947 "Embedded SELECT 349--echo # without FOR UPDATE is causing a lock". 350let $statement= select * from t2 where j in (select i from t1); 351--source include/check_no_row_lock.inc 352 353--echo # 354--echo # 2.10 SET with a subquery. 355--echo # 356--echo # In theory should not require locking as it is not written 357--echo # to the binary log. In practice InnoDB acquires shared row 358--echo # locks. 359let $statement= set @a:= (select i from t1 where i = 1); 360let $wait_statement= $statement; 361--source include/check_shared_row_lock.inc 362 363--echo # 364--echo # 2.11 SHOW with a subquery. 365--echo # 366--echo # Similarly to the previous case, in theory should not require locking 367--echo # as it is not written to the binary log. In practice InnoDB 368--echo # acquires shared row locks. 369let $statement= show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1); 370let $wait_statement= $statement; 371--source include/check_shared_row_lock.inc 372let $statement= show columns from t2 where (select i from t1 where i = 1); 373let $wait_statement= $statement; 374--source include/check_shared_row_lock.inc 375 376--echo # 377--echo # 2.12 UPDATE with a subquery. 378--echo # 379--echo # Has to take shared locks on rows in the table being read as 380--echo # this statement is written to the binary log and therefore 381--echo # should be serialized with concurrent statements. 382let $statement= update t2 set j= j-10 where j in (select i from t1); 383let $wait_statement= $statement; 384--source include/check_shared_row_lock.inc 385 386--echo # 387--echo # 2.13 MULTI-UPDATE with a subquery. 388--echo # 389--echo # Same is true for this statement as well. 390let $statement= update t2, t3 set j= j -10 where j=k and j in (select i from t1); 391let $wait_statement= $statement; 392--source include/check_shared_row_lock.inc 393 394 395--echo # 396--echo # 3. Statements which read tables through a view. 397--echo # 398 399--echo # 400--echo # 3.1 SELECT statement which uses some table through a view. 401--echo # 402--echo # Since this statement is not written to the binary log 403--echo # and old version of rows are accessible thanks to MVCC, 404--echo # no locking is necessary. 405let $statement= select * from v1; 406--source include/check_no_row_lock.inc 407let $statement= select * from v2; 408--source include/check_no_row_lock.inc 409let $statement= select * from t2 where j in (select i from v1); 410--source include/check_no_row_lock.inc 411let $statement= select * from t3 where k in (select j from v2); 412--source include/check_no_row_lock.inc 413 414--echo # 415--echo # 3.2 Statements which modify a table and use views. 416--echo # 417--echo # Since such statements are going to be written to the binary 418--echo # log they need to be serialized against concurrent statements 419--echo # and therefore should take shared row locks on data read. 420let $statement= update t2 set j= j-10 where j in (select i from v1); 421let $wait_statement= $statement; 422--source include/check_shared_row_lock.inc 423let $statement= update t3 set k= k-10 where k in (select j from v2); 424let $wait_statement= $statement; 425--source include/check_shared_row_lock.inc 426let $statement= update t2, v1 set j= j-10 where j = i; 427let $wait_statement= $statement; 428--source include/check_shared_row_lock.inc 429let $statement= update v2 set j= j-10 where j = 3; 430let $wait_statement= $statement; 431--source include/check_shared_row_lock.inc 432 433 434--echo # 435--echo # 4. Statements which read tables through stored functions. 436--echo # 437 438--echo # 439--echo # 4.1 SELECT/SET with a stored function which does not 440--echo # modify data and uses SELECT in its turn. 441--echo # 442--echo # There is no need to take row locks on the table 443--echo # being selected from in SF as the call to such function 444--echo # won't get into the binary log. 445--echo # 446--echo # However in practice innodb takes strong lock on tables 447--echo # being selected from within SF, when SF is called from 448--echo # non SELECT statements like 'set' statement below. 449let $statement= select f1(); 450let $wait_statement= select i from t1 where i = 1 into j; 451--source include/check_no_row_lock.inc 452let $statement= set @a:= f1(); 453let $wait_statement= select i from t1 where i = 1 into j; 454--source include/check_shared_row_lock.inc 455 456--echo # 457--echo # 4.2 INSERT (or other statement which modifies data) with 458--echo # a stored function which does not modify data and uses 459--echo # SELECT. 460--echo # 461--echo # Since such statement is written to the binary log it should 462--echo # be serialized with concurrent statements affecting the data 463--echo # it uses. Therefore it should take row locks on the data 464--echo # it reads. 465let $statement= insert into t2 values (f1() + 5); 466let $wait_statement= select i from t1 where i = 1 into j; 467--source include/check_shared_row_lock.inc 468 469--echo # 470--echo # 4.3 SELECT/SET with a stored function which 471--echo # reads and modifies data. 472--echo # 473--echo # Since a call to such function is written to the binary log, 474--echo # it should be serialized with concurrent statements affecting 475--echo # the data it uses. Hence, row locks on the data read 476--echo # should be taken. 477let $statement= select f2(); 478let $wait_statement= select i from t1 where i = 1 into k; 479--source include/check_shared_row_lock.inc 480let $statement= set @a:= f2(); 481let $wait_statement= select i from t1 where i = 1 into k; 482--source include/check_shared_row_lock.inc 483 484--echo # 485--echo # 4.4. SELECT/SET with a stored function which does not 486--echo # modify data and reads a table through subselect 487--echo # in a control construct. 488--echo # 489--echo # Call to this function won't get to the 490--echo # binary log and thus no locking is needed. 491--echo # 492--echo # However in practice innodb takes strong lock on tables 493--echo # being selected from within SF, when SF is called from 494--echo # non SELECT statements like 'set' statement below. 495let $statement= select f3(); 496let $wait_statement= $statement; 497--source include/check_no_row_lock.inc 498let $statement= set @a:= f3(); 499let $wait_statement= $statement; 500--source include/check_shared_row_lock.inc 501let $statement= select f4(); 502let $wait_statement= $statement; 503--source include/check_no_row_lock.inc 504let $statement= set @a:= f4(); 505let $wait_statement= $statement; 506--source include/check_shared_row_lock.inc 507 508--echo # 509--echo # 4.5. INSERT (or other statement which modifies data) with 510--echo # a stored function which does not modify data and reads 511--echo # the table through a subselect in one of its control 512--echo # constructs. 513--echo # 514--echo # Since such statement is written to the binary log it should 515--echo # be serialized with concurrent statements affecting data it 516--echo # uses. Therefore it should take row locks on the data 517--echo # it reads. 518let $statement= insert into t2 values (f3() + 5); 519let $wait_statement= $statement; 520--source include/check_shared_row_lock.inc 521let $statement= insert into t2 values (f4() + 6); 522let $wait_statement= $statement; 523--source include/check_shared_row_lock.inc 524 525--echo # 526--echo # 4.6 SELECT/SET which uses a stored function with 527--echo # DML which reads a table via a subquery. 528--echo # 529--echo # Since call to such function is written to the binary log 530--echo # it should be serialized with concurrent statements. 531--echo # Hence reads should take row locks. 532let $statement= select f5(); 533let $wait_statement= insert into t2 values ((select i from t1 where i = 1) + 5); 534--source include/check_shared_row_lock.inc 535let $statement= set @a:= f5(); 536let $wait_statement= insert into t2 values ((select i from t1 where i = 1) + 5); 537--source include/check_shared_row_lock.inc 538 539--echo # 540--echo # 4.7 SELECT/SET which uses a stored function which 541--echo # doesn't modify data and reads tables through 542--echo # a view. 543--echo # 544--echo # Calls to such functions won't get into 545--echo # the binary log and thus don't need row locks. 546--echo # 547--echo # However in practice innodb takes strong lock on tables 548--echo # being selected from within SF, when SF is called from 549--echo # non SELECT statements like 'set' statement below. 550let $statement= select f6(); 551let $wait_statement= select i from v1 where i = 1 into k; 552--source include/check_no_row_lock.inc 553let $statement= set @a:= f6(); 554let $wait_statement= select i from v1 where i = 1 into k; 555--source include/check_shared_row_lock.inc 556let $statement= select f7(); 557let $wait_statement= select j from v2 where j = 1 into k; 558--source include/check_no_row_lock.inc 559let $statement= set @a:= f7(); 560let $wait_statement= select j from v2 where j = 1 into k; 561--source include/check_shared_row_lock.inc 562 563--echo # 564--echo # 4.8 INSERT which uses stored function which 565--echo # doesn't modify data and reads a table 566--echo # through a view. 567--echo # 568--echo # Since such statement is written to the binary log and 569--echo # should be serialized with concurrent statements affecting 570--echo # the data it uses. Therefore it should take row locks on 571--echo # the rows it reads. 572let $statement= insert into t3 values (f6() + 5); 573let $wait_statement= select i from v1 where i = 1 into k; 574--source include/check_shared_row_lock.inc 575let $statement= insert into t3 values (f7() + 5); 576let $wait_statement= select j from v2 where j = 1 into k; 577--source include/check_shared_row_lock.inc 578 579 580--echo # 581--echo # 4.9 SELECT which uses a stored function which 582--echo # modifies data and reads tables through a view. 583--echo # 584--echo # Since a call to such function is written to the binary log 585--echo # it should be serialized with concurrent statements. 586--echo # Hence, reads should take row locks. 587let $statement= select f8(); 588let $wait_statement= select i from v1 where i = 1 into k; 589--source include/check_shared_row_lock.inc 590let $statement= select f9(); 591let $wait_statement= update v2 set j=j+10 where j=1; 592--source include/check_shared_row_lock.inc 593 594--echo # 595--echo # 4.10 SELECT which uses stored function which doesn't modify 596--echo # data and reads a table indirectly, by calling another 597--echo # function. 598--echo # 599--echo # Calls to such functions won't get into the binary 600--echo # log and thus don't need to acquire row locks. 601let $statement= select f10(); 602let $wait_statement= select i from t1 where i = 1 into j; 603--source include/check_no_row_lock.inc 604 605--echo # 606--echo # 4.11 INSERT which uses a stored function which doesn't modify 607--echo # data and reads a table indirectly, by calling another 608--echo # function. 609--echo # 610--echo # Since such statement is written to the binary log, it should 611--echo # be serialized with concurrent statements affecting the data it 612--echo # uses. Therefore it should take row locks on data it reads. 613let $statement= insert into t2 values (f10() + 5); 614let $wait_statement= select i from t1 where i = 1 into j; 615--source include/check_shared_row_lock.inc 616 617--echo # 618--echo # 4.12 SELECT which uses a stored function which modifies 619--echo # data and reads a table indirectly, by calling another 620--echo # function. 621--echo # 622--echo # Since a call to such function is written to the binary log 623--echo # it should be serialized from concurrent statements. 624--echo # Hence, reads should take row locks. 625let $statement= select f11(); 626let $wait_statement= select i from t1 where i = 1 into j; 627--source include/check_shared_row_lock.inc 628 629--echo # 630--echo # 4.13 SELECT that reads a table through a subquery passed 631--echo # as a parameter to a stored function which modifies 632--echo # data. 633--echo # 634--echo # Even though a call to this function is written to the 635--echo # binary log, values of its parameters are written as literals. 636--echo # So there is no need to acquire row locks on rows used in 637--echo # the subquery. 638let $statement= select f12((select i+10 from t1 where i=1)); 639--source include/check_no_row_lock.inc 640 641--echo # 642--echo # 4.14 INSERT that reads a table via a subquery passed 643--echo # as a parameter to a stored function which doesn't 644--echo # modify data. 645--echo # 646--echo # Since this statement is written to the binary log it should 647--echo # be serialized with concurrent statements affecting the data it 648--echo # uses. Therefore it should take row locks on the data it reads. 649let $statement= insert into t2 values (f13((select i+10 from t1 where i=1))); 650let $wait_statement= $statement; 651--source include/check_shared_row_lock.inc 652 653 654--echo # 655--echo # 5. Statements that read tables through stored procedures. 656--echo # 657 658--echo # 659--echo # 5.1 CALL statement which reads a table via SELECT. 660--echo # 661--echo # Since neither this statement nor its components are 662--echo # written to the binary log, there is no need to take 663--echo # row locks on the data it reads. 664let $statement= call p2(@a); 665--source include/check_no_row_lock.inc 666 667--echo # 668--echo # 5.2 Function that modifies data and uses CALL, 669--echo # which reads a table through SELECT. 670--echo # 671--echo # Since a call to such function is written to the binary 672--echo # log, it should be serialized with concurrent statements. 673--echo # Hence, in this case reads should take row locks on data. 674let $statement= select f14(); 675let $wait_statement= select i from t1 where i = 1 into p; 676--source include/check_shared_row_lock.inc 677 678--echo # 679--echo # 5.3 SELECT that calls a function that doesn't modify data and 680--echo # uses a CALL statement that reads a table via SELECT. 681--echo # 682--echo # Calls to such functions won't get into the binary 683--echo # log and thus don't need to acquire row locks. 684let $statement= select f15(); 685let $wait_statement= select i from t1 where i = 1 into p; 686--source include/check_no_row_lock.inc 687 688--echo # 689--echo # 5.4 INSERT which calls function which doesn't modify data and 690--echo # uses CALL statement which reads table through SELECT. 691--echo # 692--echo # Since such statement is written to the binary log it should 693--echo # be serialized with concurrent statements affecting data it 694--echo # uses. Therefore it should take row locks on data it reads. 695let $statement= insert into t2 values (f15()+5); 696let $wait_statement= select i from t1 where i = 1 into p; 697--source include/check_shared_row_lock.inc 698 699 700--echo # 701--echo # 6. Statements that use triggers. 702--echo # 703 704--echo # 705--echo # 6.1 Statement invoking a trigger that reads table via SELECT. 706--echo # 707--echo # Since this statement is written to the binary log it should 708--echo # be serialized with concurrent statements affecting the data 709--echo # it uses. Therefore, it should take row locks on the data 710--echo # it reads. 711let $statement= insert into t4 values (2); 712let $wait_statement= select i from t1 where i=1 into k; 713--source include/check_shared_row_lock.inc 714 715--echo # 716--echo # 6.2 Statement invoking a trigger that reads table through 717--echo # a subquery in a control construct. 718--echo # 719--echo # The above is true for this statement as well. 720let $statement= update t4 set l= 2 where l = 1; 721let $wait_statement= $statement; 722--source include/check_shared_row_lock.inc 723 724--echo # 725--echo # 6.3 Statement invoking a trigger that reads a table through 726--echo # a view. 727--echo # 728--echo # And for this statement. 729let $statement= delete from t4 where l = 1; 730let $wait_statement= $statement; 731--source include/check_shared_row_lock.inc 732 733--echo # 734--echo # 6.4 Statement invoking a trigger that reads a table through 735--echo # a stored function. 736--echo # 737--echo # And for this statement. 738let $statement= insert into t5 values (2); 739let $wait_statement= select i from t1 where i = 1 into j; 740--source include/check_shared_row_lock.inc 741 742--echo # 743--echo # 6.5 Statement invoking a trigger that reads a table through 744--echo # stored procedure. 745--echo # 746--echo # And for this statement. 747let $statement= update t5 set l= 2 where l = 1; 748let $wait_statement= select i from t1 where i = 1 into p; 749--source include/check_shared_row_lock.inc 750 751--echo # Clean-up. 752drop function f1; 753drop function f2; 754drop function f3; 755drop function f4; 756drop function f5; 757drop function f6; 758drop function f7; 759drop function f8; 760drop function f9; 761drop function f10; 762drop function f11; 763drop function f12; 764drop function f13; 765drop function f14; 766drop function f15; 767drop view v1, v2; 768drop procedure p1; 769drop procedure p2; 770drop table t1, t2, t3, t4, t5; 771disconnect con1; 772 773 774--echo # 775--echo # Test for bug#51263 "Deadlock between transactional SELECT 776--echo # and ALTER TABLE ... REBUILD PARTITION". 777--echo # 778connect (con1,localhost,root,,test,,); 779connection default; 780--disable_warnings 781drop table if exists t1, t2; 782--enable_warnings 783create table t1 (i int auto_increment not null primary key) engine=innodb; 784create table t2 (i int) engine=innodb; 785insert into t1 values (1), (2), (3), (4), (5); 786 787begin; 788--echo # Acquire SR metadata lock on t1 and LOCK_S row-locks on its rows. 789insert into t2 select count(*) from t1; 790 791connection con1; 792--echo # Sending: 793--send alter table t1 add column j int 794 795connection default; 796--echo # Wait until ALTER is blocked because it tries to upgrade SNW 797--echo # metadata lock to X lock. 798--echo # It should not be blocked during copying data to new version of 799--echo # table as it acquires LOCK_S locks on rows of old version, which 800--echo # are compatible with locks acquired by connection 'con1'. 801let $wait_condition= 802 select count(*) = 1 from information_schema.processlist 803 where state = "Waiting for table metadata lock" and 804 info = "alter table t1 add column j int"; 805--source include/wait_condition.inc 806 807--echo # The below statement will deadlock because it will try to acquire 808--echo # SW lock on t1, which will conflict with ALTER's SNW lock. And 809--echo # ALTER will be waiting for this connection to release its SR lock. 810--echo # This deadlock should be detected by an MDL subsystem and this 811--echo # statement should be aborted with an appropriate error. 812--error ER_LOCK_DEADLOCK 813insert into t1 values (6); 814--echo # Unblock ALTER TABLE. 815commit; 816 817connection con1; 818--echo # Reaping ALTER TABLE. 819--reap 820 821connection default; 822 823--echo # 824--echo # Now test for scenario in which bug was reported originally. 825--echo # 826drop tables t1, t2; 827create table t1 (i int auto_increment not null primary key) engine=innodb 828 partition by hash (i) partitions 4; 829create table t2 (i int) engine=innodb; 830insert into t1 values (1), (2), (3), (4), (5); 831 832begin; 833--echo # Acquire SR metadata lock on t1. 834select * from t1; 835 836connection con1; 837--echo # Sending: 838--send alter table t1 rebuild partition p0 839 840connection default; 841--echo # Wait until ALTER is blocked because of active SR lock. 842let $wait_condition= 843 select count(*) = 1 from information_schema.processlist 844 where state = "Waiting for table metadata lock" and 845 info = "alter table t1 rebuild partition p0"; 846--source include/wait_condition.inc 847 848--echo # The below statement should succeed as transaction 849--echo # has SR metadata lock on t1 and only going to read 850--echo # rows from it. 851insert into t2 select count(*) from t1; 852--echo # Unblock ALTER TABLE. 853commit; 854 855connection con1; 856--echo # Reaping ALTER TABLE. 857--reap 858 859connection default; 860disconnect con1; 861--echo # Clean-up. 862drop tables t1, t2; 863 864 865# Check that all connections opened by test cases in this file are really 866# gone so execution of other tests won't be affected by their presence. 867--source include/wait_until_count_sessions.inc 868