> For the complete documentation index, see [llms.txt](https://hezhiqiang-book.gitbook.io/mysql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://hezhiqiang-book.gitbook.io/mysql/di-yi-zhang/index-9/biao-suo-ding.md).

# 10.2 表锁定

在本教程中，您将学习如何使用MySQL锁来协调会话之间的表访问。

MySQL允许客户端会话明确获取表锁，以防止其他会话在特定时间段内访问表。客户端会话只能为自己获取或释放表锁。它不能获取或释放其他会话的表锁。

在详细介绍之前，我们将创建一个名为`sampledb`的示例数据库，其中包含一个简单的`tbl`表来模拟练习表锁定语句。

```sql
CREATE DATABASE IF NOT EXISTS testdb;

USE testdb;
CREATE TABLE tbl (
  id int(11) NOT NULL AUTO_INCREMENT,
  col int(11) NOT NULL,
  PRIMARY KEY (id)
);
```

## LOCK和UNLOCK TABLES语法

获取表的锁的简单形式如下：

```sql
LOCK TABLES table_name [READ | WRITE]
```

可将表的名称放在`LOCK TABLES`关键字后面，后跟一个锁类型。 MySQL提供两种锁类型：`READ`和`WRITE`。 我们将在下一节详细介绍这两种锁类型。

要释放表的锁，请使用以下语句：

```sql
UNLOCK TABLES;
```

### 表锁定为READ

表的READ锁具有以下功能：

* 同时可以通过多个会话获取表的`READ`锁。此外，其他会话可以从表中读取数据，而无需获取锁定。
* 持有`READ`锁的会话只能从表中读取数据，但不能写入。此外，其他会话在释放`READ`锁之前无法将数据写入表中。来自另一个会话的写操作将被放入等待状态，直到释放`READ`锁。
* 如果会话正常或异常终止，MySQL将会隐式释放所有锁。这也与`WRITE`锁相关。

下面我们来看看在以下情况下`READ`锁如何工作。

首先，连接到`testdb`数据库。要查找当前的连接ID，请使用`CONNECTION_ID()`函数，如下所示：

```sql
mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               6 |
+-----------------+
1 row in set
```

然后，在向`tbl`表中插入一个[新行](http://www.yiibai.com/mysql/insert-statement.html)。

```sql
INSERT INTO tbl(col) VALUES(10);
```

接下来，从上表`tbl`中检索所有行。

```sql
mysql> SELECT * FROM tbl;
+----+-----+
| id | col |
+----+-----+
|  1 |  10 |
+----+-----+
1 row in set
```

之后，要获取锁，可以使用`LOCK TABLE`语句。 最后，在同一个会话中，如果您尝试在`tbl`表中插入一个新行，将收到一条错误消息。

```sql
mysql> LOCK TABLE tbl READ;
Query OK, 0 rows affected

mysql> INSERT INTO tbl(col) VALUES(11);
1099 - Table 'tbl' was locked with a READ lock and can't be updated
mysql>
```

所以一旦获得了`READ`锁定，就不能在同一个会话中的表中写入数据。让我们从不同的会话中来查看`READ`锁。

首先，打开另一个终端并连接到数据库`testdb`，然后检查连接ID：

```sql
mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               7 |
+-----------------+
1 row in set
```

然后，从`tbl`检索数据，如下所示 -

```sql
mysql> SELECT * FROM tbl;
+----+-----+
| id | col |
+----+-----+
|  1 |  10 |
+----+-----+
1 row in set
```

接下来，从第二个会话(会话ID为`7`)插入一个新行到`tbl`表中。

![img](http://www.yiibai.com/uploads/images/201707/2107/862100707_50181.jpg)

第二个会话的插入操作处于等待状态，因为第一个会话已经在`tbl`表上获取了一个`RE`AD锁，并且尚未释放。

可以使用`SHOW PROCESSLIST`语句查看详细信息，如下所示 -

```sql
mysql> SHOW PROCESSLIST;
+----+------+-----------------+----------+---------+------+---------------------------------+---------------------------------+
| Id | User | Host            | db       | Command | Time | State                           | Info                            |
+----+------+-----------------+----------+---------+------+---------------------------------+---------------------------------+
|  2 | root | localhost:51998 | NULL     | Sleep   |  474 |                                 | NULL                            |
|  3 | root | localhost:51999 | yiibaidb | Sleep   | 3633 |                                 | NULL                            |
|  6 | root | localhost:52232 | testdb   | Query   |    0 | starting                        | SHOW PROCESSLIST                |
|  7 | root | localhost:53642 | testdb   | Query   |  110 | Waiting for table metadata lock | INSERT INTO tbl(col) VALUES(20) |
+----+------+-----------------+----------+---------+------+---------------------------------+---------------------------------+
4 rows in set
```

之后，返回第一个会话并使用`UNLOCK TABLES`语句来释放锁。从第一个会话释放`READ`锁之后，在第二个会话中执行`INSERT`操作。

最后，查看`tbl`表中的数据，以查看第二个会话中的`INSERT`操作是否真的执行。

```sql
mysql> SELECT * FROM tbl;
+----+-----+
| id | col |
+----+-----+
|  1 |  10 |
|  2 |  20 |
+----+-----+
2 rows in set
```

### 将MySQL表锁定WRITE

表锁为`WRITE`具有以下功能：

* 只有拥有表锁定的会话才能从表读取和写入数据。
* 在释放`WRITE`锁之前，其他会话不能从表中读写。

详细了解`WRITE`锁的工作原理。

首先，从第一个会话获取一个`WRITE`锁。

```sql
LOCK TABLE tbl WRITE;
```

然后，在`tbl`表中插入一个新行。

```sql
INSERT INTO tbl(col) VALUES(11);
```

没有问题，上面语句可能正常执行。接下来，从`tbl`表读取数据。

```sql
mysql> SELECT * FROM tbl;
+----+-----+
| id | col |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  11 |
+----+-----+
3 rows in set
```

之后，打开第二个连接到MySQL的会话，尝试写和读数据：

MySQL将这些操作置于等待状态。可以在第一个会话中，使用`SHOW PROCESSLIST`语句来查看它。

```sql
mysql> SHOW PROCESSLIST;
+----+------+-----------------+----------+---------+-------+---------------------------------+---------------------------------+
| Id | User | Host            | db       | Command | Time  | State                           | Info                            |
+----+------+-----------------+----------+---------+-------+---------------------------------+---------------------------------+
|  2 | root | localhost:51998 | NULL     | Sleep   |  8477 |                                 | NULL                            |
|  3 | root | localhost:51999 | yiibaidb | Sleep   | 11636 |                                 | NULL                            |
|  8 | root | localhost:54012 | testdb   | Sleep   |   119 |                                 | NULL                            |
|  9 | root | localhost:54013 | testdb   | Query   |     0 | starting                        | SHOW PROCESSLIST                |
| 10 | root | localhost:54016 | testdb   | Query   |    49 | Waiting for table metadata lock | INSERT INTO tbl(col) VALUES(21) |
+----+------+-----------------+----------+---------+-------+---------------------------------+---------------------------------+
5 rows in set
```

最后，从第一个会话释放锁。执行以下语句 -

```sql
UNLOCK TABLES;
```

执行上面语句后，将看到第二个会话中的所有待处理已经执行操作。

```sql
SELECT * FROM tbl;
Query OK, 1 row affected

+----+-----+
| id | col |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  11 |
|  4 |  21 |
+----+-----+
4 rows in set
```

在本教程中，我们向您展示了如何锁定和解锁：`READ`和`WRITE`操作，以便在会话之间配合表访问。