Erklärung zu MySQL-Deadlocks

Question

Ich brauche Hilfe bei der Lösung einer Sackgasse, mit der ich konfrontiert bin. Danke für Ihre Hilfe.

Ich denke, der Deadlock hängt mit der SELECT-Unterabfrage von Transaktion 2 zusammen, aber ich verstehe einige Dinge nicht:

• Warum hält es ein S-Schloss und wartet dann auf ein X-Schloss derselben Zeile ... warum hat es nicht zuerst ein X-Schloss bekommen?
• Warum blockiert Transaktion 1 auf jeden Fall etwas? Ich würde erwarten, dass es nur eine Sperre benötigt, daher keine Sperre für irgendetwas anderes erhält und einfach warten, bis diese Sperre für die Verarbeitung verfügbar ist ... Hält Transaktion 1 wirklich die Sperre, auf die 2 wartet? Es ergibt keinen Sinn für mich.

---

LATEST DETECTED DEADLOCK
------------------------
2020-09-09 07:56:01 2b2bf0401700
*** (1) TRANSACTION:
TRANSACTION 28039013420, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 5603884, OS thread handle 0x2b28527c6700, query id 1343987203 admin updating
UPDATE `order` SET `is_in` = 0 WHERE `order`.`id` = 2084725
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27319884 page no 45175 n bits 4 index `PRIMARY` of table `order` trx id 28039013420 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 55; compact format; info bits 0
 

*** (2) TRANSACTION:
TRANSACTION 28039013409, ACTIVE 0 sec fetching rows
mysql tables in use 4, locked 4
LOCK WAIT 435 lock struct(s), heap size 376, 39002 row lock(s)
MySQL thread id 5603883, OS thread handle 0x2b23e8e82700, query id 1343987095 admin Creating sort index
UPDATE order
            JOIN items ON items.id = order.item_id
            JOIN (              select switch_item_id, sum(quantity) total_sent from order
              inner join items on items.id = item_id
              where scenario_id =  1088
              and is_in = 1
              group by items.switch_item_id
) q on items.switch_item_id = q.switch_item_id
            SET
              total_item_quantity = q.total_sent,
            WHERE is_in = 1 and scenario_id = 1088
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 27319884 page no 45175 n bits 2 index `PRIMARY` of table `order` trx id 28039013409 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 55; compact format; info bits 0
 0: len=8; bufptr=0x2b0c9748008b; hex= 80000000001fcf73; asc        s;;
0; asc     ;;
 54: SQL NULL;

[bitmap0 of 16 bytes in hex: 7c 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ]
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27319884 page no 45175 n bits 4 index `PRIMARY` of table `order` trx id 28039013409 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 55; compact format; info bits 0
 0: len=8; bufptr=0x2b0c97480302; hex= 80000000001fcf75; asc        u;;

 54: SQL NULL;

---

VIELEN DANK!

Answer 1

Warum hält es ein S-Schloss und wartet dann auf ein X-Schloss derselben Zeile ... warum hat es nicht zuerst ein X-Schloss bekommen?

Die zweite Abfrage verwendet eine ausgewählte Unterabfrage, um eine abgeleitete Tabelle zu erstellen. Wählen Sie standardmäßig Abfragen aus. Erstellen Sie nur eine gemeinsame Sperre (S), es sei denn, Sie aktivieren die serialisierbare Isolationsstufe. Sie können die Auswahl explizit anweisen, eine exklusive Sperre zu verwenden ( for updatenaja, Absicht exklusiv, IX), indem Sie der Unterabfrage eine Klausel hinzufügen, aber ich würde damit vorsichtig sein. Sie müssen prüfen, ob die Unterabfrage mehr Datensätze sperren kann als der Aktualisierungsteil und ob es sich lohnt, diese zusätzlichen Datensätze zu sperren.

Warum blockiert Transaktion 1 auf jeden Fall etwas?

Die ausgewählte Unterabfrage der 2. Abfrage setzt eine S-Sperre für den angegebenen Datensatz. Dann folgt die erste Abfrage, in der eine X-Sperre für denselben Datensatz angefordert wird, die aufgrund der bereits vorhandenen S-Sperre nicht sofort gewährt werden kann.

Wenn die zweite Abfrage versucht, die Sperre auf X zu aktualisieren, stellt sie fest, dass sie die zweite in der Warteschlange ist, um die X-Sperre hinter der ersten Transaktion zu erhalten. Da die zweite Transaktion jedoch noch ausgeführt wird, kann die zweite Abfrage die S-Sperre nicht aufheben, wodurch verhindert wird, dass die erste Transaktion abgeschlossen wird.

Bitte fragen Sie nicht, warum MySQL so funktioniert, da nur ein MySQL-Entwickler diese Frage beantworten kann.