V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
Infernalzero
V2EX  ›  问与答

请教一个 mysql 的死锁问题

  •  
  •   Infernalzero · 2015-10-24 10:50:32 +08:00 · 2043 次点击
    这是一个创建于 3128 天前的主题,其中的信息可能已经有所发展或是发生改变。
    两条 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)
    目前尚无回复
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2266 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 01:23 · PVG 09:23 · LAX 18:23 · JFK 21:23
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.