Explication des blocages MySQL

Question

J'ai besoin d'aide pour résoudre une situation de blocage à laquelle je suis confronté. Merci de votre aide.

Je pense que le blocage est lié à la sous-requête SELECT de la transaction 2, mais je ne comprends pas plusieurs choses:

• Pourquoi tient-il un verrou S et attend ensuite un verrou X de la même rangée ... pourquoi n'a-t-il pas obtenu un verrou X pour commencer?
• Dans tous les cas, pourquoi la transaction 1 bloque-t-elle quelque chose? Je m'attendrais à ce qu'il ne nécessite qu'un seul verrou, donc ne pas obtenir un verrou de quoi que ce soit d'autre, et attendre simplement que ce verrou soit disponible pour être traité ... La transaction 1 détient-elle vraiment le verrou que 2 attend? Ça n'a aucun sens.

---

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;

---

MERCI BEAUCOUP!

Answer 1

Pourquoi tient-il un verrou S et attend ensuite un verrou X de la même rangée ... pourquoi n'a-t-il pas obtenu un verrou X pour commencer?

La deuxième requête utilise une sous-requête de sélection pour créer une table dérivée. Sélectionnez les requêtes par défaut pour créer un verrou partagé (S) uniquement, sauf si vous activez le niveau d'isolement sérialisable. Vous pouvez explicitement demander au select d'utiliser le verrou exclusif (enfin, intention exclusive, IX) en ajoutant une for updateclause à la sous-requête, mais je ferais attention à cela. Vous devez évaluer si la sous-requête peut verrouiller plus d'enregistrements que la partie mise à jour et s'il vaut la peine de verrouiller ces enregistrements supplémentaires.

Dans tous les cas, pourquoi la transaction 1 bloque-t-elle quelque chose?

La sous-requête de sélection de la 2ème requête place un verrou S sur l'enregistrement donné. Vient ensuite la première requête demandant un verrou X sur le même enregistrement, qui ne peut pas être accordé immédiatement en raison du verrou S déjà présent.

Lorsque la deuxième requête tente de mettre à niveau le verrou vers X, elle constate qu'elle est la deuxième dans la file d'attente pour obtenir le verrou X derrière la première transaction. Cependant, puisque la 2ème transaction est toujours en cours, la 2ème requête ne peut pas libérer le verrou S, empêchant la 1ère transaction de se terminer.

Veuillez ne pas demander pourquoi mysql fonctionne de cette façon car seul un développeur mysql peut répondre à cette question.