Lassen Sie uns über den Deadlock sprechen, der durch zwei INSERT-Anweisungen verursacht wird

Dieser Artikel bringt Ihnen verwandte Themen zu Deadlocks in MySQL näher. Er stellt hauptsächlich das relevante Wissen über Deadlocks vor, die durch zwei identische INSERT-Anweisungen verursacht werden.

Zwei identische INSERT-Aussagen führten tatsächlich zu einem Stillstand. Ist das eine Verzerrung der menschlichen Natur oder ein Verlust der Moral? Wir können nicht anders, als zu seufzen: Verdammt! Dies kann auch zu einem Stillstand führen und dann wird der Geschäftskodex hilflos und mit Tränen in den Augen geändert.

Okay, bevor wir eingehend analysieren, warum zwei identische INSERT-Anweisungen einen Deadlock verursachen können, wollen wir zunächst einige Grundkenntnisse einführen.

Bereiten Sie die Umgebung vor

Für die reibungslose Entwicklung der Geschichte erstellen wir eine neue Heldentabelle, die unzählige Male verwendet wurde:

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;

Dann fügen Sie ein paar Datensätze in diese Tabelle ein:

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

Jetzt hat die Heldentabelle zwei Indizes (Ein eindeutiger Sekundärindex, ein Clustered-Index). Das schematische Diagramm lautet wie folgt:

So sperren Sie die INSERT-Anweisung

Freunde, die „Wie MySQL läuft: MySQL anhand der Grundlagen verstehen“ gelesen haben, müssen wissen: Das Die INSERT-Anweisung generiert während der normalen Ausführung keine Sperrstruktur. Sie nutzt die versteckte Spalte trx_id, die mit dem Clustered-Index-Datensatz geliefert wird, als implizite Sperre, um den Datensatz zu schützen.

Aber in einigen speziellen Szenarien generiert die INSERT-Anweisung immer noch eine Sperrstruktur. Lassen Sie uns Folgendes auflisten:

1 Wenn der nächste einzufügende Datensatz durch andere Transaktionen zu einer Lückensperre hinzugefügt wurde Jedes Mal, wenn Sie einen neuen Datensatz einfügen, müssen Sie überprüfen, ob dem nächsten einzufügenden Datensatz eine Lückensperre hinzugefügt wurde. Wenn eine Lückensperre hinzugefügt wurde, sollte die INSERT-Anweisung blockiert und eine Einfügeabsichtssperre generiert werden. Zum Beispiel wird für die Hero-Tabelle die Transaktion T1 in der Isolationsstufe REPEATABLE READ (in Zukunft als RR bezeichnet, und READ COMMITTED wird in Zukunft auch als RC bezeichnet) ausgeführt und die folgende Anweisung wird ausgeführt:

# 事务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)

Diese Anweisung wird hinzugefügt. Die drei Datensätze mit den Primärschlüsselwerten 1, 3 und 8 werden alle mit X-Typ-Next-Key-Sperren hinzugefügt. Wenn Sie es nicht glauben, verwenden wir dazu die SHOW ENGINE INNODB STATUS-Anweisung Sehen Sie sich die Sperrsituation an. Der Datensatz, auf den der Pfeil im Bild zeigt, ist der Datensatz mit dem Zahlenwert 8. Datensatz:

Tipps:

Wie Sie SELECT-, DELETE- und UPDATE-Anweisungen sperren, haben wir bereits Wir haben sie in früheren Artikeln analysiert, daher werden wir hier nicht näher darauf eingehen.

Zu diesem Zeitpunkt möchte Transaktion T2 einen Clustered-Index-Datensatz mit einem Primärschlüsselwert von 4 einfügen. Vor dem Einfügen des Datensatzes muss T2 zunächst den Speicherort des Clustered-Index-Datensatzes mit einem Primärschlüsselwert von 4 auf der Seite finden und finden Sie heraus, dass der Primärschlüsselwert des nächsten Datensatzes mit dem Schlüsselwert 4 8 ist und der gruppierte Indexdatensatz mit dem Primärschlüsselwert 8 mit einer Lückensperre hinzugefügt wurde (die Sperre für den nächsten Schlüssel umfasst formale Datensatzsperren und). Lückensperren), dann Transaktion 1 Es ist notwendig, in den Blockierungsstatus einzutreten und eine Sperrstruktur vom Typ Insert Intention Lock zu generieren.

Führen wir die INSERT-Anweisung in Transaktion T2 aus, um Folgendes zu überprüfen:

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

Zu diesem Zeitpunkt tritt T2 in den Blockierungsstatus ein. Verwenden wir SHOW ENGINE INNODB STATUS, um die Sperrsituation anzuzeigen:

Es ist ersichtlich, dass die primäre Der Schlüsselwert von T2 ist 8. Dem Clustered-Index-Datensatz wird eine Einfügungsabsichtssperre hinzugefügt (das heißt, lock_mode

Okay, schauen wir uns nach der Überprüfung an, wie es im Code implementiert ist:

Die Funktion lock_rec_insert_check_and_lock wird verwendet, um zu sehen, ob andere Transaktionen diese INSERT-Einfügung verhindern. Wenn ja, wird diese Transaktion blockiert. Eine weitere Transaktion fügt einen Lückensperrdatensatz hinzu, um eine Einfügungsabsichtssperre zu generieren. Der spezifische Prozess ist wie folgt:

Tipps:

lock_rec_other_has_conflicting Funktion wird verwendet, um zu erkennen, ob die Sperre dieses Mal erworben werden soll und ob die vorhandene Sperre aktiviert ist die Akte Bei Konflikten können interessierte Studierende einen Blick darauf werfen.

2. Wenn Sie auf doppelte Schlüssel stoßen

Wenn beim Einfügen eines neuen Datensatzes festgestellt wird, dass sich der Primärschlüssel oder die eindeutige Sekundärindexspalte des vorhandenen Datensatzes auf der Seite vom Primärschlüssel oder der eindeutigen Sekundärindexspalte unterscheidet Indexspalte des einzufügenden Datensatzes Die Indexspaltenwerte sind gleich (die Werte der eindeutigen sekundären Indexspalten mehrerer Datensätze können jedoch gleichzeitig NULL sein. Diese Situation wird hier nicht berücksichtigt). Diesmal erhält die Transaktion, die den neuen Datensatz einfügt, denselben Schlüsselwert, der bereits in der Datensatzsperre vorhanden ist. Wenn der Primärschlüsselwert dupliziert ist, dann:

Wenn die Isolationsstufe nicht größer als RC ist, fügt die Transaktion, die einen neuen Datensatz einfügt, dem vorhandenen Clustered-Index-Datensatz mit doppeltem Primärschlüssel eine S-förmige formale Datensatzsperre hinzu Schlüsselwert.

• Wenn die Isolationsstufe nicht niedriger als RR ist, fügt eine Transaktion, die einen neuen Datensatz einfügt, dem vorhandenen Clustered-Index-Datensatz eine S-Typ-Next-Key-Sperre mit einem doppelten Primärschlüsselwert hinzu.

如果是唯一二级索引列重复，那不论是哪个隔离级别，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加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视频教程

Das obige ist der detaillierte Inhalt vonLassen Sie uns über den Deadlock sprechen, der durch zwei INSERT-Anweisungen verursacht wird. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!