锁定和解锁 MySQL 中的表

假设在一个银行系统中有这样的逻辑：

当用户 A 从自己的银行账户取出 500 元时，用户 A 的余额为原余额减去 500。当另一个用户 B 给用户转入 500 元时，用户 A 的余额为原余额加上 500。

如果这两个操作同时发生，则可能导致用户 A 的余额是错误的。

MySQL 的锁就是为了解决这种并发问题的。MySQL 支持三种类型的锁：表级锁、行级锁和页面所。

MySQL 允许您在会话中显式地获取表锁，以防止其他会话在您需要独占访问表的期间修改表。

锁的操作是在当前会话中进行的。一个会话只能为自己获取锁，并只能释放自己的锁。

MySQL 提供了 LOCK TABLES 和 UNLOCK TABLES 语句用于显式地的获取表锁和释放表锁。

MySQL 表锁语法

LOCK TABLES 语法

要为当前会话显式地获取表锁，请按照以下语法使用 LOCK TABLES 语句：

LOCK TABLES
    table_name [READ | WRITE]
    [, table_name [READ | WRITE], ...];

这里：

table_name 是您要获取锁的表名。
READ 和 WRITE 是锁类型。 READ 锁用于共享读取表， WRITE 锁用于排斥的读写表。
您可以在一个语句中获取多个表的锁。多个表锁之间使用逗号分隔。
LOCK TABLES 语句在获取新的表锁之前会隐式的释放当前会话持有的所有的表锁。
您可以使用 LOCK TABLE 代替 LOCK TABLES。

UNLOCK TABLES 语法

要释放当前会话获取的所有的表锁，请使用以下语句：

UNLOCK TABLES;

锁类型

表锁支持 READ 和 WRITE 两种类型的锁。 READ 锁用于共享读取表， WRITE 锁用于排斥的读写表，他们的特点如下：

`READ` 锁

持有表锁的会话只能读取表，但不能写入表。
多个会话可以同时获取一个表的 READ 锁。
其他会话无需显式获取 READ 锁即可读取该表，但是不能写入表。其他会话的写操作会一直等待知道读锁被释放。

`WRITE` 锁

持有锁的会话可以读写表。
只有持有锁的会话才能访问该表。在释放锁之前，没有其他会话可以访问它。
持有 WRITE 锁时，其他会话对表的锁请求会阻塞。

如果您没有显式地的释放表锁，当会话结束后，无论是 READ 锁还是 WRITE 锁，都会被 MySQL 释放掉。

MySQL 表锁实例

这个实例演示了如何在 MySQL 中获取表锁（ READ 和 WRITE 锁）和释放表锁。

我们使用以下语句在 testdb 数据库中创建一个 test_lock 表以实践我们的实例：

