High Performance MySQL (2012)

Appendix E. Debugging Locks

Any system that uses locks to control shared access to resources can be hard to debug when a lock contention issue crops up. Perhaps you’re trying to add a column to a table, or just trying to run a query, when suddenly you find that your queries are blocked because something else is locking the table or rows you’re trying to use. Often all you will want to do is find out why your query is blocked, but sometimes you will want to know what’s blocking it, so you know which process to kill. This appendix shows you how to achieve both goals.

The MySQL server itself uses several types of locks. If a query is waiting for a lock at the server level, you can see evidence of it in the output of SHOW PROCESSLIST. In addition to server-level locks, any storage engine that supports row-level locks, such as InnoDB, implements its own locks. In MySQL 5.0 and earlier versions, the server is unaware of such locks, and they’re mostly hidden from users and database administrators. There’s more visibility in MySQL 5.1 and later versions.

Lock Waits at the Server Level

A lock wait can happen at either the server level or the storage engine level.^[229^] (Application-level locks could be a problem too, but we’re focusing on MySQL.) Here are the kinds of locks the MySQL server uses:

Table locks

Tables can be locked with explicit read and write locks. There are a couple of variations on these locks, such as local read locks. You can learn about the variations in the LOCK TABLES section of the MySQL manual. In addition to these explicit locks, queries acquire implicit locks on tables for their durations.

Global locks

There is a single global read lock that can be acquired with FLUSH TABLES WITH READ LOCK or by setting read_only=1. This conflicts with any table locks.

Name locks

Name locks are a type of table lock that the server creates when it renames or drops a table.

String locks

You can lock and release an arbitrary string server-wide with GET_LOCK() and its associated functions.

We examine each of these lock types in more detail in the following sections.

Table Locks

Table locks can be either explicit or implicit. You create explicit locks with LOCK TABLES. For example, if you execute the following command in a mysql session, you’ll have an explicit lock on sakila.film:

mysql> LOCK TABLES sakila.film READ;

If you then execute the following command in a different session, the query will hang and not complete:

mysql> LOCK TABLES sakila.film WRITE;

You can see the waiting thread in the first connection:

mysql> SHOW PROCESSLIST\G

*************************** 1. row ***************************

Id: 7

User: baron

Host: localhost

db: NULL

Command: Query

Time: 0

State: NULL

Info: SHOW PROCESSLIST

*************************** 2. row ***************************

Id: 11

User: baron

Host: localhost

db: NULL

Command: Query

Time: 4

State: Locked

Info: LOCK TABLES sakila.film WRITE

2 rows in set (0.01 sec)

Notice that thread 11’s state is Locked. There is only one place in the MySQL server’s code where a thread enters that state: when it tries to acquire a table lock and another thread has the table locked. Thus, if you see this, you know the thread is waiting for a lock in the MySQL server, not in the storage engine.

Explicit locks, however, are not the only type of lock that might block such an operation. As we mentioned earlier, the server implicitly locks tables during queries. An easy way to show this is with a long-running query, which you can create easily with the SLEEP() function:

mysql> SELECT SLEEP(30) FROM sakila.film LIMIT 1;

If you try again to lock sakila.film while that query is running, the operation will hang because of the implicit lock, just as it did when you had the explicit lock. You’ll be able to see the effects in the process list, as before:

mysql> SHOW PROCESSLIST\G

*************************** 1. row ***************************

Id: 7

User: baron

Host: localhost

db: NULL

Command: Query

Time: 12

State: Sending data

Info: SELECT SLEEP(30) FROM sakila.film LIMIT 1

*************************** 2. row ***************************

Id: 11

User: baron

Host: localhost

db: NULL

Command: Query

Time: 9

State: Locked

Info: LOCK TABLES sakila.film WRITE

In this example, the implicit read lock for the SELECT query blocks the explicit write lock requested by LOCK TABLES. Implicit locks can block each other, too.

You might be wondering about the difference between implicit and explicit locks. Internally, they are the same type of structure, and the same MySQL server code controls them. Externally, you can control explicit locks yourself with LOCK TABLES and UNLOCK TABLES.

When it comes to storage engines other than MyISAM, however, there’s one very important difference between them. When you create a lock explicitly, it does what you tell it to, but implicit locks are hidden and “magical.” The server creates and releases implicit locks automatically as needed, and it tells the storage engine about them. Storage engines “convert” these locks as they see fit. For example, InnoDB has rules about what type of InnoDB table lock it should create for a given server-level table lock. This can make it hard to understand what locks InnoDB is really creating behind the scenes.

Finding out who holds a lock

