5.3.1 How MySQL Locks Tables

You can find a discussion about different locking methods in the appendix. See section E.4 Locking methods.

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;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

You can use the LOW_PRIORITY options with INSERT, UPDATE or DELETE or HIGH_PRIORITY with SELECT if you want to prioritise retrieval in some specific cases. You can also start mysqld with --low-priority-updates to get the same behaveour.

Using SQL_BUFFER_RESULT can also help making table locks shorter. See section 6.4.1 SELECT Syntax.

You could also 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.

User Comments

Add your own comment.