MDEV-6050 MySQL Bug#13036505 62540: TABLE LOCKS WITHIN STORED FUNCTIONS ARE BACK IN 5.5 WITH MIXED AND ROW BI

cherry-pick revno 4053
committer: Gopal Shankar <gopal.shankar@oracle.com>
branch nick: sf_mysql-5.6
timestamp: Fri 2012-07-20 12:25:34 +0530
message:
  Bug#13036505 62540: TABLE LOCKS WITHIN STORED FUNCTIONS ARE BACK IN
                      5.5 WITH MIXED AND ROW BI.

9 files changed, 321 insertions, 161 deletions

diff --git a/mysql-test/r/innodb_mysql_lock2.result b/mysql-test/r/innodb_mysql_lock2.result
index 17dd747de6f..54203c140a2 100644
--- a/mysql-test/r/innodb_mysql_lock2.result
+++ b/mysql-test/r/innodb_mysql_lock2.result
@@ -331,13 +331,14 @@ Success: 'update v2 set j= j-10 where j = 3' takes shared row locks on 't1'.
 # 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
+# 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'.
+# 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.
+Success: 'select f1()' doesn't take row locks on 't1'.
 Success: 'set @a:= f1()' takes shared row locks on 't1'.
 #
 # 4.2 INSERT (or other statement which modifies data) with
@@ -364,13 +365,15 @@ Success: 'set @a:= f2()' takes shared row locks on 't1'.
 #      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'.
+# 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.
+Success: 'select f3()' doesn't take row locks on 't1'.
 Success: 'set @a:= f3()' takes shared row locks on 't1'.
-Success: 'select f4()' takes shared row locks on 't1'.
+Success: 'select f4()' doesn't take row locks on 't1'.
 Success: 'set @a:= f4()' takes shared row locks on 't1'.
 #
 # 4.5. INSERT (or other statement which modifies data) with
@@ -398,13 +401,15 @@ Success: 'set @a:= f5()' takes shared row locks on 't1'.
 #     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'.
+# 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.
+Success: 'select f6()' doesn't take row locks on 't1'.
 Success: 'set @a:= f6()' takes shared row locks on 't1'.
-Success: 'select f7()' takes shared row locks on 't1'.
+Success: 'select f7()' doesn't take row locks on 't1'.
 Success: 'set @a:= f7()' takes shared row locks on 't1'.
 #
 # 4.8 INSERT which uses stored function which
@@ -431,10 +436,9 @@ Success: 'select f9()' takes shared row locks on 't1'.
 #      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'.
+# Calls to such functions won't get into the binary
+# log and thus don't need to acquire row locks.
+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
@@ -494,10 +498,9 @@ 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'.
+# Calls to such functions won't get into the binary
+# log and thus don't need to acquire row locks.
+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.
diff --git a/mysql-test/r/lock_sync.result b/mysql-test/r/lock_sync.result
index 8fe94679e70..219cc08342e 100644
--- a/mysql-test/r/lock_sync.result
+++ b/mysql-test/r/lock_sync.result
@@ -27,6 +27,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;
@@ -42,6 +43,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;
 create table t1 (i int primary key);
 insert into t1 values (1), (2), (3), (4), (5);
 create table t2 (j int primary key);
@@ -146,6 +149,26 @@ declare k int;
 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;
@@ -185,6 +208,7 @@ end|
 # once during its execution.
 show create procedure p1;
 show create procedure p2;
+show create procedure p3;
 show create function f1;
 show create function f2;
 show create function f3;
@@ -200,6 +224,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;
 # Switch back to connection 'default'.
 #
 # 1. Statements that read tables and do not use subqueries.
@@ -359,14 +385,11 @@ Success: 'update v2 set j= j-10 where j = 3' doesn't allow concurrent inserts in
 # 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 strong locks on the table
+# There is no need to take strong 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()' doesn't allow concurrent inserts into 't1'.
-Success: 'set @a:= f1()' doesn't allow concurrent inserts into 't1'.
+# won't get into the binary log.
+Success: 'select f1()' allows concurrent inserts into 't1'.
+Success: 'set @a:= f1()' allows concurrent inserts into 't1'.
 #
 # 4.2 INSERT (or other statement which modifies data) with
 #     a stored function which does not modify data and uses
