Databases Reference
In-Depth Information
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 pro-
cesses 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
