merge from mysql-trunk-runtime

47 files changed, 3663 insertions, 164 deletions

diff --git a/CMakeLists.txt b/CMakeLists.txt
index 889dc463769..536fae9f4b1 100755
--- a/CMakeLists.txt
+++ b/CMakeLists.txt
@@ -262,6 +262,7 @@ IF(NOT WITHOUT_SERVER)
 ENDIF()
 
 INCLUDE(cmake/abi_check.cmake)
+INCLUDE(cmake/tags.cmake)
 
 CONFIGURE_FILE(config.h.cmake   ${CMAKE_BINARY_DIR}/include/my_config.h)
 CONFIGURE_FILE(config.h.cmake   ${CMAKE_BINARY_DIR}/include/config.h)
diff --git a/cmake/Makefile.am b/cmake/Makefile.am
index 6fe1a9556be..af3ec4f980d 100644
--- a/cmake/Makefile.am
+++ b/cmake/Makefile.am
@@ -24,6 +24,7 @@ EXTRA_DIST = \
 	dtrace_prelink.cmake \
 	versioninfo.rc.in \
 	mysql_add_executable.cmake  \
+	tags.cmake \
 	install_layout.cmake \
 	build_configurations/mysql_release.cmake \
 	os/Windows.cmake \
diff --git a/cmake/tags.cmake b/cmake/tags.cmake
new file mode 100644
index 00000000000..07c1411a1d6
--- /dev/null
+++ b/cmake/tags.cmake
@@ -0,0 +1,26 @@
+# Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
+# 
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA 
+
+# Generate tag files
+IF(UNIX)
+  ADD_CUSTOM_TARGET (tags
+    COMMAND support-files/build-tags
+    WORKING_DIRECTORY ${CMAKE_SOURCE_DIR}
+  )
+  ADD_CUSTOM_TARGET (ctags
+    COMMAND  ctags -R -f CTAGS
+    WORKING_DIRECTORY ${CMAKE_SOURCE_DIR}
+  )
+ENDIF()
diff --git a/include/mysql_com.h b/include/mysql_com.h
index e4e34141d43..c510c12dbf7 100644
--- a/include/mysql_com.h
+++ b/include/mysql_com.h
@@ -197,7 +197,14 @@ enum enum_server_command
                                                & ~CLIENT_COMPRESS) \
                                                & ~CLIENT_SSL_VERIFY_SERVER_CERT)
 
-#define SERVER_STATUS_IN_TRANS     1	/* Transaction has started */
+/**
+  Is raised when a multi-statement transaction
+  has been started, either explicitly, by means
+  of BEGIN or COMMIT AND CHAIN, or
+  implicitly, by the first transactional
+  statement, when autocommit=off.
+*/
+#define SERVER_STATUS_IN_TRANS     1
 #define SERVER_STATUS_AUTOCOMMIT   2	/* Server in auto_commit mode */
 #define SERVER_MORE_RESULTS_EXISTS 8    /* Multi query - next query exists */
 #define SERVER_QUERY_NO_GOOD_INDEX_USED 16
diff --git a/mysql-test/include/check_concurrent_insert.inc b/mysql-test/include/check_concurrent_insert.inc
new file mode 100644
index 00000000000..6a9ada65562
--- /dev/null
+++ b/mysql-test/include/check_concurrent_insert.inc
@@ -0,0 +1,96 @@
+#
+# SUMMARY
+#   Check if statement reading table '$table' allows concurrent
+#   inserts in it.
+#
+# PARAMETERS 
+#   $table         Table in which concurrent inserts should be allowed.
+#   $con_aux1      Name of the first auxiliary connection to be used by this
+#                  script.
+#   $con_aux2      Name of the second auxiliary connection to be used by this
+#                  script.
+#   $statement     Statement to be checked.
+#   $restore_table Table which might be modified affected by statement to be
+#                  checked and thus needs backing up before its execution
+#                  and restoring after it (can be empty).
+#
+# EXAMPLE
+#    lock_sync.test
+#
+--disable_result_log
+--disable_query_log
+
+# Reset DEBUG_SYNC facility for safety.
+set debug_sync= "RESET";
+
+if (`SELECT '$restore_table' <> ''`)
+{
+--eval create table t_backup select * from $restore_table;
+}
+
+connection $con_aux1;
+set debug_sync='after_lock_tables_takes_lock SIGNAL parked WAIT_FOR go';
+--send_eval $statement;
+
+connection $con_aux2;
+set debug_sync='now WAIT_FOR parked';
+--send_eval insert into $table values (0);
+
+--enable_result_log
+--enable_query_log
+connection default;
+# Wait until concurrent insert is successfully executed while
+# statement being checked has its tables locked.
+# We use wait_condition.inc instead of simply executing
+# concurrent insert here in order to avoid deadlocks if test
+# fails and timing out instead.
+let $wait_condition=
+  select count(*) = 0 from information_schema.processlist
+  where info = "insert into $table values (0)";
+--source include/wait_condition.inc
+
+--disable_result_log
+--disable_query_log
+
+if ($success)
+{
+# Apparently concurrent insert was successfully executed.
+# To be safe against wait_condition.inc succeeding due to
+# races let us first reap concurrent insert to ensure that
+# it has really been successfully executed.
+connection $con_aux2;
+--reap
+connection default;
+set debug_sync= 'now SIGNAL go';
+connection $con_aux1;
+--reap
+connection default;
+--echo Success: '$statement' allows concurrent inserts into '$table'.
+}
+if (!$success)
+{
+# Waiting has timed out. Apparently concurrent insert was blocked.
+# So to be able to continue we need to end our statement first.
+set debug_sync= 'now SIGNAL go';
+connection $con_aux1;
+--reap
+connection $con_aux2;
+--reap
+connection default;
+--echo Error: '$statement' doesn't allow concurrent inserts into '$table'!
+}
+
+--eval delete from $table where i = 0;
+
+if (`SELECT '$restore_table' <> ''`)
+{
+--eval truncate table $restore_table;
+--eval insert into $restore_table select * from t_backup;
+drop table t_backup;
+}
+
+# Clean-up. Reset DEBUG_SYNC facility after use.
+set debug_sync= "RESET";
+
+--enable_result_log
+--enable_query_log
diff --git a/mysql-test/include/check_no_concurrent_insert.inc b/mysql-test/include/check_no_concurrent_insert.inc
new file mode 100644
index 00000000000..278ffeffb1e
--- /dev/null
+++ b/mysql-test/include/check_no_concurrent_insert.inc
@@ -0,0 +1,81 @@
+#
+# SUMMARY
+#   Check that statement reading table '$table' doesn't allow concurrent
+#   inserts in it.
+#
+# PARAMETERS 
+#   $table         Table in which concurrent inserts should be disallowed.
+#   $con_aux1      Name of the first auxiliary connection to be used by this
+#                  script.
+#   $con_aux2      Name of the second auxiliary connection to be used by this
+#                  script.
+#   $statement     Statement to be checked.
+#   $restore_table Table which might be modified affected by statement to be
+#                  checked and thus needs backing up before its execution
+#                  and restoring after it (can be empty).
+#
+# EXAMPLE
+#    lock_sync.test
+#
+--disable_result_log
+--disable_query_log
+
+# Reset DEBUG_SYNC facility for safety.
+set debug_sync= "RESET";
+
+if (`SELECT '$restore_table' <> ''`)
+{
+--eval create table t_backup select * from $restore_table;
+}
+
+connection $con_aux1;
+set debug_sync='after_lock_tables_takes_lock SIGNAL parked WAIT_FOR go';
+--send_eval $statement;
+
+connection $con_aux2;
+set debug_sync='now WAIT_FOR parked';
+--send_eval insert into $table values (0);
+
+--enable_result_log
+--enable_query_log
+connection default;
+# Wait until concurrent insert is successfully blocked because
+# of our statement.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Table lock" and info = "insert into $table values (0)";
+--source include/wait_condition.inc
+
+--disable_result_log
+--disable_query_log
+
+set debug_sync= 'now SIGNAL go';
+connection $con_aux1;
+--reap
+connection $con_aux2;
+--reap
+connection default;
+
+if ($success)
+{
+--echo Success: '$statement' doesn't allow concurrent inserts into '$table'.
+}
+if (!$success)
+{
+--echo Error: '$statement' allows concurrent inserts into '$table'!
+}
+
+--eval delete from $table where i = 0;
+
+if (`SELECT '$restore_table' <> ''`)
+{
+--eval truncate table $restore_table;
+--eval insert into $restore_table select * from t_backup;
+drop table t_backup;
+}
+
+# Clean-up. Reset DEBUG_SYNC facility after use.
+set debug_sync= "RESET";
+
+--enable_result_log
+--enable_query_log
diff --git a/mysql-test/include/check_no_row_lock.inc b/mysql-test/include/check_no_row_lock.inc
new file mode 100644
index 00000000000..958161b9b7f
--- /dev/null
+++ b/mysql-test/include/check_no_row_lock.inc
@@ -0,0 +1,71 @@
+#
+# SUMMARY
+#   Check if statement affecting or reading table '$table' doesn't
+#   take any kind of locks on its rows.
+#
+# PARAMETERS 
+#   $table      Table for which presence of row locks should be checked.
+#   $con_aux    Name of auxiliary connection to be used by this script.
+#   $statement  Statement to be checked.
+#
+# EXAMPLE
+#    innodb_mysql_lock2.test
+#
+--disable_result_log
+--disable_query_log
+
+connection default;
+begin;
+--eval select * from $table for update;
+
+connection $con_aux;
+begin;
+--send_eval $statement;
+
+--enable_result_log
+--enable_query_log
+
+connection default;
+# Wait until statement is successfully executed while
+# all rows in table are X-locked. This means that it
+# does not acquire any row locks.
+# We use wait_condition.inc instead of simply executing
+# statement here in order to avoid deadlocks if test
+# fails and timing out instead.
+let $wait_condition=
+  select count(*) = 0 from information_schema.processlist
+  where info = "$statement";
+--source include/wait_condition.inc
+
+--disable_result_log
+--disable_query_log
+
+if ($success)
+{
+# Apparently statement was successfully executed and thus it
+# has not required any row locks.
+# To be safe against wait_condition.inc succeeding due to
+# races let us first reap the statement being checked to
+# ensure that it has been successfully executed.
+connection $con_aux;
+--reap
+rollback;
+connection default;
+rollback;
+--echo Success: '$statement' doesn't take row locks on '$table'.
+}
+if (!$success)
+{
+# Waiting has timed out. Apparently statement was blocked on
+# some row lock. So to be able to continue we need to unlock
+# rows first.
+rollback;
+connection $con_aux;
+--reap
+rollback;
+connection default;
+--echo Error: '$statement' takes some row locks on '$table'!
+}
+
+--enable_result_log
+--enable_query_log
diff --git a/mysql-test/include/check_shared_row_lock.inc b/mysql-test/include/check_shared_row_lock.inc
new file mode 100644
index 00000000000..efc7e13b3aa
--- /dev/null
+++ b/mysql-test/include/check_shared_row_lock.inc
@@ -0,0 +1,61 @@
+#
+# SUMMARY
+#   Check if statement reading table '$table' takes shared locks
+#   on some of its rows.
+#
+# PARAMETERS 
+#   $table          Table for which presence of row locks should be checked.
+#   $con_aux        Name of auxiliary connection to be used by this script.
+#   $statement      Statement to be checked.
+#   $wait_statement Sub-statement which is supposed to acquire locks (should
+#                   be the same as $statement for ordinary statements).
+#
+# EXAMPLE
+#    innodb_mysql_lock2.test
+#
+--disable_result_log
+--disable_query_log
+
+connection default;
+begin;
+--eval select * from $table for update;
+
+connection $con_aux;
+begin;
+--send_eval $statement;
+
+--enable_result_log
+--enable_query_log
+
+connection default;
+# Wait until statement is successfully blocked because
+# all rows in table are X-locked. This means that at
+# least it acquires S-locks on some of rows.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state in ("Sending data","statistics", "preparing") and
+        info = "$wait_statement";
+--source include/wait_condition.inc
+
+--disable_result_log
+--disable_query_log
+
+rollback;
+
+connection $con_aux;
+--reap
+rollback;
+
+connection default;
+--enable_result_log
+--enable_query_log
+
+if ($success)
+{
+--echo Success: '$statement' takes shared row locks on '$table'.
+}
+
+if (!$success)
+{
+--echo Error: '$statement' hasn't taken shared row locks on '$table'!
+}
diff --git a/mysql-test/r/bug39022.result b/mysql-test/r/bug39022.result
index 5963709aa2a..75899ed686b 100644
--- a/mysql-test/r/bug39022.result
+++ b/mysql-test/r/bug39022.result
@@ -12,7 +12,7 @@ INSERT INTO t2 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
 START TRANSACTION;
 # in thread2
 REPLACE INTO t2 VALUES (-17);
-SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d);
+SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE;
 d
 # in thread1
 REPLACE INTO t1(a,b) VALUES (67,20);
@@ -21,10 +21,10 @@ COMMIT;
 START TRANSACTION;
 REPLACE INTO t1(a,b) VALUES (65,-50);
 REPLACE INTO t2 VALUES (-91);
-SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d);
+SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE;
 # in thread1
 # should not crash
-SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d);
+SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE;
 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
 # in thread2
 d
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index eb1437414e7..e37f9d580ba 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -1977,3 +1977,59 @@ CREATE TABLE t1 LIKE t2;
 ERROR 42S01: Table 't1' already exists
 DROP TABLE t2;
 DROP TABLE t1;
+#
+# Bug #48800 CREATE TABLE t...SELECT fails if t is a 
+#            temporary table
+#
+CREATE TEMPORARY TABLE t1 (a INT);
+CREATE TABLE t1 (a INT);
+CREATE TEMPORARY TABLE t2 (a INT);
+CREATE VIEW t2 AS SELECT 1;
+CREATE TABLE t3 (a INT);
+CREATE TEMPORARY TABLE t3 SELECT 1;
+CREATE TEMPORARY TABLE t4 (a INT);
+CREATE TABLE t4 AS SELECT 1;
+DROP TEMPORARY TABLE t1, t2, t3, t4;
+DROP TABLE t1, t3, t4;
+DROP VIEW t2;
+#
+# Bug #49193 CREATE TABLE reacts differently depending 
+#            on whether data is selected or not
+#
+CREATE TEMPORARY TABLE t2 (ID INT);
+INSERT INTO t2 VALUES (1),(2),(3);
+CREATE TEMPORARY TABLE t1 (ID INT);
+CREATE TABLE IF NOT EXISTS t1 (ID INT);
+INSERT INTO t1 SELECT * FROM t2;
+SELECT * FROM t1;
+ID
+1
+2
+3
+DROP TEMPORARY TABLE t1;
+SELECT * FROM t1;
+ID
+DROP TABLE t1;
+CREATE TEMPORARY TABLE t1 (ID INT);
+CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
+SELECT * FROM t1;
+ID
+DROP TEMPORARY TABLE t1;
+SELECT * FROM t1;
+ID
+1
+2
+3
+DROP TABLE t1;
+CREATE TEMPORARY TABLE t1 (ID INT);
+CREATE TABLE t1 SELECT * FROM t2;
+SELECT * FROM t1;
+ID
+DROP TEMPORARY TABLE t1;
+SELECT * FROM t1;
+ID
+1
+2
+3
+DROP TABLE t1;
+DROP TEMPORARY TABLE t2;
diff --git a/mysql-test/r/innodb-lock.result b/mysql-test/r/innodb-lock.result
index ab7e9aa7b25..41f308788a2 100644
--- a/mysql-test/r/innodb-lock.result
+++ b/mysql-test/r/innodb-lock.result
@@ -27,9 +27,10 @@ commit;
 drop table t1;
 #
 # Old lock method (where LOCK TABLE was ignored by InnoDB) no longer
-# works due to fix for bugs  #46272 "MySQL 5.4.4, new MDL: unnecessary
-# deadlock" and bug #37346 "innodb does not detect deadlock between
-# update and alter table".
+# works when LOCK TABLE ... WRITE is used due to fix for bugs #46272
+# "MySQL 5.4.4, new MDL: unnecessary and bug #37346 "innodb does not
+# detect deadlock between update and alter table". But it still works
+# for LOCK TABLE ... READ.
 #
 set @@innodb_table_locks=0;
 create table t1 (id integer primary key, x integer) engine=INNODB;
