| 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: | |
| 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: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).
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.