Skip navigation links
7.3.3 Concurrent Inserts »
Section Navigation      [Toggle]

7.3.2. Table Locking Issues

To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB, BDB, and NDBCLUSTER.

For InnoDB and BDB tables, MySQL uses table locking only if you explicitly lock the table with LOCK TABLES. For these storage engines, avoid using LOCK TABLES at all, because InnoDB uses automatic row-level locking and BDB uses page-level locking to ensure transaction isolation.

For large tables, table locking is often better than row locking, but there are some disadvantages:

  • 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. During the update, all other sessions that want to access this particular table must wait until the update is done.

  • Table locking causes problems in cases such as 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.

Table locking is also disadvantageous under the following scenario:

  • A session issues a SELECT that takes a long time to run.

  • Another session then issues an UPDATE on the same table. This session waits until the SELECT is finished.

  • Another session issues another SELECT statement on the same table. Because UPDATE has higher priority than SELECT, this SELECT waits for the UPDATE to finish, after waiting for the first SELECT to finish.

The following items describe some ways to avoid or reduce contention caused by table locking:

  • Try to get the 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 need to 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 12.2.8, “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 allows 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 7.3.3, “Concurrent Inserts”.)

  • If you mix inserts and deletes on the same table, INSERT DELAYED may be of great help. See Section 12.2.5.2, “INSERT DELAYED Syntax”.

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

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

  • 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.

Here are some tips concerning table locks in MySQL:

  • Concurrent users are not a problem if you do not mix updates with selects that need to examine many rows in the same table.

  • You can use LOCK TABLES to increase speed, because many updates within a single lock is much faster than updating without locks. Splitting table contents into separate tables may also help.

  • If you encounter speed problems with table locks in MySQL, you may be able to improve performance by converting some of your tables to InnoDB or BDB tables. See Section 13.2, “The InnoDB Storage Engine”, and Section 13.5, “The BDB (BerkeleyDB) Storage Engine”.

    MySQL Enterprise.  Lock contention can seriously degrade performance. The MySQL Enterprise Monitor provides expert advice on avoiding this problem. To subscribe, see http://www.mysql.com/products/enterprise/advisors.html.