Bug #30294 blackhole engine causes 100% with 2 alter table statements running
Submitted: 8 Aug 2007 7:06 Modified: 24 Jan 2008 19:59
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.0.48BK OS:Any
Assigned to: Sergey Glukhov CPU Architecture:Any
Tags: alter, blackhole, hang
Triage: D2 (Serious)

[8 Aug 2007 7:06] Shane Bester
Description:
100% cpu hang on this bug.  the queries aren't killable either.

mysql> show processlist;
------+---------------------+------------------------------------+
 Time | State               | Info                               |
------+---------------------+------------------------------------+
    1 |                     | NULL                               |
  271 | rename result table | alter table `t1` add key `a`(`id`) |
  271 | rename result table | alter table `t1` add key `a`(`id`) |
    0 | NULL                | show processlist                   |
------+---------------------+------------------------------------+
4 rows in set (0.00 sec)

t1 is a simple table defined as:

create table `t1`(`id` int)engine=blackhole

How to repeat:
see attachment.

Suggested fix:
If we are to support multiple storage engines and they will truly be pluggable, the locking interfaces should work better than this.
[8 Aug 2007 7:10] Shane Bester
testcase

Attachment: bug30294.c (text/plain), 5.12 KiB.

[8 Aug 2007 7:12] Shane Bester
luckily 5.1.21 didn't appear to have this problem
[9 Nov 2007 7:07] 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/37407

ChangeSet@1.2554, 2007-11-09 11:02:05+04:00, gluh@mysql.com +3 -0
  Bug#30294 blackhole engine causes 100% with 2 alter table statements running
  Implement neccessary shared lock structure for table locks.
  This is the backport of bug26241 fix.
[16 Nov 2007 10: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/37937

ChangeSet@1.2576, 2007-11-16 14:46:36+04:00, gluh@mysql.com +3 -0
  Bug#30294 blackhole engine causes 100% with 2 alter table statements running
  Implement neccessary shared lock structure for table locks.
  This is the backport of bug26241 fix.
[14 Dec 2007 8:15] Bugs System
Pushed into 5.0.54
[14 Dec 2007 8:18] Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 8:22] Bugs System
Pushed into 6.0.5-alpha
[24 Jan 2008 19:59] Paul Dubois
Noted in 5.0.54, 5.1.23, 6.0.5 changelogs.

Simultaneous ALTER TABLE statements for BLACKHOLE tables caused 100%
CPU use due to locking problems.