So lösen Sie das MySQL-Deadlock-Problem (detaillierte Beispiele)

Dieser Artikel vermittelt Ihnen relevantes Wissen über MySQL. Er führt hauptsächlich in die Analyse und Diskussion häufiger Deadlock-Fälle ein und gibt einige Vorschläge, wie Sie Deadlocks so weit wie möglich vermeiden können.

Empfohlenes Lernen: MySQL-Tutorial

1. Was ist Deadlock? Deadlock ist ein häufiges Problem in gleichzeitigen Systemen und tritt auch im Szenario gleichzeitiger Lese- und Schreibanforderungen in der Datenbank MySQL auf. Wenn zwei oder mehr Transaktionen aufeinander warten, um die bereits gehaltenen Sperren freizugeben, oder die Sperrreihenfolge inkonsistent ist und einen Zyklus des Wartens auf Sperrressourcen verursacht, kommt es zu einem „Deadlock“. Die häufigste Fehlermeldung lautet: Deadlock beim Versuch, die Sperre zu erhalten... gefunden.

Zum Beispiel hält Transaktion A eine X1-Sperre und beantragt eine X2-Sperre, Transaktion B hält eine X2-Sperre und beantragt eine X1-Sperre. Die Transaktionen A und B halten Sperren und beantragen die von der anderen Partei gehaltenen Sperren und warten in einer Schleife, was zu einem Deadlock führt. Deadlock found when trying to get lock...。

举例来说 A 事务持有 X1 锁 ，申请 X2 锁，B事务持有 X2 锁，申请 X1 锁。A 和 B 事务持有锁并且申请对方持有的锁进入循环等待，就造成了死锁。

如上图，是右侧的四辆汽车资源请求产生了回路现象，即死循环，导致了死锁。

从死锁的定义来看，MySQL 出现死锁的几个要素为：

1. 两个或者两个以上事务

2. 每个事务都已经持有锁并且申请新的锁

3. 锁资源同时只能被同一个事务持有或者不兼容

4. 事务之间因为持有锁和申请锁导致彼此循环等待

2、InnoDB 锁类型

为了分析死锁，我们有必要对 InnoDB 的锁类型有一个了解。

MySQL InnoDB 引擎实现了标准的行级别锁：共享锁( S lock ) 和排他锁 ( X lock )

1. 不同事务可以同时对同一行记录加 S 锁。

2. 如果一个事务对某一行记录加 X 锁，其他事务就不能加 S 锁或者 X 锁，从而导致锁等待。

如果事务 T1 持有行 r 的 S 锁，那么另一个事务 T2 请求 r 的锁时，会做如下处理:

1. T2 请求 S 锁立即被允许，结果 T1 T2 都持有 r 行的 S 锁

2. T2 请求 X 锁不能被立即允许

如果 T1 持有 r 的 X 锁，那么 T2 请求 r 的 X、S 锁都不能被立即允许，T2 必须等待 T1 释放 X 锁才可以，因为 X 锁与任何的锁都不兼容。共享锁和排他锁的兼容性如下所示：

2.1、间隙锁( gap lock ) 

间隙锁锁住一个间隙以防止插入。假设索引列有2, 4, 8 三个值，如果对 4 加锁，那么也会同时对(2,4)和(4,8)这两个间隙加锁。其他事务无法插入索引值在这两个间隙之间的记录。但是，间隙锁有个例外:

1. 如果索引列是唯一索引，那么只会锁住这条记录(只加行锁)，而不会锁住间隙。

2. 对于联合索引且是唯一索引，如果 where 条件只包括联合索引的一部分，那么依然会加间隙锁。

2.2、next-key lock

next-key lock 实际上就是 行锁+这条记录前面的 gap lock 的组合。假设有索引值10,11,13和 20,那么可能的 next-key lock 包括:

(负无穷,10],(10,11],(11,13],(13,20],(20,正无穷)

在 RR 隔离级别下，InnoDB 使用 next-key lock 主要是防止幻读

Wie im So lösen Sie das MySQL-Deadlock-Problem (detaillierte Beispiele) oben gezeigt, ist es sind die vier auf der rechten Seite Die Fahrzeugressourcenanforderung erzeugte ein Schleifenphänomen, also eine Endlosschleife, die zu einem Deadlock führte.

Nach der Definition von Deadlock gibt es mehrere Faktoren für Deadlock in MySQL:

1. Zwei oder mehr Transaktionen

2. Jede Transaktion hält bereits eine Sperre und beantragt eine neue Sperre

