MySQL Bugs: #53121: INSERT and unindexed SELECT on same table deadlock even with low LIMIT

Bug #53121	INSERT and unindexed SELECT on same table deadlock even with low LIMIT
Submitted:	23 Apr 2010 16:32	Modified:	23 May 2010 18:17
Reporter:	Chris Hennick	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.26	OS:	Linux (OpenSUSE)
Assigned to:		CPU Architecture:	Any
Tags:	deadlock, INDEX, performance

Description:
I encountered this bug while running a PHP script whose function was to repeatedly fill in and execute, among others, the following queries as prepared statements:

insert ignore into card (name) values (?)
insert into spell (cardid, name, oracle_text, power, toughness, loyalty)
				  values (?, ?, ?, ?, ?, ?)
				  on duplicate key update oracle_text=?
select id from card where name = ?
select id from spell where name = ?

While it was running, I ran the following query on my database:

select * from card where card.id not in (select cardid from spell) limit 10;

card.id was an auto-increment primary key. spell.cardid was not indexed. I waited over a minute for it to finish before canceling, and the insert/update script also blocked during that time. This was on my university department's LAMP server, so I could probably have exploited this to take down the other students' websites if I'd wanted to.

Both tables used MyISAM and had four-digit row counts. I don't have the server's hardware specs.

How to repeat:
See above.

Suggested fix:
Adding an index took only 1.87 seconds:

alter table spell add index (cardid);

Running the above query with the index took only 0.08 seconds. This suggests that when a SELECT is being run on an unindexed column of a table that is being inserted into, that column should be temporarily indexed.

Why do you think this is a bug? The fact that MyISAM uses table-level locks is documented.

Adding index "temporary" could be a nice feature to have in some rare cases like this, but optimizer can not replace DBA in general. The fact that it is not smart enough to create index that DBA had not added (for some unknown reason) is not a bug by itself.

@Valerie: Because shouldn't either the script's queries or the select be allowed to keep running, one waiting on the other? In this case, *neither* was making any noticeable progress. That's what I call a bug.

Then, please, send the results of

show full processlist;

at the moment when you see this problem. 

It would be also nice to upgrade to 5.0.90, and 5.0.26 is ages old version. The bug, if any, can not be fixed in old version anyway.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".