Bug #25044 ALTER TABLE ... ENABLE KEYS acquires global 'opening tables' lock.
Submitted: 13 Dec 2006 16:09 Modified: 8 Feb 2007 18:02
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.0.32-bk, 5.1.14-bk OS:Any
Assigned to: Dmitry Lenev CPU Architecture:Any
Tags: bfsm_2006_12_21, regression, rt_q1_2007

[13 Dec 2006 16:09] Domas Mituzas
Description:
ALTER TABLE tbl ENABLE KEYS;

acquires a global lock which blocks opening of all other tables. 

This especially causes trouble with 'mysqldump' imports, which use the command. 

How to repeat:
Session 1:
mysql> alter table xxx enable keys;
Query OK, 0 rows affected (12.48 sec)

xxx - any big MyISAM table having indexes.

Session 2:
mysql> select host from mysql.user limit 1;
+------+
| host |
+------+
| %    | 
+------+
1 row in set (11.60 sec)

Suggested fix:
Release global locks when not needed.
[27 Dec 2006 19:58] Andrey Hristov
I thought it could be related to bug#24395 and bug#24219, but they are noted to be part of 5.0.32 and not 5.0.30 . But it should be checked whether they actually cause the regression.
[4 Jan 2007 8:18] 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/17604

ChangeSet@1.2354, 2007-01-04 11:19:31+03:00, dlenev@mockturtle.local +3 -0
  Proposed fix for bug #25044 "ALTER TABLE ... ENABLE KEYS acquires global
  'opening tables' lock."
  
  Execution of ALTER TABLE ... ENABLE KEYS on a table (which can take rather
  long time) prevented concurrent execution of all statements using tables.
  
  The problem was caused by the fact that we were holding LOCK_open mutex
  during whole duration of this statement and particularly during call
  to handler::enable_indexes(). This behavior was introduced as part of the
  fix for bug 14262 "SP: DROP PROCEDURE|VIEW (maybe more) write to binlog
  too late (race cond)"
  
  The patch simply restores old behavior. Note that we can safely do this as
  this operation takes exclusive lock (similar to name-lock) which blocks both
  DML and DDL on the table being altered.
  
  Question for reviewers is marked by QQ.
[19 Jan 2007 13:51] 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/18435

ChangeSet@1.2354, 2007-01-19 16:22:10+03:00, dlenev@mockturtle.local +5 -0
  Fix for bug #25044 "ALTER TABLE ... ENABLE KEYS acquires global 'opening
  tables' lock."
  
  Execution of ALTER TABLE ... ENABLE KEYS on a table (which can take rather
  long time) prevented concurrent execution of all statements using tables.
  
  The problem was caused by the fact that we were holding LOCK_open mutex
  during whole duration of this statement and particularly during call
  to handler::enable_indexes(). This behavior was introduced as part of the
  fix for bug 14262 "SP: DROP PROCEDURE|VIEW (maybe more) write to binlog
  too late (race cond)"
  
  The patch simply restores old behavior. Note that we can safely do this as
  this operation takes exclusive lock (similar to name-lock) which blocks both
  DML and DDL on the table being altered.
  
  It also introduces mysql-test/include/wait_slave_status.inc helper script
  which is used to make test-case for this bug robust enough.
[19 Jan 2007 18:53] 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/18469

ChangeSet@1.2354, 2007-01-19 21:54:10+03:00, dlenev@mockturtle.local +5 -0
  Fix for bug #25044 "ALTER TABLE ... ENABLE KEYS acquires global 'opening
  tables' lock."
  
  Execution of ALTER TABLE ... ENABLE KEYS on a table (which can take rather
  long time) prevented concurrent execution of all statements using tables.
  
  The problem was caused by the fact that we were holding LOCK_open mutex
  during whole duration of this statement and particularly during call
  to handler::enable_indexes(). This behavior was introduced as part of the
  fix for bug 14262 "SP: DROP PROCEDURE|VIEW (maybe more) write to binlog
  too late (race cond)"
  
  The patch simply restores old behavior. Note that we can safely do this as
  this operation takes exclusive lock (similar to name-lock) which blocks both
  DML and DDL on the table being altered.
  
  It also introduces mysql-test/include/wait_show_pattern.inc helper script
  which is used to make test-case for this bug robust enough.
[19 Jan 2007 20: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/18476

ChangeSet@1.2354, 2007-01-19 23:15:59+03:00, dlenev@mockturtle.local +5 -0
  Fix for bug #25044 "ALTER TABLE ... ENABLE KEYS acquires global 'opening
  tables' lock."
  
  Execution of ALTER TABLE ... ENABLE KEYS on a table (which can take rather
  long time) prevented concurrent execution of all statements using tables.
  
  The problem was caused by the fact that we were holding LOCK_open mutex
  during whole duration of this statement and particularly during call
  to handler::enable_indexes(). This behavior was introduced as part of the
  fix for bug 14262 "SP: DROP PROCEDURE|VIEW (maybe more) write to binlog
  too late (race cond)"
  
  The patch simply restores old behavior. Note that we can safely do this as
  this operation takes exclusive lock (similar to name-lock) which blocks both
  DML and DDL on the table being altered.
  
  It also introduces mysql-test/include/wait_show_pattern.inc helper script
  which is used to make test-case for this bug robust enough.
[20 Jan 2007 11: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/18502

ChangeSet@1.2409, 2007-01-20 14:08:53+03:00, dlenev@mockturtle.local +2 -0
  Fixed test case for bug #25044 "ALTER TABLE ... ENABLE KEYS acquires
  global 'opening tables' lock." after merging it into 5.1 tree.
[8 Feb 2007 18:02] Paul Dubois
Noted in 5.0.36, 5.1.16 changelogs.