Sperrressourcen können nur gehalten werden von derselben Transaktion zur gleichen Zeit ausgeführt werden oder inkompatibel sind. Transaktionen warten in einer Schleife aufeinander, da sie Sperren halten und Sperren beantragen Dies ist für uns erforderlich, um die Sperrtypen von InnoDB zu verstehen.

MySQL InnoDB-Engine implementiert den StandardSperren auf Zeilenebene: Gemeinsame Sperre (S-Sperre) und exklusive Sperre (X-Sperre)

🎜🎜🎜🎜 Verschiedene Transaktionen können S-Sperren gleichzeitig zum gleichen Zeilendatensatz hinzufügen. 🎜🎜🎜🎜Wenn eine Transaktion einer bestimmten Zeile von Datensätzen eine X-Sperre hinzufügt, können andere Transaktionen keine S- oder 🎜🎜🎜🎜🎜Wenn Transaktion T1 die S-Sperre von Zeile r hält, wird die folgende Verarbeitung durchgeführt, wenn eine andere Transaktion T2 die S-Sperre von Zeile r anfordert: 🎜🎜🎜🎜🎜T2 fordert die S-Sperre sofort an und wird zugelassen Infolgedessen halten sowohl T1 als auch T2. Es gibt eine S-Sperre für r Zeilen. 🎜🎜🎜🎜T2s Anforderung einer X-Sperre kann nicht sofort gewährt werden Das Schloss ist mit keinem Schloss kompatibel. Die Kompatibilität von gemeinsam genutzten Sperren und exklusiven Sperren ist wie folgt: 🎜🎜🎜🎜2.1. Lückensperre (Lückensperre) 🎜🎜Spaltsperre verriegelt eine Lücke, um ein Einführen zu verhindern. Angenommen, die Indexspalte hat drei Werte: 2, 4 und 8. Wenn 4 gesperrt ist, werden auch die beiden Lücken (2,4) und (4,8) gleichzeitig gesperrt. Andere Transaktionen können keine Datensätze mit Indexwerten zwischen diesen beiden Lücken einfügen. Es gibt jedoch eine Ausnahme von der Lückensperre: 🎜🎜🎜🎜🎜Wenn die Indexspalte ein eindeutiger Index ist, wird nur dieser Datensatz gesperrt (es werden nur Zeilensperren hinzugefügt) und die Lücke wird nicht gesperrt. 🎜🎜🎜🎜Wenn es sich bei einem gemeinsamen Index um einen eindeutigen Index handelt, werden Lückensperren dennoch hinzugefügt, wenn die Where-Bedingung nur einen Teil des gemeinsamen Index enthält. 🎜🎜🎜🎜🎜2.2, Next-Key-Sperre🎜🎜Next-Key-Sperre ist eigentlich eine Kombination aus Zeilensperre + Lückensperre vor diesem Datensatz. Unter der Annahme, dass es Indexwerte 10, 11, 13 und 20 gibt, umfassen die möglichen Next-Key-Sperren: 🎜🎜🎜(negative Unendlichkeit, 10], (10, 11], (11, 13], (13, 20], (20, positive Unendlichkeit)🎜🎜🎜Unter der RR-Isolationsstufe verwendet InnoDB die Next-Key-Sperre hauptsächlich, um Probleme mit dem Phantom-Lesen zu verhindern 🎜🎜2.3, Absichtssperre (Absichtssperre)🎜🎜 InnoDB unterstützt die gleichzeitige Existenz von Zeilensperren und Tabellensperren. Um Sperrvorgänge mit unterschiedlichen Granularitäten zu unterstützen, unterstützt InnoDB eine zusätzliche Sperrmethode, die als Absichtssperre bezeichnet wird bedeutet, dass Transaktionen mit einer feineren Granularität gesperrt werden sollen: 🎜🎜🎜🎜🎜Intention Shared Lock (IS): Transaktionen zielen darauf ab, Objekte in der Tabelle zu sperren🎜🎜🎜 🎜Beabsichtigte exklusive Sperren (IX): Die Transaktion beabsichtigt, bestimmte Zeilen in der Tabelle exklusive Sperren hinzuzufügen.🎜🎜🎜🎜🎜Da die InnoDB-Speicher-Engine Sperren auf Zeilenebene unterstützt, werden absichtliche Sperren tatsächlich nur für vollständige Anforderungen blockiert Tabellenscans. Die Kompatibilität von Absichtssperren auf Tabellenebene und Sperren auf Zeilenebene ist wie folgt: 🎜🎜🎜🎜

2.4. Absichtssperre einfügen (Intention-Sperre einfügen)

