Bug #51263 Deadlock between transactional SELECT and ALTER TABLE ... REBUILD PARTITION
Submitted: 18 Feb 2010 1:10 Modified: 21 Jul 2010 2:07
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:mysql-next-4284,5.5-m3 OS:Any
Assigned to: Dmitry Lenev CPU Architecture:Any

[18 Feb 2010 1:10] Elena Stepanova
Description:
One thread performs ALTER TABLE .. REBUILD PARTITION on a table partitioned by hash; as soon as it the query is finished, successfully or not, it exits and a new one starts doing the same.

Another thread starts a transaction, performs a select SELECT ( SELECT COUNT(*) FROM t ) >= ( SELECT COUNT(*) FROM t ) on the table, checks for the result, and keeps re-issuing the SELECT until it succeeds. After that the thread commits and exits, and a new one starts doing the same.

Soon after this process starts, the first thread falls into an endless waiting, while the second thread keeps running SELECTS which fail due to ER_LOCK_WAIT_TIMEOUT. 

Even after the query in the 2nd thread fails, the 1st one is not progressing anyhow.

The provided test is run with MIXED binlog format. It seems important.

REBUILD PARTITION:

#0  0x00a02416 in __kernel_vsyscall ()
#1  0x00c25f72 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib/libpthread.so.0
#2  0x085e7431 in safe_cond_timedwait (cond=0xacd3764, mp=0xacd3720, abstime=0xb1b1dac8, file=0x876b178 "../include/mysql/psi/mysql_thread.h", line=812)
    at thr_mutex.c:278
#3  0x0844ffb8 in inline_mysql_cond_timedwait (that=0xacd3764, mutex=0xacd3720, abstime=0xb1b1dac8) at ../include/mysql/psi/mysql_thread.h:812
#4  0x08450045 in MDL_context::timed_wait (this=0xacd36e8, abs_timeout=0xb1b1dac8) at mdl.cc:802
#5  0x0845131a in MDL_context::acquire_lock_impl (this=0xacd36e8, mdl_request=0xb1b1db18, lock_wait_timeout=31536000) at mdl.cc:1475
#6  0x084515ea in MDL_context::upgrade_shared_lock_to_exclusive (this=0xacd36e8, mdl_ticket=0xb6c11618, lock_wait_timeout=31536000) at mdl.cc:1644
#7  0x082ae316 in wait_while_table_is_used (thd=0xacd3678, table=0xb6c09db8, function=HA_EXTRA_FORCE_REOPEN) at sql_base.cc:2151
#8  0x082ae41f in abort_and_upgrade_lock (lpt=0xb1b1dd6c) at sql_base.cc:8727
#9  0x0816fdfd in fast_alter_partition_table (thd=0xacd3678, table=0xb6c09db8, alter_info=0xb1b1fa20, create_info=0xb1b1f2f8, table_list=0xb6c046f8, 
    db=0xb6c04a90 "celosia_features", table_name=0xb6c046a0 "t_celosia_ddl_partitions", fast_alter_partition=1) at sql_partition.cc:6749
#10 0x083aa2f1 in mysql_alter_table (thd=0xacd3678, new_db=0xb6c04a90 "celosia_features", new_name=0xb6c046a0 "t_celosia_ddl_partitions", 
    create_info=0xb1b1f2f8, table_list=0xb6c046f8, alter_info=0xb1b1fa20, order_num=0, order=0x0, ignore=false) at sql_table.cc:7021
#11 0x08259d06 in mysql_execute_command (thd=0xacd3678) at sql_parse.cc:2651
#12 0x082600e3 in mysql_parse (thd=0xacd3678, inBuf=0xb6c045d0 "ALTER TABLE t_celosia_ddl_partitions REBUILD PARTITION p1", length=57, 
    found_semicolon=0xb1b20228) at sql_parse.cc:5581
#13 0x08261408 in dispatch_command (command=COM_QUERY, thd=0xacd3678, packet=0xacf6381 "ALTER TABLE t_celosia_ddl_partitions REBUILD PARTITION p1", 
    packet_length=57) at sql_parse.cc:1023
#14 0x08262646 in do_command (thd=0xacd3678) at sql_parse.cc:709
#15 0x08251210 in do_handle_one_connection (thd_arg=0xacd3678) at sql_connect.cc:1174
#16 0x082512bd in handle_one_connection (arg=0xacd3678) at sql_connect.cc:1113
#17 0x00c21ab5 in start_thread () from /lib/libpthread.so.0
#18 0x00b7883e in clone () from /lib/libc.so.6

SELECT:

#0  0x00a02416 in __kernel_vsyscall ()
#1  0x00c25c45 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib/libpthread.so.0
#2  0x085e7296 in safe_cond_wait (cond=0xa97ab4c, mp=0xa97ab00, file=0x8779244 "os/os0sync.c", line=399) at thr_mutex.c:240
#3  0x0849ba6f in os_event_wait_low (event=0xa97ab00, reset_sig_count=0) at os/os0sync.c:399
#4  0x084da5f3 in srv_suspend_mysql_thread (thr=0xb6c1d9a0) at srv/srv0srv.c:1554
#5  0x084bf827 in row_mysql_handle_errors (new_err=0xb1aed034, trx=0xad05420, thr=0xb6c1d9a0, savept=0x0) at row/row0mysql.c:534
#6  0x084d0b88 in row_search_for_mysql (buf=0xb6c29c02 "z\t", mode=1, prebuilt=0xb6c1d3e0, match_mode=0, direction=0) at row/row0sel.c:4462
#7  0x08477517 in ha_innobase::index_read (this=0xb6c1b0f8, buf=0xb6c29c02 "z\t", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY)
    at handler/ha_innodb.cc:5112
#8  0x0846fd42 in ha_innobase::index_first (this=0xb6c1b0f8, buf=0xb6c29c02 "z\t") at handler/ha_innodb.cc:5398
#9  0x0838eb3b in ha_partition::handle_ordered_index_scan (this=0xb6c0c520, buf=0xb6c0c890 "7\n", reverse_order=false) at ha_partition.cc:4798
#10 0x0838f0c8 in ha_partition::common_first_last (this=0xb6c0c520, buf=0xb6c0c890 "7\n") at ha_partition.cc:4313
#11 0x0838f196 in ha_partition::index_first (this=0xb6c0c520, buf=0xb6c0c890 "7\n") at ha_partition.cc:4262
#12 0x082c8dc4 in join_read_first (tab=0xad0d2d0) at sql_select.cc:12171
#13 0x082c9dd3 in sub_select (join=0xad0e0d0, join_tab=0xad0d2d0, end_of_records=false) at sql_select.cc:11427
#14 0x082d1f2c in do_select (join=0xad0e0d0, fields=0xad0f234, table=0x0, procedure=0x0) at sql_select.cc:11180
#15 0x082eb51e in JOIN::exec (this=0xad0e0d0) at sql_select.cc:2291
#16 0x081f996b in subselect_single_select_engine::exec (this=0xad03090) at item_subselect.cc:1965
#17 0x081fbe54 in Item_subselect::exec (this=0xad02ff8) at item_subselect.cc:261
#18 0x081f81a5 in Item_singlerow_subselect::val_int (this=0xad02ff8) at item_subselect.cc:571
#19 0x081b9638 in Arg_comparator::compare_int_signed (this=0xad03cf8) at item_cmpfunc.cc:1451
#20 0x0819e6eb in Arg_comparator::compare (this=0xad03cf8) at item_cmpfunc.h:84
#21 0x081bd881 in Item_func_ge::val_int (this=0xad03c80) at item_cmpfunc.cc:1888
#22 0x081790b2 in Item::send (this=0xad03c80, protocol=0xacdc9f4, buffer=0xb1aeda30) at item.cc:5724
#23 0x0823a01a in Protocol::send_result_set_row (this=0xacdc9f4, row_items=0xacddabc) at protocol.cc:877
#24 0x082307ef in select_send::send_data (this=0xad03ee0, items=...) at sql_class.cc:1728
#25 0x082e9755 in JOIN::exec (this=0xad0c0e8) at sql_select.cc:1768
#26 0x082e6463 in mysql_select (thd=0xacdc658, rref_pointer_array=0xacddb20, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, 
    group=0x0, having=0x0, proc_param=0x0, select_options=2148797184, result=0xad03ee0, unit=0xacdd5fc, select_lex=0xacdda28) at sql_select.cc:2480
