MySQL Bugs: #17245: Multiple select statements being locked with no update running

Bug #17245	Multiple select statements being locked with no update running
Submitted:	8 Feb 2006 21:35	Modified:	19 Mar 2006 15:18
Reporter:	Bruce Bristol	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.16	OS:	Linux (Linux db1.rev.internal 2.6.9-11.)
Assigned to:		CPU Architecture:	Any

Description:
It appears one select is running and has the others locked...  What could be causing this and is there a fix or work-around?

I'll attach a show full processlist for your review after the bug is opened.

Thank you!

How to repeat:
Happens all the time.  I may be able to send you data to experiement with.

show full processlist output

Attachment: db1.rev_lockup_error_20060207_processlist.txt (text/plain), 63.22 KiB.

We're sorry, but the bug system is not the appropriate forum for 
asking help on using MySQL products. Your problem is not the result 
of a bug.

Support on using our products is available both free in our forums
at http://forums.mysql.com and for a reasonable fee direct from our
skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

Interesting, as this does appear as a bug to me.

Why would one select statement lock up others?

According to your SHOW PROCESSLIST results, you have also delayed inserts etc. running. That results are not enough, though, to call anything a bug, because they do not show what was executed before. We need a repeatable set of actions that demonstrates the problem each and every time. Can you provide any?

Anyway, SHOW CREATE TABLE detail_current results are needed. So, please, send them.

I don't know how to reproduce the problem, I'm afraid.

Here's the create table you requested...

|CREATE TABLE `detail_current` (
  `day` date NOT NULL default '0000-00-00',
  `pplacement_id` int(11) NOT NULL default '0',
  `product_id` int(11) NOT NULL default '0',
  `publisher_id` int(11) NOT NULL default '0',
  `site_id` int(11) NOT NULL default '0',
  `advertiser_id` int(11) NOT NULL default '0',
  `campaign_id` int(11) NOT NULL default '0',
  `creative_id` int(11) NOT NULL default '0',
  `country` char(2) default NULL,
  `hour` int(2) NOT NULL default '0',
  `timeofday` enum('night','evening','day') default NULL,
  `partofweek` enum('weekday','weekend') default NULL,
  `v` int(11) NOT NULL default '0',
  `c` int(11) NOT NULL default '0',
  `p` int(11) NOT NULL default '0',
  `p2` int(11) NOT NULL default '0',
  `p3` int(11) NOT NULL default '0',
  `revenue` float(14,7) NOT NULL default '0.0000000',
  `commission` float(14,7) NOT NULL default '0.0000000',
  `clicks_unique_us` int(11) NOT NULL default '0',
  KEY `param` (`day`,`pplacement_id`,`product_id`,`publisher_id`,`site_id`,`advertiser_id`,`campaign_id`,`creative_id`,`country`,`hour`,`timeofday`,`partofweek`),
  KEY `site_idx` (`site_id`),
  KEY `campaign_idx` (`campaign_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Can you, please, try to upgrade to 4.1.18 and repeat the problem on this newer version?

In the original report you had written:

"I may be able to send you data to experiement with."

Please, upload your data (as a private file), if it is still possible.

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".