Do MyISAM holes get filled in automatically?

Question

When you run a delete on a MyISAM table, it leaves a hole in the table until the table is optimized.

This affects concurrent inserts. On the default setting, concurrent_inserts only work for tables without holes. However, in the documentation for MyISAM, under the concurrent_insert section it says:

Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_concurrent_insert

Does that mean MyISAM automatically fills in holes whenever a new row is insert into the table? Previously I thought the holes would not be fixed until you OPTIMIZE a table.

Answer 1

Yes, MyISAM tables reuses deleted/free space.(Wether this is true for all cases, I don't know).

This can easily be observed by deleting a handful of records and inserting some new one - the MyISAM data file for that table will not grow in size.

Answer 2

Inserts into the middle of the table require a lock. So, in the default setting, MySQL will favor filling the holes, even though it will prevent concurrent inserts.

So, yes, MySQL prefers to fill the holes.

Setting concurrent_inserts to 2 tells MySQL that if there is a lock on the table, insert at the end, which doesn't require a lock, even though there are still holes to be filled. Therefore, this allows concurrent inserts even if there are holes in the middle, at the cost of taking longer to fill the holes.