The table locking code in MySQL is deadlock free.
MySQL uses table locking (instead of row locking or column locking)
to achieve a very high lock speed. For large tables, table locking is MUCH
better than row locking, but there are of course 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 others threads that want to
access this particular table will wait until the update is ready.
As updates of databases 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.
One main problem with this is the following:
A client issues a
SELECT that takes a long time to run.
Another client then issues an
INSERT on a used table; This client
will wait until the
SELECT is finished..
Another client issues another
SELECT statement on the same table; As
INSERT has higher priority than
will wait for the
INSERT to finish. It will also wait for the first
SELECT to finish!
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.
--low-priority-updates. This will give
all statements that update (modify) a table lower priority than a
statement. In this case the last
SELECT statement in the previous
scenario would execute before the
You can give a specific
lower priority with the
You can specify that all updates from a specific thread should be done with
low priority by using the SQL command:
You can specify that a specific
SELECT is very important with the
If you mainly mix
SELECT statements, the
DELAYED attribute to
INSERT will probably solve your problems.
If you have problems with
DELETE may help.