@@ -61,4 +62,30 @@ commit;
 # Reap LOCK TABLE.
 unlock tables;
 # Connection 'con1'.
+select * from t1 where id = 0 for update;
+id	x
+0	1
+# Connection 'con2'.
+# The below statement should not be blocked as LOCK TABLES ... READ
+# does not take strong SQL-level lock on t1. SELECTs which do not
+# conflict with transaction in the first connections should not be
+# blocked.
+lock table t1 read;
+select * from t1;
+id	x
+0	1
+1	1
+2	2
+select * from t1 where id = 1 lock in share mode;
+id	x
+1	1
+unlock tables;
+select * from t1;
+id	x
+0	1
+1	1
+2	2
+commit;
+# Connection 'con1'.
+commit;
 drop table t1;
diff --git a/mysql-test/r/innodb_mysql_lock.result b/mysql-test/r/innodb_mysql_lock.result
index 375ae8aeb12..95adf712cb4 100644
--- a/mysql-test/r/innodb_mysql_lock.result
+++ b/mysql-test/r/innodb_mysql_lock.result
@@ -86,3 +86,33 @@ release_lock('bug42147_lock')
 UNLOCK TABLES;
 # Connection 1
 DROP TABLE t1;
+#
+# Bug#53798 OPTIMIZE TABLE breaks repeatable read
+#
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (a INT) engine=innodb;
+INSERT INTO t1 VALUES (1), (2), (3);
+# Connection con1
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
+SELECT * FROM t1;
+a
+1
+2
+3
+# Connection default
+# This should block
+# Sending:
+OPTIMIZE TABLE t1;
+# Connection con1
+SELECT * FROM t1;
+a
+1
+2
+3
+COMMIT;
+# Connection default
+# Reaping OPTIMIZE TABLE t1
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
+test.t1	optimize	status	OK
+DROP TABLE t1;
diff --git a/mysql-test/r/innodb_mysql_lock2.result b/mysql-test/r/innodb_mysql_lock2.result
new file mode 100644
index 00000000000..aed704e6b3e
--- /dev/null
+++ b/mysql-test/r/innodb_mysql_lock2.result
@@ -0,0 +1,564 @@
+#
+# 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 thanks to how MyISAM works SELECT
+# will see version of the table prior to concurrent insert.
+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 modifes 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;
diff --git a/mysql-test/r/lock_sync.result b/mysql-test/r/lock_sync.result
index 18f3f6bc1a7..e6265f1cb5e 100644
--- a/mysql-test/r/lock_sync.result
+++ b/mysql-test/r/lock_sync.result
@@ -1,4 +1,596 @@
 #
+# Test how we handle locking in various cases when
+# we read data from MyISAM tables.
+#
+# In this test we mostly check that the SQL-layer correctly
+# determines the type of thr_lock.c lock for a table being
+# read.
+# I.e. that it disallows concurrent inserts when the statement
+# is going to be written to the binary log and therefore
+# should be serialized, and allows concurrent inserts when
+# such serialization is not necessary (e.g. when 
+# the statement is not written to binary log).
+#
+# Force concurrent inserts to be performed even if the table
+# has gaps. This allows to simplify clean up in scripts
+# used below (instead of backing up table being inserted
+# into and then restoring it from backup at the end of the
+# script we can simply delete rows which were inserted).
+set @old_concurrent_insert= @@global.concurrent_insert;
+set @@global.concurrent_insert= 2;
+select @@global.concurrent_insert;
+@@global.concurrent_insert
+ALWAYS
+# 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);
+insert into t1 values (1), (2), (3), (4), (5);
+create table t2 (j int primary key);
+insert into t2 values (1), (2), (3), (4), (5);
+create table t3 (k int primary key);
+insert into t3 values (1), (2), (3);
+create table t4 (l int primary key);
+insert into t4 values (1);
+create table t5 (l int primary key);
+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 the scripts
+# called below.
+#
+# Switch to connection 'con1'.
+# Cache all functions used in the tests below so statements
+# calling them won't need to open and lock mysql.proc table
+# and we can assume that each statement locks its tables
+# once during its execution.
+show create procedure p1;
+show create procedure p2;
+show create function f1;
+show create function f2;
+show create function f3;
+show create function f4;
+show create function f5;
+show create function f6;
+show create function f7;
+show create function f8;
+show create function f9;
+show create function f10;
+show create function f11;
+show create function f12;
+show create function f13;
+show create function f14;
+show create function f15;
+# Switch back to connection 'default'.
+#
+# 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 thanks to how MyISAM works SELECT
+# will see version of the table prior to concurrent insert.
+Success: 'select * from t1' allows concurrent inserts into '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' doesn't allow concurrent inserts into 't1'.
+#
+# 1.3 Multi-DELETE statement.
+#
+# The above is true for this statement as well.
+Success: 'delete t2 from t1, t2 where i = j' doesn't allow concurrent inserts into '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.
+lock table t1 write;
+# Switching to connection 'con1'.
+# This statement should not be blocked.
+describe t1;
+# Switching to connection 'default'.
+unlock tables;
+#
+# 1.5 SHOW statements.
+# 
+# The above is true for SHOW statements as well.
+lock table t1 write;
+# Switching to connection 'con1'.
+# These statements should not be blocked.
+show keys from t1;
+# Switching to connection 'default'.
+unlock tables;
+#
+# 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).
+Success: 'call p1((select i + 5 from t1 where i = 1))' allows concurrent inserts into 't1'.
+#
+# 2.2 CREATE TABLE with a subquery.
+#
+# Has to take a strong lock on 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 select * from t1' doesn't allow concurrent inserts into 't1'.
+drop table t0;
+Success: 'create table t0 select j from t2 where j in (select i from t1)' doesn't allow concurrent inserts into '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)' doesn't allow concurrent inserts into '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)' doesn't allow concurrent inserts into 't1'.
+#
+# 2.5 DO with a subquery.
+#
+# A strong lock is not necessary as it is not logged.
+Success: 'do (select i from t1 where i = 1)' allows concurrent inserts into 't1'.
+#
+# 2.6 INSERT with a subquery.
+#
+# Has to take a strong lock on the table being read as
+# this statement is written to the binary log and therefore
+# should be serialized with concurrent inserts.
+Success: 'insert into t2 select i+5 from t1' doesn't allow concurrent inserts into 't1'.
+Success: 'insert into t2 values ((select i+5 from t1 where i = 4))' doesn't allow concurrent inserts into '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)' doesn't allow concurrent inserts into 't1'.
+#
+# 2.8 REPLACE with a subquery.
+# 
+# Same is true for this statement as well.
+Success: 'replace into t2 select i+5 from t1' doesn't allow concurrent inserts into 't1'.
+Success: 'replace into t2 values ((select i+5 from t1 where i = 4))' doesn't allow concurrent inserts into 't1'.
+#
+# 2.9 SELECT with a subquery.
+#
+# Strong locks are not necessary as this statement is not written
+# to the binary log and thanks to how MyISAM works this statement
+# sees a version of the table prior to the concurrent insert.
+Success: 'select * from t2 where j in (select i from t1)' allows concurrent inserts into 't1'.
+#
+# 2.10 SET with a subquery.
+#
+# The same is true for this statement as well.
+Success: 'set @a:= (select i from t1 where i = 1)' allows concurrent inserts into 't1'.
+#
+# 2.11 SHOW with a subquery.
+# 
+# And for this statement too.
+Success: 'show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1)' allows concurrent inserts into 't1'.
+Success: 'show columns from t2 where (select i from t1 where i = 1)' allows concurrent inserts into 't1'.
+#
+# 2.12 UPDATE with a subquery.
+#
+# Has to take a strong lock on the table being read as
+# this statement is written to the binary log and therefore
+# should be serialized with concurrent inserts.
+Success: 'update t2 set j= j-10 where j in (select i from t1)' doesn't allow concurrent inserts into '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)' doesn't allow concurrent inserts into '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
+# an old version of the table is accessible thanks to how MyISAM
+# handles concurrent insert, no locking is necessary.
+Success: 'select * from v1' allows concurrent inserts into 't1'.
+Success: 'select * from v2' allows concurrent inserts into 't1'.
+Success: 'select * from t2 where j in (select i from v1)' allows concurrent inserts into 't1'.
+Success: 'select * from t3 where k in (select j from v2)' allows concurrent inserts into '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 strong locks on the data read.
+Success: 'update t2 set j= j-10 where j in (select i from v1)' doesn't allow concurrent inserts into 't1'.
+Success: 'update t3 set k= k-10 where k in (select j from v2)' doesn't allow concurrent inserts into 't1'.
+Success: 'update t2, v1 set j= j-10 where j = i' doesn't allow concurrent inserts into 't1'.
+Success: 'update v2 set j= j-10 where j = 3' doesn't allow concurrent inserts into '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 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'.
+#
+# 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 strong lock on the data
+# it reads.
+Success: 'insert into t2 values (f1() + 5)' doesn't allow concurrent inserts into '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, a strong lock on the data read
+# should be taken.
+Success: 'select f2()' doesn't allow concurrent inserts into 't1'.
+Success: 'set @a:= f2()' doesn't allow concurrent inserts into '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 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'.
+#
+# 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 a strong lock on the data
+# it reads.
+Success: 'insert into t2 values (f3() + 5)' doesn't allow concurrent inserts into 't1'.
+Success: 'insert into t2 values (f4() + 6)' doesn't allow concurrent inserts into '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 a strong lock.
+Success: 'select f5()' doesn't allow concurrent inserts into 't1'.
+Success: 'set @a:= f5()' doesn't allow concurrent inserts into '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 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'.
+#
+# 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 a strong lock on
+# the table it reads.
+Success: 'insert into t3 values (f6() + 5)' doesn't allow concurrent inserts into 't1'.
+Success: 'insert into t3 values (f7() + 5)' doesn't allow concurrent inserts into '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 strong locks.
+Success: 'select f8()' doesn't allow concurrent inserts into 't1'.
+Success: 'select f9()' doesn't allow concurrent inserts into 't1'.
+#
+# 4.10 SELECT which uses a 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 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'.
+#
+# 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 strong locks on data it reads.
+Success: 'insert into t2 values (f10() + 5)' doesn't allow concurrent inserts into '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, read should take a strong lock.
+Success: 'select f11()' doesn't allow concurrent inserts into '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 strong locks for tables used in
+# the subquery.
+Success: 'select f12((select i+10 from t1 where i=1))' allows concurrent inserts into '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 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'.
+#
+# 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
+# strong locks on the data it reads.
+Success: 'call p2(@a)' allows concurrent inserts into 't1'.
+#
+# 5.2 Function that modifes 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 strong locks on data.
+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'.
+#
+# 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 strong locks on data it reads.
+Success: 'insert into t2 values (f15()+5)' doesn't allow concurrent inserts into '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 strong locks on the data
+# it reads.
+Success: 'insert into t4 values (2)' doesn't allow concurrent inserts into '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' doesn't allow concurrent inserts into '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' doesn't allow concurrent inserts into '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)' doesn't allow concurrent inserts into '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' doesn't allow concurrent inserts into '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;
+set @@global.concurrent_insert= @old_concurrent_insert;
+#
 # Test for bug #45143 "All connections hang on concurrent ALTER TABLE".
 #
 # Concurrent execution of statements which required weak write lock
diff --git a/mysql-test/t/bug39022.test b/mysql-test/t/bug39022.test
index 268b207e0e5..6056dbf0e7b 100644
--- a/mysql-test/t/bug39022.test
+++ b/mysql-test/t/bug39022.test
@@ -24,7 +24,7 @@ START TRANSACTION;
 connection thread2;
 --echo # in thread2
 REPLACE INTO t2 VALUES (-17);
-SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d);
+SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE;
 
 connection thread1;
 --echo # in thread1
@@ -37,14 +37,14 @@ START TRANSACTION;
 REPLACE INTO t1(a,b) VALUES (65,-50);
 REPLACE INTO t2 VALUES (-91);
 send;
-SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); #waits
+SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE; #waits
 
 connection thread1;
 --echo # in thread1
 
 --echo # should not crash
 --error ER_LOCK_DEADLOCK
-SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); #crashes
+SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE; #crashes
 
 connection thread2;
 --echo # in thread2
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index e0a6fde1381..383ba98ae6d 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -1668,3 +1668,66 @@ CREATE TABLE t1 LIKE t2;
 
 DROP TABLE t2;
 DROP TABLE t1;
+
+--echo #
+--echo # Bug #48800 CREATE TABLE t...SELECT fails if t is a 
+--echo #            temporary table
+--echo #
+
+CREATE TEMPORARY TABLE t1 (a INT);
+CREATE TABLE t1 (a INT);
+
+CREATE TEMPORARY TABLE t2 (a INT);
+CREATE VIEW t2 AS SELECT 1;
+
+CREATE TABLE t3 (a INT);
+CREATE TEMPORARY TABLE t3 SELECT 1; 
+
+CREATE TEMPORARY TABLE t4 (a INT);
+CREATE TABLE t4 AS SELECT 1;
+
+DROP TEMPORARY TABLE t1, t2, t3, t4;
+DROP TABLE t1, t3, t4;
+DROP VIEW t2;
+
+--echo #
+--echo # Bug #49193 CREATE TABLE reacts differently depending 
+--echo #            on whether data is selected or not
+--echo #
+
+CREATE TEMPORARY TABLE t2 (ID INT);
+INSERT INTO t2 VALUES (1),(2),(3);
+
+# Case 1 -- did not fail
+CREATE TEMPORARY TABLE t1 (ID INT);
+CREATE TABLE IF NOT EXISTS t1 (ID INT);
+INSERT INTO t1 SELECT * FROM t2;
+SELECT * FROM t1;
+DROP TEMPORARY TABLE t1;
+SELECT * FROM t1;
+
+DROP TABLE t1;
+
+# Case 2 -- The DROP TABLE t1 failed with 
+#  Table 'test.t1' doesn't exist in the SELECT *
+# as the (permanent) table was not created
+CREATE TEMPORARY TABLE t1 (ID INT);
+CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
+SELECT * FROM t1;
+DROP TEMPORARY TABLE t1;
+SELECT * FROM t1;
+
+DROP TABLE t1;
+
+# Case 3 -- The CREATE TABLE failed with
+#  Table 't1' already exists
+CREATE TEMPORARY TABLE t1 (ID INT);
+CREATE TABLE t1 SELECT * FROM t2;
+SELECT * FROM t1;
+DROP TEMPORARY TABLE t1;
+SELECT * FROM t1;
+ 
+DROP TABLE t1;
+ 
+DROP TEMPORARY TABLE t2;
+
diff --git a/mysql-test/t/innodb-lock.test b/mysql-test/t/innodb-lock.test
index d2f630ccaba..05df3615822 100644
--- a/mysql-test/t/innodb-lock.test
+++ b/mysql-test/t/innodb-lock.test
@@ -58,9 +58,10 @@ drop table t1;
 
 --echo #
 --echo # Old lock method (where LOCK TABLE was ignored by InnoDB) no longer
---echo # works due to fix for bugs  #46272 "MySQL 5.4.4, new MDL: unnecessary
---echo # deadlock" and bug #37346 "innodb does not detect deadlock between
---echo # update and alter table".
+--echo # works when LOCK TABLE ... WRITE is used due to fix for bugs #46272
+--echo # "MySQL 5.4.4, new MDL: unnecessary and bug #37346 "innodb does not
+--echo # detect deadlock between update and alter table". But it still works
+--echo # for LOCK TABLE ... READ.
 --echo #
 
 set @@innodb_table_locks=0;
@@ -102,6 +103,26 @@ unlock tables;
 
 --echo # Connection 'con1'.
 connection con1;
+
+select * from t1 where id = 0 for update;
+
+--echo # Connection 'con2'.
+connection con2;
+--echo # The below statement should not be blocked as LOCK TABLES ... READ
+--echo # does not take strong SQL-level lock on t1. SELECTs which do not
+--echo # conflict with transaction in the first connections should not be
+--echo # blocked.
+lock table t1 read;
+select * from t1;
+select * from t1 where id = 1 lock in share mode;
+unlock tables;
+select * from t1;
+commit;
+
+--echo # Connection 'con1'.
+connection con1;
+commit;
+
 drop table t1;
 
 # End of 4.1 tests
