READ LOCAL: Queries that need only to read block updates to prevent data from being changed during the query. Other read queries can proceed, as can concurrent inserts: if the new data can be INSERTed at the end of the .MYD file, rather than by filling existing free space, there is no wait for a READ LOCAL lock.

READ: Blocks all updates. Usually imposed by programs like myisamcheck.

WRITE: used by DELETE, UPDATE and some INSERT queries. All other read and write access to the table is blocked until the process is complete—that is, a thread (connection) may have to wait until another thread completes and releases the lock. While locks can be manually, the MyISAM engine applies the necessary locking implicitly for any statement it executes.

MyISAM does not support transactions. Effectively, this storage format always operates in AUTOCOMMIT=1 mode, where each statement is atomic. Special constructs such as INSERT ... SELECT and CREATE TABLE ... SELECT are regarded as single statements. The isolation level is fixed, but would be equivalent to READ COMMITTED.

MyISAM index blocks are cached globally in the key buffer (i.e. all threads share that cache), for table data MyISAM relies on the caching of the operating system.

Last updated 16 Aug 2024