mysql死锁分析

showEngine  InnoDB STATUS ；

死锁日志结果如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
150224 23:53:48
*** (1) TRANSACTION:
TRANSACTION 6643B841, ACTIVE 0.018 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 601951, OS thread handle 0x2b8fa8081700, query id 803818222 10.160.63.103 mocha Updating
UPDATE selfie_ll SET    ScoreAverage=85.57353,ScoreCount=3,ChangeTime=NOW(),UpdateTime=NOW() WHERE  ID=33340
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 608 page no 467 n bits 192 index `PRIMARY` of table `moocha`.`<span style="font-family: Arial, Helvetica, sans-serif;">selfie_ll</span><span style="font-family: Arial, Helvetica, sans-serif;">` trx id 6643B841 lock_mode X locks rec but not gap waiting</span>
*** (2) TRANSACTION:
TRANSACTION 6643B837, ACTIVE 0.055 sec starting index read
mysql tables in use 2, locked 2
302 lock struct(s), heap size 47544, 40666 row lock(s)
MySQL thread id 541948, OS thread handle 0x2b8da8081700, query id 803818202 10.161.182.86 mocha Sending data
UPDATE selfie_ll SL INNER  JOIN ( SELECT AA.ID,        @VAR_NvWangRank:=@VAR_NvWangRank+1 AS NvWangRank FROM   selfie_ll AA WHERE  AA.TopicID=11 AND AA.ISValid=1 ORDER  BY AA.ScoreCount DESC, AA.ScoreAverage DESC, AA.ID ASC )  AS  BB ON BB.ID=SL.ID SET    SL.NvWangRank=BB.NvWangRank
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 608 page no 467 n bits 192 index `PRIMARY` of table `moocha`.`selfie_ll` trx id 6643B837 lock mode S
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 608 page no 467 n bits 192 index `PRIMARY` of table `moocha`.`selfie_ll` trx id 6643B837 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)

RECORD LOCKS space id 608 page no 467 n bits 192 index `PRIMARY` of table `moocha`.`selfie_ll` trx id 6643B841 lock_mode X locks rec but not gap waiting

这句告诉我们锁信息，表空间id为608，page no表示锁在这个表空间的467页上面，192表示在锁bitmap的位置，6643B841表示事务的id，x表示锁为排他锁， not gap waiting表示这里使用的行级别锁类型不是gap 。 这里应该是没有使用gap锁，因为在innodb中，update 和select没有使用意向锁，而gap在innodb中作为一种意向锁，在insert时使用，并且不会有任何阻塞作用。

下面进行事务分析

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 608 page no 467 n bits 192 index `PRIMARY` of table `moocha`.&