@@ -392,14 +415,12 @@ Success: 'set @a:= f2()' doesn't allow concurrent inserts into 't1'.
 #      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 strong lock is needed. But in practice
-# we don't detect this fact early enough (get_lock_type_for_table())
-# to avoid taking a strong lock.
-Success: 'select f3()' doesn't allow concurrent inserts into 't1'.
-Success: 'set @a:= f3()' doesn't allow concurrent inserts into 't1'.
-Success: 'select f4()' doesn't allow concurrent inserts into 't1'.
-Success: 'set @a:= f4()' doesn't allow concurrent inserts into 't1'.
+# Call to this function won't get to the
+# binary log and thus no strong lock is needed.
+Success: 'select f3()' allows concurrent inserts into 't1'.
+Success: 'set @a:= f3()' allows concurrent inserts into 't1'.
+Success: 'select f4()' allows concurrent inserts into 't1'.
+Success: 'set @a:= f4()' allows concurrent inserts into 't1'.
 #
 # 4.5. INSERT (or other statement which modifies data) with
 #      a stored function which does not modify data and reads
@@ -426,14 +447,13 @@ Success: 'set @a:= f5()' doesn't allow concurrent inserts into 't1'.
 #     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 strong
-# locks. But in practice this fact is discovered
-# too late to have any effect.
-Success: 'select f6()' doesn't allow concurrent inserts into 't1'.
-Success: 'set @a:= f6()' doesn't allow concurrent inserts into 't1'.
-Success: 'select f7()' doesn't allow concurrent inserts into 't1'.
-Success: 'set @a:= f7()' doesn't allow concurrent inserts into 't1'.
+# Calls to such functions won't get into
+# the binary log and thus don't need strong
+# locks.
+Success: 'select f6()' allows concurrent inserts into 't1'.
+Success: 'set @a:= f6()' allows concurrent inserts into 't1'.
+Success: 'select f7()' allows concurrent inserts into 't1'.
+Success: 'set @a:= f7()' allows concurrent inserts into 't1'.
 #
 # 4.8 INSERT which uses stored function which
 #     doesn't modify data and reads a table
@@ -459,10 +479,9 @@ Success: 'select f9()' doesn't allow concurrent inserts into 't1'.
 #      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 strong locks. But in practice
-# this fact is discovered too late to have any effect.
-Success: 'select f10()' doesn't allow concurrent inserts into 't1'.
+# Calls to such functions won't get into the binary
+# log and thus don't need to acquire strong locks.
+Success: 'select f10()' allows concurrent inserts into 't1'.
 #
 # 4.11 INSERT which uses a stored function which doesn't modify
 #      data and reads a table indirectly, by calling another
@@ -501,6 +520,26 @@ Success: 'select f12((select i+10 from t1 where i=1))' allows concurrent inserts
 # uses. Therefore it should take strong locks on the data it reads.
 Success: 'insert into t2 values (f13((select i+10 from t1 where i=1)))' doesn't allow concurrent inserts into 't1'.
 #
+# 4.15 SELECT/SET with a stored function which 
+#      inserts data into a temporary table using
+#      SELECT on t1.
+#
+# 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 strong locks on the data it reads.
+Success: 'select f16()' doesn't allow concurrent inserts into 't1'.
+Success: 'set @a:= f16()' doesn't allow concurrent inserts into 't1'.
+#
+# 4.16 SELECT/SET with a stored function which call procedure
+#      which inserts data into a temporary table using
+#      SELECT on t1.
+#
+# 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 strong locks on the data it reads.
+Success: 'select f17()' doesn't allow concurrent inserts into 't1'.
+Success: 'set @a:= f17()' doesn't allow concurrent inserts into 't1'.
+#
 # 5. Statements that read tables through stored procedures.
 #
 #
@@ -522,10 +561,9 @@ Success: 'select f14()' doesn't allow concurrent inserts into '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 strong locks. But in practice
-# this fact is discovered too late to have any effect.
-Success: 'select f15()' doesn't allow concurrent inserts into 't1'.
+# Calls to such functions won't get into the binary
+# log and thus don't need to acquire strong locks.
+Success: 'select f15()' allows concurrent inserts into 't1'.
 #
 # 5.4 INSERT which calls function which doesn't modify data and
 #     uses CALL statement which reads table through SELECT.
