mysql如何避免死锁_MySQL死锁

有人在stackoverflow上发了一个死锁的信息，尝试直接解析此类信息对分析高并发下的SQL卡慢会有帮助因此尝试自己解析。

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]范围上的插入意向锁，然后想要在辅助索引(lower_bound,4443]的范围上加insert intention lock，但被阻塞，推断这个范围上已经有了其他事务的行锁

-- 事务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):

-- 事务2的update语句要更新user_id=4443的记录，因此首先在user_id索引的(lower_bound,4443]范围添加了X模式的next-key行锁，事务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:

-- 当事务2尝试更新主键数据时要获取user_id=4443对应主键的行锁，但是发现主键的(lower_bound,4443]范围上已经被事务1加了insert intention lock，因此被阻塞

-- 同样事务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)

所以这个死锁的出现就很容易理解了，事务1先获取了4442位置主键的插入意向锁，在获取辅助索引上的插入意向锁时被事务2 update语句的next-key行锁阻塞导致插入意向锁获取失败，而事务2的update获取了索引的next-key行锁后尝试更新主键(即在主键上加非gap行锁)却被事务1的插入意向锁阻塞。

两个事务都不能放弃自己已有的资源，都请求与对方不兼容的锁，不可剥夺且形成环路等待因此死锁。

这个死锁的根源就在于事务2的update语句持续的时间过长，导致后继insert语句卡死。

四、如何避免死锁？

但是内容有点多，我还是习惯用几句话总结下：

1、尽可能优化SQL的查询性能使得事务尽可能的短小。

2、如果不介意幻读可以使用read committed隔离级别以禁止范围锁。

3、如果前两者都做不到或者SQL优化的空间比较小，那么尽量分表分库，通过增加资源(或者叫分散资源)减少资源冲突的几率。

五、总结：

由于mysql innodb特殊的行锁机制，死锁通常都是涉及到插入意向锁和next-key锁的，因为这两个锁是范围锁，范围锁设计的目的就是为避免幻读，这会锁定一些自己不需要操作的记录。

不过在mysql中死锁从来都不是大问题，死锁通常都是数据库卡慢的果，而非因。而且由于数据库中普遍存在的死锁查杀机制，死锁产生后会很快被查杀。

真正可能引发数据库性能问题的，是高并发下的长事务，这种事务会导致undo等资源的争用，会占用binlog的提交队列导致后继事务处于commit阶段无法提交，即便强制kill也会引发长时间的rollback操作。

因此高并发下的长事务和低性能SQL才是死锁的主因，因为他们慢且作为一个整体在完成之前不会释放资源产生环路等待。