MySQL之死锁
前言
首先我们先看看死锁产生的四个必要条件:
- 互斥:一个资源只能被一个进程(事务)使用;
- 请求与条件保持:一个进程(事务)在等待请求的资源过程中,自身已经占有的资源不可释放;
- 不剥夺条件:进程(事务)在运行活跃时候,已经获得的资源未使用完之前,不可强行剥夺;
- 循环等待条件:若干个进程(事务)形成首尾相接的循环等待关系;
一般来说业务基本上就是对商品进行新增、修改和查询等操作,我们在对商品进行新增操作前,先通过select … for update操作进行查询,然后再进行相应的业务逻辑处理,所以当业务量很大时,就有可能出现死锁。
死锁的出现
环境:InNoDB引擎、可重复度隔离级别
创建表:
CREATE TABLE user (
`id` INT(11) AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
插入数据:
上面的场景,我们看到两个事物的insert into操作都陷入了阻塞等待状态
为什么会出现死锁?
在InnoDB引擎中的可重复读级别中为了解决幻读的问题,引出了next-key锁,是记录锁和间隙锁的结合。
在可重复度级别中普通的select操作是不会加锁的,通过MVCC机制实现快照读。如果想要解决幻读问题,就需要给查询语句加行锁:
//加排它锁
begin;
select ... for update;
commit;
//共享锁
begin;
select ... lock in share mode;
commit;
行锁只有在事务提交后才会释放,并不是语句执行完就释放了。
所以上面,当执行完select * from user where id > 23 for update;
通过 **select * from performance_schema.data_locks\G;**查看当前加上的锁是行锁,X(排它锁)类型的间隙锁(GAP),加锁的范围(20, +∞ )
next-key锁的加锁规则请看MySQL行锁加锁规则。
所以当事务2执行插入语句是,会在插入的间隙获取插入意向锁,插入意向锁和间隙锁会冲突,所以当其他事务持有间隙锁时,需要等待间隙锁释放,但是间隙锁和间隙锁之间可以兼容,因此两个事物的select … for update操作不会冲突。导致另个事务的间隙锁都在等待对方对方事务间隙锁释放,从而造成循环等待,导致死锁。
如何避免死锁?
死锁产生的四个必要条件:互斥、占有且等待、不可强抢占用、循环等待。只要系统发生死锁,这四个条件必然成立,只要打破一个就会解决死锁。
在数据库层面,设计两种打破死锁的循环等待条件来破坏死锁状态:
- 设置事务等待锁的超时时间:当一个事务死锁时间超过设置的等待时间参数(innodb_lock_wait_timeout)默认是50秒,这个事务发生回滚,释放锁,另一个事务就可以执行了。
- 开启死锁主动检测:主动检测到死锁发生,主动回滚一个事物,让其它事物得以执行。将参数innodb_deadlock_detect设置为on。
从业务的角度来看,插入前进行select为了保证不出现重复订单,当时也可以将不能重复的字段设置为唯一索引列,利用唯一性避免重复出现,但是插入时有可能会抛出异常。
就是这事,散会。