In a ready-heavy environment, a MyISAM table behaves a like a prioritized queue.
- SELECTs will make all DDL, INSERTs, UPDATEs, & DELETEs, wait until all SELECTs are done
- A single write operation makes all SELECT wait. The exception to this rule is Concurrent INSERT. The environment for Concurrent INSERTs:
- Only INSERTs and SELECTs
- MyISAM tables must not contain any gaps
In other words, SELECTs are not blocked on a MyISAM table as long as newly INSERTs rows are entering a table with no gaps. IF any row being INSERTed has to fill any gaps, then the conditions for Concurrent INSERTs are no longer applicable. SELECTs go back to being handled the way they are normally handled.
If you are performing bulk loading of a MyISAM table, you will need to rev up certain things. For instance, you will need to add this option and restart mysql:
[mysqld]
bulk-insert-buffer-size=256M
Next, change the way you delete the data. Instead of running the DELETE query, try copying the data to be retained into a temp table and then rename. For example, if you have to delete rows from table mydb.mytb whose id <= 500000, run these steps (it should be faster):
use mydb
create table mybt like mytb;
alter table mybt disable keys;
insert into mybt select * from mytb where id > 500000;
alter table mybt enable keys;
drop table mytb;
alter table mybt rename mytb;
Instead of lots of INSERTs into your table, try putting all your new data into a CSV file and use LOAD DATA INFILE to mass populate your production table. You should disable keys before loading and enable keys after the new data is loaded.
There is no need to optimize table doing these things. You may want to run analyze table instead. That will update the index statistics on the MyISAM table.
I hope these suggestions help !!!
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:
article and article_comments are both MyISAM tablesarticle_comments has one or more indexes with status as a column- Index page updates for
article_comments are cached in the MyISAM Key Buffer (sized by key_buffer_size), causing old index pages out of the MyISAM Key Buffer
- You have SELECT queries that perform JOINs between
article and article_comments
In my suggested scenario, SELECTs against the article table can be held up from allowing writes because of having to wait for article_comments to be free from any DML (in this case, an UPDATE)
Best Answer
MyISAM does not have deadlocking, but deadlocking in a way, is an improvement over table-level locking.
When you are trying to INSERT/UPDATE/DELETE from a locked table, you will need to wait until it is available or until you get a timeout (by default 28800 seconds). With deadlocking on a row-level locking engine, you wait a bit if it is not free and if the server detects a sort of "infinite loop" - where 2 connections will not give up a row - then it will reject both connections quickly and give a deadlock.
If you are trying to resolve deadlocking, I would suggest you look at the following things: