MySQLデータベースInnoDBエンジンの行レベルロックのロック範囲の詳細説明

Mysql データベース InnoDB エンジンは行レベルのロックをサポートしています。つまり、テーブル内のデータの特定の行に対してロック操作を実行できます。ロック操作の影響は次のとおりです。1 つのトランザクションがテーブル内の特定の行に対してロック操作を実行した場合。 、および別のトランザクションも同じ行に対してロック操作を実行する必要があるため、2 番目のトランザクションのロック操作はブロックされる可能性があり、ブロックされると、2 番目のトランザクションは最初のトランザクションが完了する (コミットまたはロールバック) までしか待機できません。またはタイムアウトになります。

この記事では主に、行ロックのロック範囲に焦点を当てて、InnoDB の行ロックに関連する概念を紹介します。

• どのような種類の SQL ステートメントがロックされますか?

• どのような種類のロックを追加すればよいですか?

• locking ステートメント によってどの行がロックされますか?

背景知識

上記では、InnoDB の行レベルのロックについて簡単に紹介しましたが、その後の検証部分を理解するには、いくつかの背景知識を追加する必要があります。対応する知識をよく知っている場合は、検証部分に直接ジャンプできます。

1. InnoDB ロックの種類

InnoDB エンジンは 7 種類のロックを使用します。

• レコードロック

• ギャップロック

• ネクストキーロック

• インテンションロック

sinceロック

• この記事には主に共有とロックが含まれています排他的ロック、レコード ロック、ギャップ ロック、ネクスト キー ロック 他のタイプのロックについては、ここでは詳しく説明しません。

1.1 共有ロックと排他ロック

• 共有ロック (S ロック) と排他ロック (X ロック) の概念は、多くのプログラミング言語で登場しています。まず、MySQL におけるこれら 2 つのロックの影響について説明します。

• トランザクションがデータの特定の行に S ロックを追加する場合、別のトランザクションも対応する行に S ロックを追加できますが、対応する行には追加できません。 Xロックを追加します。

トランザクションがデータの特定の行に X ロックを追加した場合、別のトランザクションは対応する行に S ロックも X ロックも追加できません。

古典的なマトリックス テーブルを使用して、共有ロックと排他的ロックの間の相互排他的な関係を引き続き説明します。

1
• X

1

1

図では、S は共有ロックを表し、X は排他ロックを表します。0 はロックの互換性を表し、1 はロックの競合がブロックされていないことを表します。この表から、トランザクションが排他ロックを追加すると、他のトランザクションはロックを待つ必要があることがわかります。複数の共有ロックは相互にブロックしません。

1.2 レコードロック、ギャップロック、ネクストキーロック

これら 3 種類のロックはすべてロック範囲を表すため、まとめて説明します。

次の定義は、MySQL の公式ドキュメントから引用したものです

• レコード ロック: レコード ロックは、インデックス内のレコードをロックします。

• ギャップ ロック: ギャップ ロックは、インデックス レコードの途中の値をロックするか、最初のインデックス レコードの前の値または最後のインデックス レコードの後の値をロックします。

• Next-Key ロック: Next-Key ロックは、インデックス レコードのレコード ロックとインデックス レコードの前のギャップ ロックの組み合わせです。

定義ではすべてインデックス レコードについて言及しています。なぜ？行ロックとインデックスの関係は何ですか?実際、InnoDB は、テーブル内のインデックスを検索またはスキャンすることによってロック操作を完了します。InnoDB は、検出したすべてのインデックス データに共有ロックまたは排他ロックを追加します。したがって、行レベルのロックは行に対応するインデックスに追加されるため、行レベルのロック (行レベル ロック) をインデックス レコード ロック (インデックス レコード ロック) と呼ぶことができます。

3種類のロックのロック範囲は異なり、徐々に拡大されます。例を挙げて、さまざまなロックのロック範囲を簡単に説明します。テーブル t のインデックス列に 3、5、8、9 の 4 つの数値があるとします。公式ドキュメントによると、ロックのロック範囲は次のようになります。 3 つのロックは次のように決定されます:

