|MyISAM does table-level locking with concurrent inserts, with three types of locking:
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
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
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 2019