8.11.2 Table Locking Issues

Table locking enables many sessions to read from a table at the same time, but if a session wants to write to a table, it must first get exclusive access, meaning it might have to wait for other sessions to finish with the table first. During the update, all other sessions that want to access this particular table must wait until the update is done.

Table locking causes problems when a session is waiting because the disk is full and free space needs to become available before the session can proceed. In this case, all sessions that want to access the problem table are also put in a waiting state until more disk space is made available.

A SELECT statement that takes a long time to run prevents other sessions from updating the table in the meantime, making the other sessions appear slow or unresponsive. While a session is waiting to get exclusive access to the table for updates, other sessions that issue SELECT statements will queue up behind it, reducing concurrency even for read-only sessions.

Consider switching the table to the InnoDB storage engine, either using CREATE TABLE ... ENGINE=INNODB during setup, or using ALTER TABLE ... ENGINE=INNODB for an existing table. See Chapter 15, The InnoDB Storage Engine for more details about this storage engine.

Optimize SELECT statements to run faster so that they lock tables for a shorter time. You might have to create some summary tables to do this.

Start mysqld with --low-priority-updates. For storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE), this gives all statements that update (modify) a table lower priority than SELECT statements. In this case, the second SELECT statement in the preceding scenario would execute before the UPDATE statement, and would not wait for the first SELECT to finish.

To specify that all updates issued in a specific connection should be done with low priority, set the low_priority_updates server system variable equal to 1.

To give a specific INSERT, UPDATE, or DELETE statement lower priority, use the LOW_PRIORITY attribute.

To give a specific SELECT statement higher priority, use the HIGH_PRIORITY attribute. See Section 13.2.10, “SELECT Syntax”.

Start mysqld with a low value for the max_write_lock_count system variable to force MySQL to temporarily elevate the priority of all SELECT statements that are waiting for a table after a specific number of inserts to the table occur. This permits READ locks after a certain number of WRITE locks.

If you have problems with INSERT combined with SELECT, consider switching to MyISAM tables, which support concurrent SELECT and INSERT statements. (See Section 8.11.3, “Concurrent Inserts”.)

If you have problems with mixed SELECT and DELETE statements, the LIMIT option to DELETE may help. See Section 13.2.2, “DELETE Syntax”.

Using SQL_BUFFER_RESULT with SELECT statements can help to make the duration of table locks shorter. See Section 13.2.10, “SELECT Syntax”.

Splitting table contents into separate tables may help, by allowing queries to run against columns in one table, while updates are confined to columns in a different table.

You could change the locking code in mysys/thr_lock.c to use a single queue. In this case, write locks and read locks would have the same priority, which might help some applications.