mysql - 了解 mysql 8.0.11 中的死锁

Question

我在 mysql 8.0.11 上的“显示引擎 innodb 状态”中发现了下面的死锁。根据我的理解，两个查询都应该只锁定一行（两个查询的主键 user_id 不同）。但是第二个查询"*** (2) HOLDS THE LOCK(S): "RECORD LOCKS space id 5461 page no 76054 n bits 96 index PRIMARY of table用户"`

第一个查询正在等待获取相同的锁。我不明白为什么？即使两个 lock 语句都说"locks rec but not gap"这意味着两者都只锁定单个记录（这应该是他们的主键 user_id ）想知道我的理解（如上所述）是否正确？如果是，那么如何解释以下日志？

*** (1) TRANSACTION:
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
update `user` set `user`.`xxxx` = 1 where ( user_id = 4939334)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5461 page no 76054 n bits 96 index PRIMARY of table `user` trx id 5385693398 lock_mode X locks rec but not gap waiting
Record lock, heap no 24 PHYSICAL RECORD: n_fields 20; compact format; info bits 0

*** (2) TRANSACTION:
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
update `user` set `user`.`xxxx` = 1 where ( user_id = 4917613)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5461 page no 76054 n bits 96 index PRIMARY of table `user` trx id 5385693399 lock_mode X locks rec but not gap
Record lock, heap no 24 PHYSICAL RECORD: n_fields 20; compact format; info bits 0


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5461 page no 63136 n bits 88 index PRIMARY of table `user` trx id 5385693399 lock_mode X locks rec but not gap waiting
Record lock, heap no 19 PHYSICAL RECORD: n_fields 20; compact format; info bits 0

标签： mysqlinnodbdatabase-deadlocks