diff --git a/mysql-test/t/innodb_mysql_lock.test b/mysql-test/t/innodb_mysql_lock.test
index 6469ef2d229..36d09b4c411 100644
--- a/mysql-test/t/innodb_mysql_lock.test
+++ b/mysql-test/t/innodb_mysql_lock.test
@@ -170,6 +170,45 @@ connection default;
 disconnect con2;
 DROP TABLE t1;
 
+
+--echo #
+--echo # Bug#53798 OPTIMIZE TABLE breaks repeatable read
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (a INT) engine=innodb;
+INSERT INTO t1 VALUES (1), (2), (3);
+
+--echo # Connection con1
+connect (con1, localhost, root);
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
+SELECT * FROM t1;
+
+--echo # Connection default
+connection default;
+--echo # This should block
+--echo # Sending:
+--send OPTIMIZE TABLE t1
+
+--echo # Connection con1
+connection con1;
+let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
+  WHERE state='Waiting for table' AND info='OPTIMIZE TABLE t1';
+--source include/wait_condition.inc
+SELECT * FROM t1;
+COMMIT;
+
+--echo # Connection default
+connection default;
+--echo # Reaping OPTIMIZE TABLE t1
+--reap
+disconnect con1;
+DROP TABLE t1;
+
+
 # Check that all connections opened by test cases in this file are really
 # gone so execution of other tests won't be affected by their presence.
 --source include/wait_until_count_sessions.inc
diff --git a/mysql-test/t/innodb_mysql_lock2.test b/mysql-test/t/innodb_mysql_lock2.test
new file mode 100644
index 00000000000..5111d56225a
--- /dev/null
+++ b/mysql-test/t/innodb_mysql_lock2.test
@@ -0,0 +1,765 @@
+# This test covers behavior for InnoDB tables.
+--source include/have_innodb.inc
+# This test requires statement/mixed mode binary logging.
+# Row-based mode puts weaker serializability requirements
+# so weaker locks are acquired for it.
+--source include/have_binlog_format_mixed_or_statement.inc
+# Save the initial number of concurrent sessions.
+--source include/count_sessions.inc
+
+--echo #
+--echo # Test how do we handle locking in various cases when
+--echo # we read data from InnoDB tables.
+--echo #
+--echo # In fact by performing this test we check two things:
+--echo # 1) That SQL-layer correctly determine type of thr_lock.c
+--echo #    lock to be acquired/passed to InnoDB engine.
+--echo # 2) That InnoDB engine correctly interprets this lock
+--echo #    type and takes necessary row locks or does not
+--echo #    take them if they are not necessary.
+--echo #
+
+--echo # This test makes sense only in REPEATABLE-READ mode as
+--echo # in SERIALIZABLE mode all statements that read data take
+--echo # shared lock on them to enforce its semantics.
+select @@session.tx_isolation;
+
+--echo # Prepare playground by creating tables, views,
+--echo # routines and triggers used in tests.
+connect (con1, localhost, root,,);
+connection default;
+--disable_warnings
+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;
+--enable_warnings
+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);
+delimiter |;
+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|
+delimiter ;|
+
+--echo #
+--echo # Set common variables to be used by scripts called below.
+--echo #
+let $con_aux= con1; 
+let $table= t1;
+
+
+--echo #
+--echo # 1. Statements that read tables and do not use subqueries.
+--echo #
+
+--echo #
+--echo # 1.1 Simple SELECT statement.
+--echo #
+--echo # No locks are necessary as this statement won't be written
+--echo # to the binary log and thanks to how MyISAM works SELECT
+--echo # will see version of the table prior to concurrent insert.
+let $statement= select * from t1;
+--source include/check_no_row_lock.inc
+
+--echo #
+--echo # 1.2 Multi-UPDATE statement.
+--echo #
+--echo # Has to take shared locks on rows in the table being read as this
+--echo # statement will be written to the binary log and therefore should
+--echo # be serialized with concurrent statements.
+let $statement= update t2, t1 set j= j - 1 where i = j;
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 1.3 Multi-DELETE statement.
+--echo #
+--echo # The above is true for this statement as well.
+let $statement= delete t2 from t1, t2 where i = j;
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 1.4 DESCRIBE statement.
+--echo #
+--echo # This statement does not really read data from the
+--echo # target table and thus does not take any lock on it.
+--echo # We check this for completeness of coverage.
+let $statement= describe t1;
+--source include/check_no_row_lock.inc
+
+--echo #
+--echo # 1.5 SHOW statements.
+--echo # 
+--echo # The above is true for SHOW statements as well.
+let $statement= show create table t1;
+--source include/check_no_row_lock.inc
+let $statement= show keys from t1;
+--source include/check_no_row_lock.inc
+
+
+--echo #
+--echo # 2. Statements which read tables through subqueries.
+--echo #
+
+--echo #
+--echo # 2.1 CALL with a subquery.
+--echo # 
+--echo # A strong lock is not necessary as this statement is not
+--echo # written to the binary log as a whole (it is written
+--echo # statement-by-statement) and thanks to MVCC we can always get
+--echo # versions of rows prior to the update that has locked them.
+--echo # But in practice InnoDB does locking reads for all statements
+--echo # other than SELECT (unless it is a READ-COMITTED mode or
+--echo # innodb_locks_unsafe_for_binlog is ON).
+let $statement= call p1((select i + 5 from t1 where i = 1));
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 2.2 CREATE TABLE with a subquery.
+--echo #
+--echo # Has to take shared locks on rows in the table being read as
+--echo # this statement is written to the binary log and therefore
+--echo # should be serialized with concurrent statements.
+let $statement= create table t0 engine=innodb select * from t1;
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+drop table t0;
+let $statement= create table t0 engine=innodb select j from t2 where j in (select i from t1);
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+drop table t0;
+
+--echo #
+--echo # 2.3 DELETE with a subquery.
+--echo #
+--echo # The above is true for this statement as well.
+let $statement= delete from t2 where j in (select i from t1);
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 2.4 MULTI-DELETE with a subquery.
+--echo #
+--echo # Same is true for this statement as well.
+let $statement= delete t2 from t3, t2 where k = j and j in (select i from t1);
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 2.5 DO with a subquery.
+--echo #
+--echo # In theory should not take row locks as it is not logged.
+--echo # In practice InnoDB takes shared row locks.
+let $statement= do (select i from t1 where i = 1);
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 2.6 INSERT with a subquery.
+--echo #
+--echo # Has to take shared locks on rows in the table being read as
+--echo # this statement is written to the binary log and therefore
+--echo # should be serialized with concurrent statements.
+let $statement= insert into t2 select i+5 from t1;
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+let $statement= insert into t2 values ((select i+5 from t1 where i = 4));
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 2.7 LOAD DATA with a subquery.
+--echo # 
+--echo # The above is true for this statement as well.
+let $statement= load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, @b) set j= @b + (select i from t1 where i = 1);
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 2.8 REPLACE with a subquery.
+--echo # 
+--echo # Same is true for this statement as well.
+let $statement= replace into t2 select i+5 from t1;
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+let $statement= replace into t2 values ((select i+5 from t1 where i = 4));
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 2.9 SELECT with a subquery.
+--echo #
+--echo # Locks are not necessary as this statement is not written
+--echo # to the binary log and thanks to MVCC we can always get
+--echo # versions of rows prior to the update that has locked them.
+--echo #
+--echo # Also serves as a test case for bug #46947 "Embedded SELECT
+--echo # without FOR UPDATE is causing a lock".
+let $statement= select * from t2 where j in (select i from t1);
+--source include/check_no_row_lock.inc
+
+--echo #
+--echo # 2.10 SET with a subquery.
+--echo #
+--echo # In theory should not require locking as it is not written
+--echo # to the binary log. In practice InnoDB acquires shared row
+--echo # locks.
+let $statement= set @a:= (select i from t1 where i = 1);
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 2.11 SHOW with a subquery.
+--echo # 
+--echo # Similarly to the previous case, in theory should not require locking
+--echo # as it is not written to the binary log. In practice InnoDB
+--echo # acquires shared row locks.
+let $statement= show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1);
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+let $statement= show columns from t2 where (select i from t1 where i = 1);
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 2.12 UPDATE with a subquery.
+--echo #
+--echo # Has to take shared locks on rows in the table being read as
+--echo # this statement is written to the binary log and therefore
+--echo # should be serialized with concurrent statements.
+let $statement= update t2 set j= j-10 where j in (select i from t1);
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 2.13 MULTI-UPDATE with a subquery.
+--echo #
+--echo # Same is true for this statement as well.
+let $statement= update t2, t3 set j= j -10 where j=k and j in (select i from t1);
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+
+--echo #
+--echo # 3. Statements which read tables through a view.
+--echo #
+
+--echo #
+--echo # 3.1 SELECT statement which uses some table through a view.
+--echo #
+--echo # Since this statement is not written to the binary log
+--echo # and old version of rows are accessible thanks to MVCC,
+--echo # no locking is necessary.
+let $statement= select * from v1;
+--source include/check_no_row_lock.inc
+let $statement= select * from v2;
+--source include/check_no_row_lock.inc
+let $statement= select * from t2 where j in (select i from v1);
+--source include/check_no_row_lock.inc
+let $statement= select * from t3 where k in (select j from v2);
+--source include/check_no_row_lock.inc
+
+--echo #
+--echo # 3.2 Statements which modify a table and use views.
+--echo #
+--echo # Since such statements are going to be written to the binary
+--echo # log they need to be serialized against concurrent statements
+--echo # and therefore should take shared row locks on data read.
+let $statement= update t2 set j= j-10 where j in (select i from v1);
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+let $statement= update t3 set k= k-10 where k in (select j from v2);
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+let $statement= update t2, v1 set j= j-10 where j = i;
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+let $statement= update v2 set j= j-10 where j = 3;
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+
+--echo #
+--echo # 4. Statements which read tables through stored functions.
+--echo #
+
+--echo #
+--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 # 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.
+let $statement= select f1();
+let $wait_statement= select i from t1 where i = 1 into j;
+--source include/check_shared_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
+
+--echo #
+--echo # 4.2 INSERT (or other statement which modifies data) with
+--echo #     a stored function which does not modify data and uses
+--echo #     SELECT.
+--echo #
+--echo # Since such statement is written to the binary log it should
+--echo # be serialized with concurrent statements affecting the data
+--echo # it uses. Therefore it should take row locks on the data
+--echo # it reads.
+let $statement= insert into t2 values (f1() + 5);
+let $wait_statement= select i from t1 where i = 1 into j;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 4.3 SELECT/SET with a stored function which
+--echo #     reads and modifies data.
+--echo #
+--echo # Since a call to such function is written to the binary log,
+--echo # it should be serialized with concurrent statements affecting
+--echo # the data it uses. Hence, row locks on the data read
+--echo # should be taken.
+let $statement= select f2();
+let $wait_statement= select i from t1 where i = 1 into k;
+--source include/check_shared_row_lock.inc
+let $statement= set @a:= f2();
+let $wait_statement= select i from t1 where i = 1 into k;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 4.4. SELECT/SET with a stored function which does not
+--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.
+let $statement= select f3();
+let $wait_statement= $statement;
+--source include/check_shared_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
+let $statement= set @a:= f4();
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 4.5. INSERT (or other statement which modifies data) with
+--echo #      a stored function which does not modify data and reads
+--echo #      the table through a subselect in one of its control
+--echo #      constructs.
+--echo #
+--echo # Since such statement is written to the binary log it should
+--echo # be serialized with concurrent statements affecting data it
+--echo # uses. Therefore it should take row locks on the data
+--echo # it reads.
+let $statement= insert into t2 values (f3() + 5);
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+let $statement= insert into t2 values (f4() + 6);
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 4.6 SELECT/SET which uses a stored function with
+--echo #      DML which reads a table via a subquery.
+--echo #
+--echo # Since call to such function is written to the binary log
+--echo # it should be serialized with concurrent statements.
+--echo # Hence reads should take row locks.
+let $statement= select f5();
+let $wait_statement= insert into t2 values ((select i from t1 where i = 1) + 5);
+--source include/check_shared_row_lock.inc
+let $statement= set @a:= f5();
+let $wait_statement= insert into t2 values ((select i from t1 where i = 1) + 5);
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 4.7 SELECT/SET which uses a stored function which
+--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.
+let $statement= select f6();
+let $wait_statement= select i from v1 where i = 1 into k;
+--source include/check_shared_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
+let $statement= set @a:= f7();
+let $wait_statement= select j from v2 where j = 1 into k;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 4.8 INSERT which uses stored function which
+--echo #     doesn't modify data and reads a table
+--echo #     through a view.
+--echo #
+--echo # Since such statement is written to the binary log and
+--echo # should be serialized with concurrent statements affecting
+--echo # the data it uses. Therefore it should take row locks on
+--echo # the rows it reads.
+let $statement= insert into t3 values (f6() + 5);
+let $wait_statement= select i from v1 where i = 1 into k;
+--source include/check_shared_row_lock.inc
+let $statement= insert into t3 values (f7() + 5);
+let $wait_statement= select j from v2 where j = 1 into k;
+--source include/check_shared_row_lock.inc
+
+
+--echo #
+--echo # 4.9 SELECT which uses a stored function which
+--echo #     modifies data and reads tables through a view.
+--echo #
+--echo # Since a call to such function is written to the binary log
+--echo # it should be serialized with concurrent statements.
+--echo # Hence, reads should take row locks.
+let $statement= select f8();
+let $wait_statement= select i from v1 where i = 1 into k;
+--source include/check_shared_row_lock.inc
+let $statement= select f9();
+let $wait_statement= update v2 set j=j+10 where j=1;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 4.10 SELECT which uses stored function which doesn't modify
+--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.
+let $statement= select f10();
+let $wait_statement= select i from t1 where i = 1 into j;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 4.11 INSERT which uses a stored function which doesn't modify
+--echo #      data and reads a table indirectly, by calling another
+--echo #      function. 
+--echo #
+--echo # Since such 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 row locks on data it reads.
+let $statement= insert into t2 values (f10() + 5);
+let $wait_statement= select i from t1 where i = 1 into j;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 4.12 SELECT which uses a stored function which modifies
+--echo #      data and reads a table indirectly, by calling another
+--echo #      function. 
+--echo #
+--echo # Since a call to such function is written to the binary log
+--echo # it should be serialized from concurrent statements.
+--echo # Hence, reads should take row locks.
+let $statement= select f11();
+let $wait_statement= select i from t1 where i = 1 into j;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 4.13 SELECT that reads a table through a subquery passed
+--echo #      as a parameter to a stored function which modifies
+--echo #      data.
+--echo #
+--echo # Even though a call to this function is written to the
+--echo # binary log, values of its parameters are written as literals.
+--echo # So there is no need to acquire row locks on rows used in 
+--echo # the subquery.
+let $statement= select f12((select i+10 from t1 where i=1));
+--source include/check_no_row_lock.inc
+
+--echo #
+--echo # 4.14 INSERT that reads a table via a subquery passed
+--echo #      as a parameter to a stored function which doesn't
+--echo #      modify data.
+--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 row locks on the data it reads.
+let $statement= insert into t2 values (f13((select i+10 from t1 where i=1)));
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+
+--echo #
+--echo # 5. Statements that read tables through stored procedures.
+--echo #
+
+--echo #
+--echo # 5.1 CALL statement which reads a table via SELECT.
+--echo #
+--echo # Since neither this statement nor its components are
+--echo # written to the binary log, there is no need to take
+--echo # row locks on the data it reads.
+let $statement= call p2(@a);
+--source include/check_no_row_lock.inc
+
+--echo #
+--echo # 5.2 Function that modifes data and uses CALL, 
+--echo #     which reads a table through SELECT.
+--echo #
+--echo # Since a call to such function is written to the binary
+--echo # log, it should be serialized with concurrent statements.
+--echo # Hence, in this case reads should take row locks on data.
+let $statement= select f14();
+let $wait_statement= select i from t1 where i = 1 into p;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--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.
+let $statement= select f15();
+let $wait_statement= select i from t1 where i = 1 into p;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 5.4 INSERT which calls function which doesn't modify data and
+--echo #     uses CALL statement which reads table through SELECT.
+--echo #
+--echo # Since such statement is written to the binary log it should
+--echo # be serialized with concurrent statements affecting data it
+--echo # uses. Therefore it should take row locks on data it reads.
+let $statement= insert into t2 values (f15()+5);
+let $wait_statement= select i from t1 where i = 1 into p;
+--source include/check_shared_row_lock.inc
+
+
+--echo #
+--echo # 6. Statements that use triggers.
+--echo #
+
+--echo #
+--echo # 6.1 Statement invoking a trigger that reads table via SELECT.
+--echo #
+--echo # Since this statement is written to the binary log it should
+--echo # be serialized with concurrent statements affecting the data
+--echo # it uses. Therefore, it should take row locks on the data
+--echo # it reads.
+let $statement= insert into t4 values (2);
+let $wait_statement= select i from t1 where i=1 into k;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 6.2 Statement invoking a trigger that reads table through
+--echo #     a subquery in a control construct.
+--echo #
+--echo # The above is true for this statement as well.
+let $statement= update t4 set l= 2 where l = 1;
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 6.3 Statement invoking a trigger that reads a table through
+--echo #     a view.
+--echo #
+--echo # And for this statement.
+let $statement= delete from t4 where l = 1;
+let $wait_statement= $statement;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 6.4 Statement invoking a trigger that reads a table through
+--echo #     a stored function.
+--echo #
+--echo # And for this statement.
+let $statement= insert into t5 values (2);
+let $wait_statement= select i from t1 where i = 1 into j;
+--source include/check_shared_row_lock.inc
+
+--echo #
+--echo # 6.5 Statement invoking a trigger that reads a table through
+--echo #     stored procedure.
+--echo #
+--echo # And for this statement.
+let $statement= update t5 set l= 2 where l = 1;
+let $wait_statement= select i from t1 where i = 1 into p;
+--source include/check_shared_row_lock.inc
+
+--echo # 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;
+
+# Check that all connections opened by test cases in this file are really
+# gone so execution of other tests won't be affected by their presence.
+--source include/wait_until_count_sessions.inc
diff --git a/mysql-test/t/lock_sync.test b/mysql-test/t/lock_sync.test
index 3cbe3342acc..921ce991652 100644
--- a/mysql-test/t/lock_sync.test
+++ b/mysql-test/t/lock_sync.test
@@ -4,10 +4,10 @@
 --source include/have_debug_sync.inc
 # We need InnoDB to be able use TL_WRITE_ALLOW_WRITE type of locks in our tests.
 --source include/have_innodb.inc