• レコード ロックのロック範囲は、別個のインデックス レコードであり、データ 3、5、8、9 の 4 行です。

• ギャップ ロックは、(-∞,3)、(3,5)、(5,8)、(8,9)、(9,+) のセットで表される行のギャップをロックします。 ∞）。

• ネクストキーロックは、インデックスレコードロックとその前のギャップロックを組み合わせたもので、(-∞,3],(3,5],()の集合の形で表現されます。 5,8] , (8,9], (9,+∞).

最後に、ギャップ ロックについて 3 つの点を追加する必要があります:

1. ギャップ ロックは、他のトランザクションが同時にギャップ データを挿入することを防ぎます。このため、すべてのトランザクション分離レベルがギャップ ロックを使用するわけではありません。MySQL InnoDB エンジンは、ギャップ ロックの役割をRepeatable Read (デフォルト) でのみ使用します。これは、他のトランザクションがギャップにデータを挿入することを防ぐためにのみ使用されます。これは、挿入ステートメントを除き、他の SQL ステートメントが同じ行にギャップ ロックを追加できることを意味します。ブロックされました

2. 一意のインデックスのロック動作については、現時点ではレコードロックのみが機能します
   2. すでに紹介しました。 InnoDB のロック動作は、SQL ステートメントの実行中にインデックス レコードをスキャンすることで実現されます。 次に、どのステートメントがロックされるかを 1 つずつ説明します。

3. ステートメント: InnoDB エンジンはマルチバージョン同時実行制御 (MVCC) はノンブロッキング読み取りを実装するため、通常の選択読み取りステートメントの場合、InnoDB はロックしません [注 1]

select ... からロックイン。共有モードステートメント: これの違い通常の select ステートメントでは、最後に共有モードのロックが追加されています。文字通りの意味から、これはロックされた読み取りステートメントであり、ロックの種類は共有ロック (読み取りロック) であると推測できます。ロックは検索されるすべてのインデックス レコードに追加されますが、一意のインデックスの唯一の行がスキャンされる場合、next-key はインデックス レコード ロックにダウングレードされます

select ... from for update ステートメント: 上記と同じ。ステートメントを実行すると、このステートメントは排他ロック (書き込みロック) を追加します。 InnoDB は検索されるすべてのインデックス レコードにネクスト キー ロックを追加しますが、一意のインデックスの一意の行がスキャンされると、ネクスト キーはインデックス レコード ロックにダウングレードされます。 update ... where ... ステートメント: InnoDB は、検索されるすべてのインデックス レコードにネクスト キー ロックを追加しますが、一意のインデックスの唯一の行がスキャンされる場合、ネクスト キーはインデックス レコード ロックにダウングレードされます。 ]

• delete ... where ... ステートメント: InnoDB は、検索されたすべてのインデックス レコードにネクスト キー ロックを追加しますが、一意のインデックスの一意の行がスキャンされる場合、ネクスト キーはインデックス レコード ロックにダウングレードされます。 。

• insert ステートメント: InnoDB は、挿入される行に排他的なインデックス レコード ロックのみを設定します。

• 最後の 2 つのポイント:

• クエリが補助インデックスを使用し、インデックス レコードに排他ロックを追加する場合、InnoDB は対応する集計インデックス レコードをロックします。

• SQL ステートメントがインデックスを使用できない場合、MySQL はステートメントを処理するためにテーブル全体をスキャンする必要があり、その結果、テーブルの各行がロックされ、他のユーザーによるテーブルへの挿入はすべてブロックされます。

• SQL ステートメントの検証

早速、この記事の SQL ステートメントの検証部分に入りましょう。

1。

CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `name` varchar(8) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

まず、SQL ステートメントのテンプレートを実行します:

Steps

client 1

client 2

