MySQL Bugs: #782: HANDLER locks table for writing

Bug #782	HANDLER locks table for writing
Submitted:	2 Jul 2003 9:16	Modified:	3 Jul 2003 4:36
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0.13	OS:	Linux (Redhat Linux)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
A handler query seems to place a read lock on the table.

How to repeat:
HANDLER ibf_table READ indexed_column=(12345) WHERE item LIKE 
'%rare_word%' LIMIT 100;

Execute a query in that style on a table that requires the query to read 
many rows before finding an answer.

On the same table there needs to be a large number of 
UPDATES/INSERTS and (in my case) also SELECTS.

The HANDLER query locks the table and  makes the write querys wait. 
According to the manual HANDLER should be able to perform dirty reads.

Here is the top part of my processlist:

| 7908677 | user    | localhost | DB | Query   | 19   | NULL                 | 
HANDLER ibf_post2 READ bpath=(17293) WHERE post LIKE '%search%' 
AND forum_id=51 LIMIT 100            |
| 7909529 | user | localhost | DB | Query   | 17   | Locked               | UPDATE 
ibf_post2 SET attach_hits=attach_hits+1 WHERE pid='2' AND 
bpath=17633                         |
| 7909588 | user | localhost | DB | Query   | 13   | Locked               | SELECT 
COUNT(pid) as posts FROM ibf_post2 WHERE topic_id='2' AND pid <= 
'4551' AND bpath=13185       |
| 7909593 | user | localhost | DB | Query   | 13   | Locked               | INSERT 
INTO ibf_post2 
(author_id,use_sig,ip_address,post_date,icon_id,post,author_name,foru
m_id,topi |

Suggested fix:
N/A. The query should be able to perform dirty reads.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Manual states only that the table is not locked for reading on HANDLER OPEN. And that HANDLER can do dirty reads in two "HANDLER ... READ" commands, if the table was modified in-between.

Manual does NOT say that one atomic HANDLER command can perform dirty reads or does not lock the table - and in fact it does lock.