#27 0x082eb82e in handle_select (thd=0xacdc658, lex=0xacdd5a0, result=0xad03ee0, setup_tables_done_option=0) at sql_select.cc:271
#28 0x08257778 in execute_sqlcom_select (thd=0xacdc658, all_tables=0xad02c40) at sql_parse.cc:4548
#29 0x0825867c in mysql_execute_command (thd=0xacdc658) at sql_parse.cc:2060
#30 0x082600e3 in mysql_parse (thd=0xacdc658, 
    inBuf=0xad023c0 "SELECT ( SELECT COUNT(*) FROM t_celosia_ddl_partitions ) >= ( SELECT COUNT(*) FROM t_celosia_ddl_partitions )", length=109, 
    found_semicolon=0xb1aef228) at sql_parse.cc:5581
#31 0x08261408 in dispatch_command (command=COM_QUERY, thd=0xacdc658, packet=0xacfa391 "", packet_length=109) at sql_parse.cc:1023
#32 0x08262646 in do_command (thd=0xacdc658) at sql_parse.cc:709
#33 0x08251210 in do_handle_one_connection (thd_arg=0xacdc658) at sql_connect.cc:1174
#34 0x082512bd in handle_one_connection (arg=0xacdc658) at sql_connect.cc:1113
#35 0x00c21ab5 in start_thread () from /lib/libpthread.so.0
#36 0x00b7883e in clone () from /lib/libc.so.6

How to repeat:
- unpack the attached archive in <mysql-basedir>/mysql-test. It should create
'stress_test_basedir' folder;
- run as  
perl ./stress_test_basedir/run.pl

Notes:
run.pl is a wrapper for MTR to start server, and mysql-stress-test to run a 2-thread stress test. 
For mysql-stress-test you need to have perl with threads on the top of your path (or please modify run.pl to call mysql-stress-test.pl with the right perl).

The screen output should say 'Waiting for server(s) to exit...' for a few seconds (sleep time for the server to start), and then start rapidly producing lines like
test_loop[0:0 0:26]: TID 2 test: 'stress2'  Errors: No Errors. Test Passed OK
test_loop[0:0 0:27]: TID 1 test: 'stress1'  Errors: No Errors. Test Passed OK

When the problem is hit, the lines stop appearing. Usually it happens very soon.
 
If the server does not start fast enough, the test might report something like 

test_loop[0:0 0:2]: TID 2 test: 'stress1'  Errors: Severity S1: 15 (thread aborting)

In this case please increase sleep time in run.pl.
[18 Feb 2010 1:13] Elena Stepanova
mysql-stress-test-based test

Attachment: 51263_stress_test_basedir.tar.gz (application/x-gzip, text), 2.37 KiB.

[1 Mar 2010 16:44] Philip Stoev
From the backtrace, it appears to me that the MDL wait will not be infinite. Therefore, a lower value of lock-wait-timeout should help .
[1 Mar 2010 19:54] Elena Stepanova
The original complaint was not that the situation did not have a workaround (lock wait timeout can be decreased, or the transaction with SELECT can rollback instead of retrying), but that, apparently, the conflict could have been detected and handled by giving up locks by one of the threads.
[2 Mar 2010 14:43] Dmitry Lenev
A simpler test case which does not involve stress testing:

--source include/have_innodb.inc
--source include/have_binlog_format_statement.inc

--disable_warnings
drop table if exists t1, t2, t3;
--enable_warnings

connect (con1,localhost,root,,test,,);

connection default;
create table t1 (i int auto_increment not null primary key) engine=innodb
  partition by hash (i) partitions 4;
insert into t1 values (1), (2), (3), (4), (5);

begin;
--echo # Acquire SR metadata lock on t1.
select * from t1;

--echo # Switching to connection 'con1'.
connection con1;
--send alter table t1 rebuild partition p0

--echo # Switching to connection 'default'.
connection default;
--echo # Wait until ALTER is blocked because of active SR lock.
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table" and info = "alter table t1 rebuild partition p0";
--source include/wait_condition.inc

--echo # The below statement will deadlock even though this transaction
--echo # has and SR metadata lock on t1. This is due to fact that
--echo # ALTER acquire LOCK_X row-locks on table rows and the below
--echo # statement tries to acquire LOCK_S row-locks on table rows
--echo # due to bug #46947.
select (select count(*) from t1) >= (select count(*) from t1);
[2 Mar 2010 19:30] Dmitry Lenev
Now test case which demonstrates the problem which is not caused by bug #46947.

--source include/have_innodb.inc
--source include/have_binlog_format_statement.inc

--disable_warnings
drop table if exists t1, t2, t3;
--enable_warnings

connect (con1,localhost,root,,test,,);

connection default;
create table t1 (i int auto_increment not null primary key) engine=innodb
  partition by hash (i) partitions 4;
create table t2 (i int) engine=innodb;
insert into t1 values (1), (2), (3), (4), (5);

begin;
--echo # Acquire SR metadata lock on t1.
select * from t1;

--echo # Switching to connection 'con1'.
connection con1;
--send alter table t1 rebuild partition p0

--echo # Switching to connection 'default'.
connection default;
--echo # Wait until ALTER is blocked because of active SR lock.
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table" and info = "alter table t1 rebuild partition p0";
--source include/wait_condition.inc

--echo # The below statement will deadlock even though this transaction
--echo # has and SR metadata lock on t1. This is due to fact that
--echo # ALTER acquire LOCK_X row-locks on table rows and the below
--echo # statement tries to acquire LOCK_S row-locks on table rows
--echo # (due to subselect).
insert into t2 select count(*) from t1;
[3 Mar 2010 5:43] Dmitry Lenev
Investigation shows that using partitioned table and ALTER which changes partitioning is important for repeating this bug. For ordinary ALTER InnoDB transaction is committed after copying data to new version of table and thus row LOCK_X locks are released and no deadlock occurs. This does not happen in case of ALTER TABLE ... REBUILD PARTITION thus LOCK_X are not released until metadata lock upgrade and deadlock occurs.
[12 Mar 2010 9:09] Dmitry Lenev
Finally test case which shows that similar deadlock can be repeatable even for non-partitioned table:

--source include/have_innodb.inc
--source include/have_binlog_format_statement.inc

--disable_warnings
drop table if exists t1, t2, t3;
--enable_warnings

connect (con1,localhost,root,,test,,);

connection default;
create table t1 (i int auto_increment not null primary key) engine=innodb;
create table t2 (i int) engine=innodb;
insert into t1 values (1), (2), (3), (4), (5);

begin;
--echo # Acquire SR metadata lock on t1 and LOCK_S row-locks on its rows.
insert into t2 select count(*) from t1;

--echo # Switching to connection 'con1'.
connection con1;
--send alter table t1 add column j int

--echo # Switching to connection 'default'.
connection default;
--sleep 1
show processlist;

--echo # Wait until ALTER is blocked because it tries to acquire LOCK_X
--echo # row-lock on rows of t1.
let $wait_condition=
  select count(*) = 1 from information_schema.processlist where state =
    "copy to tmp table" and info = "alter table t1 add column j int";
--source include/wait_condition.inc

