To achieve a very high lock speed, MySQL uses table locking
(instead of page, row, or column locking) for all storage
InnoDB tables, MySQL uses table locking
only if you explicitly lock the table with
LOCK TABLES. For this storage
engine, avoid using
at all, because
InnoDB uses automatic
row-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
SELECTthat takes a long time to run.
Another session issues another
SELECTstatement on the same table. Because
UPDATEhas higher priority than
SELECTwaits for the
UPDATEto finish, after waiting for the first
The following items describe some ways to avoid or reduce contention caused by table locking:
Try to get the
SELECTstatements 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
MERGE), this gives all statements that update (modify) a table lower priority than
SELECTstatements. In this case, the second
SELECTstatement in the preceding scenario would execute before the
UPDATEstatement, and would not need to wait for the first
To specify that all updates issued in a specific connection should be done with low priority, set the
low_priority_updatesserver system variable equal to 1.
Start mysqld with a low value for the
max_write_lock_countsystem variable to force MySQL to temporarily elevate the priority of all
SELECTstatements that are waiting for a table after a specific number of inserts to the table occur. This permits
READlocks after a certain number of
You could change the locking code in
mysys/thr_lock.cto 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 TABLESto 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. See Section 14.6, “The InnoDB Storage Engine”.