| Bug #54747 | Deadlock between REORGANIZE PARTITION and SELECT is not detected | ||
|---|---|---|---|
| Submitted: | 23 Jun 2010 16:06 | Modified: | 10 Sep 2010 2:47 |
| Reporter: | Elena Stepanova | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Locking | Severity: | S3 (Non-critical) |
| Version: | 5.5.3-m3, 5.5.5-m3 | OS: | Any |
| Assigned to: | Mattias Jonsson | CPU Architecture: | Any |
[23 Jun 2010 16:06]
Elena Stepanova
[23 Jun 2010 16:51]
Elena Stepanova
Also tried the patch for bug#53770 group (http://lists.mysql.com/commits/110773). With the patch, deadlock is still not detected, but the timeout does not cause disasters as it happens with current trunk, where the continuation of the original test case would be DROP DATABASE betony_features; DROP DATABASE betony_features' failed: 1010: Error dropping database (can't rmdir './betony_features', errno: 39) Since the patch for bug#53676 fixes this part, it was not filed in the original bug (the test case in 'How to repeat' does not reach this point as it fails on reap due to wrong error code).
[17 Aug 2010 11:03]
Mattias Jonsson
Seems to be solved by this patch:
=== modified file 'sql/sql_partition.cc'
--- sql/sql_partition.cc 2010-08-13 07:50:25 +0000
+++ sql/sql_partition.cc 2010-08-17 10:52:31 +0000
@@ -5535,9 +5535,20 @@
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);
+
+ /*
+ 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);
+
if ((error= file->ha_change_partitions(lpt->create_info, path, &lpt->copied,
&lpt->deleted, lpt->pack_frm_data,
lpt->pack_frm_len)))
@@ -5545,6 +5556,13 @@
file->print_error(error, MYF(error != ER_OUTOFMEMORY ? 0 : ME_FATALERROR));
DBUG_RETURN(TRUE);
}
+
+ if (ha_enable_transaction(thd, TRUE))
+ DBUG_RETURN(TRUE);
+ if (trans_commit_stmt(thd))
+ DBUG_RETURN(TRUE);
+ if (trans_commit_implicit(thd))
+ DBUG_RETURN(TRUE);
DBUG_RETURN(FALSE);
}
[17 Aug 2010 12:37]
Mattias Jonsson
seems related to bug#51263, the test case in this bug also deadlocks with REBUILD instead of REORGANIZE.
[17 Aug 2010 15:36]
Mattias Jonsson
The problem is that the alter still having internal innodb latches, which could be solved with the minimalistic patch:
=== modified file 'sql/sql_partition.cc'
--- sql/sql_partition.cc 2010-08-13 07:50:25 +0000
+++ sql/sql_partition.cc 2010-08-17 15:32:07 +0000
@@ -5545,6 +5545,7 @@
file->print_error(error, MYF(error != ER_OUTOFMEMORY ? 0 : ME_FATALERROR));
DBUG_RETURN(TRUE);
}
+ ha_release_temporary_latches(lpt->thd);
DBUG_RETURN(FALSE);
}
But I will do a patch inline with the current copy_data_between_tables() function to be consistent.
[17 Aug 2010 15:54]
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/115991 3183 Mattias Jonsson 2010-08-17 Bug#54747: Deadlock between REORGANIZE PARTITION and SELECT is not detected 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 before requesting upgrade to exclusive MDL lock. @ mysql-test/r/partition_debug_sync.result updated result @ mysql-test/t/partition_debug_sync.test added test @ sql/sql_base.cc New DEBUG_SYNC point @ 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 Clarifying the comment.
[20 Aug 2010 10:08]
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/116357 3184 Mattias Jonsson 2010-08-20 Bug#54747: Deadlock between REORGANIZE PARTITION and SELECT is not detected 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. @ mysql-test/r/partition_debug_sync.result updated result @ mysql-test/t/partition_debug_sync.test added test @ sql/sql_base.cc added debug sync for easier testing @ 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_prepare/commit_alter_copy_data
[20 Aug 2010 15:25]
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/116430 3184 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 (2). @ 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
[20 Aug 2010 17:16]
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/116441 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
[20 Aug 2010 19:06]
Mattias Jonsson
pushed into mysql-5.5-bugfixing
[25 Aug 2010 9:23]
Bugs System
Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)
[30 Aug 2010 8:32]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)
[30 Aug 2010 8:35]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)
[10 Sep 2010 2:47]
Paul DuBois
Noted in 5.5.6, 5.6.1 changelogs. With innodb_thread_concurrency = 1, ALTER TABLE ... REORGANIZE PARTITION and SELECT could deadlock. There were unreleased latches in the ALTER TABLE ... REORGANIZE PARTITION thread which were needed by the SELECT thread to be able to continue.