--echo # The below statement will deadlock because it will try to acquire
--echo # SW lock on t1, which will conflict with ALTER's SNW lock. And
--echo # ALTER will be waiting for this connection to release its LOCK_S
--echo # row-locks. Unfortunately this deadlock won't be detect by an MDL
--echo # subsystem since it also involves InnoDB row-locks.
insert into t1 values (6);
[23 Mar 2010 21:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/104128

2999 Dmitry Lenev	2010-03-24
      Pre-requisite patch for bug #51263 "Deadlock between
      transactional SELECT and ALTER TABLE ... REBUILD PARTITION".
      
      The goal of this patch is to decouple type of metadata
      lock acquired for table by open_tables() from type of
      table-level lock to be acquired on it.
      
      To achieve this we change approach to how we determine what
      type of metadata lock should be acquired on table to be open.
      Now instead of inferring it at open_tables() time from flags
      and type of table-level lock we rely on that type of metadata
      lock is properly set at parsing time and is not changed
      further.
     @ sql/ha_ndbcluster.cc
        Now one needs to properly initialize table list element's
        MDL_request object before calling mysql_rm_table_part2().
     @ sql/lock.cc
        lock_table_names() no longer initializes table list elements'
        MDL_request objects. Now proper initialization of these
        requests is a responsibility of the caller.
     @ sql/mdl.h
        Added version of new operator which simplifies allocation of
        MDL_request objects on a MEM_ROOT.
     @ sql/mysql_priv.h
        Removed MYSQL_OPEN_TAKE_UPGRADABLE_MDL flag which became
        unnecessary. Thanks to the fact that we don't reset type of
        requests for metadata locks between re-executions we now can
        figure out that upgradable locks are requested by simply
        looking at their type which were set in the parser. As result
        this flag became redundant.
     @ sql/sp_head.cc
        Added comment explaining why it is OK to infer type of
        metadata lock to request from type of table-level lock
        for prelocking.
        Added enum_mdl_type argument to sp_add_to_query_tables()
        to simplify its usage in trigger implementation.
     @ sql/sp_head.h
        Added enum_mdl_type argument to sp_add_to_query_tables()
        to simplify its usage in trigger implementation.
     @ sql/sql_base.cc
        - open_table_get_mdl_lock():
          Preserve type of MDL_request for table list element which
          was set in the parser by creating MDL_request objects on
          memory root if MYSQL_OPEN_FORCE_SHARED_MDL or
          MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL flag were specified.
          Thanks to this and to the fact that we no longer reset
          type of requests for metadata locks between re-executions
          we no longer need to acquire exclusive metadata lock on
          table to be created in a special way. This lock is acquired
          by code handling acquiring of upgradable locks.
        - Accordingly special lock strategy for table list elements
          which was used for such locks became unnecessary and was
          removed. Other strategies were renamed.
        - Since we no longer have guarantee that MDL_request object
          which were not satisfied due to lock conflict belongs to
          table list element Open_table_context class and its methods
          were extended to remember pointer to MDL_request which has
          caused problem at request_backoff_action() time and use it
          in recover_from_failed_open(). Similar approach is used
          for cases when problem from which we need to recover is
          not related to MDL but to the table itself. In this case
          we store pointer to the element of table list.
        - Changed open_tables()/open_tables_check_upgradable_mdl()/
          open_tables_acquire_upgradable_mdl() not to rely on
          MYSQL_OPEN_TAKE_UPGRADABLE_MDL flag to understand when
          upgradable metadata locks should be acquired and not to
          infer type of MDL lock from type of table-level lock.
          Instead we assume that type of MDL to be acquired was set
          in the parser (we can do this as type of MDL_request is
          no longer reset between re-executions).
     @ sql/sql_class.h
        Since we no longer have guarantee that MDL_request object
        which were not satisfied due to lock conflict belongs to
        table list element Open_table_context class and its methods
        were extended to remember pointer to MDL_request which has
        caused problem at request_backoff_action() time and use it
        in recover_from_failed_open(). Similar approach is used
        for cases when problem from which we need to recover is
        not related to MDL but to the table itself. In this case
        we store pointer to the element of table list.
     @ sql/sql_db.cc
        Now one needs to properly initialize table list element's
        MDL_request object before calling mysql_rm_table_part2()
        or mysql_rename_tables().
     @ sql/sql_lex.cc
        st_select_lex/st_select_lex_node::add_table_to_list() method
        now has argument which allows specify type of metadata lock
        to be requested for table list element being added.
        Initialize LEX::mdl_lock_type member in lex_start() to a
        value which is in sync with value of LEX::lock_option.
     @ sql/sql_lex.h
        - st_select_lex/st_select_lex_node::add_table_to_list()
          method now has argument which specifies type of metadata
          lock to be requested for table list element being added.
          This allows to explicitly set type of MDL lock to be
          acquired for a DDL statement in parser. It is also more
          future-proof than inferring type of MDL request from type
          of table-level lock.
        - Added LEX::mdl_lock_type member which specifies which type
          of metadata lock should be requested for tables to be added
          to table list by a grammar rule in cases when the same rule
          is used in several statements requiring different kinds of
          metadata locks.
     @ sql/sql_parse.cc
        - st_select_lex::add_table_to_list() method now has argument
          which specifies type of metadata lock to be requested for
          table list element being added. This allows to explicitly
          set type of MDL lock to be acquired for a DDL statement in
          parser. It is also more future-proof than inferring type of
          MDL request from type of table-level lock.
        - EXCLUSIVE_DOWNGRADABLE_MDL lock strategy has a new name -
          OTLS_DOWNGRADE_IF_EXISTS.
        - Adjusted LOCK TABLES implementation to the fact that we no
          longer infer type of metadata lock to be acquired from table
          level lock and that type of MDL request is set at parsing.
          And thus MYSQL_OPEN_TAKE_UPGRADABLE_MDL flag became
          unnecessary.
        - Adjusted mysql_init_multi_delete()'s code to set new
          LEX::mdl_lock_type value after changing LEX::lock_option.
     @ sql/sql_prepare.cc
        TABLE_LIST's lock strategy SHARED_MDL was renamed to OTLS_NONE
        as now it means that metadata lock should not be changed during
        call to open_table() (if it has been already acquired) and is
        also used for exclusive metadata lock.
     @ sql/sql_show.cc
        st_select_lex::add_table_to_list() method now has argument
        which specifies type of metadata lock to be requested for
        table list element being added.
     @ sql/sql_table.cc
        - Adjusted mysql_admin_table()'s code to the fact that
          open_tables() no longer determines what kind of metadata
          lock should be obtained basing on type of table-level
          lock and flags. Instead type of metadata lock for table
          to be open should be set before calling open_tables().
        - Changed mysql_alter_table() code to the facts:
          a) that now it is responsibility of caller to properly
          initalize MDL_request in table list elements before calling
          lock_table_names()
          b) and that MYSQL_OPEN_TAKE_UPGRADABLE_MDL is no longer
          necessary since type of metadata lock to be obtained
          at open_tables() time is set during parsing.
        - Changed code of mysql_recreate_table() to properly set
          type of metadata and table-level lock to be obtained
          by mysql_alter_table() which it calls.
     @ sql/sql_trigger.cc
        Instead of relying on MYSQL_OPEN_TAKE_UPGRADABLE_MDL flag to
        force open_tables() to take an upgradable lock we now specify
        exact type of lock to be taken when constructing table list
        element for table to be open for CREATE/DROP TRIGGER.
     @ sql/sql_view.cc
        We no longer use TABLE_LIST::EXCLUSIVE_MDL strategy to force
        open_tables() to take an exclusive metadata lock on view to
        be created. Instead we rely on parser setting proper type of
        metadata lock to request and open_tables() acquiring it.
        This became possible thanks to the fact that we no longer
        reset type of MDL_request between statement re-executions.
     @ sql/sql_yacc.yy
        Instead of inferring type of MDL_request for table to be
        open from type of table-level lock and flags passed to
        open_tables() we now explicitly specify them at parsing.
        This became possible thanks to the fact that we no longer
        reset type of MDL_request between statement re-executions.
        In future this should allow to decouple type of metadata
        lock from type of table-level lock.
        The only exception to this approach is statements implemented
        through mysql_admin_table() which re-uses same table list
        element several times with different types of table-level
        and metadata locks.
        We now also properly initialize MDL_request objects for table
        list elements which are later passed to lock_table_names()
        function.
     @ sql/table.cc
        Do not reset type of MDL_request between statement
        re-executions. This became unnecessesary as we no longer
        change type of MDL_request residing in table list element.
        In its turn this change allows to set type of MDL_request
        only once - at parsing time.
     @ sql/table.h
        Got rid of TABLE_LIST::EXCLUSIVE_MDL lock strategy.
        Now we can specify that we need to acquire exclusive lock
        on table to be processed by open_tables() through setting
        an appropriate type of MDL_request at parsing time (this
        became possible thanks to the fact that we no longer reset
        types of MDL_request's belonging to table list elements
        between statement re-execution).
        Strategy SHARED_MDL was renamed to OTLS_NONE as now it
        means that metadata lock should not be changed during call
        to open_table() (if it has been already acquired) and is
        also used for exclusive metadata lock.
        Strategy EXCLUSIVE_DOWNGRADABLE_MDL was renamed to
        OTLS_DOWNGRADE_IF_EXISTS.
