MySQL についての私の理解 5: ロックとロックルール-mysql チュートリアル-php.cn

mysqlチュートリアルコラムでは、MySQL の 5 番目の記事、ロックとロックルールについて紹介します。

MySQL シリーズの第 5 部、主な内容はロック (Lock) で、ロック、行ロック、ギャップロック、ロックルールの詳細な分類が含まれます。

MySQL では、同時書き込みの問題を解決するためにロックが導入されています。たとえば、2 つのトランザクションが同じレコードに同時に書き込みます。同時に行うことが許可されている場合、ダーティライティング が発生します。これは、どの分離レベルでも発生が許可されない異常な状況です。ロックの機能は、ダーティ書き込みの問題を回避するために、2 つの同時書き込み操作を特定の順序で実行できるようにすることです。

まず最初に、この記事で使用されている例を宣言します

CREATE TABLE `user`  (  `id` int(12) NOT NULL AUTO_INCREMENT,  `name` varchar(36) NULL DEFAULT NULL,  `age` int(12) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,  INDEX `age`(`age`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1;insert into user values (5,'重塑',5),(10,'达达',10),(15,'刺猬',15);复制代码

ログイン後にコピー

この記事で説明されている例はすべて、MySQL InnoDB ストレージエンジンとRepeatable Read (Repeatable Read) 分離レベルの下にあります。。

1. ロックの粒度の分類

ロックの粒度の観点から、MySQL のロックは、グローバルロック、テーブルレベルのロック、行ロックの 3 つのタイプに分類できます。

1.1 グローバルロック

グローバルロックはデータベース全体をロックします。このとき、データベースは読み取り専用状態になります。DDL (データ定義) を含む、データベースを変更するすべてのステートメントLanguage) を追加して、データベースのグローバルロックが解放されるまで、削除されたすべての DML (データ操作言語) ステートメントがブロックされます。

グローバルロックを使用する最も一般的な場所は、フルデータベースバックアップを実行することです。次のステートメントを通じてグローバルロックのロック操作とロック解放操作を実装できます:

-- 加全局锁flush tables with read lock;-- 释放全局锁unlock table;复制代码

ログイン後にコピー

クライアント接続が切断されると、グローバルロックは自動的に解除されます。

1.2 テーブルレベルのロック

テーブルレベルのロックはテーブル全体をロックします。MySQL のテーブルレベルのロックには次のものが含まれます: テーブルロック、メタデータロック(メタデータロック)、インテンションロック(インテンションロック)、および自動インクリメントロック(AUTO-INC ロック)。

1.2.1 テーブルロック

テーブルロックのロックと解放方法:

Lock:lock table tableName read/write;
ロックを解放します: unlock table;

テーブルロックをロックすると、同じクライアント接続の操作権限も制限されることに注意してください。 -レベルの読み取りロック (ロックテーブルユーザー読み取り) が追加され、同じクライアント接続で、テーブルレベルの読み取りロックが解放される前に、同じテーブル (ユーザーテーブル) は読み取りのみ可能になり、操作はできなくなります。他のクライアント接続はこのテーブル (ユーザーテーブル) に対して読み取り操作のみを実行できますが、書き込み操作は実行できません。

テーブルレベルの書き込みロック (ロックテーブルユーザー書き込み) が追加された場合、テーブルは同じクライアント接続で読み書きできますが、他のクライアント接続では読み取れません。書き込み操作を実行できません。

1.2.2 メタデータロック

テーブルレベルのロックの 2 番目のタイプは、メタデータロック (MDL、メタデータロック) です。メタデータロックは、 client テーブルに入るときにロックが自動的にロックされ、クライアントがトランザクションを送信するときにロックが解放されます。これにより、次のシナリオでの問題が防止されます:

## ユーザーから * を選択; 選択* from user;上記の表に示すように、

sessionA	sessionB
begin;

##テーブルユーザーを変更して列の誕生日日時を追加;

sessionA

はトランザクションを開き、クエリを実行します。その後、別のクライアント sessionB が birthday フィールドを user テーブルに追加し、sessionA が別のクエリを実行します。メタデータロックにより、同じトランザクション内で前後 2 回クエリされたレコード、テーブルのフィールドと列の数が一致しない可能性があり、これは明らかに回避する必要があります。 DDL 操作は、他のトランザクションのメタデータ読み取りおよび書き込みロックと互換性のないメタデータ書き込みロックをテーブルに追加します。DML 操作は、テーブルにメタデータ読み取りロックを追加します。これは、他のトランザクションのメタデータ読み取りおよび書き込みロックと互換性があります。他のトランザクションのメタデータ。読み取りロックは共有されますが、他のトランザクションのメタデータ書き込みロックとは互換性がありません。

1.2.3 インテンションロック

テーブルレベルのロックの 3 番目のタイプは

インテンションロック

で、トランザクションがテーブル内の特定の行のロックを取得したいことを示します。 (共有ロックまたは排他ロック)。意図的ロックは、テーブルロックを適用し、テーブル内の各行をスキャンしてテーブルに行ロックがあるかどうかを確認する別のトランザクションによるシステムの消費を回避することです。

#セッションAセッションB開始;更新には id=5 のユーザーから * を選択;##ユーザー読み取りテーブルをフラッシュ;

例如，sessionA 开启了一个事务，并对 id=5 这一行加上了行级排它锁，此时 sessionB 将对 user 表加上表级排它锁（只要 user 表中有一行被其他事务持有读锁或写锁即加锁失败）。

如果没有意向锁，sessionB 将扫描 user 表中的每一行，判断它们是否被其他事务加锁，然后才能得出 sessionB 的此次表级排它锁加锁是否成功。

而有了意向锁之后，在 sessionB 将对 user 表加锁时，会直接判断 user 表是否被其他事务加上了意向锁，若有则加锁失败，若无则可以加上表级排它锁。

意向锁的加锁规则：

事务在获取行级共享锁（S锁）前，必须获取表的意向共享锁（IS锁）或意向排它锁（IX锁）
事务在获取行级排它锁（X锁）前，必须获取表的意向排它锁（IX锁）

1.2.4 自增锁

第四种表级锁是自增锁，这是一种特殊的表级锁，只存在于被设置为 AUTO_INCREMENT 自增列，如 user 表中的 id 列。

自增锁会在 insert 语句执行完成后立即释放。同时，自增锁与其他事务的意向锁可共享，与其他事务的自增锁、共享锁和排它锁都是不兼容的。

1.3 行锁

行锁是由存储引擎实现的，从行锁的兼容性来看，InnoDB 实现了两种标准行锁：共享锁（Shared Locks，简称S锁）和排它锁（Exclusive Locks，简称X锁）。

这两种行锁的兼容关系与上面元数据锁的兼容关系是一样的，可以用下面的表格表示。

事务A\事务B	共享锁（S锁）	排它锁（X锁）
共享锁（S锁）	兼容	冲突
排它锁（X锁）	冲突	冲突

而从行锁的粒度继续细分，又可以分为记录锁（Record Lock）、间隙锁（Gap Lock）、Next-key Lock。

1.3.1 记录锁（Record Lock）

我们一般所说的行锁都是指记录锁，它会把数据库中的指定记录行加上锁。

假设事务A中执行以下语句（未提交）：

begin;update user set name='达闻西' where id=5;复制代码

ログイン後にコピー

InnoDB 至少会在 id=5 这一行上加一把行级排它锁（X锁），不允许其他事务操作 id=5 这一行。

需要注意的是，这把锁是加在 id 列的主键索引上的，也就是说行级锁是加在索引上的。

假设现在有另一个事务B想要执行一条更新语句：

update user set name='大波浪' where id=5;复制代码

ログイン後にコピー

这时候，这条更新语句将被阻塞，直到事务A提交以后，事务B才能继续执行。

1.3.2 间隙锁（Gap Lock）

间隙锁，顾名思义就是给记录之间的间隙加上锁。

需要注意的是，间隙锁只存在于可重复读（Repeatable Read）隔离级别下。

不知道大家还记不记得幻读？

幻读是指在同一事务中，连续执行两次同样的查询语句，第二次的查询语句可能会返回之前不存在的行。

间隙锁的提出正是为了防止幻读中描述的幻影记录的插入而提出的，举个例子。

sessionA	sessionB
begin;
select * from user where age=5;(N1)
	insert into user values(2, '大波浪', 5)
update user set name='达闻西' where age=5;
select * from user where age=5;(N2)

sessionA 中有两处查询N1和N2，它们的查询条件都是 age=5，唯一不同的是在N2处的查询前有一条更新语句。

照理说在 RR 隔离级别下，同一个事务中两次查询相同的记录，结果应该是一样的。但是在经过更新语句的当前读查询后（更新语句的影响行数是2），N1和N2的查询结果并不相同，N2的查询将 sessionB 插入的数据也查出来了，这就是幻读。

而如果在 sessionA 中的两次次查询都用上间隙锁，比如都改为select * from user where age=5 for update。那么 sessionA 中的当前读查询语句至少会将id在(-∞, 5)和(5, 10)之间的间隙加上间隙锁，不允许其他事务插入主键id属于这两个区间的记录，即会将 sessionB 的插入语句阻塞，直到 sessionA 提交之后，sessionB 才会继续执行。

也就是说，当N2处的查询执行时，sessionB 依旧是被阻塞的状态，所以N1和N2的查询结果是一样的，都是(5,重塑,5)，也就解决了幻读的问题。

1.3.3 Next-key Lock

Next-key Lock 其实就是记录锁与记录锁前面间隙的间隙锁组合的产物，它既阻止了其他事务在间隙的插入操作，也阻止了其他事务对记录的修改操作。

2. 加锁规则

不知道大家有没有注意到，我在行锁部分描述记录锁、间隙锁加锁的具体记录时，用的是「至少」二字，并没有详细说明具体加锁的是哪些记录，这是因为记录锁、间隙锁和 Next-key Lock 的加锁规则是十分复杂的，这也是本文主要讨论的内容。

关于加锁规则的叙述将分为三个方面：唯一索引列、普通索引列和普通列，每一方面又将细分为等值查询和范围查询两方面。

需要注意的是，这里加的锁都是指排它锁。

在开始之前，先来回顾一下示例表以及表中可能存在的行级锁。

mysql> select * from user;
+----+--------+------+| id | name   | age  |
+----+--------+------+|  5 | 重塑   |    5 |
| 10 | 达达   |   10 |
| 15 | 刺猬   |   15 |
+----+--------+------+3 rows in set (0.00 sec)复制代码

ログイン後にコピー

表中可能包含的行级锁首先是每一行的记录锁——(5,重塑,5),(10,达达,5),(15,刺猬,15)。

假设 user 表的索引值有最大值 maxIndex 和最小值 minIndex，user 表还可能存在间隙锁(minIndex,5),(5,10),(10,15),(15,maxIndex)。

共三个记录锁和四个间隙锁。

2.1 唯一索引列等值查询

首先来说唯一索引列的等值查询，这里的等值查询可以分为两种情况：命中与未命中。

当唯一索引列的等值查询命中时：

sessionA	sessionB
begin;
select * from user where id=5 for update;
	insert into user values(1,'斯斯与帆',1),(6,'夏日阳光',6),(11,'告五人',11),(16,'面孔',16);
	update user set age=18 where id=5;（Blocked）
	update user set age=18 where id=10;
	update user set age=18 where id=15;

上表の sessionB の実行結果は、ブロックされた行 id=5 の update ステートメントを除き、他のステートメントは正常に実行されます。

sessionB insert ステートメントはギャップロックをチェックし、update ステートメントはレコードロック (行ロック) をチェックします。実行結果は、ユーザーテーブル内のすべてのギャップがロックされておらず、レコードロックで id=5 の行のみがロックされていることがわかります。

したがって、一意のインデックス列の同等のクエリがヒットすると、ヒットしたレコードのみがロックされます。

一意のインデックス列の等価クエリが見つからない場合:

##begin;##select * from user where id=3 for update;） ##ユーザー設定の年齢 = 18 (id = 5) を更新します;#ユーザー値に挿入 (6,'Summer Sunshine',6);##ユーザー設定 age=18 を更新 (id=10) ;##ユーザー値に挿入 (11,'5 人を訴える',11);update user set age=18 where id=15;ユーザー値に挿入 (16,'face ',16);上記の表の実行結果は、への id=2 のレコード挿入がブロックされ、他のステートメントはブロックされます。は正常に実行されます。実行結果によると、

sessionA	sessionB


ユーザー値に挿入 (2,'Reflector',2);（ Blocked





	sessionB

sessionA

ユーザーテーブルに追加されたロックはギャップロック(1,5)であることがわかります。

したがって、一意のインデックス列の等しい値のクエリが失敗した場合、 は ID 値が存在するギャップにギャップロック

を追加します。

2.2 一意のインデックス列範囲クエリ

範囲クエリは等しい値クエリよりも複雑で、テーブル内に境界値が存在するかどうか、および境界値にヒットするかどうかを考慮する必要があります。まず、境界値がテーブルに存在するが欠落している状況を見てみましょう:

sessionA

sessionBブロック中)ブロック中）ブロックされた）ブロックされた）##ユーザー設定の年齢 = 18 を更新ここで id=15; この時点で、id=5,id=10ギャップロックレコードロックは Next-key Lock

	#begin;