@@ -585,9 +623,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;
 set @@global.concurrent_insert= @old_concurrent_insert;
 #
diff --git a/mysql-test/t/innodb_mysql_lock2.test b/mysql-test/t/innodb_mysql_lock2.test
index b7259e771ae..640f9652462 100644
--- a/mysql-test/t/innodb_mysql_lock2.test
+++ b/mysql-test/t/innodb_mysql_lock2.test
@@ -440,15 +440,16 @@ let $wait_statement= $statement;
 --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 row locks on the table
+--echo # There is no need to take row 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.
+--echo #
+--echo # However in practice innodb takes strong lock on tables
+--echo # being selected from within SF, when SF is called from
+--echo # non SELECT statements like 'set' statement below.
 let $statement= select f1();
 let $wait_statement= select i from t1 where i = 1 into j;
---source include/check_shared_row_lock.inc
+--source include/check_no_row_lock.inc
 let $statement= set @a:= f1();
 let $wait_statement= select i from t1 where i = 1 into j;
 --source include/check_shared_row_lock.inc
@@ -486,19 +487,21 @@ let $wait_statement= select i from t1 where i = 1 into k;
 --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 locking is needed. But in practice
---echo # we don't detect this fact early enough (get_lock_type_for_table())
---echo # to avoid taking row locks.
+--echo # Call to this function won't get to the
+--echo # binary log and thus no locking is needed.
+--echo #
+--echo # However in practice innodb takes strong lock on tables
+--echo # being selected from within SF, when SF is called from
+--echo # non SELECT statements like 'set' statement below.
 let $statement= select f3();
 let $wait_statement= $statement;
---source include/check_shared_row_lock.inc
+--source include/check_no_row_lock.inc
 let $statement= set @a:= f3();
 let $wait_statement= $statement;
 --source include/check_shared_row_lock.inc
 let $statement= select f4();
 let $wait_statement= $statement;
---source include/check_shared_row_lock.inc
+--source include/check_no_row_lock.inc
 let $statement= set @a:= f4();
 let $wait_statement= $statement;
 --source include/check_shared_row_lock.inc
@@ -539,19 +542,21 @@ let $wait_statement= insert into t2 values ((select i from t1 where i = 1) + 5);
 --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 row
---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 row locks.
+--echo #
+--echo # However in practice innodb takes strong lock on tables
+--echo # being selected from within SF, when SF is called from
+--echo # non SELECT statements like 'set' statement below.
 let $statement= select f6();
 let $wait_statement= select i from v1 where i = 1 into k;
---source include/check_shared_row_lock.inc
+--source include/check_no_row_lock.inc
 let $statement= set @a:= f6();
 let $wait_statement= select i from v1 where i = 1 into k;
 --source include/check_shared_row_lock.inc
 let $statement= select f7();
 let $wait_statement= select j from v2 where j = 1 into k;
---source include/check_shared_row_lock.inc
+--source include/check_no_row_lock.inc
 let $statement= set @a:= f7();
 let $wait_statement= select j from v2 where j = 1 into k;
 --source include/check_shared_row_lock.inc
@@ -592,12 +597,11 @@ let $wait_statement= update v2 set j=j+10 where j=1;
 --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 row 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 row locks.
 let $statement= select f10();
 let $wait_statement= select i from t1 where i = 1 into j;
---source include/check_shared_row_lock.inc
+--source include/check_no_row_lock.inc
 
 --echo #
 --echo # 4.11 INSERT which uses a stored function which doesn't modify
@@ -676,12 +680,11 @@ let $wait_statement= select i from t1 where i = 1 into p;
 --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 row 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 row locks.
 let $statement= select f15();
 let $wait_statement= select i from t1 where i = 1 into p;
---source include/check_shared_row_lock.inc
+--source include/check_no_row_lock.inc
 
 --echo #
 --echo # 5.4 INSERT which calls function which doesn't modify data and
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;
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index d51242c56bc..366679a2fb0 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -2323,6 +2323,11 @@ sp_head::restore_lex(THD *thd)
   */
   if (sp_update_sp_used_routines(&m_sroutines, &sublex->sroutines))
     DBUG_RETURN(TRUE);