Intention-Sperre einfügen ist eine Lückensperre, die vor dem Einfügen einer Datensatzzeile festgelegt wird. Diese Sperre gibt ein Signal des Einfügemodus frei, d. h., beim Einfügen befinden sich mehrere Transaktionen in derselben Indexlücke Wenn sie nicht an der gleichen Position in der Lücke eingefügt werden, besteht keine Notwendigkeit, aufeinander zu warten. Angenommen, eine Spalte hat die Indexwerte 2 und 6. Solange die Einfügepositionen der beiden Transaktionen unterschiedlich sind (zum Beispiel fügt Transaktion A 3 und Transaktion B 4 ein), können sie gleichzeitig eingefügt werden.

2.5. Kompatibilitätsmatrix für den Sperrmodus

Horizontal ist die Sperre, die angefordert wird:

3. Lesen Sie das Deadlock-Protokoll.

Bevor wir den konkreten Fall analysieren, wollen wir zunächst verstehen, wie es geht Lesen Sie das Deadlock-Protokoll und nutzen Sie die Informationen im Deadlock-Protokoll so weit wie möglich, um uns bei der Lösung des Deadlock-Problems zu helfen.

Das Datenbankszenario des nachfolgenden Testfalls ist wie folgt: MySQL 5.7-Transaktionsisolationsstufe ist RRMySQL 5.7 事务隔离级别为 RR

表结构和数据如下:

测试用例如下:

 通过执行show engine innodb status 可以查看到最近一次死锁的日志。

3.1、日志分析如下:

1.***** (1) TRANSACTION: TRANSACTION 2322, ACTIVE 6 sec starting index read

 事务号为2322，活跃 6秒，starting index read 表示事务状态为根据索引读取数据。常见的其他状态有:

mysql tables in use 1 说明当前的事务使用一个表。

locked 1 表示表上有一个表锁，对于 DML 语句为 LOCK_IX

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

LOCK WAIT 表示正在等待锁，2 lock struct(s) 表示 trx->trx_locks 锁链表的长度为2，每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及自增锁等。本用例中 2locks 表示 IX 锁和lock_mode X (Next-key lock)

1 row lock(s) 表示当前事务持有的行记录锁/ gap 锁的个数。

MySQL thread id 37, OS thread handle 140445500716800, query id 1234 127.0.0.1 root updating

MySQL thread id 37 表示执行该事务的线程 ID 为 37 (即 show processlist; 展示的 ID )

delete from student where stuno=5 表示事务1正在执行的 sql，比较难受的事情是 show engine innodb status 是查看不到完整的 sql 的，通常显示当前正在等待锁的 sql。

 ***** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2322 lock_mode X waiting

RECORD LOCKS 表示记录锁， 此条内容表示事务 1 正在等待表 student 上的 idx_stuno 的 X 锁，本案例中其实是 Next-Key Lock 。

事务2的 log 和上面分析类似:

2.***** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X

显示事务 2 的 insert into student(stuno,score) values(2,10) 持有了 a=5 的 Lock mode X

 LOCK_gap，不过我们从日志里面看不到事务2执行的 delete from student where stuno=5;

这点也是造成 DBA 仅仅根据日志难以分析死锁的问题的根本原因。

3.***** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X locks gap before rec insert intention waiting

表示事务 2 的 insert 语句正在等待插入意向锁 lock_mode X locks gap before rec insert intention waiting ( LOCK_X + LOCK_REC_gap )

4、经典案例分析

4.1、事务并发 insert 唯一键冲突

表结构和数据如下所示:

测试用例如下:

 日志分析如下:

1. 事务 T2 insert into t7(id,a) values (26,10) 语句 insert 成功，持有 a=10 的 排他行锁( Xlocks rec but no gap )

