mysql查询放弃使用读锁_MySQL死锁

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 ;;

-- 事务1插入数据后要更新插入user_id=4442的索引，于是想要在包含(lower_bound,4443]的范围上加insert intention lock，但是被阻塞，推断这个范围上已经有了其他非insert intention lock的行锁

-- 与此同时事务1已经在等待辅助索引的插入意向锁，说明主键的插入已经完成，此时在4442对应的主键上持有record锁。

*** (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):

-- 事务2的update语句要更新user_id=4443的记录，因此在查找阶段在对应的user_id索引的key=4443的位置添加了X模式的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:

-- 当事务2尝试更新主键数据时要获取user_id=4443对应的主键的next-key行锁，但是发现4442对应的主键已经被事务1加了record lock，因此被阻塞

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)