Databases Reference
In-Depth Information
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.
Lock Waits in InnoDB
Locks at the server level can be quite a bit easier to debug than locks in storage engines.
Storage engine locks differ from one storage engine to the next, and the engines might
not provide any means to inspect their locks. We focus on InnoDB in this appendix.
InnoDB exposes some lock information in the output of
SHOW INNODB STATUS
. If a trans-
action is waiting for a lock, the lock will appear in the
TRANSACTIONS
section of the output
from
SHOW INNODB STATUS
. For example, if you execute the following commands in one
session, you will acquire a write lock on the first row in the table:
mysql>
SET AUTOCOMMIT=0;
mysql>
BEGIN;
mysql>
SELECT film_id FROM sakila.film LIMIT 1 FOR UPDATE;
