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