To achieve a very high lock speed, MySQL uses table locking
(instead of page, row, or column locking) for all storage
MySQL uses only table locking if you explicitly lock the table
LOCK TABLES. For these table types, we
recommend that you not use
LOCK TABLES at
InnoDB uses automatic row-level
BDB uses page-level locking to
ensure transaction isolation.
For large tables, table locking is much better than row locking for most applications, but there are some pitfalls.
Table locking enables many threads to read from a table at the same time, but if a thread wants to write to a table, it must first get exclusive access. During the update, all other threads that want to access this particular table must wait until the update is done.
Table updates normally are considered to be more important than
table retrievals, so they are given higher priority. This should
ensure that updates to a table are not “starved”
even if there is heavy
SELECT activity for
Table locking causes problems in cases such as when a thread is waiting because the disk is full and free space needs to become available before the thread can proceed. In this case, all threads 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 client issues a
SELECT that takes a
long time to run.
Another client then issues an
the same table. This client waits until the
SELECT is finished.
Another client issues another
statement on the same table. Because
UPDATE has higher priority than
waits for the
UPDATE to finish,
and for the first
SELECT to finish.
The following list describes some ways to avoid or reduce contention caused by table locking:
Try to get the
SELECT statements to run
faster. You might have to create some summary tables to do
Start mysqld with
--low-priority-updates. This gives all
statements that update (modify) a table lower priority than
SELECT statements. In this case, the
SELECT statement in the preceding
scenario would execute before the
statement, and would not need to wait for the first
SELECT to finish.
You can specify that all updates issued in a specific
connection should be done with low priority by using the
SET LOW_PRIORITY_UPDATES=1 statement. See
Sección 13.5.3, “Sintaxis de
You can give a specific
statement lower priority with the
You can give a specific
higher priority with the
attribute. See Sección 13.2.7, “Sintaxis de
You can start mysqld with a low value for
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.
READ locks after a certain
If you have problems with
SELECT, you might want to consider
MyISAM tables, which support
If you mix inserts and deletes on the same table,
INSERT DELAYED may be of great help. See
Sección 18.104.22.168, “Sintaxis de
If you have problems with mixed
DELETE statements, the
LIMIT option to
may help. See Sección 13.2.1, “Sintaxis de
SELECT statements can help to make the
duration of table locks shorter. See
Sección 13.2.7, “Sintaxis de
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 don't mix updates with selects that need to examine many rows in the same table.
You can use
LOCK TABLES to increase
speed, as many updating 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 Capítulo 15, El motor de almacenamiento
See Sección 14.4, “El motor de almacenamiento
Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a email@example.com.