According to the MySQL Documentation on MetaData Locking
If the server acquires metadata locks for a statement that is
syntactically valid but fails during execution, it does not release
the locks early. Lock release is still deferred to the end of the
transaction because the failed statement is written to the binary log
and the locks protect log consistency.
Somone submitted this state as a bug, but the bug report claims it is not a bug.
Both of these links describe DDL (Data Definition Language) against a table as a contributing factor. What is interesting to keep in mind is that doing something like TRUNCATE TABLE is, in reality, DDL. We have to aware of any SQL we run. We may think it is SQL that is part of a transaction when, in fact, it is DDL. TRUNCATE TABLE is an example of DDL we think is DML. This can cause current transactions to commit and may have unpredictable results when it comes to writing binary log events as the first link states:
Before MySQL 5.5.3, when a transaction acquired a metadata lock for a
table used within a statement, it released the lock at the end of the
statement. This approach had the disadvantage that if a DDL statement
occurred for a table that was being used by another session in an
active transaction, statements could be written to the binary log in
the wrong order.
What is the lock level for a MyISAM table ? Table Level Locking
This is by design : (See http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html)
I wrote about before : Benefits of table level locking
You may want to take a shot at INSERT DELAYED. Note what page 410 bulletpoint 5 of "MySQL 5.0 Certification Study Guide" says on INSERT DELAYED:
DELAYED may be used with INSERT (and REPLACE). The server buffers the rows in memory and inserts them when the table is not being used. Delayed inserts increase efficiency because they are done in batches rather than individually. While inserting the rows, the server checks periodically to see whether other requests to use the table have arrived. If so, the server suspends insertion of delayed rows until the table becomes free again. Using DELAYED allows the client to proceed immediately after issuing the INSERT statementrather than waiting until it completes.
There are risks to using INSERT DELAYED. Page 411 says
If you use DELAYED, keep the following points in mind:
- Delayed rows tends to held for a longer time on a very busy server than on a lightly loaded one.
- If a crash occurs while the server is buffering delayed rows in memory, those rows are lost.
Looking over the whole section of the book, HIGH_PRIORITY is only applied to SELECT queries.
For more information, "MySQL 5.0 Certification Study Guide" pages 408-412 Section 29.2.
CAVEAT : When it comes to MySQL Replication, INSERT DELAYED executed on a Master is treated as INSERT on a Slave. If a master crashes, missing rows are logged in the binary logs of the Master. It would be very possible that the missing rows on the Master replicated successfully to the Slave.
UPDATE 2012-08-20 11:18 EDT
Please note what page 410 bulletpoint 3 of "MySQL 5.0 Certification Study Guide" says on LOW_PRIORITY:
The LOW_PRIORITY modifier may be applied to statements that update tables (INSERT, DELETE, REPLACE, or UPDATE). A low-priority write request waits not only until all current readers have finished, but for any pending read requests that arrive while the write request itself is waiting. That is, it waits until there are no pending read requests at all. It is therefore possible for a low-priority write request never to be performed, if read requests keep arriving while the write request is waiting.
Given this description, low-priority UPDATEs definitely take a backseat to INSERTs that have normal priority. Notwithstanding, when a SELECT occurs on a MyISAM table, all write requests take a backseat regardless of priority. If the read traffic is high enough, the precedence of INSERT over UPDATE LOW_PRIOIRTY would be negligible, nominal at best.
UPDATE 2012-08-20 11:36 EDT
If the read traffic is hindering your INSERT over UPDATE LOW_PRIOIRTY situation, I would have to recommend switching your MyISAM table to InnoDB. In fact, InnoDB can be tuned for performing heavy INSERTs. Please see my posts about tuning InnoDB for this purpose:
Best Answer
The MyISAM Storage Engine is furiously notorious for performing full table locks for any DML (INSERTs, UPDATEs, DELETEs). InnoDB would definitely solve that issue in the long term.
I wrote about pros and cons of using MyISAM vs InnoDB
With regard to your current question, here is a possible scenario:
articleandarticle_commentsare both MyISAM tablesarticle_commentshas one or more indexes withstatusas a columnarticle_commentsare cached in the MyISAM Key Buffer (sized by key_buffer_size), causing old index pages out of the MyISAM Key Bufferarticleandarticle_commentsIn my suggested scenario, SELECTs against the
articletable can be held up from allowing writes because of having to wait forarticle_commentsto be free from any DML (in this case, anUPDATE)