[13 Apr 2010 14:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105529

3000 Dmitry Lenev	2010-04-13
      Tentative fix for bug #51263 "Deadlock between transactional
      SELECT and ALTER TABLE ...  REBUILD PARTITION".
      
      ALTER TABLE on InnoDB table (including partitioned tables)
      acquired exclusive locks on rows of table being altered.
      In cases when there was concurrent transaction which did
      locking reads from this table this sometimes led to a
      deadlock which was not detected by MDL subsystem nor by
      InnoDB engine (and was reported only after exceeding
      innodb_lock_wait_timeout).
      
      This problem stemmed from the fact that ALTER TABLE acquired
      TL_WRITE_ALLOW_READ lock on table being altered. This lock
      was interpreted as a write lock and thus for table being
      altered handler::external_lock() method was called with
      F_WRLCK as an argument. As result InnoDB engine treated
      ALTER TABLE as an operation which is going to change data
      and acquired LOCK_X locks on rows being read from old
      version of table.
      
      In case when there was a transaction which already acquired
      SR metadata lock on table and some LOCK_S locks on its rows
      (e.g. by using it in subquery of DML statement) concurrent
      ALTER TABLE was blocked at the moment when it tried to
      acquire LOCK_X lock before reading one of these rows.
      The transaction's attempt to acquire SW metadata lock on
      table being altered led to deadlock, since it had to wait
      for ALTER TABLE to release SNW lock. This deadlock was not
      detected and got resolved only after timeout expiring
      because waiting were happening in two different subsystems.
      
      Similar deadlocks could have occured in other situations.
      
      This patch tries to solve the problem by changing ALTER TABLE
      implementation to use TL_READ_NO_INSERT lock instead of
      TL_WRITE_ALLOW_READ. After this step handler::external_lock()
      is called with F_RDLCK as an argument and InnoDB engine
      correctly interprets ALTER TABLE as operation which only
      reads data from original version of table. Thanks to this
      ALTER TABLE acquires only LOCK_S locks on rows it reads.
      This, in its turn, causes inter-subsystem deadlocks to go
      away, as all potential lock conflicts and thus deadlocks will
      be limited to metadata locking subsystem:
      
      - When ALTER TABLE reads rows from table being altered it
        can't encounter any locks which conflict with LOCK_S row
        locks. There should be no concurrent transactions holding
        LOCK_X row locks. Such a transaction should have been
        acquired SW metadata lock on table first which would have
        conflicted with ALTER's SNW lock.
      - Vice versa, when DML which runs concurrently with ALTER
        TABLE tries to lock row it should be requesting only LOCK_S
        lock which is compatible with locks acquired by ALTER,
        as otherwise such DML must own an SW metadata lock on table
        which would be incompatible with ALTER's SNW lock.
      
      Questions for reviewer are marked by QQ.
     @ mysql-test/r/innodb_mysql_lock2.result
        Added test for bug #51263 "Deadlock between transactional
        SELECT and ALTER TABLE ... REBUILD PARTITION".
     @ mysql-test/suite/rpl_ndb/r/rpl_ndb_binlog_format_errors.result
        Since CREATE TRIGGER no longer acquires write lock on table
        it is no longer interpreted as an operation which modifies
        table data and therefore no longer fails if invoked for
        SBR-only engine in ROW mode.
     @ mysql-test/suite/rpl_ndb/t/rpl_ndb_binlog_format_errors.test
        Since CREATE TRIGGER no longer acquires write lock on table
        it is no longer interpreted as an operation which modifies
        table data and therefore no longer failes if invoked for
        SBR-only engine in ROW mode.
     @ mysql-test/t/innodb_mysql_lock2.test
        Added test for bug #51263 "Deadlock between transactional
        SELECT and ALTER TABLE ... REBUILD PARTITION".
     @ sql/ha_ndbcluster.cc
        Added question for reviewer.
     @ sql/ha_partition.cc
        When ALTER TABLE creates a new partition to be filled from
        other partition lock it in F_WRLCK mode instead of using
        mode which was used for locking the whole table (it is
        F_RDLCK now).
     @ sql/lock.cc
        Added questions for reviewer.
     @ sql/mdl.cc
        Updated outdated comment to reflect current situation.
     @ sql/sql_base.cc
        Added question for reviewer.
     @ sql/sql_table.cc
        mysql_admin_table():
          Use TL_WRITE_ALLOW_WRITE lock type instead of
          TL_WRITE_ALLOW_READ to determine that we need to acquire
          upgradable metadata lock. This should allow to completely
          get rid of TL_WRITE_ALLOW_READ in long term.
        mysql_recreate_table():
          ALTER TABLE now requires TL_READ_NO_INSERT thr_lock.c lock
          instead of TL_WRITE_ALLOW_READ.
     @ sql/sql_trigger.cc
        Changed CREATE/DROP TRIGGER implementation to use
        TL_READ_NO_INSERT lock instead of TL_WRITE_ALLOW_READ lock.
        The latter is no longer necessary since:
        a) We now can rely on metadata locks to achieve proper
           isolation between two DDL statements or DDL and DML
           statements.
        b) This statement does not change any data in table so there
           is no need to inform storage engine about it.
     @ sql/sql_yacc.yy
        Changed implementation of ALTER TABLE (and CREATE/DROP INDEX
        as a consequence) to use TL_READ_NO_INSERT lock instead of
        TL_WRITE_ALLOW_READ lock. This is possible since:
        a) We now can rely on metadata locks to achieve proper
           isolation between two DDL statements or DDL and DML
           statements.
        b) This statement only reads data in table being open.
           We write data only to the new version of table and
           then replace with it old version of table under
           X metadata lock.
        
        Thanks to this change InnoDB will no longer acquire LOCK_X
        locks on rows being read by ALTER TABLE (instead LOCK_S
        locks will be acquired) and thus cause of bug #51263
        "Deadlock between transactional SELECT and ALTER TABLE ...
        REBUILD PARTITION" is removed.
        
        Did the similar change for CREATE TRIGGER (see comments
        for sql_trigger.cc for details).
