Explicación de los interbloqueos de MySQL

Question

Necesito ayuda para resolver una situación de punto muerto que estoy enfrentando. Gracias por tu ayuda.

Creo que el punto muerto está relacionado con la subconsulta SELECT de la transacción 2, pero no entiendo varias cosas:

• ¿Por qué tiene un candado S y luego espera un candado X de la misma fila ... para empezar, por qué no obtuvo un candado X?
• En cualquier caso, ¿por qué la transacción 1 bloquea algo? Esperaría que solo necesite un bloqueo, por lo tanto, no obtenga un bloqueo de nada más, y simplemente espere hasta que ese bloqueo esté disponible para ser procesado ... ¿La transacción 1 realmente mantiene el bloqueo que 2 está esperando? No tiene sentido para mí.

---

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;

---

¡MUCHAS GRACIAS!

Answer 1

¿Por qué tiene un candado S y luego espera un candado X de la misma fila ... para empezar, por qué no obtuvo un candado X?

La segunda consulta utiliza una subconsulta de selección para crear una tabla derivada. Las consultas seleccionadas de forma predeterminada crean un bloqueo compartido (S) únicamente, a menos que habilite el nivel de aislamiento serializable. Puede indicar explícitamente a la selección que use bloqueo exclusivo (bueno, intención exclusiva, IX) agregando una for updatecláusula a la subconsulta, pero tendría cuidado con eso. Debe evaluar si la subconsulta podría bloquear más registros que la parte de actualización y si vale la pena bloquear estos registros adicionales.

En cualquier caso, ¿por qué la transacción 1 bloquea algo?

La subconsulta de selección de la segunda consulta coloca un bloqueo S en el registro dado. Luego viene la primera consulta que solicita un bloqueo X en el mismo registro, que no se puede otorgar de inmediato debido al bloqueo S que ya está allí.

Cuando la segunda consulta intenta actualizar el bloqueo a X, encuentra que es el segundo en la cola para obtener el bloqueo X detrás de la primera transacción. Sin embargo, dado que la segunda transacción aún se está ejecutando, la segunda consulta no puede liberar el bloqueo S, lo que evita que se complete la primera transacción.

Por favor, no pregunte por qué mysql funciona de esta manera porque solo un desarrollador de mysql puede responder esta pregunta.