我需要帮助解决我面临的死锁情况。谢谢你的帮助。
我认为死锁与事务2的SELECT子查询有关，但我不理解以下几点：
- 为什么它持有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;
非常感谢！
为什么它持有一个S锁，然后等待同一行的X锁。。。为什么一开始没有X锁？
第二个查询使用select子查询创建派生表。默认情况下，选择查询仅创建共享(S(锁，除非启用可串行隔离级别。您可以通过向子查询中添加for update子句来明确指示select使用exclusive锁(好吧，intention-exclusive，IX(，但我会小心处理。您需要评估子查询是否可以锁定比更新部分更多的记录，以及是否值得锁定这些额外的记录。
无论如何，为什么事务1会阻塞任何东西？
第二个查询的select子查询在给定记录上放置S锁。然后是第一个查询，请求对同一条记录执行X锁，由于已经存在S锁，因此无法立即授予X锁。
当第二个查询试图将锁升级到X时，它发现它在队列中的第二个位置，以便在第一个事务之后获得X锁。但是，由于第二个事务仍在运行，第二个查询无法释放S锁，从而阻止第一个事务完成。
请不要问为什么mysql以这种方式工作，因为只有mysql开发人员才能回答这个问题。