Debugging Locks - High Performance MySQL - page 736

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