+
+  /* If this substatement is a update query, then mark MODIFIES_DATA */
+  if (is_update_query(sublex->sql_command))
+    m_flags|= MODIFIES_DATA;
+
   /*
     Merge tables used by this statement (but not by its functions or
     procedures) to multiset of tables used by this routine.
diff --git a/sql/sp_head.h b/sql/sp_head.h
index cc598186d08..dbdb957aa79 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -161,7 +161,21 @@ public:
     LOG_SLOW_STATEMENTS= 256,   // Used by events
     LOG_GENERAL_LOG= 512,        // Used by events
     HAS_SQLCOM_RESET= 1024,
-    HAS_SQLCOM_FLUSH= 2048
+    HAS_SQLCOM_FLUSH= 2048,
+
+    /**
+      Marks routines that directly (i.e. not by calling other routines)
+      change tables. Note that this flag is set automatically based on
+      type of statements used in the stored routine and is different
+      from routine characteristic provided by user in a form of CONTAINS
+      SQL, READS SQL DATA, MODIFIES SQL DATA clauses. The latter are
+      accepted by parser but pretty much ignored after that.
+      We don't rely on them:
+      a) for compatibility reasons.
+      b) because in CONTAINS SQL case they don't provide enough
+      information anyway.
+     */
+    MODIFIES_DATA= 4096
   };
 
   stored_procedure_type m_type;
@@ -332,11 +346,17 @@ public:
   int
   add_instr(sp_instr *instr);
 
-  inline uint
-  instructions()
-  {
-    return m_instr.elements;
-  }
+  /**
+    Returns true if any substatement in the routine directly
+    (not through another routine) modifies data/changes table.
+
+    @sa Comment for MODIFIES_DATA flag.
+  */
+  bool modifies_data() const
+  { return m_flags & MODIFIES_DATA; }
+
+  inline uint instructions()
+  { return m_instr.elements; }
 
   inline sp_instr *
   last_instruction()
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 8d5d5058ed1..342a45247c5 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -3517,9 +3517,12 @@ Open_table_context::recover_from_failed_open()
 /*
   Return a appropriate read lock type given a table object.
 
-  @param thd Thread context
-  @param prelocking_ctx Prelocking context.
-  @param table_list     Table list element for table to be locked.
+  @param thd              Thread context
+  @param prelocking_ctx   Prelocking context.
+  @param table_list       Table list element for table to be locked.
+  @param routine_modifies_data 
+                          Some routine that is invoked by statement 
+                          modifies data.
 
   @remark Due to a statement-based replication limitation, statements such as
           INSERT INTO .. SELECT FROM .. and CREATE TABLE .. SELECT FROM need
@@ -3532,9 +3535,13 @@ Open_table_context::recover_from_failed_open()
           This also applies to SELECT/SET/DO statements which use stored
           functions. Calls to such functions are going to be logged as a
           whole and thus should be serialized against concurrent changes
-          to tables used by those functions. This can be avoided if functions
-          only read data but doing so requires more complex analysis than it
-          is done now.
+          to tables used by those functions. This is avoided when functions
+          do not modify data but only read it, since in this case nothing is
+          written to the binary log. Argument routine_modifies_data
+          denotes the same. So effectively, if the statement is not a
+          update query and routine_modifies_data is false, then
+          prelocking_placeholder does not take importance.
+
           Furthermore, this does not apply to I_S and log tables as it's
           always unsafe to replicate such tables under statement-based
           replication as the table on the slave might contain other data
@@ -3549,7 +3556,8 @@ Open_table_context::recover_from_failed_open()
 
 thr_lock_type read_lock_type_for_table(THD *thd,
                                        Query_tables_list *prelocking_ctx,
-                                       TABLE_LIST *table_list)
+                                       TABLE_LIST *table_list,
+                                       bool routine_modifies_data)
 {
   /*
     In cases when this function is called for a sub-statement executed in
@@ -3564,7 +3572,7 @@ thr_lock_type read_lock_type_for_table(THD *thd,
       (table_list->table->s->table_category == TABLE_CATEGORY_LOG) ||
       (table_list->table->s->table_category == TABLE_CATEGORY_PERFORMANCE) ||
       !(is_update_query(prelocking_ctx->sql_command) ||
-        table_list->prelocking_placeholder ||
+        (routine_modifies_data && table_list->prelocking_placeholder) ||
         (thd->locked_tables_mode > LTM_LOCK_TABLES)))
     return TL_READ;
   else
@@ -3577,19 +3585,21 @@ thr_lock_type read_lock_type_for_table(THD *thd,
   and, if prelocking strategy prescribes so, extend the prelocking set
   with tables and routines used by it.
 
-  @param[in]  thd                  Thread context.
-  @param[in]  prelocking_ctx       Prelocking context.
-  @param[in]  rt                   Element of prelocking set to be processed.
-  @param[in]  prelocking_strategy  Strategy which specifies how the
-                                   prelocking set should be extended when
-                                   one of its elements is processed.
-  @param[in]  has_prelocking_list  Indicates that prelocking set/list for
-                                   this statement has already been built.
-  @param[in]  ot_ctx               Context of open_table used to recover from
-                                   locking failures.
-  @param[out] need_prelocking      Set to TRUE if it was detected that this
-                                   statement will require prelocked mode for
-                                   its execution, not touched otherwise.
+  @param[in]  thd                   Thread context.
+  @param[in]  prelocking_ctx        Prelocking context.
+  @param[in]  rt                    Element of prelocking set to be processed.
+  @param[in]  prelocking_strategy   Strategy which specifies how the
+                                    prelocking set should be extended when
+                                    one of its elements is processed.
+  @param[in]  has_prelocking_list   Indicates that prelocking set/list for
+                                    this statement has already been built.
+  @param[in]  ot_ctx                Context of open_table used to recover from
+                                    locking failures.
+  @param[out] need_prelocking       Set to TRUE if it was detected that this
+                                    statement will require prelocked mode for
+                                    its execution, not touched otherwise.
+  @param[out] routine_modifies_data Set to TRUE if it was detected that this
+                                    routine does modify table data.
 
   @retval FALSE  Success.
   @retval TRUE   Failure (Conflicting metadata lock, OOM, other errors).
@@ -3601,11 +3611,13 @@ open_and_process_routine(THD *thd, Query_tables_list *prelocking_ctx,
                          Prelocking_strategy *prelocking_strategy,
                          bool has_prelocking_list,
                          Open_table_context *ot_ctx,
-                         bool *need_prelocking)
+                         bool *need_prelocking, bool *routine_modifies_data)
 {
   MDL_key::enum_mdl_namespace mdl_type= rt->mdl_request.key.mdl_namespace();
   DBUG_ENTER("open_and_process_routine");
 
+  *routine_modifies_data= false;
+
   switch (mdl_type)
   {
   case MDL_key::FUNCTION:
@@ -3658,10 +3670,13 @@ open_and_process_routine(THD *thd, Query_tables_list *prelocking_ctx,
           DBUG_RETURN(TRUE);
 
         /* 'sp' is NULL when there is no such routine. */
