Edit File: innodb_mysql_lock2.result
# # Test how do we handle locking in various cases when # we read data from InnoDB tables. # # In fact by performing this test we check two things: # 1) That SQL-layer correctly determine type of thr_lock.c # lock to be acquired/passed to InnoDB engine. # 2) That InnoDB engine correctly interprets this lock # type and takes necessary row locks or does not # take them if they are not necessary. # # This test makes sense only in REPEATABLE-READ mode as # in SERIALIZABLE mode all statements that read data take # shared lock on them to enforce its semantics. select @@session.tx_isolation; @@session.tx_isolation REPEATABLE-READ # Prepare playground by creating tables, views, # routines and triggers used in tests. 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; 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); 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| # # Set common variables to be used by scripts called below. # # # 1. Statements that read tables and do not use subqueries. # # # 1.1 Simple SELECT statement. # # No locks are necessary as this statement won't be written # to the binary log and InnoDB supports snapshots. Success: 'select * from t1' doesn't take row locks on 't1'. # # 1.2 Multi-UPDATE statement. # # Has to take shared locks on rows in the table being read as this # statement will be written to the binary log and therefore should # be serialized with concurrent statements. Success: 'update t2, t1 set j= j - 1 where i = j' takes shared row locks on 't1'. # # 1.3 Multi-DELETE statement. # # The above is true for this statement as well. Success: 'delete t2 from t1, t2 where i = j' takes shared row locks on 't1'. # # 1.4 DESCRIBE statement. # # This statement does not really read data from the # target table and thus does not take any lock on it. # We check this for completeness of coverage. Success: 'describe t1' doesn't take row locks on 't1'. # # 1.5 SHOW statements. # # The above is true for SHOW statements as well. Success: 'show create table t1' doesn't take row locks on 't1'. Success: 'show keys from t1' doesn't take row locks on 't1'. # # 2. Statements which read tables through subqueries. # # # 2.1 CALL with a subquery. # # A strong lock is not necessary as this statement is not # written to the binary log as a whole (it is written # statement-by-statement) and thanks to MVCC we can always get # versions of rows prior to the update that has locked them. # But in practice InnoDB does locking reads for all statements # other than SELECT (unless it is a READ-COMITTED mode or # innodb_locks_unsafe_for_binlog is ON). Success: 'call p1((select i + 5 from t1 where i = 1))' takes shared row locks on 't1'. # # 2.2 CREATE TABLE with a subquery. # # Has to take shared locks on rows in the table being read as # this statement is written to the binary log and therefore # should be serialized with concurrent statements. Success: 'create table t0 engine=innodb select * from t1' takes shared row locks on 't1'. drop table t0; Success: 'create table t0 engine=innodb select j from t2 where j in (select i from t1)' takes shared row locks on 't1'. drop table t0; # # 2.3 DELETE with a subquery. # # The above is true for this statement as well. Success: 'delete from t2 where j in (select i from t1)' takes shared row locks on 't1'. # # 2.4 MULTI-DELETE with a subquery. # # Same is true for this statement as well. Success: 'delete t2 from t3, t2 where k = j and j in (select i from t1)' takes shared row locks on 't1'. # # 2.5 DO with a subquery. # # In theory should not take row locks as it is not logged. # In practice InnoDB takes shared row locks. Success: 'do (select i from t1 where i = 1)' takes shared row locks on 't1'. # # 2.6 INSERT with a subquery. # # Has to take shared locks on rows in the table being read as # this statement is written to the binary log and therefore # should be serialized with concurrent statements. Success: 'insert into t2 select i+5 from t1' takes shared row locks on 't1'. Success: 'insert into t2 values ((select i+5 from t1 where i = 4))' takes shared row locks on 't1'. # # 2.7 LOAD DATA with a subquery. # # The above is true for this statement as well. Success: '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'. # # 2.8 REPLACE with a subquery. # # Same is true for this statement as well. Success: 'replace into t2 select i+5 from t1' takes shared row locks on 't1'. Success: 'replace into t2 values ((select i+5 from t1 where i = 4))' takes shared row locks on 't1'. # # 2.9 SELECT with a subquery. # # Locks are not necessary as this statement is not written # to the binary log and thanks to MVCC we can always get # versions of rows prior to the update that has locked them. # # Also serves as a test case for bug #46947 "Embedded SELECT # without FOR UPDATE is causing a lock". Success: 'select * from t2 where j in (select i from t1)' doesn't take row locks on 't1'. # # 2.10 SET with a subquery. # # In theory should not require locking as it is not written # to the binary log. In practice InnoDB acquires shared row # locks. Success: 'set @a:= (select i from t1 where i = 1)' takes shared row locks on 't1'. # # 2.11 SHOW with a subquery. # # Similarly to the previous case, in theory should not require locking # as it is not written to the binary log. In practice InnoDB # acquires shared row locks. Success: 'show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1)' takes shared row locks on 't1'. Success: 'show columns from t2 where (select i from t1 where i = 1)' takes shared row locks on 't1'. # # 2.12 UPDATE with a subquery. # # Has to take shared locks on rows in the table being read as # this statement is written to the binary log and therefore # should be serialized with concurrent statements. Success: 'update t2 set j= j-10 where j in (select i from t1)' takes shared row locks on 't1'. # # 2.13 MULTI-UPDATE with a subquery. # # Same is true for this statement as well. Success: 'update t2, t3 set j= j -10 where j=k and j in (select i from t1)' takes shared row locks on 't1'. # # 3. Statements which read tables through a view. # # # 3.1 SELECT statement which uses some table through a view. # # Since this statement is not written to the binary log # and old version of rows are accessible thanks to MVCC, # no locking is necessary. Success: 'select * from v1' doesn't take row locks on 't1'. Success: 'select * from v2' doesn't take row locks on 't1'. Success: 'select * from t2 where j in (select i from v1)' doesn't take row locks on 't1'. Success: 'select * from t3 where k in (select j from v2)' doesn't take row locks on 't1'. # # 3.2 Statements which modify a table and use views. # # Since such statements are going to be written to the binary # log they need to be serialized against concurrent statements # and therefore should take shared row locks on data read. Success: 'update t2 set j= j-10 where j in (select i from v1)' takes shared row locks on 't1'. Success: 'update t3 set k= k-10 where k in (select j from v2)' takes shared row locks on 't1'. Success: 'update t2, v1 set j= j-10 where j = i' takes shared row locks on 't1'. Success: 'update v2 set j= j-10 where j = 3' takes shared row locks on 't1'. # # 4. Statements which read tables through stored functions. # # # 4.1 SELECT/SET with a stored function which does not # modify data and uses SELECT in its turn. # # In theory there is no need to take row locks on the table # being selected from in SF as the call to such function # won't get into the binary log. In practice, however, we # discover that fact too late in the process to be able to # affect the decision what locks should be taken. # Hence, strong locks are taken in this case. Success: 'select f1()' takes shared row locks on 't1'. Success: 'set @a:= f1()' takes shared row locks on 't1'. # # 4.2 INSERT (or other statement which modifies data) with # a stored function which does not modify data and uses # SELECT. # # Since such statement is written to the binary log it should # be serialized with concurrent statements affecting the data # it uses. Therefore it should take row locks on the data # it reads. Success: 'insert into t2 values (f1() + 5)' takes shared row locks on 't1'. # # 4.3 SELECT/SET with a stored function which # reads and modifies data. # # Since a call to such function is written to the binary log, # it should be serialized with concurrent statements affecting # the data it uses. Hence, row locks on the data read # should be taken. Success: 'select f2()' takes shared row locks on 't1'. Success: 'set @a:= f2()' takes shared row locks on 't1'. # # 4.4. SELECT/SET with a stored function which does not # modify data and reads a table through subselect # in a control construct. # # Again, in theory a call to this function won't get to the # binary log and thus no locking is needed. But in practice # we don't detect this fact early enough (get_lock_type_for_table()) # to avoid taking row locks. Success: 'select f3()' takes shared row locks on 't1'. Success: 'set @a:= f3()' takes shared row locks on 't1'. Success: 'select f4()' takes shared row locks on 't1'. Success: 'set @a:= f4()' takes shared row locks on 't1'. # # 4.5. INSERT (or other statement which modifies data) with # a stored function which does not modify data and reads # the table through a subselect in one of its control # constructs. # # Since such statement is written to the binary log it should # be serialized with concurrent statements affecting data it # uses. Therefore it should take row locks on the data # it reads. Success: 'insert into t2 values (f3() + 5)' takes shared row locks on 't1'. Success: 'insert into t2 values (f4() + 6)' takes shared row locks on 't1'. # # 4.6 SELECT/SET which uses a stored function with # DML which reads a table via a subquery. # # Since call to such function is written to the binary log # it should be serialized with concurrent statements. # Hence reads should take row locks. Success: 'select f5()' takes shared row locks on 't1'. Success: 'set @a:= f5()' takes shared row locks on 't1'. # # 4.7 SELECT/SET which uses a stored function which # doesn't modify data and reads tables through # a view. # # Once again, in theory, calls to such functions won't # get into the binary log and thus don't need row # locks. But in practice this fact is discovered # too late to have any effect. Success: 'select f6()' takes shared row locks on 't1'. Success: 'set @a:= f6()' takes shared row locks on 't1'. Success: 'select f7()' takes shared row locks on 't1'. Success: 'set @a:= f7()' takes shared row locks on 't1'. # # 4.8 INSERT which uses stored function which # doesn't modify data and reads a table # through a view. # # Since such statement is written to the binary log and # should be serialized with concurrent statements affecting # the data it uses. Therefore it should take row locks on # the rows it reads. Success: 'insert into t3 values (f6() + 5)' takes shared row locks on 't1'. Success: 'insert into t3 values (f7() + 5)' takes shared row locks on 't1'. # # 4.9 SELECT which uses a stored function which # modifies data and reads tables through a view. # # Since a call to such function is written to the binary log # it should be serialized with concurrent statements. # Hence, reads should take row locks. Success: 'select f8()' takes shared row locks on 't1'. Success: 'select f9()' takes shared row locks on 't1'. # # 4.10 SELECT which uses stored function which doesn't modify # data and reads a table indirectly, by calling another # function. # # In theory, calls to such functions won't get into the binary # log and thus don't need to acquire row locks. But in practice # this fact is discovered too late to have any effect. Success: 'select f10()' takes shared row locks on 't1'. # # 4.11 INSERT which uses a stored function which doesn't modify # data and reads a table indirectly, by calling another # function. # # Since such statement is written to the binary log, it should # be serialized with concurrent statements affecting the data it # uses. Therefore it should take row locks on data it reads. Success: 'insert into t2 values (f10() + 5)' takes shared row locks on 't1'. # # 4.12 SELECT which uses a stored function which modifies # data and reads a table indirectly, by calling another # function. # # Since a call to such function is written to the binary log # it should be serialized from concurrent statements. # Hence, reads should take row locks. Success: 'select f11()' takes shared row locks on 't1'. # # 4.13 SELECT that reads a table through a subquery passed # as a parameter to a stored function which modifies # data. # # Even though a call to this function is written to the # binary log, values of its parameters are written as literals. # So there is no need to acquire row locks on rows used in # the subquery. Success: 'select f12((select i+10 from t1 where i=1))' doesn't take row locks on 't1'. # # 4.14 INSERT that reads a table via a subquery passed # as a parameter to a stored function which doesn't # modify data. # # Since this statement is written to the binary log it should # be serialized with concurrent statements affecting the data it # uses. Therefore it should take row locks on the data it reads. Success: 'insert into t2 values (f13((select i+10 from t1 where i=1)))' takes shared row locks on 't1'. # # 5. Statements that read tables through stored procedures. # # # 5.1 CALL statement which reads a table via SELECT. # # Since neither this statement nor its components are # written to the binary log, there is no need to take # row locks on the data it reads. Success: 'call p2(@a)' doesn't take row locks on 't1'. # # 5.2 Function that modifies data and uses CALL, # which reads a table through SELECT. # # Since a call to such function is written to the binary # log, it should be serialized with concurrent statements. # Hence, in this case reads should take row locks on data. Success: 'select f14()' takes shared row locks on 't1'. # # 5.3 SELECT that calls a function that doesn't modify data and # uses a CALL statement that reads a table via SELECT. # # In theory, calls to such functions won't get into the binary # log and thus don't need to acquire row locks. But in practice # this fact is discovered too late to have any effect. Success: 'select f15()' takes shared row locks on 't1'. # # 5.4 INSERT which calls function which doesn't modify data and # uses CALL statement which reads table through SELECT. # # Since such statement is written to the binary log it should # be serialized with concurrent statements affecting data it # uses. Therefore it should take row locks on data it reads. Success: 'insert into t2 values (f15()+5)' takes shared row locks on 't1'. # # 6. Statements that use triggers. # # # 6.1 Statement invoking a trigger that reads table via SELECT. # # Since this statement is written to the binary log it should # be serialized with concurrent statements affecting the data # it uses. Therefore, it should take row locks on the data # it reads. Success: 'insert into t4 values (2)' takes shared row locks on 't1'. # # 6.2 Statement invoking a trigger that reads table through # a subquery in a control construct. # # The above is true for this statement as well. Success: 'update t4 set l= 2 where l = 1' takes shared row locks on 't1'. # # 6.3 Statement invoking a trigger that reads a table through # a view. # # And for this statement. Success: 'delete from t4 where l = 1' takes shared row locks on 't1'. # # 6.4 Statement invoking a trigger that reads a table through # a stored function. # # And for this statement. Success: 'insert into t5 values (2)' takes shared row locks on 't1'. # # 6.5 Statement invoking a trigger that reads a table through # stored procedure. # # And for this statement. Success: 'update t5 set l= 2 where l = 1' takes shared row locks on 't1'. # 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; # # Test for bug#51263 "Deadlock between transactional SELECT # and ALTER TABLE ... REBUILD PARTITION". # drop table if exists t1, t2; 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; # Acquire SR metadata lock on t1 and LOCK_S row-locks on its rows. insert into t2 select count(*) from t1; # Switching to connection 'con1'. # Sending: alter table t1 add column j int; # Switching to connection 'default'. # Wait until ALTER is blocked because it tries to upgrade SNW # metadata lock to X lock. # It should not be blocked during copying data to new version of # table as it acquires LOCK_S locks on rows of old version, which # are compatible with locks acquired by connection 'con1'. # The below statement will deadlock because it will try to acquire # SW lock on t1, which will conflict with ALTER's SNW lock. And # ALTER will be waiting for this connection to release its SR lock. # This deadlock should be detected by an MDL subsystem and this # statement should be aborted with an appropriate error. insert into t1 values (6); ERROR 40001: Deadlock found when trying to get lock; try restarting transaction # Unblock ALTER TABLE. commit; # Switching to connection 'con1'. # Reaping ALTER TABLE. # Switching to connection 'default'. # # Now test for scenario in which bug was reported originally. # 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; # Acquire SR metadata lock on t1. select * from t1; i 1 2 3 4 5 # Switching to connection 'con1'. # Sending: alter table t1 rebuild partition p0; # Switching to connection 'default'. # Wait until ALTER is blocked because of active SR lock. # The below statement should succeed as transaction # has SR metadata lock on t1 and only going to read # rows from it. insert into t2 select count(*) from t1; # Unblock ALTER TABLE. commit; # Switching to connection 'con1'. # Reaping ALTER TABLE. # Switching to connection 'default'. # Clean-up. drop tables t1, t2;
Back to File Manager