请教一个 mysql 的死锁问题

2015-10-24 10:50:32 +08:00
 Infernalzero
两条 select for update 语句,类型相同,只是条件不同,我看了半天没看出为啥这样两条会产生死锁,日志里显示没有间隙锁,如果其中一条是 update 来锁主键的话还是可以理解的,为何两条同类型的 select for update 语句会产生死锁,而且还是加了唯一索引的。
死锁场景如下
索引:
id 为主键
userId,type 两列组成唯一索引`idx_userId_type`
事务隔离级别: RR
mysql 死锁日志:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-10-23 09:33:04 7f3c7480c700
*** (1) TRANSACTION:
TRANSACTION 7648295817, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 2051036, OS thread handle 0x7f3c46efc700, query id 15026409927 192.168.10.11
select * from PayAccountBalance
where userId = 388
and type = 17 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 147 page no 12 n bits 624 index `idx_userId_type` of table `payment`.`PayAccountBalance` trx id 7648295817 lock_mode X locks rec but not gap waiting
Record lock, heap no 531 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 8000000000000184; asc ;;
1: len 8; hex 8000000000000011; asc ;;
2: len 8; hex 8000000000000728; asc (;;

*** (2) TRANSACTION:
TRANSACTION 7648295789, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 2051466, OS thread handle 0x7f3c7480c700, query id 15026409946 192.168.10.23
select * from PayAccountBalance
where userId = 388
and type = 14 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 147 page no 12 n bits 624 index `idx_userId_type` of table `payment`.`PayAccountBalance` trx id 7648295789 lock_mode X locks rec but not gap
Record lock, heap no 531 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 8000000000000184; asc ;;
1: len 8; hex 8000000000000011; asc ;;
2: len 8; hex 8000000000000728; asc (;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 147 page no 12 n bits 624 index `idx_userId_type` of table `payment`.`PayAccountBalance` trx id 7648295789 lock_mode X locks rec but not gap waiting
Record lock, heap no 527 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 8000000000000184; asc ;;
1: len 8; hex 800000000000000e; asc ;;
2: len 8; hex 8000000000000724; asc $;;

*** WE ROLL BACK TRANSACTION (2)

还有一点不理解的是,既然加了唯一索引为何行锁数还会大于 1 ?
4 lock struct(s), heap size 1184, 3 row lock(s)
2047 次点击
所在节点    问与答
0 条回复

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/230674

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX