10.12 Table locking issues

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 SELECT, this SELECT 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.
  • 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.
  • 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. SET OPTION.
  • You can specify that a specific SELECT is very important with the HIGH_PRIORITY attribute. SELECT.
  • If you mainly mix INSERT and SELECT statements, the DELAYED attribute to INSERT will probably solve your problems. INSERT.
  • If you have problems with SELECT and DELETE, the LIMIT option to DELETE may help. DELETE.