If you see a lot of processes in the Locked state, your problem might be that you’re trying to use MyISAM or a similar storage engine for a high-concurrency workload. This can block you from performing an operation manually, such as adding an index to a table. If an UPDATE query is queued and waiting for a lock on a MyISAM table, even a SELECT query won’t be allowed to run. (You can read more about MySQL’s lock queuing and priorities in the MySQL manual.)

In some cases, it can become clear that some connection has been holding a lock on a table for a very long time and just needs to be killed (or a user needs to be admonished not to hold up the works!). But how can you find out which connection that is?

There’s currently no SQL command that can show you which thread holds the table locks that are blocking your query. If you run SHOW PROCESSLIST, you can see the processes that are waiting for locks, but not which processes hold those locks. Fortunately, there’s a debug command that can print information about locks into the server’s error log. You can use the mysqladmin utility to run the command:

$ mysqladmin debug

The output in the error log includes a lot of debugging information, but near the end you’ll see something like the following. We created this output by locking the table in one connection, then trying to lock it again in another:

Thread database.table_name Locked/Waiting Lock_type

7 sakila.film Locked - read Read lock without concurrent inserts

8 sakila.film Waiting - write Highest priority write lock

You can see that thread 8 is waiting for the lock thread 7 holds.

The Global Read Lock

The MySQL server also implements a global read lock. You can obtain this lock as follows:

mysql> FLUSH TABLES WITH READ LOCK;

If you now try to lock a table in another session, it will hang as before:

mysql> LOCK TABLES sakila.film WRITE;

How can you tell that this query is waiting for the global read lock and not a table-level lock? Look at the output of SHOW PROCESSLIST:

mysql> SHOW PROCESSLIST\G

...

*************************** 2. row ***************************

Id: 22

User: baron

Host: localhost

db: NULL

Command: Query

Time: 9

State: Waiting for release of readlock

Info: LOCK TABLES sakila.film WRITE

Notice that the query’s state is Waiting for release of readlock. This is your clue that the query is waiting for the global read lock, not a table-level lock.

MySQL provides no way to find out who holds the global read lock.

Name Locks

Name locks are a type of table lock that the server creates when it renames or drops a table. A name lock conflicts with an ordinary table lock, whether implicit or explicit. For example, if we use LOCK TABLES as before, and then in another session try to rename the locked table, the query will hang, but this time not in the Locked state:

mysql> RENAME TABLE sakila.film2 TO sakila.film;

As before, the process list is the place to see the locked query, which is in the Waiting for table state:

mysql> SHOW PROCESSLIST\G

...

*************************** 2. row ***************************

Id: 27

User: baron

Host: localhost

db: NULL

Command: Query

Time: 3

State: Waiting for table

Info: rename table sakila.film to sakila.film 2

You can see the effects of a name lock in the output of SHOW OPEN TABLES, too:

mysql> SHOW OPEN TABLES;

+----------+-----------+--------+-------------+

| Database | Table | In_use | Name_locked |

+----------+-----------+--------+-------------+

| sakila | film_text | 3 | 0 |

| sakila | film | 2 | 1 |

| sakila | film2 | 1 | 1 |

+----------+-----------+--------+-------------+

3 rows in set (0.00 sec)

Notice that both names (the original and the new name) are locked. sakila.film_text is locked because there’s a trigger on sakila.film that refers to it, which illustrates another way locks can insinuate themselves into places you might not expect. If you query sakila.film, the trigger causes you to implicitly touch sakila.film_text, and therefore to implicitly lock it. It’s true that the trigger really doesn’t need to fire for the rename, and thus technically the lock isn’t required, but that’s the way it is: MySQL’s locking is sometimes not as fine-grained as you might like.

MySQL doesn’t provide any way to find out who holds name locks, but this usually isn’t a problem because they’re generally held for only a very short time. When there’s a conflict, it is generally because a name lock is waiting for a normal table lock, which you can view with mysqladmin debug, as shown earlier.

User Locks

The final type of lock implemented in the server is the user lock, which is basically a named mutex. You specify the string to lock and the number of seconds to wait before the lock attempt should time out:

mysql> SELECT GET_LOCK('my lock', 100);

+--------------------------+

| GET_LOCK('my lock', 100) |

+--------------------------+

| 1 |

+--------------------------+

1 row in set (0.00 sec)

This attempt returned success immediately, so this thread now has a lock on that named mutex. If another thread tries to lock the same string, it will hang until it times out. This time the process list shows a different state:

mysql> SHOW PROCESSLIST\G

*************************** 1. row ***************************

Id: 22

User: baron

Host: localhost

db: NULL

Command: Query

Time: 9

State: User lock

Info: SELECT GET_LOCK('my lock', 100)

The User lock state is unique to this type of lock. MySQL provides no way to find out who holds a user lock.