##更新用に ID
	ユーザー値に挿入 (1,'シシとセイル',1);(
	ユーザー設定の年齢=18 (ID=5) を更新;（
	ユーザー値に挿入 (6,'Summer Sunshine',6);（
	ユーザー設定の年齢 = 18、ID = 10 を更新;（
	ユーザー値に挿入 (11,'5 人を訴える',11);

##ユーザー値 (16,'face',16) に挿入 ;
sessionA によってユーザーテーブルに追加されたロックは、レコードロック	とギャップロック (minIndex,5) です。 (5,10)。

であることがわかっているため、上記のロック状況は 2 つの

Next-key Lock: (minIndex, 5]、(5,10]、つまり Next-key Lock —— (minIndex,10]。

#境界が値が存在しますテーブル内で同時ヒット:

##sessionA

sessionB

begin ;##select * from user where idユーザー値に挿入 (1,'シシとセイル',1);（）ブロックされたブロックされた）##ユーザー値に挿入 (11,' レポート 5)人',11);（##ユーザー設定年齢=18を更新、ID=15;（ブロック済みユーザー値(16,'face',16) ;

ブロックされた

ユーザー設定の年齢 = 18、ID = 5 を更新;（）

ユーザー値に挿入 (6,'Summer Sunshine',6);（）

#ユーザーを更新set age=18 where id=10;（
Blocked

ブロック
）

）

に挿入
#

此时 sessionA 给 user 表加上的锁是Next-key Lock —— (minIndex,15]。

当边界值不存在于表中时，不可能命中，故只有未命中一种情况：

sessionA	sessionB
begin;
select * from user where id
	insert into user values (1,'斯斯与帆',1);（Blocked）
	update user set age=18 where id=5;（Blocked）
	insert into user values (6,'夏日阳光',6);（Blocked）
	update user set age=18 where id=10;（Blocked）
	insert into user values (11,'告五人',11);
	update user set age=18 where id=15;
	insert into user values (16,'面孔',16) ;

此时 sessionA 给 user 表加上的锁是 Next-key Lock —— (minIndex,10]，与第一种情况一样。

综上所述，在对唯一索引进行范围查询时：

会给范围中的记录加上记录锁，间隙加上间隙锁
对于范围查询(大于/大于等于/小于/小于等于)是比较特殊的，它会将记录锁加到第一个边界之外的记录上，若其中有额外的间隙也会加上间隙锁（即会将 Next-key Lock 加到第一个边界之外的记录上）

需要注意的是，第一条中所说的间隙指的是，边界值所在的间隙，如间隙为(5,10)，查询条件为 id>7 时，这个间隙锁就是(5,10)，而不是(7,10)。

第二条举例1：查询条件为 idNext-key Lock 锁会加到 id=10 的记录上，被锁住的范围是(minIndex,10]。

第二条举例2：查询条件为 idNext-key Lock 锁会加到 id=15 的记录上，被锁住的范围是(minIndex,15]。

第二条举例3：查询条件为 id>10，第一个边界之外的记录是 id=10，Next-key Lock 锁会加到 id=10 的记录上，由于 Next-key Lock 锁指的是记录以左的部分，所以被锁住的范围是(5,maxIndex]。

2.3 普通索引列等值查询

普通索引与唯一索引的区别就在于唯一索引可以根据索引列确定唯一性，所以等值查询的加锁规则也有不同之处。

给 user 表再加一条记录:

INSERT INTO user VALUES (11, '达达2.0', 10);复制代码

ログイン後にコピー

这时 user 表的索引 age 结构如下图所示：

在索引 age 中可能存在的行锁是4个记录锁以及5个间隙锁。

先来看索引 age 上的加锁情况：

sessionA	sessionB
begin;
select * from user where age=10 for update;
	insert into user values (2,'达达',2);
	update user set name='痛仰' where age=5;
	insert into user values (6,'达达',6);（Blocked）
	update user set name='痛仰' where age=10 and id=10;（Blocked）
	update user set name='痛仰' where age=10 and id=16;）（Blocked）
	insert into user values (17,'达达',10);（Blocked）
	insert into user values (11,'达达',11);（Blocked）
	update user set name='痛仰' where age=15;
	insert into user values (16,'面孔',16) ;

上の表のステートメントと実行結果から判断すると、インデックス経過時間のロック状況は次のとおりです。

つまり、インデックス経過時間のロック領域は次のとおりです。 (5、15)。

通常のインデックスではレコードの一意性を判断できないため、通常のインデックス列の値が等しいクエリでインデックスの経過時間をロックする場合、は 10 未満の最初の経過時間の値 (つまり 5) を見つけます。最初の年齢は 10 (つまり 15) より大きく、この範囲内のギャップはギャップロックで追加され、レコードはレコードロックで追加されます。

これはインデックス経過時間のロック状況です。クエリステートメントはレコードのすべての列をクエリするため、クエリルールに従って、インデックス経過時間の対応する ID 値が主キーに返されます。テーブルの戻り操作のインデックスツリーを作成し、すべての列を取得するため、主キーインデックスもロックされます。ここで、age=10 を満たすレコードの主キー ID はそれぞれ 10 と 16 であるため、これら 2 つの行も主キーインデックスで排他的にロックされます。

つまり、通常のインデックス列相当のクエリです。テーブルを返す必要がある場合は、条件を満たすレコードに対応する主キーもレコードロックで追加されます。

sessionA のクエリを select id from user where age=10 lock in share mode; に変更すると、カバーにより最適化されます。 Index テーブルの復帰操作は実行されないため、主キーのインデックスはロックされません。

2.4 通常のインデックス列の等しい値のクエリ制限

ここで制限構文について言及する必要があります。そのロック範囲 (通常のインデックスのみについて説明します) はより小さくなります。例を参照してください:

#セッションAセッションB##begin;#更新には、年齢 = 10 の制限 1 のユーザーから * を選択;#ユーザー値に挿入 (2,'Dada',2);ユーザーセット名 ='Pain Yang' を更新ここで age=5; ブロック済み））##insert into user names (16,'face',16) ; 次のことがわかります。制限を追加しない場合と比較して、さらに 2 つの挿入ステートメントがスムーズに実行されます。上記の表のステートメントと実行結果から判断すると、インデックス経過時間のロック状況は次のとおりです。次のことがわかります。 limit 構文ロックは条件を満たすレコードにのみ追加され、ロックの範囲を減らすことができます。





	##ユーザー値に挿入 (6,'Dada',6);（
	ユーザーセット名 = 'Pain Yang' を更新 (年齢 = 10、ID = 10);（ブロック
	#ユーザーセット名 = 'pain' を更新 (年齢 = 10、id = 16;)
ユーザー値に挿入 (17,'Dada',10);
ユーザー値に挿入(11,'Dada',11);
##ユーザーセット名 = 'Pain Yang' を更新 (年齢 = 15);

2.5 通常のインデックス列の範囲クエリ

次に、通常のインデックス列の範囲クエリを見てみましょう (ここでは、インデックス存続期間のロック範囲のみについて説明します。主キーインデックスのロック)戻りテーブルがある場合はロックされます。対応する ID 値):

