MySQL Reference Manual

Locking Issues

How MySQL Locks Tables

You can find a discussion about different locking methods in the appendix. See Section D.4.

All locking in MySQL is deadlock-free, except for InnoDB and BDB type tables. This is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order.

InnoDB type tables automatically acquire their row locks and BDB type tables their page locks during the processing of SQL statements, not at the start of the transaction.

The locking method MySQL uses for WRITE locks works as follows:

• If there are no locks on the table, put a write lock on it.

• Otherwise, put the lock request in the write lock queue.

The locking method MySQL uses for READ locks works as follows:

• If there are no write locks on the table, put a read lock on it.

• Otherwise, put the lock request in the read lock queue.

When a lock is released, the lock is made available to the threads in the write lock queue, then to the threads in the read lock queue.

This means that if you have many updates on a table, SELECT statements will wait until there are no more updates.

To work around this for the case where you want to do many INSERT and SELECT operations on a table, you can insert rows in a temporary table and update the real table with the records from the temporary table once in a while.

This can be done with the following code:

mysql> LOCK TABLES real_table WRITE, insert_table WRITE; mysql> INSERT INTO real_table SELECT * FROM insert_table; ...