2. 事务 T1 insert into t7(id,a) values (30,10), 因为T2的第一条 insert 已经插入 a=10 的记录,事务 T1 insert a=10 则发生唯一键冲突,需要申请对冲突的唯一索引加上S Next-key Lock( 即 lock mode S waiting ) 这是一个间隙锁

   Die Tabellenstruktur und Daten sind wie folgt: 🎜🎜🎜🎜Die Testbeispiele lauten wie folgt:🎜🎜🎜🎜 Sie können das Protokoll des letzten Deadlocks anzeigen, indem Sie show engine innodb status ausführen. 🎜🎜3.1. Die Protokollanalyse lautet wie folgt: 🎜🎜1.***** (1) TRANSAKTION: TRANSAKTION 2322, AKTIV 6 Sekunden, Index lesen beginnen🎜🎜 Die Transaktionsnummer ist 2322, aktiv für 6 Sekunden, Index lesen beginnen Gibt an, dass der Transaktionsstatus auf Index-Lesedaten basiert. Andere häufige Status sind: 🎜🎜🎜🎜 Verwendete MySQL-Tabellen 1 gibt an, dass die aktuelle Transaktion eine Tabelle verwendet. 🎜🎜locked 1 bedeutet, dass es eine Tabellensperre für die Tabelle gibt, für DML-Anweisungen ist es LOCK_IX🎜rrreee🎜LOCK WAIT bedeutet Warten auf die Sperre, 2 Sperre struct(s) Gibt an, dass die Länge der trx->trx_locks-Sperrliste 2 beträgt. Jeder verknüpfte Listenknoten stellt eine von der Transaktion gehaltene Sperrstruktur dar, einschließlich Tabellensperren, Datensatzsperren und automatisch inkrementierender Sperren. In diesem Anwendungsfall stellt 2locks IX Sperren dar und lock_mode 🎜rrreee🎜MySQL-Thread-ID 37 bedeutet, dass die Thread-ID, die die Transaktion ausführt, 37 ist (d. h. Prozessliste anzeigen; die angezeigte ID) 🎜🎜aus Student löschen, wobei Stuno=5 bedeutet die Transaktion 1. Das Unangenehme an der ausgeführten SQL ist, dass show engine innodb status nicht die vollständige SQL anzeigen kann. Normalerweise wird die SQL angezeigt, die derzeit auf die Sperre wartet. 🎜rrreee🎜RECORD LOCKS stellt Datensatzsperren dar. Dieser Inhalt zeigt an, dass Transaktion 1 auf die X-Sperre von idx_stuno für die Tabelle Student wartet. In diesem Fall handelt es sich tatsächlich um eine Next-Key-Sperre. 🎜🎜Das Protokoll von Transaktion 2 ähnelt der obigen Analyse: 🎜rrreee🎜zeigt, dass die Einfügung in student(stuno,score)-Werte (2,10) von Transaktion 2 einen = 5-Sperrmodus X enthält🎜

   🎜 LOCK_gap , aber wir können den von Transaktion 2 ausgeführten Löschvorgang von student where stuno=5;🎜

   🎜 nicht aus dem Protokoll sehen. Dies ist auch der grundlegende Grund, warum es für DBA schwierig ist, das Deadlock-Problem nur anhand des Protokolls zu analysieren . 🎜🎜3.***** (2) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:🎜🎜RECORD SPERRT Space ID 11 Seite Nr. 5 n Bits 72 Index idx_stuno der Tabelle cw****.****student trx id 2321 lock_mode Konflikt mit eindeutigem Schlüssel einfügen🎜🎜Die Tabellenstruktur und Daten sind wie folgt:🎜🎜🎜🎜🎜🎜Die Testbeispiele sind wie folgt: 🎜🎜🎜🎜 Das Protokoll Die Analyse lautet wie folgt:🎜
   1. 🎜Transaktion T2-Einfügung in t7(id,a)-Werte (26,10) Die Anweisungseinfügung ist erfolgreich und hält a=10 Exklusive Zeilensperre (Xlocks rec but keine Lücke)🎜 li>
   2. 🎜Transaktion T1-Einfügung in t7(id,a)-Werte (30,10), da die erste Einfügung von T2 bereits a=10 Datensatz, Transaktion eingefügt hat Das Einfügen von a = 10 in T1 führt zu einem eindeutigen Schlüsselkonflikt. Sie müssen den eindeutigen Index des Konflikts plus S Next-Key Lock beantragen (d. h. Sperrmodus S Warten). Dies ist eine Lückensperre das gilt für eine Sperre zwischen (,10], (10,20] Lückenbereich. 🎜

   3. Transaktion T2 in t7(id,a)-Werte einfügen (40, 9) Der durch diese Anweisung eingefügte Wert von a=9 liegt zwischen der beantragten Gap-Sperre 4-10 von Transaktion T1, daher ist es erforderlich, dass die zweite Einfügeanweisung von Transaktion T2 darauf wartet, dass die S-Next-key-Sperre von Transaktion T1 freigegeben wird, und lock_mode wird im Protokoll angezeigt. gap 锁4-10之间， 故需事务 T2 的第二条 insert 语句要等待事务 T1 的 S-Next-key Lock 锁释放,在日志中显示 lock_mode X locks gap before rec insert intention waiting 。

    4.2、先 update 再 insert 的并发死锁问题

    表结构如下，无数据:

   

   测试用例如下:

   

    死锁分析:
   可以看到两个事务 update 不存在的记录，先后获得间隙锁( gap 锁)，gap 锁之间是兼容的所以在update环节不会阻塞。两者都持有 gap 锁，然后去竞争插入意向锁。当存在其他会话持有 gap 锁的时候，当前会话申请不了插入意向锁，导致死锁。

   5、如何尽可能避免死锁

   1. 合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。

   2. 调整业务逻辑 SQL 执行顺序， 避免 update/delete 长时间持有锁的 SQL 在事务前面。

   3. 避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小。

   4. 以固定的顺序访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁。

   5. 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,那么就会锁定所查找到的记录。

   6. 尽量按主键/索引去查找记录，范围查找增加了锁冲突的可能性，也不要利用数据库做一些额外额度计算工作。比如有的程序会用到 “select … where … order by rand();”这样的语句，由于类似这样的语句用不到索引，因此将导致整个表的数据都被锁住。

   7. 优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，减少连接的表，将复杂 SQL 分解

   4.2. Parallelitäts-Deadlock-Problem, zuerst aktualisieren und dann einfügen

   Die Tabellenstruktur ist wie folgt, keine Daten:

   Das Testbeispiel lautet wie folgt:

   🎜🎜 Deadlock-Analyse:
   Ja Da wir in der Transaktionsaktualisierung zwei nicht vorhandene Datensätze gesehen haben, haben wir nacheinander eine Lückensperre (Lückensperre) erhalten. Die Lückensperren sind kompatibel, sodass sie während des Aktualisierungsvorgangs nicht blockiert werden . Beide halten Lückensperren und konkurrieren dann um das Einfügen von Absichtssperren. Wenn es andere Sitzungen mit Lückensperren gibt, kann die aktuelle Sitzung die Einfügungsabsichtssperre nicht beantragen, was zu einem Deadlock führt. 🎜

   5. So vermeiden Sie Deadlocks so weit wie möglich

   🎜🎜Entwerfen Sie den Index sinnvoll und platzieren Sie die Spalten mit hoher Unterscheidung vor dem kombinierten Index, damit Business SQL als positioniert werden kann weniger als möglich durch die Index--Zeilen, um Sperrkonflikte zu reduzieren. 🎜🎜🎜🎜Passen Sie die Ausführungsreihenfolge der Geschäftslogik-SQL an, um das Aktualisieren/Löschen von SQL zu vermeiden, das vor der Transaktion lange Zeit Sperren hält. 🎜🎜🎜🎜Vermeiden Sie Große Transaktionen und versuchen Sie, große Transaktionen zur Verarbeitung in mehrere kleine Transaktionen aufzuteilen. Die Wahrscheinlichkeit von Sperrkonflikten bei kleinen Transaktionen ist ebenfalls geringer. 🎜🎜🎜🎜Zugriff auf Tabellen und Zeilen in fester Reihenfolge. Beispielsweise aktualisiert Transaktion A bei zwei Transaktionen, die Daten aktualisieren, die Daten in der Reihenfolge 1, 2; Transaktion B aktualisiert die Daten in der Reihenfolge 2, 1. Dies führt eher zu einem Deadlock. 🎜🎜🎜🎜In Systemen mit relativ hoher Parallelität nicht explizit sperren, insbesondere bei Transaktionen. Wenn sich beispielsweise die Anweisung „select... for update“ in einer Transaktion befindet (Starttransaktion wird ausgeführt oder Autocommit ist auf 0 gesetzt), wird der gefundene Datensatz gesperrt. 🎜🎜🎜🎜Versuchen Sie, nach Datensätzen nach Primärschlüssel/Index zu suchen. Verwenden Sie die Datenbank nicht für zusätzliche Quotenberechnungen. Einige Programme verwenden beispielsweise Anweisungen wie „select ... where ... order by rand();“ Da Anweisungen wie diese keine Indizes verwenden, werden die Daten der gesamten Tabelle gesperrt. 🎜🎜🎜🎜Optimieren Sie SQL und Tabellendesign, um die Situation zu reduzieren, dass zu viele Ressourcen gleichzeitig belegt werden. Beispielsweise Reduzieren Sie die Anzahl verbundener Tabellen und zerlegen Sie komplexes SQL in mehrere einfache SQLs. 🎜🎜🎜🎜Empfohlenes Lernen: 🎜MySQL-Lerntutorial🎜🎜

Das obige ist der detaillierte Inhalt vonSo lösen Sie das MySQL-Deadlock-Problem (detaillierte Beispiele). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!