[8 May 2010 8:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/107792

3015 Dmitry Lenev	2010-05-08
      Pre-requisite patch for bug #51263 "Deadlock between
      transactional SELECT and ALTER TABLE ... REBUILD PARTITION".
      
      The goal of this patch is to decouple type of metadata
      lock acquired for table by open_tables() from type of
      table-level lock to be acquired on it.
      
      To achieve this we change approach to how we determine what
      type of metadata lock should be acquired on table to be open.
      Now instead of inferring it at open_tables() time from flags
      and type of table-level lock we rely on that type of metadata
      lock is properly set at parsing time and is not changed
      further.
     @ sql/ha_ndbcluster.cc
        Now one needs to properly initialize table list element's
        MDL_request object before calling mysql_rm_table_part2().
     @ sql/lock.cc
        lock_table_names() no longer initializes table list elements'
        MDL_request objects. Now proper initialization of these
        requests is a responsibility of the caller.
     @ sql/lock.h
        Removed MYSQL_OPEN_TAKE_UPGRADABLE_MDL flag which became
        unnecessary. Thanks to the fact that we don't reset type of
        requests for metadata locks between re-executions we now can
        figure out that upgradable locks are requested by simply
        looking at their type which were set in the parser. As result
        this flag became redundant.
     @ sql/mdl.h
        Added version of new operator which simplifies allocation of
        MDL_request objects on a MEM_ROOT.
     @ sql/sp_head.cc
        Added comment explaining why it is OK to infer type of
        metadata lock to request from type of table-level lock
        for prelocking.
        Added enum_mdl_type argument to sp_add_to_query_tables()
        to simplify its usage in trigger implementation.
     @ sql/sp_head.h
        Added enum_mdl_type argument to sp_add_to_query_tables()
        to simplify its usage in trigger implementation.
     @ sql/sql_base.cc
        - open_table_get_mdl_lock():
          Preserve type of MDL_request for table list element which
          was set in the parser by creating MDL_request objects on
          memory root if MYSQL_OPEN_FORCE_SHARED_MDL or
          MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL flag were specified.
          Thanks to this and to the fact that we no longer reset
          type of requests for metadata locks between re-executions
          we no longer need to acquire exclusive metadata lock on
          table to be created in a special way. This lock is acquired
          by code handling acquiring of upgradable locks.
        - Accordingly special lock strategy for table list elements
          which was used for such locks became unnecessary and was
          removed. Other strategies were renamed.
        - Since we no longer have guarantee that MDL_request object
          which were not satisfied due to lock conflict belongs to
          table list element Open_table_context class and its methods
          were extended to remember pointer to MDL_request which has
          caused problem at request_backoff_action() time and use it
          in recover_from_failed_open(). Similar approach is used
          for cases when problem from which we need to recover is
          not related to MDL but to the table itself. In this case
          we store pointer to the element of table list.
        - Changed open_tables()/open_tables_check_upgradable_mdl()/
          open_tables_acquire_upgradable_mdl() not to rely on
          MYSQL_OPEN_TAKE_UPGRADABLE_MDL flag to understand when
          upgradable metadata locks should be acquired and not to
          infer type of MDL lock from type of table-level lock.
          Instead we assume that type of MDL to be acquired was set
          in the parser (we can do this as type of MDL_request is
          no longer reset between re-executions).
     @ sql/sql_class.h
        Since we no longer have guarantee that MDL_request object
        which were not satisfied due to lock conflict belongs to
        table list element Open_table_context class and its methods
        were extended to remember pointer to MDL_request which has
        caused problem at request_backoff_action() time and use it
        in recover_from_failed_open(). Similar approach is used
        for cases when problem from which we need to recover is
        not related to MDL but to the table itself. In this case
        we store pointer to the element of table list.
     @ sql/sql_db.cc
        Now one needs to properly initialize table list element's
        MDL_request object before calling mysql_rm_table_part2()
        or mysql_rename_tables().
     @ sql/sql_lex.cc
        st_select_lex/st_select_lex_node::add_table_to_list() method
        now has argument which allows specify type of metadata lock
        to be requested for table list element being added.
     @ sql/sql_lex.h
        - st_select_lex/st_select_lex_node::add_table_to_list()
          method now has argument which specifies type of metadata
          lock to be requested for table list element being added.
          This allows to explicitly set type of MDL lock to be
          acquired for a DDL statement in parser. It is also more
          future-proof than inferring type of MDL request from type
          of table-level lock.
        - Added Yacc_state::m_mdl_type member which specifies which
          type of metadata lock should be requested for tables to be
          added to table list by a grammar rule in cases when the same
          rule is used in several statements requiring different kinds
          of metadata locks.
     @ sql/sql_parse.cc
        - st_select_lex::add_table_to_list() method now has argument
          which specifies type of metadata lock to be requested for
          table list element being added. This allows to explicitly
          set type of MDL lock to be acquired for a DDL statement in
          parser. It is also more future-proof than inferring type of
          MDL request from type of table-level lock.
        - EXCLUSIVE_DOWNGRADABLE_MDL lock strategy has a new name -
          OTLS_DOWNGRADE_IF_EXISTS.
        - Adjusted LOCK TABLES implementation to the fact that we no
          longer infer type of metadata lock to be acquired from table
          level lock and that type of MDL request is set at parsing.
          And thus MYSQL_OPEN_TAKE_UPGRADABLE_MDL flag became
          unnecessary.
     @ sql/sql_prepare.cc
        TABLE_LIST's lock strategy SHARED_MDL was renamed to OTLS_NONE
        as now it means that metadata lock should not be changed during
        call to open_table() (if it has been already acquired) and is
        also used for exclusive metadata lock.
     @ sql/sql_show.cc
        st_select_lex::add_table_to_list() method now has argument
        which specifies type of metadata lock to be requested for
        table list element being added.
     @ sql/sql_table.cc
        - Adjusted mysql_admin_table()'s code to the fact that
          open_tables() no longer determines what kind of metadata
          lock should be obtained basing on type of table-level
          lock and flags. Instead type of metadata lock for table
          to be open should be set before calling open_tables().
        - Changed mysql_alter_table() code to the facts:
          a) that now it is responsibility of caller to properly
          initalize MDL_request in table list elements before calling
          lock_table_names()
          b) and that MYSQL_OPEN_TAKE_UPGRADABLE_MDL is no longer
          necessary since type of metadata lock to be obtained
          at open_tables() time is set during parsing.
        - Changed code of mysql_recreate_table() to properly set
          type of metadata and table-level lock to be obtained
          by mysql_alter_table() which it calls.
     @ sql/sql_trigger.cc
        Instead of relying on MYSQL_OPEN_TAKE_UPGRADABLE_MDL flag to
        force open_tables() to take an upgradable lock we now specify
        exact type of lock to be taken when constructing table list
        element for table to be open for CREATE/DROP TRIGGER.
     @ sql/sql_view.cc
        We no longer use TABLE_LIST::EXCLUSIVE_MDL strategy to force
        open_tables() to take an exclusive metadata lock on view to
        be created. Instead we rely on parser setting proper type of
        metadata lock to request and open_tables() acquiring it.
        This became possible thanks to the fact that we no longer
        reset type of MDL_request between statement re-executions.
     @ sql/sql_yacc.yy
        Instead of inferring type of MDL_request for table to be
        open from type of table-level lock and flags passed to
        open_tables() we now explicitly specify them at parsing.
        This became possible thanks to the fact that we no longer
        reset type of MDL_request between statement re-executions.
        In future this should allow to decouple type of metadata
        lock from type of table-level lock.
        The only exception to this approach is statements implemented
        through mysql_admin_table() which re-uses same table list
        element several times with different types of table-level
        and metadata locks.
        We now also properly initialize MDL_request objects for table
        list elements which are later passed to lock_table_names()
        function.
     @ sql/table.cc
        Do not reset type of MDL_request between statement
        re-executions. This became unnecessesary as we no longer
        change type of MDL_request residing in table list element.
        In its turn this change allows to set type of MDL_request
        only once - at parsing time.
     @ sql/table.h
        Got rid of TABLE_LIST::EXCLUSIVE_MDL lock strategy.
        Now we can specify that we need to acquire exclusive lock
        on table to be processed by open_tables() through setting
        an appropriate type of MDL_request at parsing time (this
        became possible thanks to the fact that we no longer reset
        types of MDL_request's belonging to table list elements
        between statement re-execution).
        Strategy SHARED_MDL was renamed to OTLS_NONE as now it
        means that metadata lock should not be changed during call
        to open_table() (if it has been already acquired) and is
        also used for exclusive metadata lock.
        Strategy EXCLUSIVE_DOWNGRADABLE_MDL was renamed to
        OTLS_DOWNGRADE_IF_EXISTS.
