Explicação de impasses do MySQL

Question

Preciso de ajuda para resolver um impasse que estou enfrentando. Obrigado pela ajuda.

Acho que o deadlock está relacionado à subconsulta SELECT da transação 2, mas não entendo várias coisas:

• Por que ele está segurando um bloqueio S e então esperando por um bloqueio X da mesma linha ... por que ele não obteve um bloqueio X para começar?
• Em qualquer caso, por que a transação 1 está bloqueando alguma coisa? Eu esperaria que ele só precisasse de um bloqueio, portanto, não obteria o bloqueio de mais nada, e apenas esperaria até que esse bloqueio estivesse disponível para ser processado ... A transação 1 está realmente segurando o bloqueio que 2 está esperando? Isso não faz sentido para mim.

---

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;

---

MUITO OBRIGADO!

Answer 1

Por que ele está segurando um bloqueio S e então esperando por um bloqueio X da mesma linha ... por que ele não obteve um bloqueio X para começar?

A segunda consulta usa uma subconsulta de seleção para criar uma tabela derivada. Consultas selecionadas por padrão criam um bloqueio compartilhado (S) apenas, a menos que você habilite o nível de isolamento serializável. Você pode instruir explicitamente o select a usar bloqueio exclusivo (bem, exclusivo de intenção, IX) adicionando uma for updatecláusula à subconsulta, mas eu teria cuidado com isso. Você precisa avaliar se a subconsulta pode bloquear mais registros do que a parte de atualização faz e se vale a pena bloquear esses registros extras.

Em qualquer caso, por que a transação 1 está bloqueando alguma coisa?

A subconsulta select da 2ª consulta coloca um bloqueio S no registro fornecido. Em seguida, vem a primeira consulta solicitando um bloqueio X no mesmo registro, que não pode ser concedido imediatamente por causa do bloqueio S já lá.

Quando a 2ª consulta tenta atualizar o bloqueio para X, ela descobre que é o 2 ° na fila para obter o bloqueio X após a primeira transação. No entanto, como a 2ª transação ainda está em execução, a 2ª consulta não pode liberar o bloqueio S, impedindo a conclusão da 1ª transação.

Por favor, não pergunte por que o mysql funciona dessa maneira porque apenas um desenvolvedor mysql pode responder a esta pergunta.