For concurrency control, MyISAM uses table-level locking with concurrent inserts. Let's spend a little bit more time on this, as it is often misunderstood. There are, in fact, three types of locks in MyISAM:
READ LOCAL lock. This is used by queries that only need to read. This type of lock merely blocks updates, to prevent the data from changing during the query. Other read queries can proceed, as can concurrent inserts: if an INSERT statement can add the new data at the end of the .MYD file, rather than by filling existing free space (from rows that were deleted), it does not have to wait on a READ LOCAL lock.
READ, or shared locks. This locking type blocks updates, including all INSERTs. It is generally used if an external tool such as myisamcheck needs to access a table file directly.
WRITE, or exclusive locks. This is used by DELETE, UPDATE and sometimes INSERT statements. All other access (readers as well as writers) to the table is blocked, to prevent updates from interfering with each other.
By "blocking" we mean that a thread (connection) may have to wait until another thread has completed its operation and releases the lock. While is is possible to set locks manually, the MySQL server employs the necessary locking implicitly for any statement it executes.
Partager