CREATE TABLE `test_lock` (
  `id` int NOT NULL AUTO_INCREMENT,
  `txt` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

如果您没有 testdb 数据库，请先使用如下语句创建数据库并选择数据库：

CREATE DATABASE testdb;
use testdb;

`READ` 锁实例

READ 锁是共享的读锁。让我们通过以下实例了解 READ 锁的特性。

先开启一个会话，并使用 SHOW PROCESSLIST 语句查看当前会话的 ID：

SHOW PROCESSLIST;

+----+-----------------+-----------+--------+---------+--------+---------------------------------+--------------------------------------------------+
| Id | User            | Host      | db     | Command | Time   | State                           | Info                                             |
+----+-----------------+-----------+--------+---------+--------+---------------------------------+--------------------------------------------------+
|  5 | event_scheduler | localhost | NULL   | Daemon  | 201156 | Waiting on empty queue          | NULL                                             |
|  8 | root            | localhost | testdb | Query   |      0 | init                            | show PROCESSLIST                                 |
+----+-----------------+-----------+--------+---------+--------+---------------------------------+--------------------------------------------------+

这里，当前会话的 ID 为 8。

在当前会话没有获取 READ 锁的情况下向表中插入一个新行，
```
INSERT INTO test_lock(txt)
VALUES('Hello');
```
这里能正常插入。
使用下面的 LOCK TABLES 获取 test_lock 表的读锁：
```
LOCK TABLES test_lock READ;
```
让我们在拥有 READ 锁的情况下向表中插入一个新行，
```
INSERT INTO test_lock(txt)
VALUES('Hello World');
```
此时，MySQL 给出一个错误： “ERROR 1099 (HY000): Table ’test_lock’ was locked with a READ lock and can’t be updated”。
您可以重新打开一个会话，并在新会话测试读表：
```
SELECT * FROM test_lock;
```
```
+----+-------------+
| id | txt         |
+----+-------------+
|  1 | Hello       |
+----+-------------+
```
这说明，拥有表的读锁的会话之外的其他会话也可以读表。
在新的会话中测试写表。请执执行上面的的 INSERT 语句。你会发现，该操作将被挂起，直到表的读锁被释放。

您可以使用 SHOW PROCESSLIST 语句查看回话是否处于等待的状态：

SHOW PROCESSLIST;

+----+-----------------+-----------+--------+---------+--------+---------------------------------+--------------------------------------------------+
| Id | User            | Host      | db     | Command | Time   | State                           | Info                                             |
+----+-----------------+-----------+--------+---------+--------+---------------------------------+--------------------------------------------------+
|  5 | event_scheduler | localhost | NULL   | Daemon  | 201156 | Waiting on empty queue          | NULL                                             |
|  8 | root            | localhost | testdb | Query   |      0 | init                            | show PROCESSLIST                                 |
|  9 | root            | localhost | testdb | Query   |     16 | Waiting for table metadata lock | INSERT INTO test_lock(txt) VALUES('Hello World') |
+----+-----------------+-----------+--------+---------+--------+---------------------------------+--------------------------------------------------+

您可以看到， Id 为 9 的会话正在等待锁释放，它的状态为：Waiting for table metadata lock。

`WRITE` 锁实例

WRITE 锁是排他锁，只有持有锁的会话才能访问表，其他会话的对表的操作都会等待锁的释放。让我们通过以下示例了解 WRITE 锁的特性。

先开启一个会话，并从此会话中获取 test_lock 的 WRITE 锁。
```
LOCK TABLES test_lock WRITE;
```
使用以下语句在 test_lock 表中插入一个新行。
```
INSERT INTO test_lock(txt)
VALUES('Hi');
```
插入成功。这说明持有表的写锁的会话可以写表。

使用以下语句从 test_lock 表中查询数据：

SELECT * FROM test_lock;

+----+-------------+
| id | txt         |
+----+-------------+
|  1 | Hello       |
|  2 | Hello World |
|  3 | Hi          |
+----+-------------+

查询成功。这说明持有表的写锁的会话可以读表。

开启另一个会话，并尝试读取数据：
```
SELECT * FROM test_lock;
```
您会发现，该操作处于等待的状体，并且没有返回。

您可以使用 SHOW PROCESSLIST 语句查看会话的状态

SHOW PROCESSLIST;

+----+-----------------+-----------+--------+---------+--------+---------------------------------+-------------------------+
| Id | User            | Host      | db     | Command | Time   | State                           | Info                    |
+----+-----------------+-----------+--------+---------+--------+---------------------------------+-------------------------+
|  5 | event_scheduler | localhost | NULL   | Daemon  | 202266 | Waiting on empty queue          | NULL                    |
|  8 | root            | localhost | testdb | Query   |      0 | init                            | SHOW PROCESSLIST        |
|  9 | root            | localhost | testdb | Query   |     81 | Waiting for table metadata lock | SELECT * FROM test_lock |
+----+-----------------+-----------+--------+---------+--------+---------------------------------+-------------------------+

在第一个会话中使用下面的语句释放掉持有的锁：

UNLOCK TABLES;

您将看到第二个会话中的 SELECT 语句的执行结果如下：

+----+-------------+
| id | txt         |
+----+-------------+
|  1 | Hello       |
|  2 | Hello World |
|  3 | Hi          |
+----+-------------+

结论

MySQL 允许您在会话中使用 LOCK TABLES 语句显式地获取表锁，以防止其他会话在您需要独占访问表的期间修改表。

MySQL 允许您在会话中使用 UNLOCK TABLES 语句显式地释放表锁。

MySQL 表锁有两种类型：共享的读锁和排他的写锁。

MySQL 表锁语法

LOCK TABLES 语法

UNLOCK TABLES 语法

锁类型

READ 锁

WRITE 锁

MySQL 表锁实例

READ 锁实例

WRITE 锁实例

结论

本页目录

`READ` 锁

`WRITE` 锁

`READ` 锁实例

`WRITE` 锁实例