MySQL MyISAM how to read without locking a table?

Question

My question is the answer to this answer. I want to learn how to execute a select statement without locking a table with the MyISAM engine.

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

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

Answer 1

This is the default behavior with MyISAM tables. If you really want to lock the MyISAM table, you must manually obtain the lock at the table level. The transaction isolation level, START TRANSACTION , COMMIT , ROLLBACK does not affect the behavior of MyISAM tables, since MyISAM does not support transactions .

Learn more about internal locking mechanisms.

The READ lock is still implicitly obtained and released after executing the SELECT . Note that several simultaneous, simultaneous, SELECT statements can be executed at the same time, since several sessions may contain a READ lock in the same table.

Conversely, a WRITE lock is implicitly obtained before executing an INSERT or UPDATE or DELETE . This means that reading (not to mention simultaneous recording) can occur as long as writing is performed ^* .

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

You might want to know more about this:

• Internal locking methods
• Read vs Write locks

---

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

The MyISAM storage engine supports parallel inserts to reduce the conflict between readers and writers for a given table: if the MyISAM table does not have 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 parallel INSERT and SELECT for the MyISAM table without locks.

Answer 2

MyISAM does indeed use read locks during SELECT . INSERT at the end of the table can get around this.

But try doing UPDATE , DELETE or ALTER TABLE while a long SELECT is running. Or vice versa, reading from a table while this table is being modified. This is the first, first, first, and subsequent threading blocks until the first thread is executed.

MyISAM does not support transactions, so it should work this way. If a SELECT reads rows from a table and a simultaneous stream changes some of these rows, you will get a race condition. SELECT can read some lines before the change and some lines after the change, which leads to a completely mixed representation of the data.

Everything you do with SET TRANSACTION ISOLATION LEVEL does not affect MyISAM.

For these reasons, it is recommended that you use InnoDB instead.