Two discrete update statements deadlocking
(5.1.28-rc-log on x86_64 GNU/Linux)
We are experiencing frequent deadlocks of the type shown below, and for the life of me I don't see why. The two statements update discrete rows based on the primary key (id) and one other non-unique indexed column (hibernateVersion). The lock type (lock_mode X locks rec but not gap) would appear to not involve the well known "gap lock" issue. Can anyone explain to me what is going on and what I might do to keep these deadlocks from happening?
------------------------
LATEST DETECTED DEADLOCK
------------------------
080925 10:57:44
*** (1) TRANSACTION:
TRANSACTION 0 2913100, ACTIVE 3 sec, process no 29553, OS thread id 1180821824 starting index read mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1216, 4 row lock(s), undo log entries 3 MySQL thread id 151549, query id 99648729 slf7.doppelganger.com 192.168.99.125 eviltwin Updating update alltime_user_social_status set hibernateVersion=34618, respektScore=369667, vpoints=127667 where id=70562 and hibernateVersion=34617
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1265 page no 500 n bits 328 index `PRIMARY` of table `eviltwin_0921`.`alltime_user_social_status` trx id 0 2913100 lock_mode X locks rec but not gap waiting Record lock, heap no 40 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 8; hex 80000000000113a2; asc ;; 1: len 6; hex 0000002c7323; asc ,s#;; 2: len 7; hex 000000003e038e; asc > ;; 3: len 4; hex 8000873a; asc :;; 4: len 4; hex 800048dc; asc H ;; 5: len 4; hex 8005a464; asc d;; 6: len 4; hex 80000000; asc ;; 7: len 4; hex 8000028a; asc ;; 8: len 8; hex 800012411a1c3977; asc A 9w;; 9: len 4; hex 8001f314; asc ;;
*** (2) TRANSACTION:
TRANSACTION 0 2913059, ACTIVE 10 sec, process no 29553, OS thread id 1166444864 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1
64 lock struct(s), heap size 14320, 60 row lock(s), undo log entries 59 MySQL thread id 151514, query id 99662088 slf8.doppelganger.com 192.168.99.127 eviltwin Updating update alltime_user_social_status set hibernateVersion=19306, respektScore=293749, vpoints=56974 where id=196161 and hibernateVersion=19305
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1265 page no 500 n bits 328 index `PRIMARY` of table `eviltwin_0921`.`alltime_user_social_status` trx id 0 2913059 lock_mode X locks rec but not gap Record lock, heap no 40 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 8; hex 80000000000113a2; asc ;; 1: len 6; hex 0000002c7323; asc ,s#;; 2: len 7; hex 000000003e038e; asc > ;; 3: len 4; hex 8000873a; asc :;; 4: len 4; hex 800048dc; asc H ;; 5: len 4; hex 8005a464; asc d;; 6: len 4; hex 80000000; asc ;; 7: len 4; hex 8000028a; asc ;; 8: len 8; hex 800012411a1c3977; asc A 9w;; 9: len 4; hex 8001f314; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1265 page no 1246 n bits 328 index `PRIMARY` of table `eviltwin_0921`.`alltime_user_social_status` trx id 0 2913059 lock_mode X locks rec but not gap waiting Record lock, heap no 194 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 8; hex 800000000002fe41; asc A;; 1: len 6; hex 0000002c734c; asc ,sL;; 2: len 7; hex 000000003f0bd5; asc ? ;; 3: len 4; hex 80004b6a; asc Kj;; 4: len 4; hex 800042a4; asc B ;; 5: len 4; hex 80047b14; asc { ;; 6: len 4; hex 80000000; asc ;; 7: len 4; hex 800002ed; asc ;; 8: len 8; hex 800012411a1c1ce2; asc A ;; 9: len 4; hex 8000de2d; asc -;;
*** WE ROLL BACK TRANSACTION (1)