DEVELOPER ZONE ::
Login / Register
To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB
.
For InnoDB
tables, MySQL uses only table locking if you explicitly lock the table with LOCK TABLES
. For these storage engines, we recommend that you not use LOCK TABLES
at all, because InnoDB
uses automatic row-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 the table.
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 UPDATE
on the same table. This client waits until the SELECT
is finished.
Another client issues another SELECT
statement on the same table. Because UPDATE
has higher priority than SELECT
, this SELECT
waits for the UPDATE
to finish, and 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
. 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.
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 Section 13.5.3, “SET
Syntax”.
You can give a specific INSERT
, UPDATE
, or DELETE
statement lower priority with the LOW_PRIORITY
attribute.
You can give a specific SELECT
statement higher priority with the HIGH_PRIORITY
attribute. See Section 13.2.7, “SELECT
Syntax”.
You can 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
, you might want to 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 13.2.4.2, “INSERT DELAYED
Syntax”.
If you have problems with mixed SELECT
and DELETE
statements, the LIMIT
option to DELETE
may help. See Section 13.2.1, “DELETE
Syntax”.
Using SQL_BUFFER_RESULT
with SELECT
statements can help to make the duration of table locks shorter. See Section 13.2.7, “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
. See Section 14.5, “The InnoDB
Storage Engine”.
MySQL Enterprise. Lock contention can seriously degrade performance. The MySQL Network Monitoring and Advisory Service provides expert advice on avoiding this problem. To subscribe see http://www.mysql.com/products/enterprise/advisors.html.
User Comments
Add your own comment.