1begin;-- 2選択 * FROM user where name='e' for update;--3--begin;4--INSERT INTO `user` (`id`, ` name` ) VALUES (, #{name});5ロールバック;--ロールバック;Reステップ内の地名値の 5、観察結果:

10
		6--
名前の値	実行結果

a

ブロックしていませんbdefhi 結果を観察すると、SQL ステートメント SELECT * FROM user where name='e' for update が合計 3 つをロックしていることがわかりました。インデックス名のレコードの行、(c, e] 間隔は次のキー ロックである必要があり、(e, h) 間隔はインデックス レコード e の後のギャップです。SELECT * FROM user where name='e' for update;

ブロック禁止
ブロック
ブロック
ブロック
ブロック禁止
ブロックなし
次に、次の-のどの部分かを決定します。 key ロックはインデックス レコード ロックであり、その部分がギャップ ロックです。 --	2

--

SELECT * FROM user where name='e' for update
一共锁住索引name中三行记录，(c,e]区间应该是next-key锁而(e,h)区间是索引记录e后面的间隙。

接下来我们确定next-key锁中哪部分是索引记录锁哪部分是间隙锁。

执行SQL语句的模板：

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name='e' for update;	--
3	--	SELECT * FROM user where name=#{name} for update;
5	rollback;	--
6	--	rollback;

替换步骤5中name的值，观察结果：

name的值	执行结果
d	不阻塞
e	阻塞
f	不阻塞

因为间隙锁只会阻止insert语句，所以同样的索引数据，insert语句阻塞而select for update语句不阻塞的就是间隙锁，如果两条语句都阻塞就是索引记录锁。

观察执行结果可知，d和f为间隙锁，e为索引记录锁。

结论：通过两条SQL，我们确定了对于辅助索引name在查询条件为 where name='e'  

3

• --
  SELECT * FROM user where name=#{name} 更新用;

• 5
• rollback;
  --

• 6

--

rollback;

ステップ 5 の name の値を置き換えて、結果を観察します。 dブロックしないeブロックするfブロックしていませんギャップのため、ロックは挿入ステートメントのみをブロックするため、同じインデックスデータ、insert ステートメントはブロックされるが、select for update ステートメントはブロックされない場合両方のステートメントがブロックされている場合、それはギャップ ロックです。それはインデックス レコード ロックです。 実行結果を観察すると、dとfはギャップロック、eはインデックスレコードロックであることがわかります。 結論: 2 つの SQL を通じて、クエリ条件が where name='e' である場合の補助インデックス名のロック範囲は (c, e], (e, g) , SQL ステートメントによってスキャンされたインデックス レコード e にインデックス レコード ロック [e] を追加します。 e の前のギャップをロックし、c 間のデータ (c, e) にギャップ ロックを追加します。と e. 最初の 2 つは次のキー ロック (c,e] を構成します。

	nameの値	実行結果

🎜🎜🎜 e の後ろのギャップ (e,g) もロックされていることは注目に値します。ここの読者は、テスト データにギャップ境界データ c と g がないことが判明した可能性があると述べています。 次に、ギャップ境界値をテストします: 🎜🎜🎜🎜🎜ステップ 🎜🎜クライアント 1🎜🎜クライアント 2。 🎜🎜🎜🎜1🎜🎜begin;🎜🎜--🎜🎜🎜🎜2🎜🎜SELECT * FROM user where name='e' for update;🎜🎜--🎜🎜🎜🎜3🎜🎜-始める; 🎜🎜🎜🎜4🎜🎜--🎜🎜INSERT INTO `user` (`id`, `name`) VALUES (#{id}, #{name});🎜🎜🎜🎜5🎜🎜ロールバック;🎜 🎜- -🎜🎜🎜🎜6🎜🎜--🎜🎜ロールバック;🎜🎜🎜🎜

ステップ 5 の id と name の値を置き換えて、結果を確認します:

------g----gc-1c 1c2ブロックブロックブロック6ブロックしています7ブロックしていません7ブロックしていません8ブロック中8ブロックしていません9ブロックしていません9 c -c--

select * from user where id = x for update; ステートメントを使用して、上記のギャップに追加されたロックがすべてギャップ ロックであることを確認できます。 client 1client 2 1--SELECT * FROM user where name='e' for update;--3-

id の値	name=c				-3
グループプラグ			--		-2
ブロック			-1		ブロックしていません
g	ブロックしています		1		ブロックしていません
g	ブロック不可		2		ブロックしていません
g	ブロックしています		3 ブロックしていません ブロックしています	4	g
	5	c	ブロック	5	g
	6	c			g
		c			g
		c			g
		c		g	ブロック禁止
10	c	ブロックしています	10	g	ブロックしていません
11	ブロック中	-	-		12
blocking		-	上記の実行結果を観察すると、nameがcとeに等しい場合、insertが行われることがわかりました。 code> ステートメントの結果は、id 値に応じて、しばらくロックされたり、しばらくロックされなかったりします。このような結果が発生するには、ID 列がロックされている必要があります。	データ行 id=5 の結果を見なければ、次のパターンが見つかります:	When name= c、name=c に対応する <code>id=3 の ID 集約インデックス データ レコードの後のギャップ (3,5)、(5,7) 、(7,9)、(9,∞)はすべてロックされています。 name=e の場合、name=e に対応する id=7 の ID 集約インデックス データ レコード前のギャップ (5,7)、(3,5)、(1,3)、および (-∞,1) はすべてロックされています。
次に、id=5 のロック状況を説明します SQL ステートメントを実行するためのテンプレート:		Steps
begin;		2

SELECT * FROM user where id=#{id} for update;insert语句的结果随着id值得不同一会儿锁定，一会儿不锁定。那一定是id列加了锁才会造成这样的结果。

如果先不看id=5这一行数据的结果，我们发现一个规律：

• 当name=c时，name=c对应的id=3的id聚合索引数据记录之后的间隙(3,5)，(5,7)，(7,9)，(9,∞)都被加上了锁。

• 当name=e时，name=e对应的id=7的id聚合索引数据记录之前的间隙(5,7)，(3,5)，(1,3)，(-∞,1)都被加上了锁。

• 我们可用select * from user where id = x for update;语句判断出以上间隙上加的锁都为间隙锁。

接下来我们解释一下id=5

5rollback;--rollback;手順のIDを置き換えます5 値、観測結果: 3ブロックしていません4ブロック禁止

通过观察执行结果可知，id=5的聚合索引记录上添加了索引记录锁。根据MySQL官方文档描述，InnoDB引擎在对辅助索引加锁的时候，也会对辅助索引所在行所对应的聚合索引（主键）加锁。而主键是唯一索引，在对唯一索引加锁时，间隙锁失效，只使用索引记录锁。所以SELECT * FROM user where name='e' for update;不仅对辅助索引name=e列加上了next-key锁，还对对应的聚合索引id=5列加上了索引记录锁。

最终结论：  
对于SELECT * FROM user where name='e' for update;一共有三种锁定行为:

1. 对SQL语句扫描过的辅助索引记录行加上next-key锁（注意也锁住记录行之后的间隙）。

2. 对辅助索引对应的聚合索引加上索引记录锁。

3. 当辅助索引为间隙锁“最小”和“最大”值时，对聚合索引相应的行加间隙锁。“最小”锁定对应聚合索引之后的行间隙。“最大”值锁定对应聚合索引之前的行间隙。

上面我们将对辅助索引加锁的情况介绍完了，接下来我们测试一下对聚合索引和唯一索引加锁。

2.2 场景二

建表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意与场景一表user不同的是name列为唯一索引。

插入数据：

INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

首先我们执行SQL语句的模板：

--		6
idの値	実行結果
		5

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name='e' for update;
3	--	begin;
4	--	INSERT INTO `user` (`id`, `name`) VALUES (10, #{name});
5	rollback;	--
6	--	rollback;

替换步骤5中name的值，观察结果：

name的值	执行结果
a	不阻塞
b	不阻塞
c	不阻塞
d	不阻塞
e	阻塞
f	不阻塞
g	不阻塞
h	不阻塞
i	不阻塞

由测试结果可知，只有name='e'这行数据被锁定。

通过SQL语句我们验证了，对于唯一索引列加锁，间隙锁失效，

2.3 场景三

场景一和场景二都是在查询条件等于的情况下做出的范围判断，现在我们尝试一下其他查询条件，看看结论是否一致。

借用场景一的表和数据。

建表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

执行SQL语句的模板：

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name>'e' for update;	--
3	--	begin;
4	--	INSERT INTO `user` (`id`, `name`) VALUES ('10', #{name});
5	rollback;	--
6	--	rollback;

替换步骤5中name的值，观察结果：

name的值	执行结果
a	阻塞
b	阻塞
c	阻塞
d	阻塞
e	阻塞
f	阻塞
g	阻塞
h	阻塞
i	阻塞

这个结果是不是和你想象的不太一样，这个结果表明where name>'e'这个查询条件并不是锁住'e'列之后的数据，而锁住了所有name列中所有数据和间隙。这是为什么呢？

我们执行以下的SQL语句执行计划：

 explain select * from user where name>'e' for update;

执行结果：

+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | index_name    | index_name | 26      | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

如果你的结果与上面不同先执行一下OPTIMIZE TABLE user;再执行以上语句。

通过观察SQL语句的执行计划我们发现，语句使用了name列索引，且rows参数等于5，user表中一共也只有5行数据。SQL语句的执行过程中一共扫描了name索引记录5行数据且对这5行数据都加上了next-key锁，符合我们上面的执行结果。

接下来我们再制造一组数据。  
建表：

CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `name` varchar(8) NOT NULL,
 `age` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');

这张表和前表的区别是多了一列非索引列age。

我们再执行一下同样的SQL语句执行计划：

 explain select * from user where name>'e' for update;

执行结果：

+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | index_name    | index_name | 26      | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

是不是和第一次执行结果不同了，rows参数等于2，说明扫描了两行记录，结合SQL语句select * from user where name>'e' for update;执行后返回结果我们判断这两行记录应该为g和i。

因为select * from user where name>'e' for update;语句扫描了两行索引记录分别是g和i，所以我们将g和i的锁定范围叠就可以得到where name>'e'的锁定范围：

1. 索引记录g在name列锁定范围为(e,g],(g,i)。索引记录i的在name列锁定范围为(g,i],(i,+∞)。两者叠加后锁定范围为(e,g],(g,i],(i,+∞)。其中g,i为索引记录锁。

2. g和i对应id列中的7和9加索引记录锁。

3. 当name列的值为锁定范围上边界e时，还会在e所对应的id列值为5之后的所有值之间加上间隙锁，范围为(5,7),(7,9),(9,+∞)。下边界为+∞无需考虑。

接下来我们逐一测试：

首先测试验证了next-key锁范围，执行SQL语句的模板：

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name>'e' for update;	--
3	--	begin;
4	--	INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18');
5	rollback;	--
6	--	rollback;

替换步骤5中name的值，观察结果：

name的值	执行结果
a	不阻塞
b	不阻塞
c	不阻塞
d	不阻塞
f	阻塞
g	阻塞
h	阻塞
i	阻塞
j	阻塞
k	阻塞

下面验证next-key锁中哪部分是间隙锁，哪部分是索引记录锁，执行SQL语句的模板：

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name>'e' for update;	--
3	--	SELECT * FROM user where name=#{name} for update;
5	rollback;	--
6	--	rollback;

替换步骤5中name的值，观察结果：

name的值	执行结果
e	不阻塞
f	不阻塞
g	阻塞
h	不阻塞
i	阻塞
j	不阻塞

接下来验证对id列加索引记录锁，执行SQL语句的模板：

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name>'e' for update;	--
3	--	SELECT * FROM user where id=#{id} for update;
5	rollback;	--
6	--	rollback;

替换步骤5中id的值，观察结果：

id的值	执行结果
5	不阻塞
6	不阻塞
7	阻塞
8	不阻塞
9	阻塞
10	不阻塞

最后我们验证name列的值为边界数据e时，id列间隙锁的范围，执行SQL语句的模板：

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name>'e' for update;	--
3	--	begin;
4	--	INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, 'e','18');
5	rollback;	--
6	--	rollback;

替换步骤5中id的值，观察结果：

id的值	执行结果
-1	不阻塞
1	不阻塞
2	不阻塞
3	不阻塞
4	不阻塞
5	不阻塞
6	阻塞
7	阻塞
8	阻塞
9	阻塞
10	阻塞
11	阻塞
12	阻塞

注意7和9是索引记录锁记录锁。

观察上面的所有SQL语句执行结果，可以验证select * from user where name>'e' for update的锁定范围为此语句扫描name列索引记录g和i的锁定范围的叠加组合。

2.4 场景四

我们通过场景三验证了普通索引的范围查询语句加锁范围，现在我们来验证一下唯一索引的范围查询情况下的加锁范围。有了场景三的铺垫我们直接跳过扫描全部索引的情况，创建可以扫描范围记录的表结构并插入相应数据测试。

建表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');

和场景三表唯一不同是name列为唯一索引。

SQL语句select * from user where name>'e'扫描name列两条索引记录g和i。如果需要只对g和i这两条记录加上记录锁无法避免幻读的发生，索引锁定范围应该还是两条数据next-key锁锁的组合：(e,g],(g,i],(i,+∞)。其中g,i为索引记录锁。

我们通过SQL验证我们的结论，执行SQL语句的模板：

步骤	client 1	client 2
1	begin;	--
2	SELECT * FROM user where name>'e' for update;	--
3	--	begin;
4	--	INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18');
5	rollback;	--
6	--	rollback;

ステップ 5 の name の値を置き換えて、結果を確認します:

name の値	実行結果
a	ブロックなし
b	ブロックしていません
c	ブロックしていません
d	ブロックしていません
f	ブロック
g	ブロック
h	ブロック
i	ブロック
j	ブロック
k	ブロッキング

次のキー ロックのどの部分がギャップ ロックで、どの部分がインデックス レコード ロックであるかを確認してみましょう。 SQL ステートメントを実行するためのテンプレート:

。 56 ステップ 5 の name の値を置き換えて、結果を観察します。ネームバリュー実行結果

ステップ	クライアント 1	クライアント 2
1	begin;	--
2	SELECT * FROM user where name>'e' 更新の場合; name=#{name} 更新の場合;
rollback;	--
--	rollback;

e

ブロックなしf次に、補助インデックスをロックした後に集約インデックスへのロック転送を確認し、SQL ステートメント テンプレートを実行しましょう: Stepsclient 2

ブロックなし	g
ブロック	h
ブロックしていません	i
ブロックしています	j
ブロックしていません	上記2つのSQL文の検証結果により、証明されました。 g と i のロック レンジ トレンドは、2 つの次のキーの重ね合わせの組み合わせであることがわかります。
	client 1

1

begin ;--2SELECT * FROM user where name>'e' for update;--3--SELECT * FROM user where id=#{id } 更新用;5rollback;--6--rollback;ID値5

	ステップ 5 の id の値を置き換え、結果を観察します。
実行結果

ブロックしていませんブロックしていませんブロック8 この結果から、補助インデックス インデックス レコード ロックが、 name の g 列と i 列。 Stepsclient 21--2 SELECT * FROM user where name> ;'e' 更新用;--begin;

	6
	7
これまでのところ、すべての実験結果はシナリオ 3 とまったく同じであり、結局のところ、シナリオ 4 とシナリオ 3 は補助インデックス name のインデックス タイプが異なるだけです。 1 つは一意のインデックス、もう 1 つは通常のインデックスです。 最後に意図を確認し、ネクストキーで境界データ e をロックし、結論がシナリオ 3 と同じであることを確認します。	SQL ステートメントを実行するためのテンプレート:
client 1
begin;
--	3

name中的g和i列对应的聚合索引id列中的7和9加上了索引记录锁。

到目前为止所有实验结果和场景三完全一样，这也很好理解，毕竟场景四和场景三只是辅助索引name

4

--

INSERT INTO `user` (`id`, `name`,` age`) VALUES (#{id}, 'e','18');6

ステップ 5 の id の値を置き換えて、結果を観察します:

5	ロールバック;	--
--	ロールバック;

id の値	実行結果
-1	ブロックなし
1	ブロックしていません
2	ブロックしていません
3	ブロックしていません
4	ブロック禁止
5	ブロックしない
6	ブロックしない
7	ブロックする
8	ブロック禁止
9	ブロックしています
10	ブロックしていません
11	ブロックしていません
12	ブロック禁止

7 と 9 はインデックス レコード ロック レコード ロック であることに注意してください。

結果から、name 列がインデックス レコードの上限 e である場合、シナリオ 3 とは異なり、ID に対するロック動作がないことがわかります。

一意のインデックスの範囲クエリは、通常のインデックスの範囲クエリと似ていますが、唯一の違いは、補助インデックスが上限範囲と下位範囲の境界値に等しい場合、ギャップ ロックが主キーに追加されないことです。

一意のインデックス範囲クエリのロック範囲:

• スキャンされた補助インデックス レコードのロック範囲は、複数のインデックス レコードの次のキー範囲を重ね合わせた組み合わせです。

• 集約インデックス(主キー)の排他範囲について、複数の補助インデックスに対応する集約インデックス列にインデックスレコード排他が追加されます。

結論

InnoDB エンジンは、スキャンしたインデックス レコードに対応するロックを追加します。「シナリオ 1」を通じて、通常のインデックス レコードのスキャンのロック範囲を明確にしました。通常のインデックス インデックス レコードのロック範囲の数は任意です。 「シナリオ 2」を通じて、一意のインデックス レコード (または主キー) をスキャンするためのロック範囲を決定しました。 「シナリオ 4」を通じて、任意の数のスキャン インデックス レコード (または主キー) のロック範囲を推測できます。実際のアプリケーションで柔軟に使用して、2 つの SQL ステートメントが相互にロックされているかどうかを判断できます。ここで、インデックスのクエリ条件は、多くの場合、私たちが理解しているものではないことにも注意してください。インデックスによって最終的にスキャンされるレコードの数は、実行計画に基づいて判断する必要があります。ロッキング範囲の理解にずれが生じる原因となります。

---

---

---

備考

注 1: トランザクション分離レベルが SERIALIZABLE の場合、通常の select ステートメントは、ステートメントの実行中にスキャンされたインデックスにネクスト キー ロックも追加します。ステートメントが一意のインデックスをスキャンする場合、ネクスト キー ロックはインデックス レコード ロックにダウングレードされます。
注 2: update ステートメントが集約インデックス (主キー) レコードを変更すると、影響を受ける補助インデックスに対して暗黙的なロック操作が実行されます。新しいセカンダリ インデックス レコードが挿入される前に重複チェック スキャンが実行され、新しいセカンダリ インデックス レコードが挿入されると、更新操作により、影響を受けるセカンダリ インデックス レコードに共有ロックも追加されます。

関連する推奨事項: SQL ファイル実行時の mysql エラー エラー: 不明なストレージ エンジン「InnoDB」の解決方法

MySQL の起動時に InnoDB エンジンが無効になっている場合はどうすればよいですか?

MySQL ストレージ エンジンMyISAM と InnoDB の比較

以上がMySQLデータベースInnoDBエンジンの行レベルロックのロック範囲の詳細説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。