-# The test for Bug#50821 requires binary logging turned on.
-# With binary logging on, sub-queries in DML statements acquire
-# TL_READ_NO_INSERT which was needed to reproduce this deadlock bug.
---source include/have_log_bin.inc
+# This test requires statement/mixed mode binary logging.
+# Row-based mode puts weaker serializability requirements
+# so weaker locks are acquired for it.
+--source include/have_binlog_format_mixed_or_statement.inc
 # Until bug#41971 'Thread state on embedded server is always "Writing to net"'
 # is fixed this test can't be run on embedded version of server.
 --source include/not_embedded.inc
@@ -17,6 +17,818 @@
 
 
 --echo #
+--echo # Test how we handle locking in various cases when
+--echo # we read data from MyISAM tables.
+--echo #
+--echo # In this test we mostly check that the SQL-layer correctly
+--echo # determines the type of thr_lock.c lock for a table being
+--echo # read.
+--echo # I.e. that it disallows concurrent inserts when the statement
+--echo # is going to be written to the binary log and therefore
+--echo # should be serialized, and allows concurrent inserts when
+--echo # such serialization is not necessary (e.g. when 
+--echo # the statement is not written to binary log).
+--echo #
+
+--echo # Force concurrent inserts to be performed even if the table
+--echo # has gaps. This allows to simplify clean up in scripts
+--echo # used below (instead of backing up table being inserted
+--echo # into and then restoring it from backup at the end of the
+--echo # script we can simply delete rows which were inserted).
+set @old_concurrent_insert= @@global.concurrent_insert;
+set @@global.concurrent_insert= 2;
+select @@global.concurrent_insert;
+
+--echo # Prepare playground by creating tables, views,
+--echo # routines and triggers used in tests.
+connect (con1, localhost, root,,);
+connect (con2, localhost, root,,);
+connection default;
+--disable_warnings
+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;
+--enable_warnings
+create table t1 (i int primary key);
+insert into t1 values (1), (2), (3), (4), (5);
+create table t2 (j int primary key);
+insert into t2 values (1), (2), (3), (4), (5);
+create table t3 (k int primary key);
+insert into t3 values (1), (2), (3);
+create table t4 (l int primary key);
+insert into t4 values (1);
+create table t5 (l int primary key);
+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);
+delimiter |;
+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|
+delimiter ;|
+
+--echo #
+--echo # Set common variables to be used by the scripts
+--echo # called below.
+--echo #
+let $con_aux1= con1; 
+let $con_aux2= con2; 
+let $table= t1;
+
+--echo # Switch to connection 'con1'.
+connection con1;
+--echo # Cache all functions used in the tests below so statements
+--echo # calling them won't need to open and lock mysql.proc table
+--echo # and we can assume that each statement locks its tables
+--echo # once during its execution.
+--disable_result_log
+show create procedure p1;
+show create procedure p2;
+show create function f1;
+show create function f2;
+show create function f3;
+show create function f4;
+show create function f5;
+show create function f6;
+show create function f7;
+show create function f8;
+show create function f9;
+show create function f10;
+show create function f11;
+show create function f12;
+show create function f13;
+show create function f14;
+show create function f15;
+--enable_result_log
+--echo # Switch back to connection 'default'.
+connection default;
+
+--echo #
+--echo # 1. Statements that read tables and do not use subqueries.
+--echo #
+
+--echo #
+--echo # 1.1 Simple SELECT statement.
+--echo #
+--echo # No locks are necessary as this statement won't be written
+--echo # to the binary log and thanks to how MyISAM works SELECT
+--echo # will see version of the table prior to concurrent insert.
+let $statement= select * from t1;
+let $restore_table= ;
+--source include/check_concurrent_insert.inc
+
+--echo #
+--echo # 1.2 Multi-UPDATE statement.
+--echo #
+--echo # Has to take shared locks on rows in the table being read as this
+--echo # statement will be written to the binary log and therefore should
+--echo # be serialized with concurrent statements.
+let $statement= update t2, t1 set j= j - 1 where i = j;
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 1.3 Multi-DELETE statement.
+--echo #
+--echo # The above is true for this statement as well.
+let $statement= delete t2 from t1, t2 where i = j;
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 1.4 DESCRIBE statement.
+--echo #
+--echo # This statement does not really read data from the
+--echo # target table and thus does not take any lock on it.
+--echo # We check this for completeness of coverage.
+lock table t1 write;
+--echo # Switching to connection 'con1'.
+connection con1;
+--echo # This statement should not be blocked.
+--disable_result_log
+describe t1;
+--enable_result_log
+--echo # Switching to connection 'default'.
+connection default;
+unlock tables;
+
+--echo #
+--echo # 1.5 SHOW statements.
+--echo # 
+--echo # The above is true for SHOW statements as well.
+lock table t1 write;
+--echo # Switching to connection 'con1'.
+connection con1;
+--echo # These statements should not be blocked.
+# The below test for SHOW CREATE TABLE is disabled until bug 52593
+# "SHOW CREATE TABLE is blocked if table is locked for write by another
+# connection" is fixed.
+--disable_parsing
+show create table t1;
+--enable_parsing
+--disable_result_log
+show keys from t1;
+--enable_result_log
+--echo # Switching to connection 'default'.
+connection default;
+unlock tables;
+
+
+--echo #
+--echo # 2. Statements which read tables through subqueries.
+--echo #
+
+--echo #
+--echo # 2.1 CALL with a subquery.
+--echo # 
+--echo # A strong lock is not necessary as this statement is not
+--echo # written to the binary log as a whole (it is written
+--echo # statement-by-statement).
+let $statement= call p1((select i + 5 from t1 where i = 1));
+let $restore_table= t2;
+--source include/check_concurrent_insert.inc
+
+--echo #
+--echo # 2.2 CREATE TABLE with a subquery.
+--echo #
+--echo # Has to take a strong lock on the table being read as
+--echo # this statement is written to the binary log and therefore
+--echo # should be serialized with concurrent statements.
+let $statement= create table t0 select * from t1;
+let $restore_table= ;
+--source include/check_no_concurrent_insert.inc
+drop table t0;
+let $statement= create table t0 select j from t2 where j in (select i from t1);
+let $restore_table= ;
+--source include/check_no_concurrent_insert.inc
+drop table t0;
+
+--echo #
+--echo # 2.3 DELETE with a subquery.
+--echo #
+--echo # The above is true for this statement as well.
+let $statement= delete from t2 where j in (select i from t1);
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 2.4 MULTI-DELETE with a subquery.
+--echo #
+--echo # Same is true for this statement as well.
+let $statement= delete t2 from t3, t2 where k = j and j in (select i from t1);
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+
+--echo #
+--echo # 2.5 DO with a subquery.
+--echo #
+--echo # A strong lock is not necessary as it is not logged.
+let $statement= do (select i from t1 where i = 1);
+let $restore_table= ;
+--source include/check_concurrent_insert.inc
+
+--echo #
+--echo # 2.6 INSERT with a subquery.
+--echo #
+--echo # Has to take a strong lock on the table being read as
+--echo # this statement is written to the binary log and therefore
+--echo # should be serialized with concurrent inserts.
+let $statement= insert into t2 select i+5 from t1;
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+let $statement= insert into t2 values ((select i+5 from t1 where i = 4));
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 2.7 LOAD DATA with a subquery.
+--echo # 
+--echo # The above is true for this statement as well.
+let $statement= load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, @b) set j= @b + (select i from t1 where i = 1);
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 2.8 REPLACE with a subquery.
+--echo # 
+--echo # Same is true for this statement as well.
+let $statement= replace into t2 select i+5 from t1;
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+let $statement= replace into t2 values ((select i+5 from t1 where i = 4));
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 2.9 SELECT with a subquery.
+--echo #
+--echo # Strong locks are not necessary as this statement is not written
+--echo # to the binary log and thanks to how MyISAM works this statement
+--echo # sees a version of the table prior to the concurrent insert.
+let $statement= select * from t2 where j in (select i from t1);
+let $restore_table= ;
+--source include/check_concurrent_insert.inc
+
+--echo #
+--echo # 2.10 SET with a subquery.
+--echo #
+--echo # The same is true for this statement as well.
+let $statement= set @a:= (select i from t1 where i = 1);
+let $restore_table= ;
+--source include/check_concurrent_insert.inc
+
+--echo #
+--echo # 2.11 SHOW with a subquery.
+--echo # 
+--echo # And for this statement too.
+let $statement= show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1);
+let $restore_table= ;
+--source include/check_concurrent_insert.inc
+let $statement= show columns from t2 where (select i from t1 where i = 1);
+let $restore_table= ;
+--source include/check_concurrent_insert.inc
+
+--echo #
+--echo # 2.12 UPDATE with a subquery.
+--echo #
+--echo # Has to take a strong lock on the table being read as
+--echo # this statement is written to the binary log and therefore
+--echo # should be serialized with concurrent inserts.
+let $statement= update t2 set j= j-10 where j in (select i from t1);
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 2.13 MULTI-UPDATE with a subquery.
+--echo #
+--echo # Same is true for this statement as well.
+let $statement= update t2, t3 set j= j -10 where j=k and j in (select i from t1);
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+
+--echo #
+--echo # 3. Statements which read tables through a view.
+--echo #
+
+--echo #
+--echo # 3.1 SELECT statement which uses some table through a view.
+--echo #
+--echo # Since this statement is not written to the binary log and
+--echo # an old version of the table is accessible thanks to how MyISAM
+--echo # handles concurrent insert, no locking is necessary.
+let $statement= select * from v1;
+let $restore_table= ;
+--source include/check_concurrent_insert.inc
+let $statement= select * from v2;
+let $restore_table= ;
+--source include/check_concurrent_insert.inc
+let $statement= select * from t2 where j in (select i from v1);
+let $restore_table= ;
+--source include/check_concurrent_insert.inc
+let $statement= select * from t3 where k in (select j from v2);
+let $restore_table= ;
+--source include/check_concurrent_insert.inc
+
+--echo #
+--echo # 3.2 Statements which modify a table and use views.
+--echo #
+--echo # Since such statements are going to be written to the binary
+--echo # log they need to be serialized against concurrent statements
+--echo # and therefore should take strong locks on the data read.
+let $statement= update t2 set j= j-10 where j in (select i from v1);
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+let $statement= update t3 set k= k-10 where k in (select j from v2);
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+let $statement= update t2, v1 set j= j-10 where j = i;
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+let $statement= update v2 set j= j-10 where j = 3;
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+
+--echo #
+--echo # 4. Statements which read tables through stored functions.
+--echo #
+
+--echo #
+--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 # 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.
+let $statement= select f1();
+let $restore_table= ;
+--source include/check_no_concurrent_insert.inc
+let $statement= set @a:= f1();
+let $restore_table= ;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 4.2 INSERT (or other statement which modifies data) with
+--echo #     a stored function which does not modify data and uses
+--echo #     SELECT.
+--echo #
+--echo # Since such statement is written to the binary log it should
+--echo # be serialized with concurrent statements affecting the data
+--echo # it uses. Therefore it should take strong lock on the data
+--echo # it reads.
+let $statement= insert into t2 values (f1() + 5);
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 4.3 SELECT/SET with a stored function which
+--echo #     reads and modifies data.
+--echo #
+--echo # Since a call to such function is written to the binary log,
+--echo # it should be serialized with concurrent statements affecting
+--echo # the data it uses. Hence, a strong lock on the data read
+--echo # should be taken.
+let $statement= select f2();
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+let $statement= set @a:= f2();
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 4.4. SELECT/SET with a stored function which does not
+--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.
+let $statement= select f3();
+let $restore_table= ;
+--source include/check_no_concurrent_insert.inc
+let $statement= set @a:= f3();
+let $restore_table= ;
+--source include/check_no_concurrent_insert.inc
+let $statement= select f4();
+let $restore_table= ;
+--source include/check_no_concurrent_insert.inc
+let $statement= set @a:= f4();
+let $restore_table= ;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 4.5. INSERT (or other statement which modifies data) with
+--echo #      a stored function which does not modify data and reads
+--echo #      the table through a subselect in one of its control
+--echo #      constructs.
+--echo #
+--echo # Since such statement is written to the binary log it should
+--echo # be serialized with concurrent statements affecting data it
+--echo # uses. Therefore it should take a strong lock on the data
+--echo # it reads.
+let $statement= insert into t2 values (f3() + 5);
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+let $statement= insert into t2 values (f4() + 6);
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 4.6 SELECT/SET which uses a stored function with
+--echo #      DML which reads a table via a subquery.
+--echo #
+--echo # Since call to such function is written to the binary log
+--echo # it should be serialized with concurrent statements.
+--echo # Hence reads should take a strong lock.
+let $statement= select f5();
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+let $statement= set @a:= f5();
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 4.7 SELECT/SET which uses a stored function which
+--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.
+let $statement= select f6();
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+let $statement= set @a:= f6();
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+let $statement= select f7();
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+let $statement= set @a:= f7();
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 4.8 INSERT which uses stored function which
+--echo #     doesn't modify data and reads a table
+--echo #     through a view.
+--echo #
+--echo # Since such statement is written to the binary log and
+--echo # should be serialized with concurrent statements affecting
+--echo # the data it uses. Therefore it should take a strong lock on
+--echo # the table it reads.
+let $statement= insert into t3 values (f6() + 5);
+let $restore_table= t3;
+--source include/check_no_concurrent_insert.inc
+let $statement= insert into t3 values (f7() + 5);
+let $restore_table= t3;
+--source include/check_no_concurrent_insert.inc
+
+
+--echo #
+--echo # 4.9 SELECT which uses a stored function which
+--echo #     modifies data and reads tables through a view.
+--echo #
+--echo # Since a call to such function is written to the binary log
+--echo # it should be serialized with concurrent statements.
+--echo # Hence, reads should take strong locks.
+let $statement= select f8();
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+let $statement= select f9();
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 4.10 SELECT which uses a stored function which doesn't modify
+--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.
+let $statement= select f10();
+let $restore_table= ;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 4.11 INSERT which uses a stored function which doesn't modify
+--echo #      data and reads a table indirectly, by calling another
+--echo #      function. 
+--echo #
+--echo # Since such 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 data it reads.
+let $statement= insert into t2 values (f10() + 5);
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 4.12 SELECT which uses a stored function which modifies
+--echo #      data and reads a table indirectly, by calling another
+--echo #      function. 
+--echo #
+--echo # Since a call to such function is written to the binary log
+--echo # it should be serialized from concurrent statements.
+--echo # Hence, read should take a strong lock.
+let $statement= select f11();
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 4.13 SELECT that reads a table through a subquery passed
+--echo #      as a parameter to a stored function which modifies
+--echo #      data.
+--echo #
+--echo # Even though a call to this function is written to the
+--echo # binary log, values of its parameters are written as literals.
+--echo # So there is no need to acquire strong locks for tables used in
+--echo # the subquery.
+let $statement= select f12((select i+10 from t1 where i=1));
+let $restore_table= t2;
+--source include/check_concurrent_insert.inc
+
+--echo #
+--echo # 4.14 INSERT that reads a table via a subquery passed
+--echo #      as a parameter to a stored function which doesn't
+--echo #      modify data.
+--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= 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 # 5. Statements that read tables through stored procedures.
+--echo #
+
+--echo #
+--echo # 5.1 CALL statement which reads a table via SELECT.
+--echo #
+--echo # Since neither this statement nor its components are
+--echo # written to the binary log, there is no need to take
+--echo # strong locks on the data it reads.
+let $statement= call p2(@a);
+let $restore_table= ;
+--source include/check_concurrent_insert.inc
+
+--echo #
+--echo # 5.2 Function that modifes data and uses CALL, 
+--echo #     which reads a table through SELECT.
+--echo #
+--echo # Since a call to such function is written to the binary
+--echo # log, it should be serialized with concurrent statements.
+--echo # Hence, in this case reads should take strong locks on data.
+let $statement= select f14();
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--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.
+let $statement= select f15();
+let $restore_table= ;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 5.4 INSERT which calls function which doesn't modify data and
+--echo #     uses CALL statement which reads table through SELECT.
+--echo #
+--echo # Since such statement is written to the binary log it should
+--echo # be serialized with concurrent statements affecting data it
+--echo # uses. Therefore it should take strong locks on data it reads.
+let $statement= insert into t2 values (f15()+5);
+let $restore_table= t2;
+--source include/check_no_concurrent_insert.inc
+
+
+--echo #
+--echo # 6. Statements that use triggers.
+--echo #
+
+--echo #
+--echo # 6.1 Statement invoking a trigger that reads table via SELECT.
+--echo #
+--echo # Since this statement is written to the binary log it should
+--echo # be serialized with concurrent statements affecting the data
+--echo # it uses. Therefore, it should take strong locks on the data
+--echo # it reads.
+let $statement= insert into t4 values (2);
+let $restore_table= t4;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 6.2 Statement invoking a trigger that reads table through
+--echo #     a subquery in a control construct.
+--echo #
+--echo # The above is true for this statement as well.
+let $statement= update t4 set l= 2 where l = 1;
+let $restore_table= t4;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 6.3 Statement invoking a trigger that reads a table through
+--echo #     a view.
+--echo #
+--echo # And for this statement.
+let $statement= delete from t4 where l = 1;
+let $restore_table= t4;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 6.4 Statement invoking a trigger that reads a table through
+--echo #     a stored function.
+--echo #
+--echo # And for this statement.
+let $statement= insert into t5 values (2);
+let $restore_table= t5;
+--source include/check_no_concurrent_insert.inc
+
+--echo #
+--echo # 6.5 Statement invoking a trigger that reads a table through
+--echo #     stored procedure.
+--echo #
+--echo # And for this statement.
+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;
+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;
+disconnect con2;
+
+set @@global.concurrent_insert= @old_concurrent_insert;
+
+
+--echo #
 --echo # Test for bug #45143 "All connections hang on concurrent ALTER TABLE".
 --echo #
 --echo # Concurrent execution of statements which required weak write lock
