3190 Mattias Jonsson 2010-08-20
Bug#54747: Deadlock between REORGANIZE PARTITION and SELECT is not detected
The ALTER PARTITION and SELECT seemed to be deadlocked
when having innodb_thread_concurrency = 1.
Problem was that there was unreleased latches
in the ALTER PARTITION thread which was needed
by the SELECT thread to be able to continue.
Solution was to release the latches by commit
before requesting upgrade to exclusive MDL lock.
Updated according to reviewers comments (3).
@ mysql-test/r/partition_innodb.result
updated test result
@ mysql-test/t/partition_innodb.test
added test
@ sql/sql_partition.cc
Moved implicit commit into mysql_change_partition
so that if latches are taken, they are always released
before waiting on exclusive lock.
@ sql/sql_table.cc
refactored the code to prepare and commit
around copy_data_between_tables, to be able
to reuse it in mysql_change_partitions
@ sql/sql_table.h
exporting mysql_trans_prepare/commit_alter_copy_data
modified:
mysql-test/r/partition_innodb.result
mysql-test/t/partition_innodb.test
sql/sql_partition.cc
sql/sql_table.cc
sql/sql_table.h
3189 Christopher Powers 2010-08-20 [merge]
merge
removed:
mysql-test/include/have_thread_concurrency.inc
mysql-test/suite/rpl/t/rpl_mixed_row_innodb.test
added:
mysql-test/r/ctype_utf16_def.result
mysql-test/suite/rpl_ndb/t/rpl_ndb_stm_innodb-slave.opt
mysql-test/t/ctype_utf16_def-master.opt
mysql-test/t/ctype_utf16_def.test
sql/sql_reload.cc
sql/sql_reload.h
modified:
.bzrignore
client/mysql.cc
config/ac-macros/plugins.m4
configure.in
include/my_pthread.h
include/mysql/psi/mysql_thread.h
libmysqld/CMakeLists.txt
libmysqld/Makefile.am
mysql-test/CMakeLists.txt
mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test
mysql-test/extra/binlog_tests/mix_innodb_myisam_side_effects.test
mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.inc
mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.test
mysql-test/extra/rpl_tests/rpl_innodb.test
mysql-test/include/check_no_concurrent_insert.inc
mysql-test/include/ctype_numconv.inc
mysql-test/include/handler.inc
mysql-test/include/mix1.inc
mysql-test/r/case.result
mysql-test/r/ctype_binary.result
mysql-test/r/ctype_cp1251.result
mysql-test/r/ctype_cp932_binlog_stm.result
mysql-test/r/ctype_latin1.result
mysql-test/r/ctype_ucs.result
mysql-test/r/ctype_utf32.result
mysql-test/r/ctype_utf8.result
mysql-test/r/events_trans.result
mysql-test/r/flush.result
mysql-test/r/func_str.result
mysql-test/r/information_schema_inno.result
mysql-test/r/mdl_sync.result
mysql-test/r/mysql.result
mysql-test/r/mysqlbinlog.result
mysql-test/r/query_cache.result
mysql-test/r/schema.result
mysql-test/r/sp-lock.result
mysql-test/r/sp-threads.result
mysql-test/suite/binlog/r/binlog_database.result
mysql-test/suite/binlog/r/binlog_innodb_row.result
mysql-test/suite/binlog/r/binlog_row_binlog.result
mysql-test/suite/binlog/r/binlog_row_drop_tbl.result
mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result
mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
mysql-test/suite/binlog/r/binlog_stm_binlog.result
mysql-test/suite/binlog/r/binlog_stm_blackhole.result
mysql-test/suite/binlog/r/binlog_stm_drop_tbl.result
mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result
mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
mysql-test/suite/binlog/r/binlog_stm_row.result
mysql-test/suite/binlog/t/binlog_stm_row.test
mysql-test/suite/funcs_1/datadict/processlist_val.inc
mysql-test/suite/funcs_1/r/processlist_val_no_prot.result
mysql-test/suite/funcs_1/r/processlist_val_ps.result
mysql-test/suite/funcs_2/charset/charset_master.test
mysql-test/suite/funcs_2/r/innodb_charset.result
mysql-test/suite/funcs_2/r/memory_charset.result
mysql-test/suite/funcs_2/r/myisam_charset.result
mysql-test/suite/funcs_2/t/disabled.def
mysql-test/suite/innodb/t/innodb-lock.test
mysql-test/suite/parts/t/partition_debug_sync_innodb.test
mysql-test/suite/perfschema/r/binlog_mix.result
mysql-test/suite/perfschema/r/binlog_row.result
mysql-test/suite/perfschema/r/binlog_stmt.result
mysql-test/suite/perfschema/r/dml_setup_instruments.result
mysql-test/suite/perfschema/r/server_init.result
mysql-test/suite/rpl/r/rpl_conditional_comments.result
mysql-test/suite/rpl/r/rpl_drop_if_exists.result
mysql-test/suite/rpl/r/rpl_mixed_drop_create_temp_table.result
mysql-test/suite/rpl/r/rpl_mixed_implicit_commit_binlog.result
mysql-test/suite/rpl/r/rpl_mixed_mixing_engines.result
mysql-test/suite/rpl/r/rpl_non_direct_mixed_mixing_engines.result
mysql-test/suite/rpl/r/rpl_non_direct_row_mixing_engines.result
mysql-test/suite/rpl/r/rpl_non_direct_stm_mixing_engines.result
mysql-test/suite/rpl/r/rpl_row_drop.result
mysql-test/suite/rpl/r/rpl_row_drop_create_temp_table.result
mysql-test/suite/rpl/r/rpl_row_implicit_commit_binlog.result
mysql-test/suite/rpl/r/rpl_row_log.result
mysql-test/suite/rpl/r/rpl_row_log_innodb.result
mysql-test/suite/rpl/r/rpl_row_mixing_engines.result
mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
mysql-test/suite/rpl/r/rpl_row_show_relaylog_events.result
mysql-test/suite/rpl/r/rpl_server_id.result
mysql-test/suite/rpl/r/rpl_sp.result
mysql-test/suite/rpl/r/rpl_stm_drop_create_temp_table.result
mysql-test/suite/rpl/r/rpl_stm_implicit_commit_binlog.result
mysql-test/suite/rpl/r/rpl_stm_log.result
mysql-test/suite/rpl/r/rpl_stm_mix_show_relaylog_events.result
mysql-test/suite/rpl/r/rpl_stm_mixing_engines.result
mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result
mysql-test/suite/rpl/t/rpl_savepoint.test
mysql-test/suite/rpl/t/rpl_sp.test
mysql-test/suite/rpl/t/rpl_stm_innodb.test
mysql-test/suite/rpl/t/rpl_view_multi.test
mysql-test/suite/rpl_ndb/r/rpl_ndb_dd_basic.result
mysql-test/suite/rpl_ndb/r/rpl_ndb_mixed_implicit_commit_binlog.result
mysql-test/suite/rpl_ndb/r/rpl_ndb_row_implicit_commit_binlog.result
mysql-test/suite/rpl_ndb/t/disabled.def
mysql-test/suite/rpl_ndb/t/rpl_ndb_2other-slave.opt
mysql-test/suite/sys_vars/r/concurrent_insert_func.result
mysql-test/suite/sys_vars/r/query_cache_wlock_invalidate_func.result
mysql-test/suite/sys_vars/t/concurrent_insert_func.test
mysql-test/suite/sys_vars/t/delayed_insert_limit_func.test
mysql-test/suite/sys_vars/t/query_cache_wlock_invalidate_func.test
mysql-test/suite/sys_vars/t/sql_low_priority_updates_func.test
mysql-test/suite/sys_vars/t/thread_concurrency_basic.test
mysql-test/t/case.test
mysql-test/t/ctype_utf32.test
mysql-test/t/ctype_utf8.test
mysql-test/t/delayed.test
mysql-test/t/disabled.def
mysql-test/t/events_trans.test
mysql-test/t/flush.test
mysql-test/t/func_str.test
mysql-test/t/information_schema.test
mysql-test/t/information_schema_inno.test
mysql-test/t/innodb_mysql_lock.test
mysql-test/t/innodb_mysql_lock2.test
mysql-test/t/insert_notembedded.test
mysql-test/t/kill.test
mysql-test/t/lock_multi.test
mysql-test/t/lock_sync.test
mysql-test/t/mdl_sync.test
mysql-test/t/merge-big.test
mysql-test/t/multi_update.test
mysql-test/t/mysql.test
mysql-test/t/query_cache.test
mysql-test/t/query_cache_28249.test
mysql-test/t/schema.test
mysql-test/t/sp-lock.test
mysql-test/t/sp_notembedded.test
mysql-test/t/sp_sync.test
mysql-test/t/status.test
mysql-test/t/trigger_notembedded.test
mysql-test/t/view.test
mysys/thr_lock.c
mysys/thr_rwlock.c
scripts/CMakeLists.txt
scripts/mysqlaccess.conf*
sql/CMakeLists.txt
sql/Makefile.am
sql/datadict.cc
sql/event_db_repository.cc
sql/events.cc
sql/ha_ndbcluster.cc
sql/ha_ndbcluster_binlog.cc
sql/ha_ndbcluster_binlog.h
sql/handler.cc
sql/handler.h
sql/item.h
sql/item_cmpfunc.cc
sql/item_func.cc
sql/item_strfunc.cc
sql/item_strfunc.h
sql/lock.cc
sql/lock.h
sql/log.cc
sql/log_event.cc
sql/mdl.cc
sql/mdl.h
sql/mysqld.cc
sql/mysqld.h
sql/sql_admin.cc
sql/sql_base.cc
sql/sql_base.h
sql/sql_class.cc
sql/sql_handler.cc
sql/sql_insert.cc
sql/sql_lex.cc
sql/sql_lex.h
sql/sql_parse.cc
sql/sql_parse.h
sql/sql_partition.cc
sql/sql_plugin.cc
sql/sql_rename.cc
sql/sql_servers.cc
sql/sql_show.cc
sql/sql_table.cc
sql/sql_table.h
sql/sql_test.cc
sql/sql_trigger.cc
sql/sql_truncate.cc
sql/sql_view.cc
sql/sql_yacc.yy
sql/sys_vars.cc
sql/table.cc
sql/table.h
storage/innobase/plug.in
storage/myisam/ft_stopwords.c
=== modified file 'mysql-test/r/partition_innodb.result'
--- a/mysql-test/r/partition_innodb.result 2010-08-13 07:50:25 +0000
+++ b/mysql-test/r/partition_innodb.result 2010-08-20 17:15:48 +0000
@@ -1,5 +1,41 @@
drop table if exists t1, t2;
#
+# Bug#54747: Deadlock between REORGANIZE PARTITION and
+# SELECT is not detected
+#
+SET @old_innodb_thread_concurrency:= @@innodb_thread_concurrency;
+SET GLOBAL innodb_thread_concurrency = 1;
+CREATE TABLE t1
+(user_num BIGINT,
+hours SMALLINT,
+KEY user_num (user_num))
+ENGINE = InnoDB
+PARTITION BY RANGE COLUMNS (hours)
+(PARTITION hour_003 VALUES LESS THAN (3),
+PARTITION hour_004 VALUES LESS THAN (4),
+PARTITION hour_005 VALUES LESS THAN (5),
+PARTITION hour_last VALUES LESS THAN (MAXVALUE));
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+BEGIN;
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+5
+# con1
+# SEND a ALTER PARTITION which waits on the ongoing transaction.
+ALTER TABLE t1
+REORGANIZE PARTITION hour_003, hour_004 INTO
+(PARTITION oldest VALUES LESS THAN (4));
+# Connection default wait until the ALTER is in 'waiting for table...'
+# state and then continue the transaction by trying a SELECT
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+5
+COMMIT;
+# con1, reaping ALTER.
+# Disconnecting con1 and switching to default. Cleaning up.
+SET GLOBAL innodb_thread_concurrency = @old_innodb_thread_concurrency;
+DROP TABLE t1;
+#
# Bug#50418: DROP PARTITION does not interact with transactions
#
CREATE TABLE t1 (
=== modified file 'mysql-test/t/partition_innodb.test'
--- a/mysql-test/t/partition_innodb.test 2010-08-13 07:50:25 +0000
+++ b/mysql-test/t/partition_innodb.test 2010-08-20 17:15:48 +0000
@@ -9,6 +9,63 @@ drop table if exists t1, t2;
let $MYSQLD_DATADIR= `SELECT @@datadir`;
--echo #
+--echo # Bug#54747: Deadlock between REORGANIZE PARTITION and
+--echo # SELECT is not detected
+--echo #
+
+SET @old_innodb_thread_concurrency:= @@innodb_thread_concurrency;
+SET GLOBAL innodb_thread_concurrency = 1;
+
+CREATE TABLE t1
+(user_num BIGINT,
+ hours SMALLINT,
+ KEY user_num (user_num))
+ENGINE = InnoDB
+PARTITION BY RANGE COLUMNS (hours)
+(PARTITION hour_003 VALUES LESS THAN (3),
+ PARTITION hour_004 VALUES LESS THAN (4),
+ PARTITION hour_005 VALUES LESS THAN (5),
+ PARTITION hour_last VALUES LESS THAN (MAXVALUE));
+
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+
+BEGIN;
+SELECT COUNT(*) FROM t1;
+
+--echo # con1
+--connect (con1,localhost,root,,)
+--echo # SEND a ALTER PARTITION which waits on the ongoing transaction.
+--send
+ALTER TABLE t1
+REORGANIZE PARTITION hour_003, hour_004 INTO
+(PARTITION oldest VALUES LESS THAN (4));
+
+--echo # Connection default wait until the ALTER is in 'waiting for table...'
+--echo # state and then continue the transaction by trying a SELECT
+--connection default
+let $wait_condition =
+SELECT COUNT(*) = 1
+FROM information_schema.processlist
+WHERE INFO like 'ALTER TABLE t1%REORGANIZE PARTITION hour_003, hour_004%'
+AND STATE = 'Waiting for table metadata lock';
+--source include/wait_condition.inc
+SELECT COUNT(*) FROM t1;
+COMMIT;
+
+--echo # con1, reaping ALTER.
+--connection con1
+--reap
+
+--echo # Disconnecting con1 and switching to default. Cleaning up.
+--disconnect con1
+
+--connection default
+
+SET GLOBAL innodb_thread_concurrency = @old_innodb_thread_concurrency;
+DROP TABLE t1;
+
+
+--echo #
--echo # Bug#50418: DROP PARTITION does not interact with transactions
--echo #
CREATE TABLE t1 (
=== modified file 'sql/sql_partition.cc'
--- a/sql/sql_partition.cc 2010-08-18 11:29:04 +0000
+++ b/sql/sql_partition.cc 2010-08-20 17:15:48 +0000
@@ -63,6 +63,7 @@
#include "sql_table.h" // build_table_filename,
// build_table_shadow_filename,
// table_to_filename
+ // mysql_*_alter_copy_data
#include "opt_range.h" // store_key_image_to_rec
#include "sql_analyse.h" // append_escaped
@@ -4377,7 +4378,6 @@ static int fast_end_partition(THD *thd,
ALTER_PARTITION_PARAM_TYPE *lpt,
bool written_bin_log)
{
- int error;
char tmp_name[80];
DBUG_ENTER("fast_end_partition");
@@ -4386,13 +4386,6 @@ static int fast_end_partition(THD *thd,
if (!is_empty)
query_cache_invalidate3(thd, table_list, 0);
- error= trans_commit_stmt(thd);
- if (trans_commit_implicit(thd))
- error= 1;
-
- if (error)
- DBUG_RETURN(TRUE); /* The error has been reported */
-
if ((!is_empty) && (!written_bin_log) &&
(!thd->lex->no_write_to_binlog) &&
write_bin_log(thd, FALSE, thd->query(), thd->query_length()))
@@ -5535,17 +5528,25 @@ static bool mysql_change_partitions(ALTE
char path[FN_REFLEN+1];
int error;
handler *file= lpt->table->file;
+ THD *thd= lpt->thd;
DBUG_ENTER("mysql_change_partitions");
build_table_filename(path, sizeof(path) - 1, lpt->db, lpt->table_name, "", 0);
+
+ if(mysql_trans_prepare_alter_copy_data(thd))
+ DBUG_RETURN(TRUE);
+
if ((error= file->ha_change_partitions(lpt->create_info, path, &lpt->copied,
&lpt->deleted, lpt->pack_frm_data,
lpt->pack_frm_len)))
{
file->print_error(error, MYF(error != ER_OUTOFMEMORY ? 0 : ME_FATALERROR));
- DBUG_RETURN(TRUE);
}
- DBUG_RETURN(FALSE);
+
+ if (mysql_trans_commit_alter_copy_data(thd))
+ DBUG_RETURN(TRUE); /* The error has been reported */
+
+ DBUG_RETURN(test(error));
}
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2010-08-20 08:24:32 +0000
+++ b/sql/sql_table.cc 2010-08-20 17:15:48 +0000
@@ -6740,6 +6740,54 @@ err_with_mdl:
}
/* mysql_alter_table */
+
+
+/**
+ Prepare the transaction for the alter table's copy phase.
+*/
+
+bool mysql_trans_prepare_alter_copy_data(THD *thd)
+{
+ DBUG_ENTER("mysql_prepare_alter_copy_data");
+ /*
+ Turn off recovery logging since rollback of an alter table is to
+ delete the new table so there is no need to log the changes to it.
+
+ This needs to be done before external_lock.
+ */
+ if (ha_enable_transaction(thd, FALSE))
+ DBUG_RETURN(TRUE);
+ DBUG_RETURN(FALSE);
+}
+
+
+/**
+ Commit the copy phase of the alter table.
+*/
+
+bool mysql_trans_commit_alter_copy_data(THD *thd)
+{
+ bool error= FALSE;
+ DBUG_ENTER("mysql_commit_alter_copy_data");
+
+ if (ha_enable_transaction(thd, TRUE))
+ DBUG_RETURN(TRUE);
+
+ /*
+ Ensure that the new table is saved properly to disk before installing
+ the new .frm.
+ And that InnoDB's internal latches are released, to avoid deadlock
+ when waiting on other instances of the table before rename (Bug#54747).
+ */
+ if (trans_commit_stmt(thd))
+ error= TRUE;
+ if (trans_commit_implicit(thd))
+ error= TRUE;
+
+ DBUG_RETURN(error);
+}
+
+
static int
copy_data_between_tables(TABLE *from,TABLE *to,
List<Create_field> &create,
@@ -6766,14 +6814,7 @@ copy_data_between_tables(TABLE *from,TAB
ulonglong prev_insert_id;
DBUG_ENTER("copy_data_between_tables");
- /*
- Turn off recovery logging since rollback of an alter table is to
- delete the new table so there is no need to log the changes to it.
-
- This needs to be done before external_lock
- */
- error= ha_enable_transaction(thd, FALSE);
- if (error)
+ if (mysql_trans_prepare_alter_copy_data(thd))
DBUG_RETURN(-1);
if (!(copy= new Copy_field[to->s->fields]))
@@ -6932,20 +6973,8 @@ copy_data_between_tables(TABLE *from,TAB
}
to->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
- if (ha_enable_transaction(thd, TRUE))
- {
+ if (mysql_trans_commit_alter_copy_data(thd))
error= 1;
- goto err;
- }
-
- /*
- Ensure that the new table is saved properly to disk so that we
- can do a rename
- */
- if (trans_commit_stmt(thd))
- error=1;
- if (trans_commit_implicit(thd))
- error=1;
err:
thd->variables.sql_mode= save_sql_mode;
=== modified file 'sql/sql_table.h'
--- a/sql/sql_table.h 2010-08-20 02:59:58 +0000
+++ b/sql/sql_table.h 2010-08-20 17:15:48 +0000
@@ -143,6 +143,8 @@ bool mysql_create_table_no_lock(THD *thd
bool mysql_prepare_alter_table(THD *thd, TABLE *table,
HA_CREATE_INFO *create_info,
Alter_info *alter_info);
+bool mysql_trans_prepare_alter_copy_data(THD *thd);
+bool mysql_trans_commit_alter_copy_data(THD *thd);
bool mysql_alter_table(THD *thd, char *new_db, char *new_name,
HA_CREATE_INFO *create_info,
TABLE_LIST *table_list,
Attachment: [text/bzr-bundle] bzr/mattias.jonsson@oracle.com-20100820171548-6lx5keun4ovejysz.bundle
| Thread |
|---|
| • bzr push into mysql-5.5-bugfixing branch (mattias.jonsson:3189 to 3190)Bug#54747 | Mattias Jonsson | 20 Aug |
