path: root/mysql-test/r/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.
connect  con1, localhost, root,,;
connection default;
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.
connection default;
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.
connection default;
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.
connection default;
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.
connection default;
Success: 'describe t1' doesn't take row locks on 't1'.
#
# 1.5 SHOW statements.
# 
# The above is true for SHOW statements as well.
connection default;
Success: 'show create table t1' doesn't take row locks on 't1'.
connection default;
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).
connection default;
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.
connection default;
Success: 'create table t0 engine=innodb select * from t1' takes shared row locks on 't1'.
drop table t0;
connection default;
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.
connection default;
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.
connection default;
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.
connection default;
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.
connection default;
Success: 'insert into t2 select i+5 from t1' takes shared row locks on 't1'.
connection default;
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.
connection default;
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.
connection default;
Success: 'replace into t2 select i+5 from t1' takes shared row locks on 't1'.
connection default;
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".
connection default;
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.
connection default;
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.
connection default;
Success: 'show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1)' takes shared row locks on 't1'.
connection default;
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.
connection default;
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.
connection default;
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.
connection default;
Success: 'select * from v1' doesn't take row locks on 't1'.
connection default;
Success: 'select * from v2' doesn't take row locks on 't1'.
connection default;
Success: 'select * from t2 where j in (select i from v1)' doesn't take row locks on 't1'.
connection default;
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.
connection default;
Success: 'update t2 set j= j-10 where j in (select i from v1)' takes shared row locks on 't1'.
connection default;
Success: 'update t3 set k= k-10 where k in (select j from v2)' takes shared row locks on 't1'.
connection default;
Success: 'update t2, v1 set j= j-10 where j = i' takes shared row locks on 't1'.
connection default;
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.
#
# 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.
#
# However in practice innodb takes strong lock on tables
# being selected from within SF, when SF is called from
# non SELECT statements like 'set' statement below.
connection default;
Success: 'select f1()' doesn't take row locks on 't1'.
connection default;
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.
connection default;
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.
connection default;
Success: 'select f2()' takes shared row locks on 't1'.
connection default;
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.
#
# Call to this function won't get to the
# binary log and thus no locking is needed.
#
# However in practice innodb takes strong lock on tables
# being selected from within SF, when SF is called from
# non SELECT statements like 'set' statement below.
connection default;
Success: 'select f3()' doesn't take row locks on 't1'.
connection default;
Success: 'set @a:= f3()' takes shared row locks on 't1'.
connection default;
Success: 'select f4()' doesn't take row locks on 't1'.
connection default;
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.
connection default;
Success: 'insert into t2 values (f3() + 5)' takes shared row locks on 't1'.
connection default;
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.
connection default;
Success: 'select f5()' takes shared row locks on 't1'.
connection default;
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.
#
# Calls to such functions won't get into
# the binary log and thus don't need row locks.
#
# However in practice innodb takes strong lock on tables
# being selected from within SF, when SF is called from
# non SELECT statements like 'set' statement below.
connection default;
Success: 'select f6()' doesn't take row locks on 't1'.
connection default;
Success: 'set @a:= f6()' takes shared row locks on 't1'.
connection default;
Success: 'select f7()' doesn't take row locks on 't1'.
connection default;
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.
connection default;
Success: 'insert into t3 values (f6() + 5)' takes shared row locks on 't1'.
connection default;
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.
connection default;
Success: 'select f8()' takes shared row locks on 't1'.
connection default;
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.
#
# Calls to such functions won't get into the binary
# log and thus don't need to acquire row locks.
connection default;
Success: 'select f10()' doesn't take 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.
connection default;
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.
connection default;
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.
connection default;
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.
connection default;
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.
connection default;
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.
connection default;
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.
#
# Calls to such functions won't get into the binary
# log and thus don't need to acquire row locks.
connection default;
Success: 'select f15()' doesn't take 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.
connection default;
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.
connection default;
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.
connection default;
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.
connection default;
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.
connection default;
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.
connection default;
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;
disconnect con1;
#
# Test for bug#51263 "Deadlock between transactional SELECT
# and ALTER TABLE ... REBUILD PARTITION".
#
connect  con1,localhost,root,,test,,;
connection default;
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;
connection con1;
# Sending:
alter table t1 add column j int;
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;
connection con1;
# Reaping ALTER TABLE.
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
connection con1;
# Sending:
alter table t1 rebuild partition p0;
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;
connection con1;
# Reaping ALTER TABLE.
connection default;
disconnect con1;
# Clean-up.
drop tables t1, t2;