MySQL Bugs: #46397: Waiting for tables with delay key writes

Bug #46397	Waiting for tables with delay key writes
Submitted:	27 Jul 2009 9:21	Modified:	27 Aug 2009 9:43
Reporter:	dimitar nenchev	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S1 (Critical)
Version:	4.1	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	Waiting for tables with delay key writes

Description:
Hi
I check the bugs list, i found that this bug has occured in other situations but i did not find any solution or work arround.

I am using MySql 4.1. I have MyIsam table about 3 million records for now. It has fulltext index on 2 coloumns, and 3 indexes on other coloumns.
This table has to take bulk inserts(not concurrent), many selects and sometimes deletes or updates. I use deley key writes option!

My problem is that sometimes when delete is executed(may be this can happen with update, but i have not catch such case) the query stays in state "waiting for tables" sometimes it is executed in the moment.
I think that if a ordering of the delayed inexes is running and during that a delete is executed it stucks. Also if slow query search occures all other selects after that are locked.

How to repeat:
Problem: Sometimes when delete is executed(may be this can happen with update, but i have not catch such case) the query stays in state "waiting for tables" sometimes it is executed in the moment.
I think that if a ordering of the delayed inexes is running and during that a delete is executed it stucks. Also if slow query search occures all other selects after that are locked.

Suggested fix:
The waiting for tables state occures during:

The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.

This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

Thank you for the report.

This bug database is for reporting bugs in MySQL code, not for help with usage. 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/

You said:

----<q>----
Suggested fix:
The waiting for tables state occures during:

The thread got a notification that the underlying structure for a table has changed and it
needs to reopen the table to get the new structure. However, to reopen the table, it must
wait until all other threads have closed the table in question.

This notification takes place if another thread has used FLUSH TABLES or one of the
following statements on the table in question: FLUSH TABLES tbl_name, ALTER TABLE, RENAME
TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.
----</q>----

This is correct, you have to wait when "another thread" finishes its job. Please explain what is MySQL bug in your case.

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

I reported a problem similar in:
http://bugs.mysql.com/bug.php?id=43726
(THR_LOCK_myisam is locked while flushing unwritten key blocks to disk)