-        if (sp && !has_prelocking_list)
+        if (sp)
         {
-          prelocking_strategy->handle_routine(thd, prelocking_ctx, rt, sp,
-                                              need_prelocking);
+          *routine_modifies_data= sp->modifies_data();
+
+          if (!has_prelocking_list)
+            prelocking_strategy->handle_routine(thd, prelocking_ctx, rt, sp,
+                                                need_prelocking);
         }
       }
       else
@@ -4006,16 +4021,7 @@ open_and_process_table(THD *thd, LEX *lex, TABLE_LIST *tables,
       goto end;
   }
 
-  if (tables->lock_type != TL_UNLOCK && ! thd->locked_tables_mode)
-  {
-    if (tables->lock_type == TL_WRITE_DEFAULT)
-      tables->table->reginfo.lock_type= thd->update_lock_default;
-    else if (tables->lock_type == TL_READ_DEFAULT)
-      tables->table->reginfo.lock_type=
-        read_lock_type_for_table(thd, lex, tables);
-    else
-      tables->table->reginfo.lock_type= tables->lock_type;
-  }
+  /* Copy grant information from TABLE_LIST instance to TABLE one. */
   tables->table->grant= tables->grant;
 
   /* Check and update metadata version of a base table. */
@@ -4354,6 +4360,7 @@ bool open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags,
   Open_table_context ot_ctx(thd, flags);
   bool error= FALSE;
   MEM_ROOT new_frm_mem;
+  bool some_routine_modifies_data= FALSE;
   bool has_prelocking_list;
   DBUG_ENTER("open_tables");
 
