MySQL允许客户端会话显式获取表锁，以防止其他会话在特定时间段内访问表。客户端会话只能为自己获取或释放表锁。它无法获取或释放其他会话的表锁。
在详细讨论之前，我们将创建一个名为示例的数据库sbtest，其中包含一个名为tbl实践表锁定语句的简单表。
|
1 2 3 4 5 6 |
CREATE DATABASE sbtest; CREATE TABLE tbl ( id int(11) NOT NULL AUTO_INCREMENT, col int(11) NOT NULL, PRIMARY KEY (id) ); |
一、LOCK和UNLOCK TABLES
LOCK TABLE的简单语法形式如下：
|
1 |
LOCK TABLES table_name [READ | WRITE] |
MySQL提供了两种锁类型：READ和WRITE，下面介绍。
要释放表的锁定，请使用以下语句：
|
1 |
UNLOCK TABLES; |
二、表锁为READ类型
READ锁具有以下功能：
- 一个表的READ锁可以同时被多个会话获取。另外，其他会话可以从表中读取数据而不需要获取锁。
- 保存READ锁的会话只能从表中读取数据，但不能写入。另外，其他会话在READ锁定释放之前不能将数据写入表中。来自另一个会话的写入操作将被置于等待状态，直到READ锁定被释放。
- 如果会话正常或异常终止，MySQL将隐式释放所有锁，WRITE锁也是如此。
让我们来看看READ锁在以下场景中的工作方式。
首先，连接到sbtest数据库。要找出当前的连接ID，可以使用CONNECTION_ID()函数：
|
1 2 3 4 5 6 7 |
mysql> SELECT CONNECTION_ID(); +-----------------+ | CONNECTION_ID() | +-----------------+ | 46083 | +-----------------+ 1 row in set (0.00 sec) |
然后，在tbl表中插入一个新行。
|
1 |
mysql> INSERT INTO tbl(col) VALUES(10); |
之后，要获得一个锁，你可以使用LOCK TABLE语句。
|
1 |
mysql> LOCK TABLE tbl READ; |
看一下此时表打开的情况。
|
1 2 3 4 5 6 7 |
mysql> show open tables where in_use >= 1; +----------+-------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+-------+--------+-------------+ | sbtest | tbl | 1 | 0 | +----------+-------+--------+-------------+ 1 row in set (0.00 sec) |
最后，在同一个会话中，如果你尝试向表中插入新行，你将收到错误消息。
|
1 2 |
mysql> INSERT INTO tbl(col) VALUES(11); ERROR 1099 (HY000): Table 'tbl' was locked with a READ lock and can't be updated |
再来看看同一个会话中查询是否正常。
|
1 2 3 4 5 6 7 |
mysql> SELECT * FROM tbl; +----+-----+ | id | col | +----+-----+ | 1 | 10 | +----+-----+ 1 row in set (0.00 sec) |
因此，获取一次READ锁定后，你无法在同一个会话中将数据写入表中。然后再来检查来自不同会话的READ锁。
首先，连接sbtest并检查连接ID：
|
1 2 3 4 5 6 7 |
mysql> SELECT CONNECTION_ID(); +-----------------+ | CONNECTION_ID() | +-----------------+ | 46092 | +-----------------+ 1 row in set (0.00 sec) |
然后，查询tbl表数据。
|
1 2 3 4 5 6 7 |
mysql> SELECT * FROM tbl; +----+-----+ | id | col | +----+-----+ | 1 | 10 | +----+-----+ 1 row in set (0.00 sec) |
接下来，tbl从第二个会话中插入一个新行到表中。
|
1 |
mysql> INSERT INTO tbl(col) VALUES(20); |
来自第二个会话的插入操作处于等待状态，因为第一个会话已经在tbl表上获取了一个READ锁并且它尚未释放。
你可以从SHOW PROCESSLIST中看到详细的元数据锁等待信息。
|
1 2 3 4 5 6 7 8 |
mysql> SHOW PROCESSLIST; +-------+------+-------------------+--------------------+-------------+--------+--------------------------------------+---------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-------+------+-------------------+--------------------+-------------+--------+--------------------------------------+---------------------------------+----------+ | 46083 | root | localhost | sbtest | Query | 0 | init | SHOW PROCESSLIST | 0.000 | | 46092 | root | localhost | sbtest | Query | 20 | Waiting for table level lock | INSERT INTO tbl(col) VALUES(20) | 0.000 | +-------+------+-------------------+--------------------+-------------+--------+--------------------------------------+---------------------------------+----------+ 5 rows in set (0.00 sec) |
之后，回到第一个会话并使用UNLOCK TABLES语句释放锁（或者中断第一个会话也会释放锁）。从第一个会话释放READ锁后，第二个会话中的INSERT操作将会立即执行成功。
lock tables有一个local关键字，如lock tables tbl read local，意思就是只针对当前会话加read锁。其他会话可以正常写入数据的，但是只能针对非InnoDB存储引擎才有效，对于InnoDB存储引擎无效。
最后还有一个小问题，其它会话能否也能给该表加LOCK TABLES READ？ 其它会话是否也能给该表加LOCK TABLES WRITE？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> LOCK TABLES tbl READ; Query OK, 0 rows affected (0.00 sec) mysql> show open tables where in_use >= 1; +----------+-------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+-------+--------+-------------+ | sbtest | tbl | 2 | 0 | +----------+-------+--------+-------------+ 1 row in set (0.00 sec) mysql> LOCK TABLES tbl WRITE; waiting... |
结果表明，其他会话可以加LOCK TABLES READ，但不能加LOCK TABLES WRITE。
三、表锁为WRITE类型
WRITE的表锁具有以下功能：
- 只有拥有表锁的会话才能读取和写入表中的数据，且可以删除表，但不能执行RENAME操作。
- 在写入锁定被释放之前，其他会话不能读取和写入数据到表。
我们来详细了解WRITE锁的工作原理。
首先，从第一次会话中获取一个WRITE锁。
|
1 |
mysql> LOCK TABLE tbl WRITE; |
然后，在tbl表中插入一个新行。
|
1 2 |
mysql> INSERT INTO tbl(col) VALUES(11); Query OK, 1 row affected (0.02 sec) |
虽然写入成功了，但是不支持RENAME操作。
|
1 2 |
mysql> rename table tbl to tbl_new; ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction |
接下来，从tbl表中读取数据。
|
1 |
mysql> SELECT * FROM tbl; |
之后，从第二会话尝试写入和读取数据：
|
1 |
mysql> SELECT * FROM tbl; |
MySQL将这些操作置于等待状态，你可以使用SHOW PROCESSLIST语句进行查看。
最后，释放第一次会话的锁定。
|
1 |
UNLOCK TABLES; |
你将看到第二个会话中的所有操作执行成功。
LOCK TABLES对于VIEW加锁，LOCK TABLES语句会为VIEW中使用的所有基表加锁。对触发器使用LOCK TABLE，那么就会锁定触发器中所包含的全部表（any tables used in triggers are also locked implicitly）。