[8 May 2010 10:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/107796

3016 Dmitry Lenev	2010-05-08
      Tentative fix for bug #51263 "Deadlock between transactional
      SELECT and ALTER TABLE ...  REBUILD PARTITION".
      
      ALTER TABLE on InnoDB table (including partitioned tables)
      acquired exclusive locks on rows of table being altered.
      In cases when there was concurrent transaction which did
      locking reads from this table this sometimes led to a
      deadlock which was not detected by MDL subsystem nor by
      InnoDB engine (and was reported only after exceeding
      innodb_lock_wait_timeout).
      
      This problem stemmed from the fact that ALTER TABLE acquired
      TL_WRITE_ALLOW_READ lock on table being altered. This lock
      was interpreted as a write lock and thus for table being
      altered handler::external_lock() method was called with
      F_WRLCK as an argument. As result InnoDB engine treated
      ALTER TABLE as an operation which is going to change data
      and acquired LOCK_X locks on rows being read from old
      version of table.
      
      In case when there was a transaction which already acquired
      SR metadata lock on table and some LOCK_S locks on its rows
      (e.g. by using it in subquery of DML statement) concurrent
      ALTER TABLE was blocked at the moment when it tried to
      acquire LOCK_X lock before reading one of these rows.
      The transaction's attempt to acquire SW metadata lock on
      table being altered led to deadlock, since it had to wait
      for ALTER TABLE to release SNW lock. This deadlock was not
      detected and got resolved only after timeout expiring
      because waiting were happening in two different subsystems.
      
      Similar deadlocks could have occured in other situations.
      
      This patch tries to solve the problem by changing ALTER TABLE
      implementation to use TL_READ_NO_INSERT lock instead of
      TL_WRITE_ALLOW_READ. After this step handler::external_lock()
      is called with F_RDLCK as an argument and InnoDB engine
      correctly interprets ALTER TABLE as operation which only
      reads data from original version of table. Thanks to this
      ALTER TABLE acquires only LOCK_S locks on rows it reads.
      This, in its turn, causes inter-subsystem deadlocks to go
      away, as all potential lock conflicts and thus deadlocks will
      be limited to metadata locking subsystem:
      
      - When ALTER TABLE reads rows from table being altered it
        can't encounter any locks which conflict with LOCK_S row
        locks. There should be no concurrent transactions holding
        LOCK_X row locks. Such a transaction should have been
        acquired SW metadata lock on table first which would have
        conflicted with ALTER's SNW lock.
      - Vice versa, when DML which runs concurrently with ALTER
        TABLE tries to lock row it should be requesting only LOCK_S
        lock which is compatible with locks acquired by ALTER,
        as otherwise such DML must own an SW metadata lock on table
        which would be incompatible with ALTER's SNW lock.
      
      Questions for reviewer are marked by QQ.
     @ mysql-test/r/innodb_mysql_lock2.result
        Added test for bug #51263 "Deadlock between transactional
        SELECT and ALTER TABLE ... REBUILD PARTITION".
     @ mysql-test/suite/rpl_ndb/r/rpl_ndb_binlog_format_errors.result
        Since CREATE TRIGGER no longer acquires write lock on table
        it is no longer interpreted as an operation which modifies
        table data and therefore no longer fails if invoked for
        SBR-only engine in ROW mode.
     @ mysql-test/suite/rpl_ndb/t/rpl_ndb_binlog_format_errors.test
        Since CREATE TRIGGER no longer acquires write lock on table
        it is no longer interpreted as an operation which modifies
        table data and therefore no longer fails if invoked for
        SBR-only engine in ROW mode.
     @ mysql-test/t/innodb_mysql_lock2.test
        Added test for bug #51263 "Deadlock between transactional
        SELECT and ALTER TABLE ... REBUILD PARTITION".
     @ sql/ha_ndbcluster.cc
        Added question for reviewer.
     @ sql/ha_partition.cc
        When ALTER TABLE creates a new partition to be filled from
        other partition lock it in F_WRLCK mode instead of using
        mode which was used for locking the whole table (it is
        F_RDLCK now).
     @ sql/lock.cc
        Added questions for reviewer.
     @ sql/mdl.cc
        Updated outdated comment to reflect current situation.
     @ sql/sql_base.cc
        Added question for reviewer.
     @ sql/sql_table.cc
        mysql_admin_table():
          Use TL_WRITE_ALLOW_WRITE lock type instead of
          TL_WRITE_ALLOW_READ to determine that we need to acquire
          upgradable metadata lock. This should allow to completely
          get rid of TL_WRITE_ALLOW_READ in long term.
        mysql_recreate_table():
          ALTER TABLE now requires TL_READ_NO_INSERT thr_lock.c lock
          instead of TL_WRITE_ALLOW_READ.
     @ sql/sql_trigger.cc
        Changed CREATE/DROP TRIGGER implementation to use
        TL_READ_NO_INSERT lock instead of TL_WRITE_ALLOW_READ lock.
        The latter is no longer necessary since:
        a) We now can rely on metadata locks to achieve proper
           isolation between two DDL statements or DDL and DML
           statements.
        b) This statement does not change any data in table so there
           is no need to inform storage engine about it.
     @ sql/sql_yacc.yy
        Changed implementation of ALTER TABLE (and CREATE/DROP INDEX
        as a consequence) to use TL_READ_NO_INSERT lock instead of
        TL_WRITE_ALLOW_READ lock. This is possible since:
        a) We now can rely on metadata locks to achieve proper
           isolation between two DDL statements or DDL and DML
           statements.
        b) This statement only reads data in table being open.
           We write data only to the new version of table and
           then replace with it old version of table under
           X metadata lock.
        
        Thanks to this change InnoDB will no longer acquire LOCK_X
        locks on rows being read by ALTER TABLE (instead LOCK_S
        locks will be acquired) and thus cause of bug #51263
        "Deadlock between transactional SELECT and ALTER TABLE ...
        REBUILD PARTITION" is removed.
        
        Did the similar change for CREATE TRIGGER (see comments
        for sql_trigger.cc for details).
[25 May 2010 12:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109162

3022 Dmitry Lenev	2010-05-25
      Pre-requisite patch for bug #51263 "Deadlock between
      transactional SELECT and ALTER TABLE ... REBUILD PARTITION".
      
      The goal of this patch is to decouple type of metadata
      lock acquired for table by open_tables() from type of
      table-level lock to be acquired on it.
      
      To achieve this we change approach to how we determine what
      type of metadata lock should be acquired on table to be open.
      Now instead of inferring it at open_tables() time from flags
      and type of table-level lock we rely on that type of metadata
      lock is properly set at parsing time and is not changed
      further.
     @ sql/ha_ndbcluster.cc
        Now one needs to properly initialize table list element's
        MDL_request object before calling mysql_rm_table_part2().
     @ sql/lock.cc
        lock_table_names() no longer initializes table list elements'
        MDL_request objects. Now proper initialization of these
        requests is a responsibility of the caller.
     @ sql/lock.h
        Removed MYSQL_OPEN_TAKE_UPGRADABLE_MDL flag which became
        unnecessary. Thanks to the fact that we don't reset type of
        requests for metadata locks between re-executions we now can
        figure out that upgradable locks are requested by simply
        looking at their type which were set in the parser. As result
        this flag became redundant.
     @ sql/mdl.h
        Added version of new operator which simplifies allocation of
        MDL_request objects on a MEM_ROOT.
     @ sql/sp_head.cc
        Added comment explaining why it is OK to infer type of
        metadata lock to request from type of table-level lock
        for prelocking.
        Added enum_mdl_type argument to sp_add_to_query_tables()
        to simplify its usage in trigger implementation.
     @ sql/sp_head.h
        Added enum_mdl_type argument to sp_add_to_query_tables()
        to simplify its usage in trigger implementation.
     @ sql/sql_base.cc
        - open_table_get_mdl_lock():
          Preserve type of MDL_request for table list element which
          was set in the parser by creating MDL_request objects on
          memory root if MYSQL_OPEN_FORCE_SHARED_MDL or
          MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL flag were specified.
          Thanks to this and to the fact that we no longer reset
          type of requests for metadata locks between re-executions
          we no longer need to acquire exclusive metadata lock on
          table to be created in a special way. This lock is acquired
          by code handling acquiring of upgradable locks.
          Also changed signature/calling convention for this function
          to simplify its usage.
        - Accordingly special lock strategy for table list elements
          which was used for such locks became unnecessary and was
          removed. Other strategies were renamed.
        - Since we no longer have guarantee that MDL_request object
          which were not satisfied due to lock conflict belongs to
          table list element Open_table_context class and its methods
          were extended to remember pointer to MDL_request which has
          caused problem at request_backoff_action() time and use it
          in recover_from_failed_open(). Similar approach is used
          for cases when problem from which we need to recover is
          not related to MDL but to the table itself. In this case
          we store pointer to the element of table list.
        - Changed open_tables()/open_tables_check_upgradable_mdl()/
          open_tables_acquire_upgradable_mdl() not to rely on
          MYSQL_OPEN_TAKE_UPGRADABLE_MDL flag to understand when
          upgradable metadata locks should be acquired and not to
          infer type of MDL lock from type of table-level lock.
          Instead we assume that type of MDL to be acquired was set
          in the parser (we can do this as type of MDL_request is
          no longer reset between re-executions).
     @ sql/sql_class.h
        Since we no longer have guarantee that MDL_request object
        which were not satisfied due to lock conflict belongs to
        table list element Open_table_context class and its methods
        were extended to remember pointer to MDL_request which has
        caused problem at request_backoff_action() time and use it
        in recover_from_failed_open(). Similar approach is used
        for cases when problem from which we need to recover is
        not related to MDL but to the table itself. In this case
        we store pointer to the element of table list.
     @ sql/sql_db.cc
        Now one needs to properly initialize table list element's
        MDL_request object before calling mysql_rm_table_part2()
        or mysql_rename_tables().
     @ sql/sql_lex.cc
        st_select_lex/st_select_lex_node::add_table_to_list() method
        now has argument which allows specify type of metadata lock
        to be requested for table list element being added.
     @ sql/sql_lex.h
        - st_select_lex/st_select_lex_node::add_table_to_list()
          method now has argument which specifies type of metadata
          lock to be requested for table list element being added.
          This allows to explicitly set type of MDL lock to be
          acquired for a DDL statement in parser. It is also more
          future-proof than inferring type of MDL request from type
          of table-level lock.
        - Added Yacc_state::m_mdl_type member which specifies which
          type of metadata lock should be requested for tables to be
          added to table list by a grammar rule in cases when the same
          rule is used in several statements requiring different kinds
          of metadata locks.
     @ sql/sql_parse.cc
        - st_select_lex::add_table_to_list() method now has argument
          which specifies type of metadata lock to be requested for
          table list element being added. This allows to explicitly
          set type of MDL lock to be acquired for a DDL statement in
          parser. It is also more future-proof than inferring type of
          MDL request from type of table-level lock.
        - EXCLUSIVE_DOWNGRADABLE_MDL lock strategy has a new name -
          OTLS_DOWNGRADE_IF_EXISTS.
        - Adjusted LOCK TABLES implementation to the fact that we no
          longer infer type of metadata lock to be acquired from table
          level lock and that type of MDL request is set at parsing.
          And thus MYSQL_OPEN_TAKE_UPGRADABLE_MDL flag became
          unnecessary.
     @ sql/sql_prepare.cc
        TABLE_LIST's lock strategy SHARED_MDL was renamed to OTLS_NONE
        as now it means that metadata lock should not be changed during
        call to open_table() (if it has been already acquired) and is
        also used for exclusive metadata lock.
     @ sql/sql_show.cc
        st_select_lex::add_table_to_list() method now has argument
        which specifies type of metadata lock to be requested for
        table list element being added.
     @ sql/sql_table.cc
        - Adjusted mysql_admin_table()'s code to the fact that
          open_tables() no longer determines what kind of metadata
          lock should be obtained basing on type of table-level
          lock and flags. Instead type of metadata lock for table
          to be open should be set before calling open_tables().
        - Changed mysql_alter_table() code to the facts:
          a) that now it is responsibility of caller to properly
          initalize MDL_request in table list elements before calling
          lock_table_names()
          b) and that MYSQL_OPEN_TAKE_UPGRADABLE_MDL is no longer
          necessary since type of metadata lock to be obtained
          at open_tables() time is set during parsing.
        - Changed code of mysql_recreate_table() to properly set
          type of metadata and table-level lock to be obtained
          by mysql_alter_table() which it calls.
     @ sql/sql_trigger.cc
        Instead of relying on MYSQL_OPEN_TAKE_UPGRADABLE_MDL flag to
        force open_tables() to take an upgradable lock we now specify
        exact type of lock to be taken when constructing table list
        element for table to be open for CREATE/DROP TRIGGER.
     @ sql/sql_view.cc
        We no longer use TABLE_LIST::EXCLUSIVE_MDL strategy to force
        open_tables() to take an exclusive metadata lock on view to
        be created. Instead we rely on parser setting proper type of
        metadata lock to request and open_tables() acquiring it.
        This became possible thanks to the fact that we no longer
        reset type of MDL_request between statement re-executions.
     @ sql/sql_yacc.yy
        Instead of inferring type of MDL_request for table to be
        open from type of table-level lock and flags passed to
        open_tables() we now explicitly specify them at parsing.
        This became possible thanks to the fact that we no longer
        reset type of MDL_request between statement re-executions.
        In future this should allow to decouple type of metadata
        lock from type of table-level lock.
        The only exception to this approach is statements implemented
        through mysql_admin_table() which re-uses same table list
        element several times with different types of table-level
        and metadata locks.
        We now also properly initialize MDL_request objects for table
        list elements which are later passed to lock_table_names()
        function.
     @ sql/table.cc
        Do not reset type of MDL_request between statement
        re-executions. This became unnecessesary as we no longer
        change type of MDL_request residing in table list element.
        In its turn this change allows to set type of MDL_request
        only once - at parsing time.
     @ sql/table.h
        Got rid of TABLE_LIST::EXCLUSIVE_MDL lock strategy.
        Now we can specify that we need to acquire exclusive lock
        on table to be processed by open_tables() through setting
        an appropriate type of MDL_request at parsing time (this
        became possible thanks to the fact that we no longer reset
        types of MDL_request's belonging to table list elements
        between statement re-execution).
        Strategy SHARED_MDL was renamed to OTLS_NONE as now it
        means that metadata lock should not be changed during call
        to open_table() (if it has been already acquired) and is
        also used for exclusive metadata lock.
        Strategy EXCLUSIVE_DOWNGRADABLE_MDL was renamed to
        OTLS_DOWNGRADE_IF_EXISTS.
