MySQLデータベースInnoDBエンジンの行レベルロックのロック範囲の詳細説明
- 小云云オリジナル
- 2018-02-23 13:49:111867ブラウズ
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
| -- | 2 | |
|---|---|---|
| 3 | -- | |
| 4 | -- | |
| 10 | , #{name});5 | |
| -- | 6-- | |
| Reステップ内の地名値の 5、観察結果: | ||
| 名前の値 | 実行結果 |
a
| ブロック禁止 | d |
| ブロック | e |
| ブロック | f |
| ブロック | h |
| ブロック禁止 | i |
| ブロックなし | 結果を観察すると、SQL ステートメント |
| 次に、次の-のどの部分かを決定します。 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'
- --SELECT * FROM user where name=#{name} 更新用;
- 5
- rollback;--
- 6
| nameの値 | 実行結果 | |
|---|---|---|
| d | ブロックしない | |
| e | ||
| f | ||
| ブロックしていません | ||
| 実行結果を観察すると、dとfはギャップロック、eはインデックスレコードロックであることがわかります。 | 結論: 2 つの SQL を通じて、クエリ条件が | |
| e の前のギャップをロックし、c 間のデータ (c, e) にギャップ ロックを追加します。と e. |
ステップ 5 の id と name の値を置き換えて、結果を確認します:
| id の値 | name=c | ---- | -- | -3 | |
|---|---|---|---|---|---|
| グループプラグ | -- | -- | ---2 | g||
| ブロック | -1 | cブロックしていません | |||
| g | ブロックしています | 1 | cブロックしていません | ||
| g | ブロック不可 | 2 | cブロックしていません | ||
| g | ブロックしています | 3 ブロックしていません ブロックしています | 4 | g | |
| 5 | c | ブロック | 5 | g | |
| 6 | c | ブロック6 | g | ブロックしています||
| 7 | c | ブロックしていません7 | g | ブロックしていません||
| 8 | c | ブロック中8 | g | ブロックしていません||
| 9 | c | ブロックしていません9 | g | ブロック禁止 | |
| 10 | c | ブロックしています | 10 | g | ブロックしていません |
| 11 | cブロック中 | - | -- | 12 | c|
| 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 | client 1client 2 | |||
| begin; | --2 | SELECT * FROM user where name='e' for update;-- | 3 |
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
| -- | 6 | |
|---|---|---|
| 手順のIDを置き換えます5 値、観測結果: | ||
| idの値 | 実行結果 | |
| 3 | ブロックしていません | |
| 4 | ||
| 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'的锁定范围：
索引记录g在
name列锁定范围为(e,g],(g,i)。索引记录i的在name列锁定范围为(g,i],(i,+∞)。两者叠加后锁定范围为(e,g],(g,i],(i,+∞)。其中g,i为索引记录锁。g和i对应
id列中的7和9加索引记录锁。当
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 ステートメントを実行するためのテンプレート:
| ステップ | クライアント 1 | 。クライアント 2 |
|---|---|---|
| 1 | begin; | -- |
| 2 | SELECT * FROM user where name>'e' 更新の場合; name=#{name} 更新の場合; | |
| rollback; | -- | |
| -- | rollback; | |
| 実行結果 |
| ブロックなし | g |
| ブロック | h |
| ブロックしていません | i |
| ブロックしています | j |
| ブロックしていません | 上記2つのSQL文の検証結果により、証明されました。 g と i のロック レンジ トレンドは、2 つの次のキーの重ね合わせの組み合わせであることがわかります。 |
| Steps | client 1 | client 2
1
| 2 | SELECT * FROM user where name>'e' for update; | |
|---|---|---|
| 3 | -- | SELECT * FROM user where id=#{id } 更新用; |
| 5 | rollback; | -- |
| 6 | -- | rollback; |
| ステップ 5 の id の値を置き換え、結果を観察します。 | ||
| 実行結果 |
| 6 | |
|---|---|
| 7 | |
| 8 | |
これまでのところ、すべての実験結果はシナリオ 3 とまったく同じであり、結局のところ、シナリオ 4 とシナリオ 3 は補助インデックス name のインデックス タイプが異なるだけです。 1 つは一意のインデックス、もう 1 つは通常のインデックスです。 最後に意図を確認し、ネクストキーで境界データ e をロックし、結論がシナリオ 3 と同じであることを確認します。 |
SQL ステートメントを実行するためのテンプレート: |
| client 1 | client 2|
| begin; | --2 |
| -- | 3 | --
name中的g和i列对应的聚合索引id列中的7和9加上了索引记录锁。
到目前为止所有实验结果和场景三完全一样，这也很好理解，毕竟场景四和场景三只是辅助索引name
| 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データベースInnoDBエンジンの行レベルロックのロック範囲の詳細説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。