diff --git a/sql/ha_ndbcluster.cc b/sql/ha_ndbcluster.cc
index 38324f3cf19..2d082cc71f6 100644
--- a/sql/ha_ndbcluster.cc
+++ b/sql/ha_ndbcluster.cc
@@ -4628,7 +4628,7 @@ int ha_ndbcluster::start_statement(THD *thd,
   trans_register_ha(thd, FALSE, ndbcluster_hton);
   if (!thd_ndb->trans)
   {
-    if (thd->in_multi_stmt_transaction())
+    if (thd->in_multi_stmt_transaction_mode())
       trans_register_ha(thd, TRUE, ndbcluster_hton);
     DBUG_PRINT("trans",("Starting transaction"));      
     thd_ndb->trans= ndb->startTransaction();
@@ -4698,7 +4698,7 @@ int ha_ndbcluster::init_handler_for_statement(THD *thd, Thd_ndb *thd_ndb)
   }
 #endif
 
-  if (thd->in_multi_stmt_transaction())
+  if (thd->in_multi_stmt_transaction_mode())
   {
     const void *key= m_table;
     HASH_SEARCH_STATE state;
@@ -4782,7 +4782,7 @@ int ha_ndbcluster::external_lock(THD *thd, int lock_type)
     if (opt_ndb_cache_check_time && m_rows_changed)
     {
       DBUG_PRINT("info", ("Rows has changed and util thread is running"));
-      if (thd->in_multi_stmt_transaction())
+      if (thd->in_multi_stmt_transaction_mode())
       {
         DBUG_PRINT("info", ("Add share to list of tables to be invalidated"));
         /* NOTE push_back allocates memory using transactions mem_root! */
@@ -4801,7 +4801,7 @@ int ha_ndbcluster::external_lock(THD *thd, int lock_type)
       DBUG_PRINT("trans", ("Last external_lock"));
       PRINT_OPTION_FLAGS(thd);
 
-      if (!thd->in_multi_stmt_transaction())
+      if (!thd->in_multi_stmt_transaction_mode())
       {
         if (thd_ndb->trans)
         {
@@ -4911,7 +4911,7 @@ static int ndbcluster_commit(handlerton *hton, THD *thd, bool all)
   PRINT_OPTION_FLAGS(thd);
   DBUG_PRINT("enter", ("Commit %s", (all ? "all" : "stmt")));
   thd_ndb->start_stmt_count= 0;
-  if (trans == NULL || (!all && thd->in_multi_stmt_transaction()))
+  if (trans == NULL || (!all && thd->in_multi_stmt_transaction_mode()))
   {
     /*
       An odditity in the handler interface is that commit on handlerton
@@ -4981,7 +4981,7 @@ static int ndbcluster_rollback(handlerton *hton, THD *thd, bool all)
   DBUG_ASSERT(ndb);
   thd_ndb->start_stmt_count= 0;
   if (trans == NULL || (!all &&
-      thd->in_multi_stmt_transaction()))
+      thd->in_multi_stmt_transaction_mode()))
   {
     /* Ignore end-of-statement until real rollback or commit is called */
     DBUG_PRINT("info", ("Rollback before start or end-of-statement only"));
@@ -8271,7 +8271,7 @@ ndbcluster_cache_retrieval_allowed(THD *thd,
   DBUG_ENTER("ndbcluster_cache_retrieval_allowed");
   DBUG_PRINT("enter", ("dbname: %s, tabname: %s", dbname, tabname));
 
-  if (thd->in_multi_stmt_transaction())
+  if (thd->in_multi_stmt_transaction_mode())
   {
     DBUG_PRINT("exit", ("No, don't use cache in transaction"));
     DBUG_RETURN(FALSE);
@@ -8339,7 +8339,7 @@ ha_ndbcluster::register_query_cache_table(THD *thd,
   DBUG_ENTER("ha_ndbcluster::register_query_cache_table");
   DBUG_PRINT("enter",("dbname: %s, tabname: %s", m_dbname, m_tabname));
 
-  if (thd->in_multi_stmt_transaction())
+  if (thd->in_multi_stmt_transaction_mode())
   {
     DBUG_PRINT("exit", ("Can't register table during transaction"));
     DBUG_RETURN(FALSE);
diff --git a/sql/handler.cc b/sql/handler.cc
index ee02441e7ff..c0a5e2ff55c 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -1245,7 +1245,14 @@ end:
 /**
   @note
   This function does not care about global read lock. A caller should.
+
+  @param[in]  all  Is set in case of explicit commit
+                   (COMMIT statement), or implicit commit
+                   issued by DDL. Is not set when called
+                   at the end of statement, even if
+                   autocommit=1.
 */
+
 int ha_commit_one_phase(THD *thd, bool all)
 {
   int error=0;
@@ -1253,9 +1260,15 @@ int ha_commit_one_phase(THD *thd, bool all)
   /*
     "real" is a nick name for a transaction for which a commit will
     make persistent changes. E.g. a 'stmt' transaction inside a 'all'
-    transation is not 'real': even though it's possible to commit it,
+    transaction is not 'real': even though it's possible to commit it,
     the changes are not durable as they might be rolled back if the
     enclosing 'all' transaction is rolled back.
+    We establish the value of 'is_real_trans' by checking
+    if it's an explicit COMMIT/BEGIN statement, or implicit
+    commit issued by DDL (all == TRUE), or if we're running
+    in autocommit mode (it's only in the autocommit mode
+    ha_commit_one_phase() can be called with an empty
+    transaction.all.ha_list, see why in trans_register_ha()).
   */
   bool is_real_trans=all || thd->transaction.all.ha_list == 0;
   Ha_trx_info *ha_info= trans->ha_list, *ha_info_next;
@@ -1303,9 +1316,15 @@ int ha_rollback_trans(THD *thd, bool all)
   /*
     "real" is a nick name for a transaction for which a commit will
     make persistent changes. E.g. a 'stmt' transaction inside a 'all'
-    transation is not 'real': even though it's possible to commit it,
+    transaction is not 'real': even though it's possible to commit it,
     the changes are not durable as they might be rolled back if the
     enclosing 'all' transaction is rolled back.
+    We establish the value of 'is_real_trans' by checking
+    if it's an explicit COMMIT or BEGIN statement, or implicit
+    commit issued by DDL (in these cases all == TRUE),
+    or if we're running in autocommit mode (it's only in the autocommit mode
+    ha_commit_one_phase() is called with an empty
+    transaction.all.ha_list, see why in trans_register_ha()).
   */
   bool is_real_trans=all || thd->transaction.all.ha_list == 0;
   DBUG_ENTER("ha_rollback_trans");
@@ -1358,7 +1377,7 @@ int ha_rollback_trans(THD *thd, bool all)
     if (all)
       thd->variables.tx_isolation=thd->session_tx_isolation;
   }
-  /* Always cleanup. Even if there nht==0. There may be savepoints. */
+  /* Always cleanup. Even if nht==0. There may be savepoints. */
   if (is_real_trans)
     thd->transaction.cleanup();
   if (all)
diff --git a/sql/log.cc b/sql/log.cc
index 18f812d96bf..6c0ab33e87a 100644
--- a/sql/log.cc
+++ b/sql/log.cc
@@ -1686,7 +1686,7 @@ static int binlog_commit(handlerton *hton, THD *thd, bool all)
   DBUG_PRINT("debug",
              ("all: %d, in_transaction: %s, all.modified_non_trans_table: %s, stmt.modified_non_trans_table: %s",
               all,
-              YESNO(thd->in_multi_stmt_transaction()),
+              YESNO(thd->in_multi_stmt_transaction_mode()),
               YESNO(thd->transaction.all.modified_non_trans_table),
               YESNO(thd->transaction.stmt.modified_non_trans_table)));
 
@@ -4267,7 +4267,7 @@ bool use_trans_cache(const THD* thd, bool is_transactional)
 */
 bool ending_trans(THD* thd, const bool all)
 {
-  return (all || (!all && !thd->in_multi_stmt_transaction()));
+  return (all || (!all && !thd->in_multi_stmt_transaction_mode()));
 }
 
 /**
@@ -4370,7 +4370,7 @@ THD::binlog_start_trans_and_stmt()
       cache_mngr->trx_cache.get_prev_position() == MY_OFF_T_UNDEF)
   {
     this->binlog_set_stmt_begin();
-    if (in_multi_stmt_transaction())
+    if (in_multi_stmt_transaction_mode())
       trans_register_ha(this, TRUE, binlog_hton);
     trans_register_ha(this, FALSE, binlog_hton);
     /*
diff --git a/sql/log_event.cc b/sql/log_event.cc
index 2fdc1da4249..d5ae1c954ff 100644
--- a/sql/log_event.cc
+++ b/sql/log_event.cc
@@ -2485,13 +2485,13 @@ Query_log_event::Query_log_event(THD* thd_arg, const char* query_arg,
       implicit_commit= TRUE;
       break;
     case SQLCOM_DROP_TABLE:
-      force_trans= lex->drop_temporary && thd->in_multi_stmt_transaction();
+      force_trans= lex->drop_temporary && thd->in_multi_stmt_transaction_mode();
       implicit_commit= !force_trans;
       break;
     case SQLCOM_ALTER_TABLE:
     case SQLCOM_CREATE_TABLE:
       force_trans= (lex->create_info.options & HA_LEX_CREATE_TMP_TABLE) &&
-                    thd->in_multi_stmt_transaction();
+                    thd->in_multi_stmt_transaction_mode();
       implicit_commit= !force_trans &&
                        !(lex->select_lex.item_list.elements &&
                          thd->is_current_stmt_binlog_format_row());
@@ -4226,7 +4226,7 @@ void Load_log_event::print_query(bool need_db, const char *cs, char *buf,
 
   pos= strmov(pos, "LOAD DATA ");
 
-  if (thd->lex->lock_option == TL_WRITE_CONCURRENT_INSERT)
+  if (is_concurrent)
     pos= strmov(pos, "CONCURRENT ");
 
   if (fn_start)
@@ -4368,6 +4368,7 @@ bool Load_log_event::write_data_body(IO_CACHE* file)
 Load_log_event::Load_log_event(THD *thd_arg, sql_exchange *ex,
 			       const char *db_arg, const char *table_name_arg,
 			       List<Item> &fields_arg,
+                               bool is_concurrent_arg,
 			       enum enum_duplicates handle_dup,
 			       bool ignore, bool using_trans)
   :Log_event(thd_arg,
@@ -4378,7 +4379,8 @@ Load_log_event::Load_log_event(THD *thd_arg, sql_exchange *ex,
    num_fields(0),fields(0),
    field_lens(0),field_block_len(0),
    table_name(table_name_arg ? table_name_arg : ""),
-   db(db_arg), fname(ex->file_name), local_fname(FALSE)
+   db(db_arg), fname(ex->file_name), local_fname(FALSE),
+   is_concurrent(is_concurrent_arg)
 {
   time_t end_time;
   time(&end_time);
@@ -4459,7 +4461,13 @@ Load_log_event::Load_log_event(const char *buf, uint event_len,
                                const Format_description_log_event *description_event)
   :Log_event(buf, description_event), num_fields(0), fields(0),
    field_lens(0),field_block_len(0),
-   table_name(0), db(0), fname(0), local_fname(FALSE)
+   table_name(0), db(0), fname(0), local_fname(FALSE),
+   /*
+     Load_log_event which comes from the binary log does not contain
+     information about the type of insert which was used on the master.
+     Assume that it was an ordinary, non-concurrent LOAD DATA.
+    */
+   is_concurrent(FALSE)
 {
   DBUG_ENTER("Load_log_event");
   /*
@@ -6149,11 +6157,14 @@ int Stop_log_event::do_update_pos(Relay_log_info *rli)
 Create_file_log_event::
 Create_file_log_event(THD* thd_arg, sql_exchange* ex,
 		      const char* db_arg, const char* table_name_arg,
-		      List<Item>& fields_arg, enum enum_duplicates handle_dup,
+                      List<Item>& fields_arg,
+                      bool is_concurrent_arg,
+                      enum enum_duplicates handle_dup,
                       bool ignore,
 		      uchar* block_arg, uint block_len_arg, bool using_trans)
-  :Load_log_event(thd_arg,ex,db_arg,table_name_arg,fields_arg,handle_dup, ignore,
-		  using_trans),
+  :Load_log_event(thd_arg, ex, db_arg, table_name_arg, fields_arg,
+                  is_concurrent_arg,
+                  handle_dup, ignore, using_trans),
    fake_base(0), block(block_arg), event_buf(0), block_len(block_len_arg),
    file_id(thd_arg->file_id = mysql_bin_log.next_file_id())
 {
diff --git a/sql/log_event.h b/sql/log_event.h
index 36397c427e5..e281fd6e206 100644
--- a/sql/log_event.h
+++ b/sql/log_event.h
@@ -2069,6 +2069,17 @@ public:
   uint32 skip_lines;
   sql_ex_info sql_ex;
   bool local_fname;
+  /**
+    Indicates that this event corresponds to LOAD DATA CONCURRENT,
+
+    @note Since Load_log_event event coming from the binary log
+          lacks information whether LOAD DATA on master was concurrent
+          or not, this flag is only set to TRUE for an auxiliary
+          Load_log_event object which is used in mysql_load() to
+          re-construct LOAD DATA statement from function parameters,
+          for logging.
+  */
+  bool is_concurrent;
 
   /* fname doesn't point to memory inside Log_event::temp_buf  */
   void set_fname_outside_temp_buf(const char *afname, uint alen)
@@ -2089,7 +2100,9 @@ public:
 
   Load_log_event(THD* thd, sql_exchange* ex, const char* db_arg,
 		 const char* table_name_arg,
-		 List<Item>& fields_arg, enum enum_duplicates handle_dup, bool ignore,
+		 List<Item>& fields_arg,
+                 bool is_concurrent_arg,
+                 enum enum_duplicates handle_dup, bool ignore,
 		 bool using_trans);
   void set_fields(const char* db, List<Item> &fields_arg,
                   Name_resolution_context *context);
@@ -2708,6 +2721,7 @@ public:
   Create_file_log_event(THD* thd, sql_exchange* ex, const char* db_arg,
 			const char* table_name_arg,
 			List<Item>& fields_arg,
+                        bool is_concurrent_arg,
 			enum enum_duplicates handle_dup, bool ignore,
 			uchar* block_arg, uint block_len_arg,
 			bool using_trans);
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index c91ba2a68b4..2e66aec91e5 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -2127,6 +2127,9 @@ sp_head::reset_lex(THD *thd)
   sublex->interval_list.empty();
   sublex->type= 0;
 
+  /* Reset part of parser state which needs this. */
+  thd->m_parser_state->m_yacc.reset_before_substatement();
+
   DBUG_RETURN(FALSE);
 }
 
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index f8be3ff6d4a..55d83f49245 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -3159,6 +3159,12 @@ int mysql_table_grant(THD *thd, TABLE_LIST *table_list,
   */
   Query_tables_list backup;
   thd->lex->reset_n_backup_query_tables_list(&backup);
+  /*
+    Restore Query_tables_list::sql_command value, which was reset
+    above, as the code writing query to the binary log assumes that
+    this value corresponds to the statement being executed.
+  */
+  thd->lex->sql_command= backup.sql_command;
   if (open_and_lock_tables(thd, tables, FALSE, MYSQL_LOCK_IGNORE_TIMEOUT))
   {						// Should never happen
     close_thread_tables(thd);			/* purecov: deadcode */
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index baf13431d77..28633365e28 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1558,7 +1558,7 @@ void close_thread_tables(THD *thd)
     - If in autocommit mode, or outside a transactional context,
     automatically release metadata locks of the current statement.
   */
-  if (! thd->in_multi_stmt_transaction() &&
+  if (! thd->in_multi_stmt_transaction_mode() &&
       ! (thd->state_flags & Open_tables_state::BACKUPS_AVAIL))
   {
     thd->mdl_context.release_transactional_locks();
@@ -3783,7 +3783,7 @@ end_with_lock_open:
 Open_table_context::Open_table_context(THD *thd, ulong timeout)
   :m_action(OT_NO_ACTION),
    m_start_of_statement_svp(thd->mdl_context.mdl_savepoint()),
-   m_has_locks((thd->in_multi_stmt_transaction() &&
+   m_has_locks((thd->in_multi_stmt_transaction_mode() &&
                 thd->mdl_context.has_locks()) ||
                 thd->mdl_context.trans_sentinel()),
    m_global_mdl_request(NULL),
@@ -3963,7 +3963,8 @@ recover_from_failed_open(THD *thd, MDL_request *mdl_request,
   Return a appropriate read lock type given a table object.
 
   @param thd Thread context
-  @param table TABLE object for table to be locked
+  @param prelocking_ctx Prelocking context.
+  @param table_list     Table list element for table to be locked.
 
   @remark Due to a statement-based replication limitation, statements such as
           INSERT INTO .. SELECT FROM .. and CREATE TABLE .. SELECT FROM need
@@ -3972,20 +3973,44 @@ recover_from_failed_open(THD *thd, MDL_request *mdl_request,
           source table. If such a statement gets applied on the slave before
           the INSERT .. SELECT statement finishes, data on the master could
           differ from data on the slave and end-up with a discrepancy between
-          the binary log and table state. 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 (ie: general_log is enabled on the slave). The
-          statement will be marked as unsafe for SBR in decide_logging_format().
+          the binary log and table state.
+          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.
+          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
+          (ie: general_log is enabled on the slave). The statement will
+          be marked as unsafe for SBR in decide_logging_format().
+  @remark Note that even in prelocked mode it is important to correctly
+          determine lock type value. In this mode lock type is passed to
+          handler::start_stmt() method and can be used by storage engine,
+          for example, to determine what kind of row locks it should acquire
+          when reading data from the table.
 */
 
-thr_lock_type read_lock_type_for_table(THD *thd, TABLE *table)
+thr_lock_type read_lock_type_for_table(THD *thd,
+                                       Query_tables_list *prelocking_ctx,
+                                       TABLE_LIST *table_list)
 {
-  bool log_on= mysql_bin_log.is_open() && (thd->variables.option_bits & OPTION_BIN_LOG);
+  /*
+    In cases when this function is called for a sub-statement executed in
+    prelocked mode we can't rely on OPTION_BIN_LOG flag in THD::options
+    bitmap to determine that binary logging is turned on as this bit can
+    be cleared before executing sub-statement. So instead we have to look
+    at THD::sql_log_bin_toplevel member.
+  */
+  bool log_on= mysql_bin_log.is_open() && thd->sql_log_bin_toplevel;
   ulong binlog_format= thd->variables.binlog_format;
   if ((log_on == FALSE) || (binlog_format == BINLOG_FORMAT_ROW) ||
-      (table->s->table_category == TABLE_CATEGORY_LOG) ||
-      (table->s->table_category == TABLE_CATEGORY_PERFORMANCE))
+      (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 ||
+        (thd->locked_tables_mode > LTM_LOCK_TABLES)))
     return TL_READ;
   else
     return TL_READ_NO_INSERT;
@@ -4336,7 +4361,7 @@ open_and_process_table(THD *thd, LEX *lex, TABLE_LIST *tables,
       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, tables->table);
+        read_lock_type_for_table(thd, lex, tables);
     else
       tables->table->reginfo.lock_type= tables->lock_type;
   }
@@ -4989,35 +5014,49 @@ handle_view(THD *thd, Query_tables_list *prelocking_ctx,
 }
 
 
-/*
+/**
   Check that lock is ok for tables; Call start stmt if ok
 
-  SYNOPSIS
-    check_lock_and_start_stmt()
-    thd			Thread handle
-    table_list		Table to check
-    lock_type		Lock used for table
+  @param thd             Thread handle.
+  @param prelocking_ctx  Prelocking context.
+  @param table_list      Table list element for table to be checked.
 
-  RETURN VALUES
-  0	ok
-  1	error
+  @retval FALSE - Ok.
+  @retval TRUE  - Error.
 */
 
-static bool check_lock_and_start_stmt(THD *thd, TABLE *table,
-				      thr_lock_type lock_type)
+static bool check_lock_and_start_stmt(THD *thd,
+                                      Query_tables_list *prelocking_ctx,
+                                      TABLE_LIST *table_list)
 {
   int error;
+  thr_lock_type lock_type;
   DBUG_ENTER("check_lock_and_start_stmt");
 
+  /*
+    TL_WRITE_DEFAULT and TL_READ_DEFAULT are supposed to be parser only
+    types of locks so they should be converted to appropriate other types
+    to be passed to storage engine. The exact lock type passed to the
+    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.
+  */
+  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);
+  else
+    lock_type= table_list->lock_type;
+
   if ((int) lock_type >= (int) TL_WRITE_ALLOW_READ &&
-      (int) table->reginfo.lock_type < (int) TL_WRITE_ALLOW_READ)
+      (int) table_list->table->reginfo.lock_type < (int) TL_WRITE_ALLOW_READ)
   {
-    my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0),table->alias);
+    my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0), table_list->alias);
     DBUG_RETURN(1);
   }
-  if ((error=table->file->start_stmt(thd, lock_type)))
+  if ((error= table_list->table->file->start_stmt(thd, lock_type)))
   {
-    table->file->print_error(error,MYF(0));
+    table_list->table->file->print_error(error, MYF(0));
     DBUG_RETURN(1);
   }
   DBUG_RETURN(0);
@@ -5162,7 +5201,7 @@ TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type lock_type,
     table->grant= table_list->grant;
     if (thd->locked_tables_mode)
     {
-      if (check_lock_and_start_stmt(thd, table, lock_type))
+      if (check_lock_and_start_stmt(thd, thd->lex, table_list))
 	table= 0;
     }
     else
@@ -5390,7 +5429,7 @@ bool lock_tables(THD *thd, TABLE_LIST *tables, uint count,
         if (!table->placeholder())
         {
           table->table->query_id= thd->query_id;
-          if (check_lock_and_start_stmt(thd, table->table, table->lock_type))
+          if (check_lock_and_start_stmt(thd, thd->lex, table))
           {
             mysql_unlock_tables(thd, thd->lock);
             thd->lock= 0;
@@ -5444,7 +5483,7 @@ bool lock_tables(THD *thd, TABLE_LIST *tables, uint count,
         }
       }
 
-      if (check_lock_and_start_stmt(thd, table->table, table->lock_type))
+      if (check_lock_and_start_stmt(thd, thd->lex, table))
       {
 	DBUG_RETURN(TRUE);
       }
diff --git a/sql/sql_base.h b/sql/sql_base.h
index a57666afe49..0c16151e43a 100644
--- a/sql/sql_base.h
+++ b/sql/sql_base.h
@@ -56,9 +56,6 @@ enum enum_resolution_type {
   RESOLVED_AGAINST_ALIAS
 };
 
-enum enum_open_table_action {OT_NO_ACTION= 0, OT_BACK_OFF_AND_RETRY,
-                             OT_DISCOVER, OT_REPAIR};
-
 enum find_item_error_report_type {REPORT_ALL_ERRORS, REPORT_EXCEPT_NOT_FOUND,
 				  IGNORE_ERRORS, REPORT_EXCEPT_NON_UNIQUE,
                                   IGNORE_EXCEPT_NON_UNIQUE};
@@ -123,7 +120,9 @@ TABLE *open_temporary_table(THD *thd, const char *path, const char *db,
 TABLE *find_locked_table(TABLE *list, const char *db, const char *table_name);
 TABLE *find_write_locked_table(TABLE *list, const char *db,
                                const char *table_name);
-thr_lock_type read_lock_type_for_table(THD *thd, TABLE *table);
+thr_lock_type read_lock_type_for_table(THD *thd,
+                                       Query_tables_list *prelocking_ctx,
+                                       TABLE_LIST *table_list);
 
 my_bool mysql_rm_tmp_tables(void);
 bool rm_temporary_table(handlerton *base, char *path);
diff --git a/sql/sql_cache.cc b/sql/sql_cache.cc
index 1e4161dfa1c..92d54c8e71b 100644
--- a/sql/sql_cache.cc
+++ b/sql/sql_cache.cc
@@ -1177,7 +1177,7 @@ void Query_cache::store_query(THD *thd, TABLE_LIST *tables_used)
     DBUG_ASSERT(flags.protocol_type != (unsigned int) Protocol::PROTOCOL_LOCAL);
     flags.more_results_exists= test(thd->server_status &
                                     SERVER_MORE_RESULTS_EXISTS);
-    flags.in_trans= test(thd->server_status & SERVER_STATUS_IN_TRANS);
+    flags.in_trans= thd->in_active_multi_stmt_transaction();
     flags.autocommit= test(thd->server_status & SERVER_STATUS_AUTOCOMMIT);
     flags.pkt_nr= net->pkt_nr;
     flags.character_set_client_num=
@@ -1470,7 +1470,7 @@ Query_cache::send_result_to_client(THD *thd, char *sql, uint query_length)
   flags.protocol_type= (unsigned int) thd->protocol->type();
   flags.more_results_exists= test(thd->server_status &
                                   SERVER_MORE_RESULTS_EXISTS);
-  flags.in_trans= test(thd->server_status & SERVER_STATUS_IN_TRANS);
+  flags.in_trans= thd->in_active_multi_stmt_transaction();
   flags.autocommit= test(thd->server_status & SERVER_STATUS_AUTOCOMMIT);
   flags.pkt_nr= thd->net.pkt_nr;
   flags.character_set_client_num= thd->variables.character_set_client->number;
@@ -1541,7 +1541,7 @@ def_week_frmt: %lu, in_trans: %d, autocommit: %d",
   }
   DBUG_PRINT("qcache", ("Query have result 0x%lx", (ulong) query));
 
-  if (thd->in_multi_stmt_transaction() &&
+  if (thd->in_multi_stmt_transaction_mode() &&
       (query->tables_type() & HA_CACHE_TBL_TRANSACT))
   {
     DBUG_PRINT("qcache",
@@ -1698,7 +1698,7 @@ void Query_cache::invalidate(THD *thd, TABLE_LIST *tables_used,
   if (is_disabled())
     DBUG_VOID_RETURN;
 
-  using_transactions= using_transactions && thd->in_multi_stmt_transaction();
+  using_transactions= using_transactions && thd->in_multi_stmt_transaction_mode();
   for (; tables_used; tables_used= tables_used->next_local)
   {
     DBUG_ASSERT(!using_transactions || tables_used->table!=0);
@@ -1782,7 +1782,7 @@ void Query_cache::invalidate(THD *thd, TABLE *table,
   if (is_disabled())
     DBUG_VOID_RETURN;
 
-  using_transactions= using_transactions && thd->in_multi_stmt_transaction();
+  using_transactions= using_transactions && thd->in_multi_stmt_transaction_mode();
   if (using_transactions && 
       (table->file->table_cache_type() == HA_CACHE_TBL_TRANSACT))
     thd->add_changed_table(table);
@@ -1800,7 +1800,7 @@ void Query_cache::invalidate(THD *thd, const char *key, uint32  key_length,
   if (is_disabled())
    DBUG_VOID_RETURN;
 
-  using_transactions= using_transactions && thd->in_multi_stmt_transaction();
+  using_transactions= using_transactions && thd->in_multi_stmt_transaction_mode();
   if (using_transactions) // used for innodb => has_transactions() is TRUE
     thd->add_changed_table(key, key_length);
   else
@@ -3572,7 +3572,7 @@ Query_cache::is_cacheable(THD *thd, size_t query_len, const char *query,
                                                 tables_type)))
       DBUG_RETURN(0);
 
-    if (thd->in_multi_stmt_transaction() &&
+    if (thd->in_multi_stmt_transaction_mode() &&
 	((*tables_type)&HA_CACHE_TBL_TRANSACT))
     {
       DBUG_PRINT("qcache", ("not in autocommin mode"));
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 9ed6bdcf098..789b01443f7 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -1443,7 +1443,7 @@ void THD::add_changed_table(TABLE *table)
 {
   DBUG_ENTER("THD::add_changed_table(table)");
 
-  DBUG_ASSERT(in_multi_stmt_transaction() && table->file->has_transactions());
+  DBUG_ASSERT(in_multi_stmt_transaction_mode() && table->file->has_transactions());
   add_changed_table(table->s->table_cache_key.str,
                     (long) table->s->table_cache_key.length);
   DBUG_VOID_RETURN;
diff --git a/sql/sql_class.h b/sql/sql_class.h
index d9bf6c9bb98..916b79f8353 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -2352,10 +2352,6 @@ public:
   {
     return limit_found_rows;
   }
-  inline bool active_transaction()
-  {
-    return server_status & SERVER_STATUS_IN_TRANS;
-  }
   /**
     Returns TRUE if session is in a multi-statement transaction mode.
 
@@ -2366,11 +2362,60 @@ public:
     OPTION_BEGIN: Regardless of the autocommit status, a multi-statement
     transaction can be explicitly started with the statements "START
     TRANSACTION", "BEGIN [WORK]", "[COMMIT | ROLLBACK] AND CHAIN", etc.
+
+    Note: this doesn't tell you whether a transaction is active.
+    A session can be in multi-statement transaction mode, and yet
+    have no active transaction, e.g., in case of:
+    set @@autocommit=0;
+    set @a= 3;                                     <-- these statements don't
+    set transaction isolation level serializable;  <-- start an active
+    flush tables;                                  <-- transaction
+
+    I.e. for the above scenario this function returns TRUE, even
+    though no active transaction has begun.
+    @sa in_active_multi_stmt_transaction()
   */
-  inline bool in_multi_stmt_transaction()
+  inline bool in_multi_stmt_transaction_mode()
   {
     return variables.option_bits & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN);
   }
+  /**
+    TRUE if the session is in a multi-statement transaction mode
+    (@sa in_multi_stmt_transaction_mode()) *and* there is an
+    active transaction, i.e. there is an explicit start of a
+    transaction with BEGIN statement, or implicit with a
+    statement that uses a transactional engine.
+
+    For example, these scenarios don't start an active transaction
+    (even though the server is in multi-statement transaction mode):
+
+    set @@autocommit=0;
+    select * from nontrans_table;
+    set @var=TRUE;
+    flush tables;
+
+    Note, that even for a statement that starts a multi-statement
+    transaction (i.e. select * from trans_table), this
+    flag won't be set until we open the statement's tables
+    and the engines register themselves for the transaction
+    (see trans_register_ha()),
+    hence this method is reliable to use only after
+    open_tables() has completed.
+
+    Why do we need a flag?
+    ----------------------
+    We need to maintain a (at first glance redundant)
+    session flag, rather than looking at thd->transaction.all.ha_list
+    because of explicit start of a transaction with BEGIN. 
+
+    I.e. in case of
+    BEGIN;
+    select * from nontrans_t1; <-- in_active_multi_stmt_transaction() is true
+  */
+  inline bool in_active_multi_stmt_transaction()
+  {
+    return server_status & SERVER_STATUS_IN_TRANS;
+  }
   inline bool fill_derived_tables()
   {
     return !stmt_arena->is_stmt_prepare() && !lex->only_view_structure();
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 860296b4050..1795bc272f1 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -377,7 +377,6 @@ void lex_start(THD *thd)
   lex->subqueries= FALSE;
   lex->view_prepare_mode= FALSE;
   lex->derived_tables= 0;
-  lex->lock_option= TL_READ;
   lex->safe_to_cache_query= 1;
   lex->leaf_tables_insert= 0;
   lex->parsing_options.reset();
@@ -390,7 +389,6 @@ void lex_start(THD *thd)
   lex->select_lex.ftfunc_list= &lex->select_lex.ftfunc_list_alloc;
   lex->select_lex.group_list.empty();
   lex->select_lex.order_list.empty();
-  lex->sql_command= SQLCOM_END;
   lex->duplicates= DUP_ERROR;
   lex->ignore= 0;
   lex->spname= NULL;
@@ -1735,7 +1733,6 @@ void st_select_lex::init_query()
   exclude_from_table_unique_test= no_wrap_view_item= FALSE;
   nest_level= 0;
   link_next= 0;
-  lock_option= TL_READ_DEFAULT;
 }
 
 void st_select_lex::init_select()
@@ -2246,6 +2243,7 @@ void LEX::cleanup_lex_after_parse_error(THD *thd)
 
 void Query_tables_list::reset_query_tables_list(bool init)
 {
+  sql_command= SQLCOM_END;
   if (!init && query_tables)
   {
     TABLE_LIST *table= query_tables;
@@ -2308,8 +2306,7 @@ void Query_tables_list::destroy_query_tables_list()
 */
 
 LEX::LEX()
-  :result(0),
-   sql_command(SQLCOM_END), option_type(OPT_DEFAULT), is_lex_started(0)
+  :result(0), option_type(OPT_DEFAULT), is_lex_started(0)
 {
 
   my_init_dynamic_array2(&plugins, sizeof(plugin_ref),
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 5230753e374..2ce6bdeed42 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -739,14 +739,6 @@ public:
 
   List<udf_func>     udf_list;                  /* udf function calls stack */
 
-  /**
-    Per sub-query locking strategy.
-    Note: This variable might interfer with the corresponding statement-level
-    variable Lex::lock_option because on how different parser rules depend
-    on eachother.
-  */
-  thr_lock_type lock_option;
-
   /* 
     This is a copy of the original JOIN USING list that comes from
     the parser. The parser :
@@ -1005,8 +997,11 @@ extern const LEX_STRING empty_lex_str;
 
 
 /*
-  Class representing list of all tables used by statement.
-  It also contains information about stored functions used by statement
+  Class representing list of all tables used by statement and other
+  information which is necessary for opening and locking its tables,
+  like SQL command for this statement.
+
+  Also contains information about stored functions used by statement
   since during its execution we may have to add all tables used by its
   stored functions/triggers to this list in order to pre-open and lock
   them.
@@ -1018,6 +1013,13 @@ extern const LEX_STRING empty_lex_str;
 class Query_tables_list
 {
 public:
+  /**
+    SQL command for this statement. Part of this class since the
+    process of opening and locking tables for the statement needs
+    this information to determine correct type of lock for some of
+    the tables.
+  */
+  enum_sql_command sql_command;
   /* Global list of all tables used by this statement */
   TABLE_LIST *query_tables;
   /* Pointer to next_global member of last element in the previous list. */
@@ -1920,7 +1922,6 @@ struct LEX: public Query_tables_list
     the variable can contain 0 or 1 for each nest level.
   */
   nesting_map allow_sum_func;
-  enum_sql_command sql_command;
 
   Sql_statement *m_stmt;
 
@@ -1932,7 +1933,6 @@ struct LEX: public Query_tables_list
   */
   bool expr_allows_subselect;
 
-  thr_lock_type lock_option;
   enum SSL_type ssl_type;			/* defined in violite.h */
   enum enum_duplicates duplicates;
   enum enum_tx_isolation tx_isolation;
@@ -2248,11 +2248,21 @@ public:
     yacc_yyss= NULL;
     yacc_yyvs= NULL;
     m_set_signal_info.clear();
+    m_lock_type= TL_READ_DEFAULT;
   }
 
   ~Yacc_state();
 
   /**
+    Reset part of the state which needs resetting before parsing
+    substatement.
+  */
+  void reset_before_substatement()
+  {
+    m_lock_type= TL_READ_DEFAULT;
+  }
+
+  /**
     Bison internal state stack, yyss, when dynamically allocated using
     my_yyoverflow().
   */
@@ -2270,6 +2280,25 @@ public:
   */
   Set_signal_information m_set_signal_info;
 
+  /**
+    Type of lock to be used for tables being added to the statement's
+    table list in table_factor, table_alias_ref, single_multi and
+    table_wild_one rules.
+    Statements which use these rules but require lock type different
+    from one specified by this member have to override it by using
+    st_select_lex::set_lock_for_tables() method.
+
+    The default value of this member is TL_READ_DEFAULT. The only two
+    cases in which we change it are:
+    - When parsing SELECT HIGH_PRIORITY.
+    - Rule for DELETE. In which we use this member to pass information
+      about type of lock from delete to single_multi part of rule.
+
+    We should try to avoid introducing new use cases as we would like
+    to get rid of this member eventually.
+  */
+  thr_lock_type m_lock_type;
+
   /*
     TODO: move more attributes from the LEX structure here.
   */
diff --git a/sql/sql_load.cc b/sql/sql_load.cc
index 9bab87e2720..ff9c16d229b 100644
--- a/sql/sql_load.cc
+++ b/sql/sql_load.cc
@@ -132,6 +132,7 @@ static int read_xml_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list,
 static bool write_execute_load_query_log_event(THD *thd, sql_exchange* ex,
                                                const char* db_arg, /* table's database */
                                                const char* table_name_arg,
+                                               bool is_concurrent,
                                                enum enum_duplicates duplicates,
                                                bool ignore,
                                                bool transactional_table,
@@ -184,6 +185,7 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
   char *tdb= thd->db ? thd->db : db;		// Result is never null
   ulong skip_lines= ex->skip_lines;
   bool transactional_table;
+  bool is_concurrent;
   THD::killed_state killed_status= THD::NOT_KILLED;
   DBUG_ENTER("mysql_load");
 
@@ -245,6 +247,7 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
 
   table= table_list->table;
   transactional_table= table->file->has_transactions();
+  is_concurrent= (table_list->lock_type == TL_WRITE_CONCURRENT_INSERT);
 
   if (!fields_vars.elements)
   {
@@ -562,6 +565,7 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
             (void) write_execute_load_query_log_event(thd, ex,
                                                       table_list->db, 
                                                       table_list->table_name,
+                                                      is_concurrent,
                                                       handle_duplicates, ignore,
                                                       transactional_table,
                                                       errcode);
@@ -610,6 +614,7 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
         int errcode= query_error_code(thd, killed_status == THD::NOT_KILLED);
         error= write_execute_load_query_log_event(thd, ex,
                                                   table_list->db, table_list->table_name,
+                                                  is_concurrent,
                                                   handle_duplicates, ignore,
                                                   transactional_table,
                                                   errcode);
@@ -638,6 +643,7 @@ err:
 static bool write_execute_load_query_log_event(THD *thd, sql_exchange* ex,
                                                const char* db_arg,  /* table's database */
                                                const char* table_name_arg,
+                                               bool is_concurrent,
                                                enum enum_duplicates duplicates,
                                                bool ignore,
                                                bool transactional_table,
@@ -673,8 +679,8 @@ static bool write_execute_load_query_log_event(THD *thd, sql_exchange* ex,
     tbl= string_buf.c_ptr_safe();
   }
 
-  Load_log_event       lle(thd, ex, tdb, tbl, fv, duplicates,
-                           ignore, transactional_table);
+  Load_log_event       lle(thd, ex, tdb, tbl, fv, is_concurrent,
+                           duplicates, ignore, transactional_table);
 
   /*
     force in a LOCAL if there was one in the original.
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index c787ee02f28..60b2ffa4179 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2765,7 +2765,7 @@ end_with_restore_list:
     client thread has locked tables
   */
   if (thd->locked_tables_mode ||
-      thd->active_transaction() || thd->global_read_lock.is_acquired())
+      thd->in_active_multi_stmt_transaction() || thd->global_read_lock.is_acquired())
   {
     my_message(ER_LOCK_OR_ACTIVE_TRANSACTION,
                ER(ER_LOCK_OR_ACTIVE_TRANSACTION), MYF(0));
@@ -3273,7 +3273,7 @@ end_with_restore_list:
       Don't allow this within a transaction because we want to use
       re-generate table
     */
-    if (thd->active_transaction())
+    if (thd->in_active_multi_stmt_transaction())
     {
       my_message(ER_LOCK_OR_ACTIVE_TRANSACTION,
                  ER(ER_LOCK_OR_ACTIVE_TRANSACTION), MYF(0));
@@ -4703,6 +4703,9 @@ finish:
     thd->global_read_lock.start_waiting_global_read_lock(thd);
   }
 
+  DBUG_ASSERT(!thd->in_active_multi_stmt_transaction() ||
+               thd->in_multi_stmt_transaction_mode());
+
   if (stmt_causes_implicit_commit(thd, CF_IMPLICIT_COMMIT_END))
   {
     /* If commit fails, we should be able to reset the OK status. */
@@ -5516,7 +5519,7 @@ void THD::reset_for_next_command()
     OPTION_STATUS_NO_TRANS_UPDATE | OPTION_KEEP_LOG to not get warnings
     in ha_rollback_trans() about some tables couldn't be rolled back.
   */
-  if (!thd->in_multi_stmt_transaction())
+  if (!thd->in_multi_stmt_transaction_mode())
   {
     thd->variables.option_bits&= ~OPTION_KEEP_LOG;
     thd->transaction.all.modified_non_trans_table= FALSE;
@@ -5702,7 +5705,6 @@ void mysql_init_multi_delete(LEX *lex)
   lex->select_lex.select_limit= 0;
   lex->unit.select_limit_cnt= HA_POS_ERROR;
   lex->select_lex.table_list.save_and_clear(&lex->auxiliary_table_list);
-  lex->lock_option= TL_READ_DEFAULT;
   lex->query_tables= 0;
   lex->query_tables_last= &lex->query_tables;
 }
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 739b6576a99..c2d3c595d95 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1708,7 +1708,7 @@ static bool mysql_test_create_table(Prepared_statement *stmt)
 
     res= select_like_stmt_test(stmt, 0, 0);
 
-    lex->link_first_table_back(create_table, &link_to_local);
+    lex->link_first_table_back(create_table, link_to_local);
   }
   else
   {
@@ -3246,7 +3246,7 @@ bool Prepared_statement::prepare(const char *packet, uint packet_len)
     locks have already been released and our savepoint points
     to ticket which has been released as well.
   */
-  if (thd->in_multi_stmt_transaction())
+  if (thd->in_multi_stmt_transaction_mode())
     thd->mdl_context.rollback_to_savepoint(mdl_savepoint);
   thd->restore_backup_statement(this, &stmt_backup);
   thd->stmt_arena= old_stmt_arena;
diff --git a/sql/sql_priv.h b/sql/sql_priv.h
index eeefd3cac04..20893e0caa8 100644
--- a/sql/sql_priv.h
+++ b/sql/sql_priv.h
@@ -129,6 +129,12 @@ extern char err_shared_dir[];
 */
 #define TMP_TABLE_FORCE_MYISAM          (1ULL << 32)
 #define OPTION_PROFILING                (1ULL << 33)
+/**
+  Indicates that this is a HIGH_PRIORITY SELECT.
+  Currently used only for printing of such selects.
+  Type of locks to be acquired is specified directly.
+*/
+#define SELECT_HIGH_PRIORITY            (1ULL << 34)     // SELECT, user
 
 
 /* The rest of the file is included in the server only */
diff --git a/sql/sql_rename.cc b/sql/sql_rename.cc
index d387010141c..ea95b59b0c2 100644
--- a/sql/sql_rename.cc
+++ b/sql/sql_rename.cc
@@ -54,7 +54,7 @@ bool mysql_rename_tables(THD *thd, TABLE_LIST *table_list, bool silent)
     if the user is trying to to do this in a transcation context
   */
 
-  if (thd->locked_tables_mode || thd->active_transaction())
+  if (thd->locked_tables_mode || thd->in_active_multi_stmt_transaction())
   {
     my_message(ER_LOCK_OR_ACTIVE_TRANSACTION,
                ER(ER_LOCK_OR_ACTIVE_TRANSACTION), MYF(0));
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 62a51a32ca2..d126d0e4ec6 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -17179,8 +17179,7 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type)
   /* First add options */
   if (options & SELECT_STRAIGHT_JOIN)
     str->append(STRING_WITH_LEN("straight_join "));
-  if ((thd->lex->lock_option == TL_READ_HIGH_PRIORITY) &&
-      (this == &thd->lex->select_lex))
+  if (options & SELECT_HIGH_PRIORITY)
     str->append(STRING_WITH_LEN("high_priority "));
   if (options & SELECT_DISTINCT)
     str->append(STRING_WITH_LEN("distinct "));
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 41117650e4a..f1db513d0e2 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -3341,7 +3341,6 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
   LEX *lex= thd->lex;
   TABLE *table= tables->table;
   SELECT_LEX *old_all_select_lex= lex->all_selects_list;
-  enum_sql_command save_sql_command= lex->sql_command;
   SELECT_LEX *lsel= tables->schema_select_lex;
   ST_SCHEMA_TABLE *schema_table= tables->schema_table;
   SELECT_LEX sel;
@@ -3377,6 +3376,12 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
 
   lex->view_prepare_mode= TRUE;
   lex->reset_n_backup_query_tables_list(&query_tables_list_backup);
+  /*
+    Restore Query_tables_list::sql_command value, which was reset
+    above, as ST_SCHEMA_TABLE::process_table() functions often rely
+    that this value reflects which SHOW statement is executed.
+  */
+  lex->sql_command= query_tables_list_backup.sql_command;
 
   /*
     We should not introduce deadlocks even if we already have some
@@ -3539,7 +3544,7 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
                    (MYSQL_OPEN_IGNORE_FLUSH |
                     MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL |
                     (can_deadlock ? MYSQL_OPEN_FAIL_ON_MDL_CONFLICT : 0)));
-            lex->sql_command= save_sql_command;
+            lex->sql_command= query_tables_list_backup.sql_command;
             /*
               XXX:  show_table_list has a flag i_is_requested,
               and when it's set, open_normal_and_derived_tables()
@@ -3598,7 +3603,6 @@ err:
   lex->derived_tables= derived_tables;
   lex->all_selects_list= old_all_select_lex;
   lex->view_prepare_mode= save_view_prepare_mode;
-  lex->sql_command= save_sql_command;
   DBUG_RETURN(error);
 }
 
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index c752905d14c..2b8e7de3a60 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -4808,6 +4808,7 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables,
       /* purecov: begin inspected */
       char buff[FN_REFLEN + MYSQL_ERRMSG_SIZE];
       size_t length;
+      enum_sql_command save_sql_command= lex->sql_command;
       DBUG_PRINT("admin", ("sending error message"));
       protocol->prepare_for_resend();
       protocol->store(table_name, system_charset_info);
@@ -4821,6 +4822,11 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables,
       close_thread_tables(thd);
       thd->mdl_context.release_transactional_locks();
       lex->reset_query_tables_list(FALSE);
+      /*
+        Restore Query_tables_list::sql_command value to make statement
+        safe for re-execution.
+      */
+      lex->sql_command= save_sql_command;
       table->table=0;				// For query cache
       if (protocol->write())
 	goto err;
@@ -5018,7 +5024,7 @@ send_result_message:
         /* Clear the ticket released in close_thread_tables(). */
         table->mdl_request.ticket= NULL;
         DEBUG_SYNC(thd, "ha_admin_open_ltable");
-        if (table->table= open_ltable(thd, table, lock_type, 0))
+        if ((table->table= open_ltable(thd, table, lock_type, 0)))
         {
           result_code= table->table->file->ha_analyze(thd, check_opt);
           if (result_code == HA_ADMIN_ALREADY_DONE)
@@ -6553,7 +6559,7 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
       if the user is trying to to do this in a transcation context
     */
 
-    if (thd->locked_tables_mode || thd->active_transaction())
+    if (thd->locked_tables_mode || thd->in_active_multi_stmt_transaction())
     {
       my_message(ER_LOCK_OR_ACTIVE_TRANSACTION,
                  ER(ER_LOCK_OR_ACTIVE_TRANSACTION), MYF(0));
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 701a2ec93c2..9ce62d9f2a4 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -411,6 +411,13 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create)
       destructive changes necessary to open the trigger's table.
     */
     thd->lex->reset_n_backup_query_tables_list(&backup);
+    /*
+      Restore Query_tables_list::sql_command, which was
+      reset above, as the code that writes the query to the
+      binary log assumes that this value corresponds to the
+      statement that is being executed.
+    */
+    thd->lex->sql_command= backup.sql_command;
 
     if (add_table_for_trigger(thd, thd->lex->spname, if_exists, & tables))
       goto end;
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index fb3a7605a94..300ca1098fb 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1053,7 +1053,7 @@ int mysql_multi_update_prepare(THD *thd)
         be write-locked (for example, trigger to be invoked might try
         to update this table).
       */
-      tl->lock_type= read_lock_type_for_table(thd, table);
+      tl->lock_type= read_lock_type_for_table(thd, lex, tl);
       tl->updating= 0;
       /* Update TABLE::lock_type accordingly. */
       if (!tl->placeholder() && !using_lock_tables)
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index a0d64e6a378..aa336f3c072 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -29,6 +29,7 @@
 #define YYLEX_PARAM yythd
 #define YYTHD ((THD *)yythd)
 #define YYLIP (& YYTHD->m_parser_state->m_lip)
+#define YYPS (& YYTHD->m_parser_state->m_yacc)
 
 #define MYSQL_YACC
 #define YYINITDEPTH 100
@@ -4937,7 +4938,6 @@ create_select:
           SELECT_SYM
           {
             LEX *lex=Lex;
-            lex->lock_option= TL_READ_DEFAULT;
             if (lex->sql_command == SQLCOM_INSERT)
               lex->sql_command= SQLCOM_INSERT_SELECT;
             else if (lex->sql_command == SQLCOM_REPLACE)
@@ -7302,7 +7302,6 @@ select_lock_type:
           {
             LEX *lex=Lex;
             lex->current_select->set_lock_for_tables(TL_WRITE);
-            lex->current_select->lock_option= TL_WRITE;
             lex->safe_to_cache_query=0;
             lex->protect_against_global_read_lock= TRUE;
           }
@@ -7311,7 +7310,6 @@ select_lock_type:
             LEX *lex=Lex;
             lex->current_select->
               set_lock_for_tables(TL_READ_WITH_SHARED_LOCKS);
-            lex->current_select->lock_option= TL_READ_WITH_SHARED_LOCKS;
             lex->safe_to_cache_query=0;
           }
         ;
@@ -9221,7 +9219,7 @@ table_factor:
           {
             if (!($$= Select->add_table_to_list(YYTHD, $2, $3,
                                                 Select->get_table_join_options(),
-                                                Lex->lock_option,
+                                                YYPS->m_lock_type,
                                                 Select->pop_index_hints())))
               MYSQL_YYABORT;
             Select->add_joined_table($$);
@@ -10278,7 +10276,7 @@ table_alias_ref:
           {
             if (!Select->add_table_to_list(YYTHD, $1, NULL,
                                            TL_OPTION_UPDATING | TL_OPTION_ALIAS,
-                                           Lex->lock_option ))
+                                           YYPS->m_lock_type))
               MYSQL_YYABORT;
           }
         ;
@@ -10303,8 +10301,6 @@ insert:
             lex->sql_command= SQLCOM_INSERT;
             lex->duplicates= DUP_ERROR; 
             mysql_init_select(lex);
-            /* for subselects */
-            lex->lock_option= TL_READ_DEFAULT;
           }
           insert_lock_option
           opt_ignore insert2
@@ -10495,7 +10491,6 @@ update:
             LEX *lex= Lex;
             mysql_init_select(lex);
             lex->sql_command= SQLCOM_UPDATE;
-            lex->lock_option= TL_UNLOCK; /* Will be set later */
             lex->duplicates= DUP_ERROR; 
           }
           opt_low_priority opt_ignore join_table_list
@@ -10562,7 +10557,7 @@ delete:
             LEX *lex= Lex;
             lex->sql_command= SQLCOM_DELETE;
             mysql_init_select(lex);
-            lex->lock_option= TL_WRITE_DEFAULT;
+            YYPS->m_lock_type= TL_WRITE_DEFAULT;
             lex->ignore= 0;
             lex->select_lex.init_order();
           }
@@ -10573,20 +10568,27 @@ single_multi:
           FROM table_ident
           {
             if (!Select->add_table_to_list(YYTHD, $2, NULL, TL_OPTION_UPDATING,
-                                           Lex->lock_option))
+                                           YYPS->m_lock_type))
               MYSQL_YYABORT;
+            YYPS->m_lock_type= TL_READ_DEFAULT;
           }
           where_clause opt_order_clause
           delete_limit_clause {}
         | table_wild_list
-          { mysql_init_multi_delete(Lex); }
+          {
+            mysql_init_multi_delete(Lex);
+            YYPS->m_lock_type= TL_READ_DEFAULT;
+          }
           FROM join_table_list where_clause
           {
             if (multi_delete_set_locks_and_link_aux_tables(Lex))
               MYSQL_YYABORT;
           }
         | FROM table_alias_ref_list
-          { mysql_init_multi_delete(Lex); }
+          {
+            mysql_init_multi_delete(Lex);
+            YYPS->m_lock_type= TL_READ_DEFAULT;
+          }
           USING join_table_list where_clause
           {
             if (multi_delete_set_locks_and_link_aux_tables(Lex))
@@ -10609,7 +10611,7 @@ table_wild_one:
                                            ti,
                                            NULL,
                                            TL_OPTION_UPDATING | TL_OPTION_ALIAS,
-                                           Lex->lock_option))
+                                           YYPS->m_lock_type))
               MYSQL_YYABORT;
           }
         | ident '.' ident opt_wild
@@ -10621,7 +10623,7 @@ table_wild_one:
                                            ti,
                                            NULL,
                                            TL_OPTION_UPDATING | TL_OPTION_ALIAS,
-                                           Lex->lock_option))
+                                           YYPS->m_lock_type))
               MYSQL_YYABORT;
           }
         ;
@@ -10638,7 +10640,7 @@ opt_delete_options:
 
 opt_delete_option:
           QUICK        { Select->options|= OPTION_QUICK; }
-        | LOW_PRIORITY { Lex->lock_option= TL_WRITE_LOW_PRIORITY; }
+        | LOW_PRIORITY { YYPS->m_lock_type= TL_WRITE_LOW_PRIORITY; }
         | IGNORE_SYM   { Lex->ignore= 1; }
         ;
 
@@ -10724,7 +10726,6 @@ show:
           {
             LEX *lex=Lex;
             lex->wild=0;
-            lex->lock_option= TL_READ;
             mysql_init_select(lex);
             lex->current_select->parsing_place= SELECT_LIST;
             bzero((char*) &lex->create_info,sizeof(lex->create_info));
@@ -11077,7 +11078,6 @@ describe:
           describe_command table_ident
           {
             LEX *lex= Lex;
-            lex->lock_option= TL_READ;
             mysql_init_select(lex);
             lex->current_select->parsing_place= SELECT_LIST;
             lex->sql_command= SQLCOM_SHOW_FIELDS;
@@ -11291,7 +11291,6 @@ load:
           {
             LEX *lex=Lex;
             lex->sql_command= SQLCOM_LOAD;
-            lex->lock_option= $4;
             lex->local_file=  $5;
             lex->duplicates= DUP_ERROR;
             lex->ignore= 0;
@@ -11302,7 +11301,7 @@ load:
           {
             LEX *lex=Lex;
             if (!Select->add_table_to_list(YYTHD, $12, NULL, TL_OPTION_UPDATING,
-                                           lex->lock_option))
+                                           $4))
               MYSQL_YYABORT;
             lex->field_list.empty();
             lex->update_list.empty();
@@ -13734,17 +13733,6 @@ subselect_start:
 subselect_end:
           {
             LEX *lex=Lex;
-            /*
-              Set the required lock level for the tables associated with the
-              current sub-select. This will overwrite previous lock options set
-              using st_select_lex::add_table_to_list in any of the following
-              rules: single_multi, table_wild_one, load_data, table_alias_ref,
-              table_factor.
-              The default lock level is TL_READ_DEFAULT but it can be modified
-              with query options specific for a certain (sub-)SELECT.
-            */
-            lex->current_select->
-              set_lock_for_tables(lex->current_select->lock_option);
 
             lex->pop_context();
             SELECT_LEX *child= lex->current_select;
@@ -13776,8 +13764,8 @@ query_expression_option:
           {
             if (check_simple_select())
               MYSQL_YYABORT;
-            Lex->lock_option= TL_READ_HIGH_PRIORITY;
-            Lex->current_select->lock_option= TL_READ_HIGH_PRIORITY;
+            YYPS->m_lock_type= TL_READ_HIGH_PRIORITY;
+            Select->options|= SELECT_HIGH_PRIORITY;
           }
         | DISTINCT         { Select->options|= SELECT_DISTINCT; }
         | SQL_SMALL_RESULT { Select->options|= SELECT_SMALL_RESULT; }
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index b5df2ae58c1..b8312fc3255 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -295,7 +295,7 @@ static bool binlog_format_check(sys_var *self, THD *thd, set_var *var)
   /*
     Make the session variable 'binlog_format' read-only inside a transaction.
   */
-  if (thd->active_transaction())
+  if (thd->in_active_multi_stmt_transaction())
   {
     my_error(ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT, MYF(0));
     return true;
@@ -348,7 +348,7 @@ static bool binlog_direct_check(sys_var *self, THD *thd, set_var *var)
      Makes the session variable 'binlog_direct_non_transactional_updates'
      read-only inside a transaction.
    */
-   if (thd->active_transaction())
+   if (thd->in_active_multi_stmt_transaction())
    {
      my_error(ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT, MYF(0));
      return true;
@@ -1428,7 +1428,7 @@ static my_bool read_only;
 static bool check_read_only(sys_var *self, THD *thd, set_var *var)
 {
   /* Prevent self dead-lock */
-  if (thd->locked_tables_mode || thd->active_transaction())
+  if (thd->locked_tables_mode || thd->in_active_multi_stmt_transaction())
   {
     my_error(ER_LOCK_OR_ACTIVE_TRANSACTION, MYF(0));
     return true;
@@ -2006,15 +2006,20 @@ static Sys_var_ulong Sys_thread_pool_size(
        VALID_RANGE(1, 16384), DEFAULT(20), BLOCK_SIZE(0));
 #endif
 
-//  Can't change the 'next' tx_isolation if we are already in a transaction
+/**
+  Can't change the 'next' tx_isolation if we are already in a
+  transaction.
+*/
+
 static bool check_tx_isolation(sys_var *self, THD *thd, set_var *var)
 {
-  if (var->type == OPT_DEFAULT && (thd->server_status & SERVER_STATUS_IN_TRANS))
+  if (var->type == OPT_DEFAULT && thd->in_active_multi_stmt_transaction())
   {
+    DBUG_ASSERT(thd->in_multi_stmt_transaction_mode());
     my_error(ER_CANT_CHANGE_TX_ISOLATION, MYF(0));
-    return true;
+    return TRUE;
   }
-  return false;
+  return FALSE;
 }
 
 /*
@@ -2027,6 +2032,7 @@ static bool fix_tx_isolation(sys_var *self, THD *thd, enum_var_type type)
     thd->session_tx_isolation= (enum_tx_isolation)thd->variables.tx_isolation;
   return false;
 }
+
 // NO_CMD_LINE - different name of the option
 static Sys_var_enum Sys_tx_isolation(
        "tx_isolation", "Default transaction isolation level",
diff --git a/sql/transaction.cc b/sql/transaction.cc
index ff4eabc2b0f..5047de1ccdc 100644
--- a/sql/transaction.cc
+++ b/sql/transaction.cc
@@ -169,7 +169,7 @@ bool trans_commit_implicit(THD *thd)
   if (trans_check(thd))
     DBUG_RETURN(TRUE);
 
-  if (thd->in_multi_stmt_transaction() ||
+  if (thd->in_multi_stmt_transaction_mode() ||
       (thd->variables.option_bits & OPTION_TABLE_LOCK))
   {
     /* Safety if one did "drop table" on locked tables */
@@ -305,7 +305,7 @@ bool trans_savepoint(THD *thd, LEX_STRING name)
   SAVEPOINT **sv, *newsv;
   DBUG_ENTER("trans_savepoint");
 
-  if (!(thd->in_multi_stmt_transaction() || thd->in_sub_stmt) ||
+  if (!(thd->in_multi_stmt_transaction_mode() || thd->in_sub_stmt) ||
       !opt_using_transactions)
     DBUG_RETURN(FALSE);
 
@@ -467,7 +467,7 @@ bool trans_xa_start(THD *thd)
     my_error(ER_XAER_INVAL, MYF(0));
   else if (xa_state != XA_NOTR)
     my_error(ER_XAER_RMFAIL, MYF(0), xa_state_names[xa_state]);
-  else if (thd->locked_tables_mode || thd->active_transaction())
+  else if (thd->locked_tables_mode || thd->in_active_multi_stmt_transaction())
     my_error(ER_XAER_OUTSIDE, MYF(0));
   else if (xid_cache_search(thd->lex->xid))
     my_error(ER_XAER_DUPID, MYF(0));