GNU Info

(mysql.info)Internal locking

---

Next: Table locking Prev: Locking Issues Up: Locking Issues

---

Enter node , (file) or (file)node

How MySQL Locks Tables
----------------------

You can find a discussion about different locking methods in the
appendix.  Note: Locking methods.

All locking in MySQL is deadlock-free.  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.

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 prioritize
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.
Note: SELECT.

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.