MySQL 表锁定

锁是与表关联的一种机制，用于限制对表中数据的未经授权的访问。 MySQL允许客户端会话显式地获取表锁以与其他会话协作访问表的数据 。MySQL还允许表锁定防止在特定时间段内对同一表的未经授权修改。

MySQL中的会话只能为自己的表获取或释放锁。因此，一个会话不能为其他会话获取或释放表锁定。需要注意的是，对于表锁定，我们必须具有TABLE LOCK和SELECT权限。

MySQL中的表锁定主要是为了解决并发问题。 它将在执行事务时使用，即首先从表（数据库）中读取值，然后将其写入表（数据库）中。

MySQL 提供两种锁定类型 写锁定： 具有此锁的用户只能从表中读取数据。

写锁定： 具有此锁的用户可以对表进行读写操作。

需要注意的是，MySQL中使用的默认存储引擎是InnoDB。InnoDB存储引擎不需要手动进行表锁定，因为MySQL会自动对InnoDB表使用行级锁定。因此，我们可以在同一表上进行多个事务，同时进行读写操作，而不需要等待彼此。所有其他存储引擎在MySQL中使用表锁定。

在了解表锁定概念之前，我们首先会使用以下语句创建一个名为“ info_table ”的新表：

CREATE TABLE info_table ( 
    Id INT NOT NULL AUTO_INCREMENT, 
    Name VARCHAR(50) NOT NULL, 
    Message VARCHAR(80) NOT NULL,
    PRIMARY KEY (Id) 
);

MySQL锁表语句

以下是允许我们显式获取表锁的语法:

LOCK TABLES table_name [READ | WRITE];

在上述语法中，我们指定了在 LOCK TABLES 关键字之后要获取锁的 表名 。我们可以指定 锁类型 ，要么是READ，要么是WRITE。

在MySQL中，我们还可以通过使用逗号分隔的表名列表与锁类型一起锁定多个表。请参见下面的语法：

LOCK TABLES tab_name1 [READ | WRITE], 
            tab_name2 [READ | WRITE],...... ;

MySQL解锁表语句

下面是允许我们在MySQL中为一张表释放锁定的语法： to release a lock for a table in MySQL:

mysql> UNLOCK TABLES;

锁类型

让我们详细了解锁类型。

读锁

读锁的特点如下：

• 同时，MySQL允许多个会话对表获取读锁，而所有其他会话都可以在不获取锁的情况下读取表中的数据。
• 如果会话对表持有读锁，则不能在该表上执行写操作。这是因为读锁只能从表中读取数据。如果没有获取读锁的其他会话，将无法在释放读锁之前向表中写入数据。写操作会进入等待状态，直到未释放读锁为止。
• 当会话正常或异常终止时，MySQL会隐式地释放对表的所有类型的锁。这个特性对于写锁也是相关的。

让我们举个例子来看看在给定的场景中MySQL中的读锁是如何工作的。我们首先连接到数据库并使用 CONNECTION_ID（） 函数在第一个会话中获得当前连接id，如下所示：

mysql> SELECT CONNECTION_ID();

看下面的输出：

接下来，我们将使用以下语句向 info_table 插入几行：

mysql> INSERT INTO info_table (name, message) 
VALUES('Peter', 'Hi'),
('Joseph', 'Hello'),
('Mark', 'Welcome');

现在，使用以下语句验证表中的数据：

mysql> SELECT * FROM info_table;

我们应该看到以下输出：

现在，我们将执行LOCK TABLE语句来获得对表的锁定：

mysql> LOCK TABLE info_table READ;

此后，我们将尝试将一条新记录插入到info_table中，如下所示：

mysql> INSERT INTO info_table (name, message) 
VALUES ('Suzi', 'Hi');

我们将会获得以下输出，其中MySQL会发出以下消息： “表格 ‘info_table’ 已被READ锁定，无法进行更新” .

因此，我们可以看到，一旦在表上获取了读锁，在同一个会话中就无法向表中写入数据。

现在，我们将检查从不同会话中读锁的工作方式。首先，我们将连接到数据库并查看连接ID：

接下来，我们将从info_table查询数据，返回的结果如下：

然后，按照以下方式在该表中插入一些行：

mysql> INSERT INTO info_table (name, message) 
VALUES ('Stephen', 'Hello');

我们应该看到以下输出：

在上述的输出中，我们可以看到第二个会话中的插入操作处于 等待状态 。这是由于第一个会话已经获取了表上的读锁，并且尚未释放。

我们可以使用 SHOW PROCESSLIST 语句在第一个会话中查看它们的详细信息。请参考下面的输出：

最后，我们需要在第一个会话中使用 **UNLOCK TABLES 语句释放锁。现在，我们可以在第二个会话中执行INSERT操作了。**

写锁

下面是写锁的特点：

• 它是持有表锁并能够从表中读取和写入数据的会话。
• 它是唯一通过持有锁来访问表的会话。所有其他会话在写锁释放之前无法访问表的数据。

让我们通过以下情景来看看MySQL中写锁的工作原理。在第一个会话中，我们将使用以下语句获取写锁：

mysql> LOCK TABLE info_table WRITE;

然后，我们将按以下方式将新记录插入到info_table中：

mysql> INSERT INTO info_table (name, message) 
VALUES ('Stephen', 'How R U');

上述语句已经生效。现在，我们可以使用SELECT语句验证输出：

再次尝试从第二个会话中访问（读取/写入）该表：

INSERT INTO info_table (name, message) 
VALUES ('George', 'Welcome');

SELECT * FROM info_table;

我们可以看到这些操作被置于等待状态。使用SHOW PROCESSLIST语句可以查看关于它们的详细信息：

最后，我们将释放第一个会话中的锁。现在，我们可以执行待处理的操作。

读锁 vs 写锁

• 读锁类似于“ 共享 ”锁，因为多个线程可以同时获得它。
• 写锁是一种“ 独占 ”锁，因为另一个线程无法读取它。
• 我们不能同时为表提供读锁和写锁。
• 读锁的优先级 较低 ，确保尽快进行更新。

本文链接：http://so.lmcjl.com/news/527/