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 ;

Solution 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.

Solution 2

MyISAM does indeed use a read lock during SELECT. An INSERT at the end of the table can get around that.

But try doing an UPDATE, DELETE, or ALTER TABLE while a long-running SELECT is in progress. Or vice-versa, reading from a table while a change to that table is running. It's first-come, first-serve, and the later thread blocks until the first thread is done.

MyISAM doesn't have any support for transactions, so it must work this way. If a SELECT were reading rows from a table, and a concurrent thread changes some of those rows, you would get a race condition. The SELECT may read some of the rows before the change, and some of the rows after the change, resulting in a completely mixed-up view of the data.

Anything you do with SET TRANSACTION ISOLATION LEVEL has no effect with MyISAM.

For these reasons, it's recommended to use InnoDB instead.

Comments

[dev.e.loper]

can you clarify 'beyond it's execution'? The way you stated it, it means means whlie SELECT is "executing", the table is locked...

[RandomSeed]

Well, I have almost entirely rewritten my answer... It should have read "A SELECT will never lock a MyISAM table past the end of its execution", but I hope the new phrasing is less obscure.

[user2451227]

So MyISAM still keeps a read lock while the select is running. If it is a complex aggregating select, it is blocking all other inserts in the mean time. And you can not disable this lock - what the OP actually asked about. So why do you say "This is the default behaviour with MyISAM tables". Seems more like "This is impossible with MyISAM tables".

[RandomSeed]

I believe the question was implcitely asked from a transactional perspective. As far as I understand, and also by the look of the question s/he linked to, @dev.e.loper was looking to obtain the same behaviour with MyISAM as the one resulting from his/her code snippet. S/he might want to clarify. My answer was formulated as to answer this question (i.e. by default, MyISAM will behave just like InnoDB with a "READ UNCOMMITTED" isolation level)

[RandomSeed]

BTW, "MyISAM still keeps a read lock while the select is running" is not entirely true (e.g. one can still insert into a table where a concurrent SELECT *, SLEEP(10) is running). I will rephrase.

[RandomSeed]

Strike that, I was testing in a special scenario (no "holes" in the table), some optimisation I overlooked was kicking in.