MySQL死锁的解释 [英] MySQL deadlocks explanation

查看:325
本文介绍了MySQL死锁的解释的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我需要帮助来解决我所面临的僵局。谢谢您的帮助。

I need help solving a deadlock situation I'm facing. Thanks for your help.

我认为死锁与事务2的SELECT子查询有关,但我不了解几件事:

I think the deadlock is related to the SELECT subquery of transaction 2, but I don't understand several things:


  • 为什么它持有S锁,然后等待同一
    行的X锁...为什么它没有得到X锁呢?

  • 无论如何,为什么事务1阻止了什么?我希望它只需要一个
    锁,因此不会得到任何其他锁,而只是等到
    那个锁可用以进行处理...交易1确实是
    持有2正在等待的锁?对我来说这没有意义。

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;




感谢很多!


THANKS A LOT!

推荐答案


为什么它持有S锁,然后等待同一行的X锁...为什么没有得到X锁呢?

Why is it holding a S lock and then waiting for a X lock of the same row... why didn't it get an X lock to begin with?

第二个查询使用select子查询创建派生表。默认情况下,选择查询仅创建共享(S)锁,除非您启用了可串行化的隔离级别。您可以通过向子查询添加 for update 子句来明确指示select使用排他锁(嗯,意图排他,IX),但是我会小心一点。您需要评估子查询是否可以比更新部分锁定更多的记录,以及是否值得锁定这些额外的记录。

The 2nd query uses a select subquery to create a derived table. Select queries by default create a shared (S) lock only, unless you enable serialisable isolation level. You can explicitly instruct the select to use exclusive lock (well, intention exclusive, IX) by adding for update clause to the subquery, but I would be careful with that. You need to evaluate if the subquery could lock more records than the update part does and if it is worth locking these extra records.


无论如何,为什么?事务1阻塞了什么?

In any case, why is transaction 1 blocking anything?

第二个查询的select子查询在给定记录上放置了S锁。然后出现第一个查询,请求在同一记录上加一个X锁,由于已有S锁,因此无法立即授予该锁。

The select subquery of the 2nd query places an S lock on the given record. Then comes the 1st query along requesting an X lock on the same record, which cannot be granted immediatelly because of the S lock already there.

第二个查询试图将锁升级到X时,它发现在第一个事务后面获得X锁是在队列中的第二个。但是,由于第二个事务仍在运行,第二个查询无法释放S锁,从而阻止了第一个事务完成。

Whent the 2nd query tries to upgrade the lock to X, it finds that it is 2nd in the queue to get the X lock behind the first transaction. However, since the 2nd transaction is still running, the 2nd query cannot release the S lock, preventing the 1st transaction from completing.

请不要问为什么mysql这样工作,因为只有一个mysql开发人员可以回答这个问题。

Pls do not ask why mysql works this way because only a mysql developer can answer this question.

这篇关于MySQL死锁的解释的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