@@ -4526,11 +4533,16 @@ restart:
            sroutine_to_open= &rt->next, rt= rt->next)
       {
         bool need_prelocking= false;
+        bool routine_modifies_data;
         TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last;
 
         error= open_and_process_routine(thd, thd->lex, rt, prelocking_strategy,
                                         has_prelocking_list, &ot_ctx,
-                                        &need_prelocking);
+                                        &need_prelocking,
+                                        &routine_modifies_data);
+
+        // Remember if any of SF modifies data.
+        some_routine_modifies_data|= routine_modifies_data;
 
         if (need_prelocking && ! thd->lex->requires_prelocking())
           thd->lex->mark_as_requiring_prelocking(save_query_tables_last);
@@ -4570,6 +4582,10 @@ restart:
     children, attach the children to their parents. At end of statement,
     the children are detached. Attaching and detaching are always done,
     even under LOCK TABLES.
+
+    We also convert all TL_WRITE_DEFAULT and TL_READ_DEFAULT locks to
+    appropriate "real" lock types to be used for locking and to be passed
+    to storage engine.
   */
   for (tables= *start; tables; tables= tables->next_global)
   {
@@ -4586,6 +4602,19 @@ restart:
         goto err;
       }
     }
+
+    /* Set appropriate TABLE::lock_type. */
+    if (tbl && tables->lock_type != TL_UNLOCK && !thd->locked_tables_mode)
+    {
+      if (tables->lock_type == TL_WRITE_DEFAULT)
+        tbl->reginfo.lock_type= thd->update_lock_default;
+      else if (tables->lock_type == TL_READ_DEFAULT)
+          tbl->reginfo.lock_type=
+            read_lock_type_for_table(thd, thd->lex, tables,
+                                     some_routine_modifies_data);
+      else
+        tbl->reginfo.lock_type= tables->lock_type;
+    }
   }
 
 err:
@@ -4847,11 +4876,15 @@ static bool check_lock_and_start_stmt(THD *thd,
     engine is important as, for example, InnoDB uses it to determine
     what kind of row locks should be acquired when executing statement
     in prelocked mode or under LOCK TABLES with @@innodb_table_locks = 0.
+
+    Last argument routine_modifies_data for read_lock_type_for_table()
+    is ignored, as prelocking placeholder will never be set here.
   */
+  DBUG_ASSERT(table_list->prelocking_placeholder == false);
   if (table_list->lock_type == TL_WRITE_DEFAULT)
     lock_type= thd->update_lock_default;
   else if (table_list->lock_type == TL_READ_DEFAULT)
-    lock_type= read_lock_type_for_table(thd, prelocking_ctx, table_list);
+    lock_type= read_lock_type_for_table(thd, prelocking_ctx, table_list, true);
   else
     lock_type= table_list->lock_type;
 
diff --git a/sql/sql_base.h b/sql/sql_base.h
index 61442843a39..8a0a1e42500 100644
--- a/sql/sql_base.h
+++ b/sql/sql_base.h
@@ -136,7 +136,8 @@ TABLE *find_write_locked_table(TABLE *list, const char *db,
                                const char *table_name);
 thr_lock_type read_lock_type_for_table(THD *thd,
                                        Query_tables_list *prelocking_ctx,
-                                       TABLE_LIST *table_list);
+                                       TABLE_LIST *table_list,
+                                       bool routine_modifies_data);
 
 my_bool mysql_rm_tmp_tables(void);
 bool rm_temporary_table(handlerton *base, const char *path);
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 1b808d333d8..1eda5b2681a 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1431,11 +1431,15 @@ int mysql_multi_update_prepare(THD *thd)
         another table instance used by this statement which is going to
         be write-locked (for example, trigger to be invoked might try
         to update this table).
+        Last argument routine_modifies_data for read_lock_type_for_table()
+        is ignored, as prelocking placeholder will never be set here.
       */
+      DBUG_ASSERT(tl->prelocking_placeholder == false);
+      thr_lock_type lock_type= read_lock_type_for_table(thd, lex, tl, true);
       if (using_lock_tables)
-        tl->lock_type= read_lock_type_for_table(thd, lex, tl);
+        tl->lock_type= lock_type;
       else
-        tl->set_lock_type(thd, read_lock_type_for_table(thd, lex, tl));
+        tl->set_lock_type(thd, lock_type);
       tl->updating= 0;
     }
   }