Committing on behalf or Dmitry Lenev:

Fix for bug #46947 "Embedded SELECT without FOR UPDATE is
causing a lock", with after-review fixes.

SELECT statements with subqueries referencing InnoDB tables
were acquiring shared locks on rows in these tables when they
were executed in REPEATABLE-READ mode and with statement or
mixed mode binary logging turned on.

This was a regression which were introduced when fixing
bug 39843.

The problem was that for tables belonging to subqueries
parser set TL_READ_DEFAULT as a lock type. In cases when
statement/mixed binary logging at open_tables() time this
type of lock was converted to TL_READ_NO_INSERT lock at
open_tables() time and caused InnoDB engine to acquire
shared locks on reads from these tables. Although in some
cases such behavior was correct (e.g. for subqueries in
DELETE) in case of SELECT it has caused unnecessary locking.

This patch tries to solve this problem by rethinking our
approach to how we handle locking for SELECT and subqueries.
Now we always set TL_READ_DEFAULT lock type for all cases
when we read data. When at open_tables() time this lock
is interpreted as TL_READ_NO_INSERT or TL_READ depending
on whether this statement as a whole or call to function
which uses particular table should be written to the
binary log or not (if yes then statement should be properly
serialized with concurrent statements and stronger lock
should be acquired).

Test coverage is added for both InnoDB and MyISAM.

This patch introduces an "incompatible" change in locking
scheme for subqueries used in SELECT ... FOR UPDATE and
SELECT .. IN SHARE MODE.
In 4.1 the server would use a snapshot InnoDB read for 
subqueries in SELECT FOR UPDATE and SELECT .. IN SHARE MODE
statements, regardless of whether the binary log is on or off.
If the user required a different type of read (i.e. locking read),
he/she could request so explicitly by providing FOR UPDATE/IN SHARE MODE
clause for each individual subquery.
On of the patches for 5.0 broke this behaviour (which was not documented
or tested), and started to use locking reads fora all subqueries in SELECT ... 
FOR UPDATE/IN SHARE MODE. This patch restored 4.1 behaviour.

mysql-test/include/check_concurrent_insert.inc:
  Added auxiliary script which allows to check if statement
  reading table allows concurrent inserts in it.
mysql-test/include/check_no_concurrent_insert.inc:
  Added auxiliary script which allows to check that statement
  reading table doesn't allow concurrent inserts in it.
mysql-test/include/check_no_row_lock.inc:
  Added auxiliary script which allows to check if statement
  reading table doesn't take locks on its rows.
mysql-test/include/check_shared_row_lock.inc:
  Added auxiliary script which allows to check if statement
  reading table takes shared locks on some of its rows.
mysql-test/r/bug39022.result:
  After bug #46947 'Embedded SELECT without FOR UPDATE is
  causing a lock' was fixed test case for bug 39022 has to
  be adjusted in order to trigger execution path on which
  original problem was encountered.
