MySQL 表锁定

2024年03月27日 MySQL 表锁定 极客笔记

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/

展开阅读全文
上一篇:MySQL 视图 下一篇:MySQL 锁定账户