LATEST DETECTED DEADLOCK
------------------------
130409  0:40:58
*** (1) TRANSACTION:
TRANSACTION 3D61D41F, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 43 lock struct(s), heap size 6960, 358 row lock(s), undo log entries 43
MySQL thread id 17241690, OS thread handle 0x7ffd3469a700, query id 860259163 localhost root update
#############
INSERT INTO `notification` (`other_grouped_notifications_count`, `user_id`, `notifiable_type`, `action_item`, `action_id`, `created_at`, `status`, `updated_at`) 
VALUES (0, 4442, 'MATCH', 'MATCH', 224716, 1365448255, 1, 1365448255)
#############
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 272207 n bits 1272 index `user_id` of table `notification` trx id 3D61D41F lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 69 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000115b; asc    [;;
 1: len 4; hex 0005e0bb; asc     ;;
-- 事務(wù)1欲插入數(shù)據(jù)user_id=4442，因此首先獲取了對應(yīng)主鍵(lower_bound,4443]范圍上的插入意向鎖，然后想要在輔助索引(lower_bound,4443]的范圍上加insert intention lock，但被阻塞，推斷這個范圍上已經(jīng)有了其他事務(wù)的行鎖
-- 事務(wù)1需要獲取2個插入意向鎖后才會開始插入操作，這兩個鎖的獲取是不可分割的
*** (2) TRANSACTION:
TRANSACTION 3D61C472, ACTIVE 15 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 17266704, OS thread handle 0x7ffd34b01700, query id 860250374 localhost root Updating
#############
UPDATE `notification` SET `status`=0 WHERE user_id = 4443 and status=1
#############
*** (2) HOLDS THE LOCK(S):
-- 事務(wù)2的update語句要更新user_id=4443的記錄，因此首先在user_id索引的(lower_bound,4443]范圍添加了X模式的next-key行鎖，事務(wù)1就是被這個next-key行鎖阻塞的
RECORD LOCKS space id 0 page no 272207 n bits 1272 index `user_id` of table `notification` trx id 3D61C472 lock_mode X
Record lock, heap no 69 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000115b; asc    [;;
 1: len 4; hex 0005e0bb; asc     ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
-- 當(dāng)事務(wù)2嘗試更新主鍵數(shù)據(jù)時要獲取user_id=4443對應(yīng)主鍵的行鎖，但是發(fā)現(xiàn)主鍵的(lower_bound,4443]范圍上已經(jīng)被事務(wù)1加了insert intention lock，因此被阻塞
-- 同樣事務(wù)2獲取輔助索引的next-key和主鍵的record鎖也是不可分割的，只有都獲取完畢才能進行update
RECORD LOCKS space id 0 page no 261029 n bits 248 index `PRIMARY` of table `notification` trx id 3D61C472 lock_mode X locks rec but not gap waiting
Record lock, heap no 161 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
 0: len 4; hex 0005e0bb; asc     ;;
 1: len 6; hex 00000c75178f; asc    u  ;;
 2: len 7; hex 480007c00c1d10; asc H      ;;
 3: len 4; hex 8000115b; asc    [;;
 4: len 8; hex 5245474953544552; asc REGISTER;;
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: len 4; hex d117dd91; asc     ;;
 9: len 4; hex d117dd91; asc     ;;
 10: len 1; hex 80; asc  ;;
 11: SQL NULL;
 12: SQL NULL;
 13: SQL NULL;
 14: SQL NULL;
 15: len 4; hex 80000000; asc     ;;

*** WE ROLL BACK TRANSACTION (2)