MySQL Bugs: #2350: Locked table, with nothing using it.

Bug #2350	Locked table, with nothing using it.
Submitted:	11 Jan 2004 18:32	Modified:	14 Jan 2004 4:57
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:		OS:	Linux (Redhat 9)
Assigned to:		CPU Architecture:	Any

Description:
I will notice mysql giving max connections errors. When I run show processlist, every single query 
is locked, and none are running. I have seen it run for 35,000 seconds with no change.

The hard thing is all I can reproduce is that it happens with a big table often, if I split the table into 
2 smaller tables, the problem goes away until it gets larger again.

The table type is MyISAM, with lots of inserts, selects, and updates. No fulltext index is on the 
table.

How to repeat:
Maybe someone with knowledge of mysql myisam locking can suggest how I can give you data that 
will help you reproduce it.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

Can you please provide repeatable test case?

Send us table structure as provided by SHOW CREATE TABLE and some instructions how to observe thsi lock issye.

I would be more than happy to upload my multi-GB database and a log of the last 20 days (at 200
-300 queries per second) If you run those queries for a few days I bet you can make the bug 
repeatable.

Maybe it would be easier for someone familiar with the MyISAM locking code to think about what 
could be wrong. All the queries are locked yet none are running, so what can lock without being 
seen or forgot to remove it's lock. Another interesting fact I can give, show processlist shows the 
oldest query and then the next being tens or hundreds of seconds after the first. All other locked 
queries are jsut a few seconds apart. Another fact, the lock is never released even after waiting 
hours.

It would also make it easier for me and you if you provide some way of viewing the locks (maybe a 
little script) to see what asked for it.

Consider running our debug binary (from our site) and when this "lock" occurs
consider running 

mysqladmin debug

to get exactly which tables are locked.

If mysqladmin proc returns that there are no threads running, please report all these results to us.