GNU Info
(mysql.info)Table locking
Table Locking Issues
--------------------
The table locking code in MySQL is deadlock free.
MySQL uses table locking (instead of row locking or column locking) on
all table types, except `BDB' tables, to achieve a very high lock
speed. For large tables, table locking is MUCH better than row locking
for most applications, but there are, of course, some pitfalls.
For `BDB' and `InnoDB' tables, MySQL only uses table locking if you
explicitely lock the table with `LOCK TABLES' or execute a command that
will modify every row in the table, like `ALTER TABLE'. For these
table types we recommend you to not use `LOCK TABLES' at all.
In MySQL Version 3.23.7 and above, you can insert rows into `MyISAM'
tables at the same time other threads are reading from the table. Note
that currently this only works if there are no holes after deleted rows
in the table at the time the insert is made. When all holes has been
filled with new data, concurrent inserts will automatically be enabled
again.
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 will wait until the update is ready.
As updates on tables normally are considered to be more important than
`SELECT', all statements that update a table have higher priority than
statements that retrieve information from a table. This should ensure
that updates are not 'starved' because one issues a lot of heavy
queries against a specific table. (You can change this by using
LOW_PRIORITY with the statement that does the update or `HIGH_PRIORITY'
with the `SELECT' statement.)
Starting from MySQL Version 3.23.7 one can use the
`max_write_lock_count' variable to force MySQL to temporary give all
`SELECT' statements, that wait for a table, a higher priority after a
specific number of inserts on a table.
Table locking is, however, not very good under the following senario:
* A client issues a `SELECT' that takes a long time to run.
* Another client then issues an `UPDATE' on a used table. This client
will wait until the `SELECT' is finished.
* Another client issues another `SELECT' statement on the same
table. As `UPDATE' has higher priority than `SELECT', this `SELECT'
will wait for the `UPDATE' to finish. It will also wait for the
first `SELECT' to finish!
* A thread is waiting for something like `full disk', in which case
all threads that wants to access the problem table will also be
put in a waiting state until more disk space is made available.
Some possible solutions to this problem are:
* Try to get the `SELECT' statements to run faster. You may have to
create some summary tables to do this.
* Start `mysqld' with `--low-priority-updates'. This will give all
statements that update (modify) a table lower priority than a
`SELECT' statement. In this case the last `SELECT' statement in
the previous scenario would execute before the `INSERT' statement.
* You can give a specific `INSERT', `UPDATE', or `DELETE' statement
lower priority with the `LOW_PRIORITY' attribute.
* Start `mysqld' with a low value for *max_write_lock_count* to give
`READ' locks after a certain number of `WRITE' locks.
* You can specify that all updates from a specific thread should be
done with low priority by using the SQL command: `SET
SQL_LOW_PRIORITY_UPDATES=1'. Note: `SET OPTION'.
* You can specify that a specific `SELECT' is very important with the
`HIGH_PRIORITY' attribute. Note: `SELECT'.
* If you have problems with `INSERT' combined with `SELECT', switch
to use the new `MyISAM' tables as these support concurrent
`SELECT's and `INSERT's.
* If you mainly mix `INSERT' and `SELECT' statements, the `DELAYED'
attribute to `INSERT' will probably solve your problems. Note:
`INSERT'.
* If you have problems with `SELECT' and `DELETE', the `LIMIT'
option to `DELETE' may help. Note: `DELETE'.
automatically generated by info2www version 1.2.2.9