谁来帮忙分析一下死锁日志

[如一宝宝]

2014-01-26 08:13:26 7fac7c28b700InnoDB: transactions deadlock detected, dumping detailed information.
2014-01-26 08:13:26 7fac7c28b700
*** (1) TRANSACTION:
TRANSACTION 126709, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 244, OS thread handle 0x7fac4769a700, query id 81788 localhost 127.0.0.1 root updating
UPDATE monster_usermonsterbar SET MonsterCount = MonsterCount + AddCount_in WHERE UserID = UserID_in
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 3 n bits 280 index `PRIMARY` of table `k2_gamedb`.`monster_usermonsterbar` trx id 126709 lock_mode X locks rec but not gap waiting
Record lock, heap no 210 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 85f5e157; asc W;;
1: len 6; hex 00000001eee7; asc ;;
2: len 7; hex 4a000001ec1329; asc J );;
3: len 4; hex 80000032; asc 2;;
4: len 1; hex 30; asc 0;;
5: len 4; hex 80000021; asc !;;
6: len 4; hex 80000000; asc ;;

*** (2) TRANSACTION:
TRANSACTION 126710, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 238, OS thread handle 0x7fac7c28b700, query id 81789 localhost 127.0.0.1 root updating
UPDATE monster_usermonsterbar SET MonsterCount = MonsterCount + AddCount_in WHERE UserID = UserID_in
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 28 page no 3 n bits 280 index `PRIMARY` of table `k2_gamedb`.`monster_usermonsterbar` trx id 126710 lock mode S locks rec but not gap
Record lock, heap no 210 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 85f5e157; asc W;;
1: len 6; hex 00000001eee7; asc ;;
2: len 7; hex 4a000001ec1329; asc J );;
3: len 4; hex 80000032; asc 2;;
4: len 1; hex 30; asc 0;;
5: len 4; hex 80000021; asc !;;
6: len 4; hex 80000000; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 3 n bits 280 index `PRIMARY` of table `k2_gamedb`.`monster_usermonsterbar` trx id 126710 lock_mode X locks rec but not gap waiting
Record lock, heap no 210 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 85f5e157; asc W;;
1: len 6; hex 00000001eee7; asc ;;
2: len 7; hex 4a000001ec1329; asc J );;
3: len 4; hex 80000032; asc 2;;
4: len 1; hex 30; asc 0;;
5: len 4; hex 80000021; asc !;;
6: len 4; hex 80000000; asc ;;

*** WE ROLL BACK TRANSACTION (2)

两条一样的sql，怎么会产生死锁呢? userid还是主键~

[rucypli]

锁的是索引块 一个可能从上往下锁 一个可能从下往上锁 就互相锁上了

[如一宝宝]

引用 1 楼 rucypli 的回复:

锁的是索引块 一个可能从上往下锁 一个可能从下往上锁 就互相锁上了

难道两条一样的sql，扫描时的读取顺序还不一样？

[rucypli]

也可能修改的顺序不一样 我是这么猜测的

[thomasLand]

LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s)
锁链表长度为4,事务分配的堆内存大小为1248,当前事务持有2个行记录锁定

RECORD LOCKS space id 28 page no 3 n bits 280 index `PRIMARY` of table `k2_gamedb`.`monster_usermonsterbar` trx id 126709 lock_mode X locks rec but not gap waiting

记录锁定 对应的聚集索引是28 第三页 该页共有216条记录 事务126709占有这个锁,写锁定,记录锁定,但并不是间隙锁定(过滤条件是等值而非范围),next-key locking 基本排除

Record lock, heap no 210 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
  0: len 4; hex 85f5e157; asc    W;;
  1: len 6; hex 00000001eee7; asc       ;;
  2: len 7; hex 4a000001ec1329; asc J     );;
  3: len 4; hex 80000032; asc    2;;
  4: len 1; hex 30; asc 0;;
  5: len 4; hex 80000021; asc    !;;
  6: len 4; hex 80000000; asc     ;;

索引堆中该条记录的排序记录为201,7列,行格式为compact,blabla..字段内容。

比较诡异的是事务126710持有一个读锁,等待写锁。而执行的均为update操作。 基本的想法是: 1)排除意向锁,intention lock（表级别）,MVCC？ 2) 事务126710持有一个读锁? 提供下 `k2_gamedb`.`monster_usermonsterbar`的结构,最好有个锁定场景的说明。