[25 May 2010 16:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109210

3023 Dmitry Lenev	2010-05-25 [merge]
      Null-merged preliminary version of pre-requisite patch
      for bug #51263 "Deadlock between transactional SELECT
      and ALTER TABLE ... REBUILD PARTITION" into
      work-in-progress tree.
[26 May 2010 12:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109272

3024 Dmitry Lenev	2010-05-26
      Fix for bug #51263 "Deadlock between transactional
      SELECT and ALTER TABLE ...  REBUILD PARTITION".
      
      ALTER TABLE on InnoDB table (including partitioned tables)
      acquired exclusive locks on rows of table being altered.
      In cases when there was concurrent transaction which did
      locking reads from this table this sometimes led to a
      deadlock which was not detected by MDL subsystem nor by
      InnoDB engine (and was reported only after exceeding
      innodb_lock_wait_timeout).
      
      This problem stemmed from the fact that ALTER TABLE acquired
      TL_WRITE_ALLOW_READ lock on table being altered. This lock
      was interpreted as a write lock and thus for table being
      altered handler::external_lock() method was called with
      F_WRLCK as an argument. As result InnoDB engine treated
      ALTER TABLE as an operation which is going to change data
      and acquired LOCK_X locks on rows being read from old
      version of table.
      
      In case when there was a transaction which already acquired
      SR metadata lock on table and some LOCK_S locks on its rows
      (e.g. by using it in subquery of DML statement) concurrent
      ALTER TABLE was blocked at the moment when it tried to
      acquire LOCK_X lock before reading one of these rows.
      The transaction's attempt to acquire SW metadata lock on
      table being altered led to deadlock, since it had to wait
      for ALTER TABLE to release SNW lock. This deadlock was not
      detected and got resolved only after timeout expiring
      because waiting were happening in two different subsystems.
      
      Similar deadlocks could have occured in other situations.
      This patch tries to solve the problem by changing ALTER TABLE
      implementation to use TL_READ_NO_INSERT lock instead of
      TL_WRITE_ALLOW_READ. After this step handler::external_lock()
      is called with F_RDLCK as an argument and InnoDB engine
      correctly interprets ALTER TABLE as operation which only
      reads data from original version of table. Thanks to this
      ALTER TABLE acquires only LOCK_S locks on rows it reads.
      This, in its turn, causes inter-subsystem deadlocks to go
      away, as all potential lock conflicts and thus deadlocks will
      be limited to metadata locking subsystem:
      
      - When ALTER TABLE reads rows from table being altered it
        can't encounter any locks which conflict with LOCK_S row
        locks. There should be no concurrent transactions holding
        LOCK_X row locks. Such a transaction should have been
        acquired SW metadata lock on table first which would have
        conflicted with ALTER's SNW lock.
      - Vice versa, when DML which runs concurrently with ALTER
        TABLE tries to lock row it should be requesting only LOCK_S
        lock which is compatible with locks acquired by ALTER,
        as otherwise such DML must own an SW metadata lock on table
        which would be incompatible with ALTER's SNW lock.
     @ mysql-test/r/innodb_mysql_lock2.result
        Added test for bug #51263 "Deadlock between transactional
        SELECT and ALTER TABLE ... REBUILD PARTITION".
     @ mysql-test/suite/rpl_ndb/r/rpl_ndb_binlog_format_errors.result
        Since CREATE TRIGGER no longer acquires write lock on table
        it is no longer interpreted as an operation which modifies
        table data and therefore no longer fails if invoked for
        SBR-only engine in ROW mode.
     @ mysql-test/suite/rpl_ndb/t/rpl_ndb_binlog_format_errors.test
        Since CREATE TRIGGER no longer acquires write lock on table
        it is no longer interpreted as an operation which modifies
        table data and therefore no longer fails if invoked for
        SBR-only engine in ROW mode.
     @ mysql-test/t/innodb_mysql_lock2.test
        Added test for bug #51263 "Deadlock between transactional
        SELECT and ALTER TABLE ... REBUILD PARTITION".
     @ sql/ha_partition.cc
        When ALTER TABLE creates a new partition to be filled from
        other partition lock it in F_WRLCK mode instead of using
        mode which was used for locking the whole table (it is
        F_RDLCK now).
     @ sql/lock.cc
        Replaced conditions which used TL_WRITE_ALLOW_READ
        lock type with equivalent conditions using
        TL_WRITE_ALLOW_WRITE. This should allow to get rid
        of TL_WRITE_ALLOW_READ lock type eventually.
     @ sql/mdl.cc
        Updated outdated comment to reflect current situation.
     @ sql/sql_base.cc
        Replaced conditions which used TL_WRITE_ALLOW_READ
        lock type with equivalent conditions using
        TL_WRITE_ALLOW_WRITE. This should allow to get rid
        of TL_WRITE_ALLOW_READ lock type eventually.
     @ sql/sql_table.cc
        mysql_admin_table():
          Use TL_WRITE_ALLOW_WRITE lock type instead of
          TL_WRITE_ALLOW_READ to determine that we need to acquire
          upgradable metadata lock. This should allow to completely
          get rid of TL_WRITE_ALLOW_READ in long term.
        mysql_recreate_table():
          ALTER TABLE now requires TL_READ_NO_INSERT thr_lock.c lock
          instead of TL_WRITE_ALLOW_READ.
     @ sql/sql_trigger.cc
        Changed CREATE/DROP TRIGGER implementation to use
        TL_READ_NO_INSERT lock instead of TL_WRITE_ALLOW_READ lock.
        The latter is no longer necessary since:
        a) We now can rely on metadata locks to achieve proper
           isolation between two DDL statements or DDL and DML
           statements.
        b) This statement does not change any data in table so there
           is no need to inform storage engine about it.
     @ sql/sql_yacc.yy
        Changed implementation of ALTER TABLE (and CREATE/DROP INDEX
        as a consequence) to use TL_READ_NO_INSERT lock instead of
        TL_WRITE_ALLOW_READ lock. This is possible since:
        a) We now can rely on metadata locks to achieve proper
           isolation between two DDL statements or DDL and DML
           statements.
        b) This statement only reads data in table being open.
           We write data only to the new version of table and
           then replace with it old version of table under
           X metadata lock.
        
        Thanks to this change InnoDB will no longer acquire LOCK_X
        locks on rows being read by ALTER TABLE (instead LOCK_S
        locks will be acquired) and thus cause of bug #51263
        "Deadlock between transactional SELECT and ALTER TABLE ...
        REBUILD PARTITION" is removed.
        
        Did the similar change for CREATE TRIGGER (see comments
        for sql_trigger.cc for details).
