MySQL MyISAM how to perform a read without locking a table?

Question

My question is a follow up to this answer. I want to find out how to perform a select statement without locking a table with MyISAM engine.

The answer states the following if you have InnoDB but not MyISAM. What is the equivalent for MyISAM engine?

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
COMMIT ;

Answer 1

This is the default behaviour with MyISAM tables. If one actually wants to lock a MyISAM table, one must manually acquire a table-level lock. Transaction isolation level, START TRANSACTION, COMMIT, ROLLBACK have no effect on MyISAM tables behaviour since MyISAM does not support transactions.

More about internal locking mechanisms

A READ lock is implicitely acquired before, and released after execution of a SELECT statement. Notice that several concurrent, simultaneous, SELECT statements could be running at the same time, because several sessions may hold a READ lock on the same table.

Conversely, a WRITE lock is implicitely acquired before executing an INSERT or UPDATE or DELETE statement. This means that no read (let alone a concurrent write) can take place as long as a write is in progress^*.

The above applies to MyISAM, MEMORY, and MERGE tables only.

You might want to read more about this here:

• Internal locking methods
• Read vs Write locks

---

^* However, these locks are not always required thanks to this clever trick:

The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no free blocks in the middle of the data file, rows are always inserted at the end of the data file. In this case, you can freely mix concurrent INSERT and SELECT statements for a MyISAM table without locks.