mysql-test/r/innodb_mysql_lock2.result:
  Added coverage for handling of locking in various cases when
  we read data from InnoDB tables (includes test case for
  bug #46947 'Embedded SELECT without FOR UPDATE is causing a
  lock').
mysql-test/r/lock_sync.result:
  Added coverage for handling of locking in various cases when
  we read data from MyISAM tables.
mysql-test/t/bug39022.test:
  After bug #46947 'Embedded SELECT without FOR UPDATE is
  causing a lock' was fixed test case for bug 39022 has to
  be adjusted in order to trigger execution path on which
  original problem was encountered.
mysql-test/t/innodb_mysql_lock2.test:
  Added coverage for handling of locking in various cases when
  we read data from InnoDB tables (includes test case for
  bug #46947 'Embedded SELECT without FOR UPDATE is causing a
  lock').
mysql-test/t/lock_sync.test:
  Added coverage for handling of locking in various cases when
  we read data from MyISAM tables.
sql/log_event.cc:
  Since LEX::lock_option member was removed we no longer can
  rely on its value in Load_log_event::print_query() to
  determine that log event correponds to LOAD DATA CONCURRENT
  statement (this was not correct in all situations anyway).
  A new Load_log_event's member was introduced as a replacement.
  It is initialized at event object construction time and
  explicitly indicates whether LOAD DATA was concurrent.
sql/log_event.h:
  Since LEX::lock_option member was removed we no longer can
  rely on its value in Load_log_event::print_query() to
  determine that log event correponds to LOAD DATA CONCURRENT
  statement (this was not correct in all situations anyway).
  A new Load_log_event's member was introduced as a replacement.
  It is initialized at event object construction time and
  explicitly indicates whether LOAD DATA was concurrent.
sql/sp_head.cc:
  sp_head::reset_lex():
    Before parsing substatement reset part of parser state
    which needs this (e.g. set Yacc_state::m_lock_type to
    default value).
sql/sql_acl.cc:
  Since LEX::reset_n_backup_query_tables_list() now also
  resets LEX::sql_command member (as it became part of
  Query_tables_list class) we have to restore it in cases
  when while working with proxy Query_table_list we assume
  that LEX::sql_command still corresponds to original SQL
  command being executed (for example, when we are logging
  statement to the binary log while having Query_tables_list
  reset and backed up).
sql/sql_base.cc:
  Changed read_lock_type_for_table() to return a weak TL_READ
  type of lock in cases when we are executing statement which
  won't update tables directly and table doesn't belong to
  statement's prelocking list and thus can't be used by a
  stored function. It is OK to do so since in this case table
  won't be used by statement or function call which will be
  written to the binary log, so serializability requirements
  for it can be relaxed.
  One of results from this change is that SELECTs on InnoDB
  tables no longer takes shared row locks for tables which
  are used in subqueries (i.e. bug #46947 is fixed).
  Another result is that for similar SELECTs on MyISAM tables
  concurrent inserts are allowed.
  In order to implement this change signature of
  read_lock_type_for_table() function was changed to take
  pointers to Query_tables_list and TABLE_LIST objects.
sql/sql_base.h:
  - Function read_lock_type_for_table() now takes pointers
    to Query_tables_list and TABLE_LIST elements as its
    arguments since to correctly determine lock type it needs
    to know what statement is being performed and whether table
    element for which lock type to be determined belongs to
    prelocking list.
sql/sql_lex.cc:
  - Removed LEX::lock_option and st_select_lex::lock_option
    members. Places in parser that were using them now use
    Yacc_state::m_lock_type instead.
  - To emphasize that LEX::sql_command member is used during
    process of opening and locking of tables it was moved to
    Query_tables_list class. It is now reset by
    Query_tables_list::reset_query_tables_list() method.
sql/sql_lex.h:
  - Removed st_select_lex::lock_option member as there is no
    real need for per-SELECT lock type (HIGH_PRIORITY option
    should apply to the whole statement. FOR UPDATE/LOCK IN
    SHARE MODE clauses can be handled without this member).
    The main effect which was achieved by introduction of this
    member, i.e. using TL_READ_DEFAULT lock type for
    subqueries, is now achieved by setting LEX::lock_option
    (or rather its replacement - Yacc_state::m_lock_type) to
    TL_READ_DEFAULT in almost all cases.
  - To emphasize that LEX::sql_command member is used during
    process of opening and locking of tables it was moved to
    Query_tables_list class.
  - Replaced LEX::lock_option with Yacc_state::m_lock_type
    in order to emphasize that this value is relevant only
    during parsing. Unlike for LEX::lock_option the default
    value for Yacc_state::m_lock_type is TL_READ_DEFAULT.
    Note that for cases when it is OK to take a "weak" read
    lock (e.g. simple SELECT) this lock type will be converted
    to TL_READ at open_tables() time. So this change won't
    cause negative change in behavior for such statements.
    OTOH this change ensures that, for example, for SELECTs
    which are used in stored functions TL_READ_NO_INSERT lock
    is taken when necessary and as result calls to such stored
    functions can be written to the binary log with correct
    serialization.
sql/sql_load.cc:
  Load_log_event constructor now requires a parameter that
  indicates whether LOAD DATA is concurrent.
sql/sql_parse.cc:
  LEX::lock_option was replaced with Yacc_state::m_lock_type.
  And instead of resetting the latter implicitly in
  mysql_init_multi_delete() we do it explicitly in the
  places in parser which call this function.
sql/sql_priv.h:
  - To be able more easily distinguish high-priority SELECTs
    in st_select_lex::print() method added flag for
    HIGH_PRIORITY option.
sql/sql_select.cc:
  Changed code not to rely on LEX::lock_option to determine
  that it is high-priority SELECT. It was replaced with
  Yacc_state::m_lock_type which is accessible only at
  parse time. So instead of LEX::lock_option we now rely
  on a newly introduced flag for st_select_lex::options -
  SELECT_HIGH_PRIORITY.
sql/sql_show.cc:
  Since LEX::reset_n_backup_query_tables_list() now also
  resets LEX::sql_command member (as it became part of
  Query_tables_list class) we have to restore it in cases
  when while working with proxy Query_table_list we assume
  that LEX::sql_command still corresponds to original SQL
  command being executed.
sql/sql_table.cc:
  Since LEX::reset_query_tables_list() now also resets
  LEX::sql_command member (as it became part of
  Query_tables_list class) we have to restore value of this
  member when this method is called by mysql_admin_table(),
  to make this code safe for re-execution.
sql/sql_trigger.cc:
  Since LEX::reset_n_backup_query_tables_list() now also
  resets LEX::sql_command member (as it became part of
  Query_tables_list class) we have to restore it in cases
  when while working with proxy Query_table_list we assume
  that LEX::sql_command still corresponds to original SQL
  command being executed (for example, when we are logging
  statement to the binary log while having Query_tables_list
  reset and backed up).
sql/sql_update.cc:
  Function read_lock_type_for_table() now takes pointers
  to Query_tables_list and TABLE_LIST elements as its
  arguments since to correctly determine lock type it needs
  to know what statement is being performed and whether table
  element for which lock type to be determined belongs to
  prelocking list.
sql/sql_yacc.yy:
  - Removed st_select_lex::lock_option member as there is no
    real need for per-SELECT lock type (HIGH_PRIORITY option
    should apply to the whole statement. FOR UPDATE/LOCK IN
    SHARE MODE clauses can be handled without this member).
    The main effect which was achieved by introduction of this
    member, i.e. using TL_READ_DEFAULT lock type for
    subqueries, is now achieved by setting LEX::lock_option
    (or rather its replacement - Yacc_state::m_lock_type) to
    TL_READ_DEFAULT in almost all cases.
  - Replaced LEX::lock_option with Yacc_state::m_lock_type
    in order to emphasize that this value is relevant only
    during parsing. Unlike for LEX::lock_option the default
    value for Yacc_state::m_lock_type is TL_READ_DEFAULT.
    Note that for cases when it is OK to take a "weak" read
    lock (e.g. simple SELECT) this lock type will be converted
    to TL_READ at open_tables() time. So this change won't
    cause negative change in behavior for such statements.
    OTOH this change ensures that, for example, for SELECTs
    which are used in stored functions TL_READ_NO_INSERT lock
    is taken when necessary and as result calls to such stored
    functions can be written to the binary log with correct
    serialization.
  - To be able more easily distinguish high-priority SELECTs
    in st_select_lex::print() method we now use new flag
    in st_select_lex::options bit-field.

1 files changed, 6 insertions, 0 deletions

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 */