[30 May 2010 7:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109551

3030 Dmitry Lenev	2010-05-30
      Fixed typo which was introduced by pre-requisite patch for
      bug #51263 "Deadlock between transactional SELECT and ALTER
      TABLE ... REBUILD PARTITION" and has been causing compilation
      error when server was built with NDB support.
[31 May 2010 19:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109636

3033 Konstantin Osipov	2010-05-31
      A follow up patch for the fix for Bug#51263 "Deadlock between 
      transactional SELECT and ALTER TABLE ...  REBUILD PARTITION".
      
      Remove unused code - TL_WRITE_ALLOW_READ thr_lock.c lock.
     @ include/thr_lock.h
        Remove TL_WRITE_ALLOW_READ.
     @ mysys/CMakeLists.txt
        Add thr_lock test to the list of CMake executables to build.
     @ mysys/thr_lock.c
        Remove TL_WRITE_ALLOW_READ thr_lock.c lock.
        Consequently, simplify lock compatibility rules.
        Remove unused code.
     @ sql/sql_test.cc
        Remove TL_WRITE_ALLOW_READ lock.
[31 May 2010 20:11] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109637

3034 Konstantin Osipov	2010-06-01
      A follow up patch for the fix for Bug#51263 "Deadlock between
       transactional SELECT and ALTER TABLE ...  REBUILD PARTITION".
      
      Move declarations of sql_base.cc classes to sql_base.h
      (previously declared in sql_class.h).
      Became possible after a header file split.
     @ sql/sql_base.cc
        Make sql_base.h the first include in sql_base.cc.
     @ sql/sql_base.h
        Add declarations of Prelocking_strategy and Open_table_context.
     @ sql/sql_class.h
        Remove declarations of Prelocking_strategy and Open_table_context
        (moved).
[31 May 2010 21:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109638

3035 Konstantin Osipov	2010-06-01
      A follow up patch for the fix for Bug#51263 "Deadlock between
      transactional SELECT and ALTER TABLE ...  REBUILD PARTITION".
      
      Make open flags part of Open_table_context.
      This allows to simplify some code and (in future)
      enforce some invariants.
     @ sql/sql_base.cc
        open_table() flags are part of Open_table_context.
        Remove dead code that would check for OPEN_VIEW_NO_PARSE,
        which is not an open table flag.
     @ sql/sql_base.h
        Move flags to Open_table_context. Reorder Open_table_context
        members to compact the structure footprint.
[1 Jun 2010 10:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109682

3033 Konstantin Osipov	2010-06-01
      A follow up patch for the fix for Bug#51263 "Deadlock between 
      transactional SELECT and ALTER TABLE ...  REBUILD PARTITION".
      
      Remove unused code - TL_WRITE_ALLOW_READ thr_lock.c lock.
     @ include/thr_lock.h
        Remove TL_WRITE_ALLOW_READ.
     @ mysys/CMakeLists.txt
        Add thr_lock test to the list of CMake executables to build.
     @ mysys/thr_lock.c
        Remove TL_WRITE_ALLOW_READ thr_lock.c lock.
        Consequently, simplify lock compatibility rules.
        Remove unused code.
     @ sql/sql_test.cc
        Remove TL_WRITE_ALLOW_READ lock.
[1 Jun 2010 10:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109684

3034 Konstantin Osipov	2010-06-01
      A follow up patch for the fix for Bug#51263 "Deadlock between
       transactional SELECT and ALTER TABLE ...  REBUILD PARTITION".
      
      Move declarations of sql_base.cc classes to sql_base.h
      (previously declared in sql_class.h).
      Became possible after a header file split.
     @ sql/sql_base.cc
        Make sql_base.h the first include in sql_base.cc.
     @ sql/sql_base.h
        Add declarations of Prelocking_strategy and Open_table_context.
     @ sql/sql_class.h
        Remove declarations of Prelocking_strategy and Open_table_context
        (moved).
[1 Jun 2010 10:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109689

3035 Konstantin Osipov	2010-06-01
      A follow up patch for the fix for Bug#51263 "Deadlock between
      transactional SELECT and ALTER TABLE ...  REBUILD PARTITION".
      
      Make open flags part of Open_table_context.
      This allows to simplify some code and (in future)
      enforce the invariant that we don't, say, request a back 
      off on the table when there is MYSQL_OPEN_IGNORE_FLUSH 
      flag.
     @ sql/sql_base.cc
        open_table() flags are part of Open_table_context.
        Remove dead code that would check for OPEN_VIEW_NO_PARSE,
        which is not an open table flag.
     @ sql/sql_base.h
        Move flags to Open_table_context. Reorder Open_table_context
        members to compact the structure footprint.
     @ sql/sql_insert.cc
        Update with a new calling signature of open_table().
     @ sql/sql_table.cc
        Update with a new calling signature of open_table().
[1 Jun 2010 10:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109698

3035 Konstantin Osipov	2010-06-01
      A follow up patch for the fix for Bug#51263 "Deadlock between
      transactional SELECT and ALTER TABLE ...  REBUILD PARTITION".
      
      Make open flags part of Open_table_context.
      This allows to simplify some code and (in future)
      enforce the invariant that we don't, say, request a back 
      off on the table when there is MYSQL_OPEN_IGNORE_FLUSH 
      flag.
     @ sql/sql_base.cc
        open_table() flags are part of Open_table_context.
        Remove dead code that would check for OPEN_VIEW_NO_PARSE,
        which is not an open table flag.
     @ sql/sql_base.h
        Move flags to Open_table_context. Reorder Open_table_context
        members to compact the structure footprint.
     @ sql/sql_insert.cc
        Update with a new calling signature of open_table().
     @ sql/sql_table.cc
        Update with a new calling signature of open_table().
[22 Jun 2010 13:08] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100622130139-u05awgya93zvbsop) (version source revid:marko.makela@oracle.com-20100603095032-v5ptkkzt1bhz0m1d) (merge vers: 5.1.48) (pib:16)
[22 Jun 2010 13:10] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100622130623-r7yhm89fz9n5t9nb) (version source revid:alik@sun.com-20100622130528-187gd949sa9b6pa6) (pib:16)
[21 Jul 2010 2:07] Paul Dubois
Noted in 5.5.5 changelog.

ALTER TABLE on InnoDB tables (including partitioned tables) acquired
exclusive locks on rows of the table being altered. If there was a 
concurrent transaction that did locking reads from this table, this
sometimes led to a deadlock that was not detected by the metadata
lock subsystem or by InnoDB (and was reported only after exceeding
innodb_lock_wait_timeout).