diff options
Diffstat (limited to 'mysql-test/t/lock_sync.test')
| -rw-r--r-- | mysql-test/t/lock_sync.test | 114 |
1 files changed, 82 insertions, 32 deletions
diff --git a/mysql-test/t/lock_sync.test b/mysql-test/t/lock_sync.test index d5ad7becd7d..f00080d917b 100644 --- a/mysql-test/t/lock_sync.test +++ b/mysql-test/t/lock_sync.test @@ -49,6 +49,7 @@ 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 procedure if exists p3; drop function if exists f1; drop function if exists f2; drop function if exists f3; @@ -64,6 +65,8 @@ drop function if exists f12; drop function if exists f13; drop function if exists f14; drop function if exists f15; +drop function if exists f16; +drop function if exists f17; --enable_warnings create table t1 (i int primary key); insert into t1 values (1), (2), (3), (4), (5); @@ -170,6 +173,26 @@ begin call p2(k); return k; end| +create function f16() returns int +begin + create temporary table if not exists temp1 (a int); + insert into temp1 select * from t1; + drop temporary table temp1; + return 1; +end| +create function f17() returns int +begin + declare j int; + select i from t1 where i = 1 into j; + call p3; + return 1; +end| +create procedure p3() +begin + create temporary table if not exists temp1 (a int); + insert into temp1 select * from t1; + drop temporary table temp1; +end| create trigger t4_bi before insert on t4 for each row begin declare k int; @@ -217,6 +240,7 @@ connection con1; --disable_result_log show create procedure p1; show create procedure p2; +show create procedure p3; show create function f1; show create function f2; show create function f3; @@ -232,6 +256,8 @@ show create function f12; show create function f13; show create function f14; show create function f15; +show create function f16; +show create function f17; --enable_result_log --echo # Switch back to connection 'default'. connection default; @@ -492,18 +518,15 @@ let $restore_table= t2; --echo # 4.1 SELECT/SET with a stored function which does not --echo # modify data and uses SELECT in its turn. --echo # ---echo # In theory there is no need to take strong locks on the table +--echo # There is no need to take strong locks on the table --echo # being selected from in SF as the call to such function ---echo # won't get into the binary log. In practice, however, we ---echo # discover that fact too late in the process to be able to ---echo # affect the decision what locks should be taken. ---echo # Hence, strong locks are taken in this case. +--echo # won't get into the binary log. let $statement= select f1(); let $restore_table= ; ---source include/check_no_concurrent_insert.inc +--source include/check_concurrent_insert.inc let $statement= set @a:= f1(); let $restore_table= ; ---source include/check_no_concurrent_insert.inc +--source include/check_concurrent_insert.inc --echo # --echo # 4.2 INSERT (or other statement which modifies data) with @@ -538,22 +561,20 @@ let $restore_table= t2; --echo # modify data and reads a table through subselect --echo # in a control construct. --echo # ---echo # Again, in theory a call to this function won't get to the ---echo # binary log and thus no strong lock is needed. But in practice ---echo # we don't detect this fact early enough (get_lock_type_for_table()) ---echo # to avoid taking a strong lock. +--echo # Call to this function won't get to the +--echo # binary log and thus no strong lock is needed. let $statement= select f3(); let $restore_table= ; ---source include/check_no_concurrent_insert.inc +--source include/check_concurrent_insert.inc let $statement= set @a:= f3(); let $restore_table= ; ---source include/check_no_concurrent_insert.inc +--source include/check_concurrent_insert.inc let $statement= select f4(); let $restore_table= ; ---source include/check_no_concurrent_insert.inc +--source include/check_concurrent_insert.inc let $statement= set @a:= f4(); let $restore_table= ; ---source include/check_no_concurrent_insert.inc +--source include/check_concurrent_insert.inc --echo # --echo # 4.5. INSERT (or other statement which modifies data) with @@ -591,22 +612,21 @@ let $restore_table= t2; --echo # doesn't modify data and reads tables through --echo # a view. --echo # ---echo # Once again, in theory, calls to such functions won't ---echo # get into the binary log and thus don't need strong ---echo # locks. But in practice this fact is discovered ---echo # too late to have any effect. +--echo # Calls to such functions won't get into +--echo # the binary log and thus don't need strong +--echo # locks. let $statement= select f6(); let $restore_table= t2; ---source include/check_no_concurrent_insert.inc +--source include/check_concurrent_insert.inc let $statement= set @a:= f6(); let $restore_table= t2; ---source include/check_no_concurrent_insert.inc +--source include/check_concurrent_insert.inc let $statement= select f7(); let $restore_table= t2; ---source include/check_no_concurrent_insert.inc +--source include/check_concurrent_insert.inc let $statement= set @a:= f7(); let $restore_table= t2; ---source include/check_no_concurrent_insert.inc +--source include/check_concurrent_insert.inc --echo # --echo # 4.8 INSERT which uses stored function which @@ -644,12 +664,11 @@ let $restore_table= t2; --echo # data and reads a table indirectly, by calling another --echo # function. --echo # ---echo # In theory, calls to such functions won't get into the binary ---echo # log and thus don't need to acquire strong locks. But in practice ---echo # this fact is discovered too late to have any effect. +--echo # Calls to such functions won't get into the binary +--echo # log and thus don't need to acquire strong locks. let $statement= select f10(); let $restore_table= ; ---source include/check_no_concurrent_insert.inc +--source include/check_concurrent_insert.inc --echo # --echo # 4.11 INSERT which uses a stored function which doesn't modify @@ -700,6 +719,36 @@ let $statement= insert into t2 values (f13((select i+10 from t1 where i=1))); let $restore_table= t2; --source include/check_no_concurrent_insert.inc +--echo # +--echo # 4.15 SELECT/SET with a stored function which +--echo # inserts data into a temporary table using +--echo # SELECT on t1. +--echo # +--echo # Since this statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting the data it +--echo # uses. Therefore it should take strong locks on the data it reads. +let $statement= select f16(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc +let $statement= set @a:= f16(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.16 SELECT/SET with a stored function which call procedure +--echo # which inserts data into a temporary table using +--echo # SELECT on t1. +--echo # +--echo # Since this statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting the data it +--echo # uses. Therefore it should take strong locks on the data it reads. +let $statement= select f17(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc +let $statement= set @a:= f17(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc + --echo # --echo # 5. Statements that read tables through stored procedures. @@ -730,12 +779,11 @@ let $restore_table= t2; --echo # 5.3 SELECT that calls a function that doesn't modify data and --echo # uses a CALL statement that reads a table via SELECT. --echo # ---echo # In theory, calls to such functions won't get into the binary ---echo # log and thus don't need to acquire strong locks. But in practice ---echo # this fact is discovered too late to have any effect. +--echo # Calls to such functions won't get into the binary +--echo # log and thus don't need to acquire strong locks. let $statement= select f15(); let $restore_table= ; ---source include/check_no_concurrent_insert.inc +--source include/check_concurrent_insert.inc --echo # --echo # 5.4 INSERT which calls function which doesn't modify data and @@ -800,7 +848,6 @@ let $statement= update t5 set l= 2 where l = 1; let $restore_table= t5; --source include/check_no_concurrent_insert.inc - --echo # Clean-up. drop function f1; drop function f2; @@ -817,9 +864,12 @@ drop function f12; drop function f13; drop function f14; drop function f15; +drop function f16; +drop function f17; drop view v1, v2; drop procedure p1; drop procedure p2; +drop procedure p3; drop table t1, t2, t3, t4, t5; disconnect con1; |