To achieve a very high lock speed, MySQL uses table locking
(instead of page, row, or column locking) for all storage
MySQL uses table locking only if you explicitly lock the table
LOCK TABLES. For these
storage engines, avoid using
TABLES at all, because
automatic row-level locking and
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
that takes a long time to run.
Another session issues another
SELECT statement on the same
higher priority than
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
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
storage engines that use only table-level locking (such as
MERGE), this gives all statements that
update (modify) a table lower priority than
SELECT statements. In this
case, the second
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
system variable equal to 1.
Starting from MySQL 3.23.7, you can start
mysqld with a low value for the
variable to force MySQL to temporarily elevate the priority
SELECT statements that
are waiting for a table after a specific number of inserts
to the table occur. This permits
locks after a certain number of
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
BDB tables. See
Section 13.2, “The InnoDB Storage Engine”, and
Section 13.5, “The BDB (BerkeleyDB) Storage Engine”.