2 つの INSERT ステートメントによって引き起こされるデッドロックについて話しましょう

この記事では、mysql のデッドロックに関する関連問題を紹介します。主に、2 つの同一の INSERT ステートメントによって引き起こされるデッドロックに関する関連知識を紹介します。皆様のお役に立てれば幸いです。

2 つの同一の INSERT ステートメントが実際にデッドロックを引き起こしました。これは人間性の歪みでしょうか、それとも道徳の喪失でしょうか? 私たちはため息をつかずにはいられません。これが行き詰まりにつながることもあり、その後、目に涙を浮かべながらなすすべもなく業務規定が変更されることになる。

さて、2 つの同一の INSERT ステートメントがデッドロックを引き起こす理由を深く分析する前に、いくつかの基本的な知識を紹介しましょう。

環境を準備する

ストーリーをスムーズに進めるために、何度も使用されている新しいヒーロー テーブルを作成します。

CREATE TABLE hero (
    number INT AUTO_INCREMENT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number),
    UNIQUE KEY uk_name (name)
) Engine=InnoDB CHARSET=utf8;

次に、これにいくつかのレコードを挿入します。 table:

INSERT INTO hero VALUES
    (1, 'l刘备', '蜀'),
    (3, 'z诸葛亮', '蜀'),
    (8, 'c曹操', '魏'),
    (15, 'x荀彧', '魏'),
    (20, 's孙权', '吴');

これで、ヒーロー テーブルには 2 つのインデックス (一意のセカンダリ インデックスとクラスター化インデックス) が追加されました。図は次のとおりです。 INSERT ステートメント? ロック

「MySQL の実行方法: MySQL を基礎から理解する」を読んだことがある方は、INSERT ステートメントが通常の実行中にロック構造を生成せず、クラスター化インデックスに依存していることを知っているはずです。レコードに付属する trx_id 非表示列は、レコードを保護するための暗黙的なロックとして使用されます。

ただし、一部の特殊なシナリオでは、INSERT ステートメントでもロック構造が生成されます。列挙してみましょう:

1. 挿入される次のレコードはすでに Whenギャップ ロックは他のトランザクションに追加されます

新しいレコードが挿入されるたびに、次に挿入されるレコードがギャップ ロックに追加されているかどうかを確認する必要があります。が追加された場合、INSERT ステートメントはブロックされ、挿入意図のロックが生成される必要があります。 たとえば、ヒーロー テーブルの場合、トランザクション T1 は REPEATABLE READ (将来 RR と呼ばれ、READ COMMITTED も将来 RC と呼ばれる) 分離レベルで実行され、次のコードを実行します。ステートメント:

# 事务T1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM hero WHERE number < 8 FOR UPDATE;
+--------+------------+---------+
| number | name       | country |
+--------+------------+---------+
|      1 | l刘备      | 蜀      |
|      3 | z诸葛亮    | 蜀      |
+--------+------------+---------+
2 rows in set (0.02 sec)

このステートメントは、主キー値 1、3、および 8 を持つ 3 つのレコードに X タイプのネクストキー ロックを追加します。信じられない場合は、SHOW を使用できます。 ENGINE INNODB STATUS ステートメントを使用して、ロックの状況を確認します。図内の矢印は、レコードが数値 8 のレコードです:

ヒント:

SELECT、DELETE、UPDATE ステートメントをロックする方法については、以前の記事で分析されているため、ここでは繰り返しません。

現時点では、トランザクション T2 は主キー値が 4 のクラスター化インデックス レコードを挿入しようとしています。T2 はレコードを挿入する前に、まず主キー値が 4 のクラスター化インデックス レコードを見つける必要があります。ページ上の 4 の位置では、主キー値 4 の次のレコードの主キー値が 8 であり、主キー値 8 のクラスター化インデックス レコードがギャップ ロックで追加されていることがわかります (次-キー ロックには正式なレコード ロックとギャップ ロックが含まれます)、トランザクション 1 はブロッキング状態に入り、挿入意図ロック タイプのロック構造を生成する必要があります。

トランザクション T2 で INSERT ステートメントを実行して確認してみましょう:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO hero VALUES(4, 'g关羽', '蜀');

この時点で、T2 はブロッキング状態になり、SHOW ENGINE INNODB STATUS を使用してロックの状況を確認します:

