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:
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.0.13 OS:Linux (Redhat Linux)
Assigned to: Sergei Golubchik CPU Architecture:Any

[2 Jul 2003 9:16] [ name withheld ]
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 
m_id,topi |

Suggested fix:
N/A. The query should be able to perform dirty reads.
[3 Jul 2003 4:36] Sergei Golubchik
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.