sessionAsessionB

begin; #更新対象の年齢が 8 歳以上、年齢が 12 歳以下のユーザーから * を選択;##ユーザー値 (2,'Dada',2) に挿入;ユーザーセット名 = 'Pain Yang' を更新、年齢 = 5;Blocked）ユーザーセット名='埐ANG'を更新します。 age=10 および id=10 ;（ブロックユーザーセット名='Pain Yang'を更新、ここで年齢= 10 および id=16;（ Blockedユーザー値に挿入 (17,'Dada',10 );（ブロック##ユーザー値に挿入 (11,'Dada',11);（ブロックされた）ブロックされた）




	##ユーザー値に挿入(6,'Dada', 6);（

）
）
）
	##ユーザーセット名='Pain Yang'を更新、年齢=15;（
	ユーザー値 (16,'face',16) に挿入 ;
与普通索引列等值查询不同的是，范围查询比等值查询多了一个 age=15 的记录锁。这个边界值与唯一索引列范围查询的原理是一样的，可以参照上文所述来理解，这里不多加赘述了。《MySQL实战45讲》的作者丁奇认为这是一个 BUG，但并未被官方接收，如果要深究这个边界值的原理，可能就需要看 MySQL 的源码了。 3. 温故知新 MySQL 中的锁按粒度来分可以分为几种？分别描述一下。 MySQL 中行锁的加锁规则？请说出下面几条 SQL 的加锁区域： select * from user where age=10 for update;select * from user where age>=10 and age=10 and age<blockquote><p><strong>更多相关免费学习推荐：</strong><a href="https://www.php.cn/course/list/51.html" target="_blank"><strong>mysql教程</strong></a><strong>(视频)</strong></p></blockquote> ログイン後にコピー