T2 が、主キー値 8 を持つクラスター化インデックス レコードに挿入意図ロックを追加していることがわかります (つまり、lock_mode X は、 が指す rec 挿入意図の前のギャップをロックします)矢印）になり、待機状態になります。

わかりました。検証後、コードでどのように実装されているかを見てみましょう:

## lock_rec_insert_check_and_lock 関数は、他のトランザクションがブロックされているかどうかを確認するために使用されます。この INSERT の場合、「はい」の場合、このトランザクションは、別のトランザクションによってギャップ ロックを使用して追加されたレコードの挿入意図ロックを生成します。具体的なプロセスは次のとおりです:

#ヒント:

lock_rec_other_has_conflicting 関数は、今回取得するロックがレコード上の既存のロックと競合するかどうかを検出するために使用されます。興味のある学生は参照してください。

2. 重複キーが見つかった場合

新しいレコードを挿入するときに、ページ上の既存のレコードの主キーがが見つかった、または一意の副次インデックス列が、挿入されるレコードの主キーまたは一意の副次インデックス列と同じ値を持つ (ただし、複数のレコードの一意の副次インデックス列の値が同時に NULL になる可能性があり、この状況はここでは考慮されません)、このとき、新しいレコードを挿入するトランザクションは、ページ内に既に存在する同じキー値を持つレコードのロックを取得します。

主キー値が重複している場合:

分離レベルが RC 以下の場合、新しいレコードを挿入するトランザクションは既存の主キーを複製します。キー値: S タイプの正式なレコード ロックをクラスター化インデックス レコードに追加します。

分離レベルが RR 以上の場合、新しいレコードを挿入するトランザクションは、重複する主キーを持つ既存のクラスター化インデックス レコードに S タイプのネクスト キー ロックを追加します。価値。

如果是唯一二级索引列重复，那不论是哪个隔离级别，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加S型next-key锁，再强调一遍，加的是next-key锁！加的是next-key锁！加的是next-key锁！这是rc隔离级别中为数不多的给记录添加gap锁的场景。

小贴士：

本来设计InnoDB的大叔并不想在RC隔离级别引入gap锁，但是由于某些原因，如果不添加gap锁的话，会让唯一二级索引中出现多条唯一二级索引列值相同的记录，这就违背了UNIQUE约束。所以后来设计InnoDB的大叔就很不情愿的在RC隔离级别也引入了gap锁。

我们也来做一个实验，现在假设上边的T1和T2都回滚了，现在将隔离级别调至RC，重新开启事务进行测试。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.01 sec)
# 事务T1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO hero VALUES(30, 'x荀彧', '魏');
ERROR 1062 (23000): Duplicate entry 'x荀彧' for key 'uk_name'

然后执行SHOW ENGINE INNODB STATUS语句看一下T1加了什么锁：

可以看到即使现在T1的隔离级别为RC，T1仍然给name列值为'x荀彧'的二级索引记录添加了S型next-key锁（图中红框中的lock mode S）。

如果我们的INSERT语句还带有ON DUPLICATE KEY... 这样的子句，如果遇到主键值或者唯一二级索引列值重复的情况，会对B+树中已存在的相同键值的记录加X型锁，而不是S型锁（不过具体锁的具体类型是和前面描述一样的）。

好了，又到了看代码求证时间了，我们看一下吧：

row_ins_scan_sec_index_for_duplicate是检测唯一二级索引列值是否重复的函数，具体加锁的代码如下所示：

如上图所示，在遇到唯一二级索引列重复的情况时：

• 1号红框表示对带有ON DUPLICATE ...子句时的处理方案，具体就是添加X型锁。

• 2号红框表示对正常INSERT语句的处理方案，具体就是添加S型锁。

不过不论是那种情况，添加的lock_typed的值都是LOCK_ORDINARY，表示next-key锁。

在主键重复时INSERT语句的加锁代码我们就不列举了。

3. 外键检查时

当我们向子表中插入记录时，我们分两种情况讨论：

• 当子表中的外键值可以在父表中找到时，那么无论当前事务是什么隔离级别，只需要给父表中对应的记录添加一个S型正经记录锁就好了。

• 当子表中的外键值在父表中找不到时：那么如果当前隔离级别不大于RC时，不对父表记录加锁；当隔离级别不小于RR时，对父表中该外键值所在位置的下一条记录添加gap锁。

