MySQL 表锁定

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

锁是与表关联的标志。 MySQL 允许客户端会话显式获取表锁，以防止其他会话在特定时间段内访问同一个表。

客户端会话只能为自己获取或释放表锁。并且客户端会话无法获取或释放其他客户端会话的表锁。

在继续之前，让我们创建一个名为messages的表来练习表锁定语句。

CREATE TABLE messages ( 
    id INT NOT NULL AUTO_INCREMENT, 
    message VARCHAR(100) NOT NULL, 
    PRIMARY KEY (id) 
);Code language: SQL (Structured Query Language) (sql)

MySQL 锁定表语句

以下LOCK TABLES语句显式获取表锁：

LOCK TABLES table_name [READ | WRITE]Code language: SQL (Structured Query Language) (sql)

在此语法中，您可以在LOCK TABLES关键字之后指定要锁定的表的名称。此外，您还可以指定锁的类型： READ或WRITE 。

MySQL 允许您通过在LOCK TABLES关键字后指定逗号分隔的表名列表以及要锁定的锁定类型来锁定多个表：

LOCK TABLES table_name1 [READ | WRITE], 
            table_name2 [READ | WRITE],
             ... ;
Code language: SQL (Structured Query Language) (sql)

MySQL UNLOCK TABLES 语句

要释放表的锁，可以使用以下UNLOCK TABLES语句：

UNLOCK TABLES;Code language: SQL (Structured Query Language) (sql)

`READ`锁

READ锁具有以下特点：

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

我们来看看下面场景中READ锁是如何工作的。

首先，在第一个会话中连接到数据库，并使用CONNECTION_ID()函数获取当前连接 id，如下所示：

SELECT CONNECTION_ID();Code language: SQL (Structured Query Language) (sql)

然后，将新行插入messages表中。

INSERT INTO messages(message) 
VALUES('Hello');Code language: SQL (Structured Query Language) (sql)

接下来，从messages表中查询数据。

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)

之后，使用LOCK TABLE语句获取锁。

LOCK TABLE messages READ;Code language: SQL (Structured Query Language) (sql)

最后，尝试向messages表中插入新行：

INSERT INTO messages(message) 
VALUES('Hi');Code language: SQL (Structured Query Language) (sql)

MySQL 发出以下错误：

Error Code: 1099. Table 'messages' was locked with a READ lock and can't be updated.Code language: SQL (Structured Query Language) (sql)

因此，一旦获得READ锁，就无法在同一会话内向表中写入数据。

让我们检查来自不同会话的READ锁。

首先，连接数据库并检查连接id：

SELECT CONNECTION_ID();Code language: SQL (Structured Query Language) (sql)

接下来，从messages表中查询数据：

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)

然后，在messages表中插入一个新行：

INSERT INTO messages(message) 
VALUES('Bye');Code language: SQL (Structured Query Language) (sql)

这是输出：

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

从第一个会话开始，使用SHOW PROCESSLIST语句显示详细信息：

SHOW PROCESSLIST;Code language: SQL (Structured Query Language) (sql)

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

最后，检查messages表的数据，看看第二个会话的INSERT操作是否真的执行了。

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)

写锁

WRITE锁具有以下特点：

持有表锁的唯一会话可以从表中读取和写入数据。
在释放WRITE锁之前，其他会话无法从表中读取数据或向表中写入数据。

下面我们详细看看WRITE锁是如何工作的。

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

LOCK TABLE messages WRITE;Code language: SQL (Structured Query Language) (sql)

然后，将新行插入messages表中。

INSERT INTO messages(message) 
VALUES('Good Morning');Code language: SQL (Structured Query Language) (sql)

有效。

接下来，从messages表中查询数据。

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)

它也有效。

之后，从第二个会话开始，尝试写入和读取数据：

INSERT INTO messages(message) 
VALUES('Bye Bye');

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)

MySQL 将这些操作置于等待状态。您可以使用SHOW PROCESSLIST语句检查它：

SHOW PROCESSLIST;Code language: SQL (Structured Query Language) (sql)

最后，释放第一个会话的锁定。

UNLOCK TABLES;Code language: SQL (Structured Query Language) (sql)

您将看到执行的第二个会话中的所有待处理操作，下图说明了结果：

读锁与写锁

读锁是“共享”锁，可以防止获取写锁，但不能阻止其他读锁。
写锁是“独占”锁，可以阻止任何其他任何类型的锁。

在本教程中，您学习了如何锁定和解锁表以配合会话之间的表访问。

本教程有帮助吗？

MySQL 锁定表语句

MySQL UNLOCK TABLES 语句

READ锁

写锁

读锁与写锁

`READ`锁