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）。