死锁要出场了

好了，基础知识预习完了，该死锁出场了。

看下边这个平平无奇的INSERT语句：

INSERT INTO hero(name, country) VALUES('g关羽', '蜀'), ('d邓艾', '魏');

这个语句用来插入两条记录，不论是在RC，还是RR隔离级别，如果两个事务并发执行它们是有一定几率触发死锁的。为了稳定复现这个死锁，我们把上边一条语句拆分成两条语句：

INSERT INTO hero(name, country) VALUES('g关羽', '蜀');
INSERT INTO hero(name, country) VALUES('d邓艾', '魏');

拆分前和拆分后起到的作用是相同的，只不过拆分后我们可以人为的控制插入记录的时机。如果T1和T2的执行顺序是这样的：

也就是：

• T1先插入name值为g关羽的记录，可以插入成功，此时对应的唯一二级索引记录被隐式锁保护，我们执行SHOW ENGINE INNODB STATUS语句，发现啥一个行锁（row lock）都没有（因为SHOW ENGINE INNODB STATUS不显示隐式锁）：

• 接着T2也插入name值为g关羽的记录。由于T1已经插入name值为g关羽的记录，所以T2在插入二级索引记录时会遇到重复的唯一二级索引列值，此时T2想获取一个S型next-key锁，但是T1并未提交，T1插入的name值为g关羽的记录上的隐式锁相当于一个X型正经记录锁（RC隔离级别），所以T2向获取S型next-key锁时会遇到锁冲突，T2进入阻塞状态，并且将T1的隐式锁转换为显式锁（就是帮助T1生成一个正经记录锁的锁结构）。这时我们再执行SHOW ENGINE INNODB STATUS语句：

可见，T1持有的name值为g关羽的隐式锁已经被转换为显式锁（X型正经记录锁，lock_mode X locks rec but not gap）；T2正在等待获取一个S型next-key锁（lock mode S waiting）。

• 接着T1再插入一条name值为d邓艾的记录。在插入一条记录时，会在页面中先定位到这条记录的位置。在插入name值为d邓艾的二级索引记录时，发现现在页面中的记录分布情况如下所示：

很显然，name值为'd邓艾'的二级索引记录所在位置的下一条二级索引记录的name值应该是'g关羽'（按照汉语拼音排序）。那么在T1插入name值为d邓艾的二级索引记录时，就需要看一下name值为'g关羽'的二级索引记录上有没有被别的事务加gap锁。

有同学想说：目前只有T2想在name值为'g关羽'的二级索引记录上添加S型next-key锁（next-key锁包含gap锁），但是T2并没有获取到锁呀，目前正在等待状态。那么T1不是能顺利插入name值为'g关羽'的二级索引记录么？

我们看一下执行结果：

# 事务T2
mysql> INSERT INTO hero(name, country) VALUES('g关羽', '蜀');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

很显然，触发了一个死锁，T2被InnoDB回滚了。

这是为啥呢？T2明明没有获取到name值为'g关羽'的二级索引记录上的S型next-key锁，为啥T1还不能插入入name值为d邓艾的二级索引记录呢？

这我们还得回到代码上来，看一下插入新记录时是如何判断锁是否冲突的：

看一下画红框的注释，意思是：只要别的事务生成了一个显式的gap锁的锁结构，不论那个事务已经获取到了该锁(granted)，还是正在等待获取（waiting），当前事务的INSERT操作都应该被阻塞。

回到我们的例子中来，就是T2已经在name值为'g关羽'的二级索引记录上生成了一个S型next-key锁的锁结构，虽然T2正在阻塞（尚未获取锁），但是T1仍然不能插入name值为d邓艾的二级索引记录。

这样也就解释了死锁产生的原因：

• T1在等待T2释放name值为'g关羽'的二级索引记录上的gap锁。

• T2在等待T1释放name值为'g关羽'的二级索引记录上的X型正经记录锁。

两个事务相互等待对方释放锁，这样死锁也就产生了。

怎么解决这个死锁问题？

两个方案：

• 方案一：一个事务中只插入一条记录。

• 方案二：先插入name值为'd邓艾'的记录，再插入name值为'g关羽'的记录

推荐学习：mysql视频教程

以上が2 つの INSERT ステートメントによって引き起こされるデッドロックについて話しましょうの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。