How MySQL Uses Table Locks

The previous code was a simple example of applying a write lock to a single table. There are also read locks. It is possible to lock any number of tables at once.

An Introduction to LOCK TABLES and UNLOCK TABLES

Here's the syntax (in simplified form) for LOCK TABLES:

LOCK TABLES table_name1 {READ | WRITE} [, table_name2 {READ | WRITE} ...]

As you can see from the syntax, you can give LOCK TABLES the names of one or more tables. Each named table requires the word READ or WRITE to specify what kind of lock the client thread should obtain on that table.

What's the difference between a read lock and a write lock?

• If a client thread obtains a read lock, that thread and all other threads will only be able to read from that table; no thread can write to the table.

• If a client thread obtains a write lock, it becomes the only thread with either read or write access. No other threads will have read or write access until the lock is released.

Here's an example of locking two tables for read, and one for write:

mysql> LOCK TABLES products READ, customers READ, stock_level WRITE;
Query OK, 0 rows affected (0.00 sec)

In the preceding query, products and customers were locked with a read lock, and stock_level was locked with a write lock.

When tables are locked, they remain locked until another LOCK TABLES command is issued, the thread exits, or (most commonly) an UNLOCK TABLES command is issued. UNLOCK TABLES is used like this:

UNLOCK TABLES

So if you try this in the mysql console, you would do this:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

The Full Syntax for LOCK TABLES

Here's the full syntax of LOCK TABLES, showing all possible options:

LOCK TABLES table_name1 [AS alias1] {READ | READ LOCAL | [LOW_PRIORITY] WRITE}
  [, table_name2 [AS alias2] {READ | READ LOCAL | [LOW_PRIORITY] WRITE}]
  ...

You'll notice a few extra things in the full syntax (though they're less often used):

• You can give each table an alias; if you do so you will need to use this alias to refer to such tables in your queries.

• You can issue a READ LOCAL. This is like a read lock, but allows inserts to be performed by other threads while the lock is in operation.

• You can issue a write lock with LOW_PRIORITY. Write locks are normally obtained before read locks, but LOW_PRIORITY gives a write lock a lower priority. (You'll read more about the process of obtaining locks in a moment.)

Here's an example of a more complex lock statement:

mysql> LOCK TABLES products AS p READ,
    -> customers AS c READ LOCAL,
    -> stock_level AS s WRITE;
Query OK, 0 rows affected (0.00 sec)

In this example, products has a read lock and alias p, customers has a read local lock and alias c, and stock_level has a write lock and alias s.

Queuing Lock Requests

Issuing a LOCK TABLES command does not mean that the client that issues it gains a lock immediately. The tables requested may be in use or locked by another client, so the client that wants to obtain a lock on those tables will have to wait for the lock to be granted.

In addition, when a client requests a lock, it may have to wait until any other waiting clients have been served their locks. There's a queuing mechanism, so that clients can be served in order.

There are actually two queues—you'll see why in a moment. According to a strict set of rules, a client in one queue may have to wait for clients in the other queue to be granted their lock first.

The two queues are a write lock queue and a read lock queue, and they work in slightly different ways.

When a write lock is issued by a client

• If no locks are currently on the requested tables, the write lock is granted without queuing.

• Otherwise, the write lock request is put into the write lock queue.

When a read lock is issued by a client:

• Where tables have no write locks on them, the read lock is granted without queuing.

• Otherwise, the read lock request is put into the read lock queue.

Whenever a lock is released, clients in the write lock queue are given priority over those in the read lock queue. This is done to help get updates processed as soon as possible.

When no clients are waiting in the write lock queue, clients in the read lock queue are in turn granted their read lock.

Overriding the Lock Request Queue

There are ways to override the lock queuing protocol. For example, you may have an application in which it's important for read locks to be granted with higher priority than write locks.

As you saw in the LOCK TABLES syntax, there is the option of issuing a LOW_PRIORITY WRITE. Issuing a LOW_PRIORITY WRITE lock makes the queuing system behave the other way around for this request: A low priority write lock will have to wait for all read locks to clear the queue before it is granted.

Take care when using this, however. If you have a busy system with a near-continuous stream of read operations, you should ensure that there is time for write operations to occur at some point! Otherwise, you run the risk of write operations being delayed unacceptably if the wait is too long.

Another query that can influence the queuing policy is the SELECT query. When you issue a SELECT (with or without a lock), it normally has to wait behind any write requests that are waiting in the queue.

However, if you issue a SELECT HIGH PRIORITY, it allows the SELECT to read from the table even if a write lock is waiting in the queue.

Locking Multiple Tables

It is important to note that when your application is going to run queries on several tables, you must lock all those tables.

Locking all tables is essential to prevent a deadlock situation from arising. How might a deadlock occur?

Imagine that one client is allowed to lock just table1 of the set of tables, table1 and table2, on which it intends to run a number of queries.

Then another client locks table2 (before the first client has finished its business) but also wants to run queries on table1 and table2. When it finds table1 locked, the thread has to wait for the first client to release it.

If the first client then tries to run a query on table2, it finds that table locked and has to wait. There's now a situation of two clients, each waiting for the other to release its locks. Neither can make progress, and both table1 and table2 are locked by threads that are halted.

This would be a gridlock situation for your database. MySQL prevents it from arising by insisting that when you have a lock on one or several tables, you cannot run queries on any other tables.

Permissions for Locking Tables

Prior to MySQL version 4.0.2, no special client privilege was required to lock tables, although the client should have Select_priv, Insert_priv, Delete_priv, and Update_priv, according to what action your query intends to perform after the lock is granted.

However, in version 4.0.2 and later, with the new-style privilege tables, the client user must also have the Lock_tables_priv privilege.

See Day 14, “Security,” for more about MySQL's grant tables and user privileges.

Using Table Locking: A Quick Recap

As you've seen, a client should issue a lock when

• It wants to ensure that it can perform a sequence of queries (often a SELECT and then an UPDATE) without interference from other clients in those tables.

• It wants to ensure that other threads don't see data that is partially updated (it might have to perform UPDATEs on several tables before the overall picture is consistent).

Remember that you must lock all tables that are going to be used by your queries until those tables are unlocked.