(mysql.info) table-locking
Info Catalog
(mysql.info) internal-locking
(mysql.info) locking-issues
(mysql.info) concurrent-inserts
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'
and `BDB'.
For `InnoDB' and `BDB' 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 and `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 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 set-option.
* 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 select.
* 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.
* If you mix inserts and deletes on the same table, `INSERT DELAYED'
may be of great help. See insert-delayed.
* If you have problems with mixed `SELECT' and `DELETE' statements,
the `LIMIT' option to `DELETE' may help. See delete.
* Using `SQL_BUFFER_RESULT' with `SELECT' statements can help to
make the duration of table locks shorter. See select.
* 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 innodb, and
bdb-storage-engine.
Info Catalog
(mysql.info) internal-locking
(mysql.info) locking-issues
(mysql.info) concurrent-inserts
automatically generated byinfo2html