mysql 原理 ~ 死锁问题

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

一锁
1 锁的定义
1 按照宏观角度
共享锁【S锁】
又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
排他锁【X锁】
又称写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。
2 按照微观角度
recrod lock 行锁锁定相关行
gap lock 间隙锁锁定相关间隙,目的为了防止幻读的发生,阻止insert操作
Next-Key(record lock+gap lock)
二死锁
1 死锁的定义
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时,时，就会导致这几个线程都进入无限等待的状态,称之为死锁,死锁会回滚成本较高的事务,执行成本较低的事务
2 死锁检测参数,默认开启
innodb_deadlock_detect=ON
3 死锁记录日志
show engine innodb status
LATEST DETECTED DEADLOCK
事务1
具体的sql语句
WAITING FOR THIS LOCK TO BE GRANTED//等待被授予的锁
包括(table,index,wait for lock,no 923 n bits(类似这种锁定的具体数据页))
事务2
具体的sql语句
HOLDS THE LOCK(S) //拥有的锁
包括(table,index,hold lock,no 923 n bits(类似这种锁定的具体数据页))
WAITING FOR THIS LOCK TO BE GRANTED//等待被授予的锁
包括(table,index,wait for lock)

三死锁的分析
1 基础分析
死锁问题分为两种,一种是必然会发生的死锁场景,不过在业务研发上很少见一种是高并发导致的死锁场景,在业务研发上很常见,也是死锁发生的最主要场景
2 基础知识
1 mysql的加锁方式如果sql语句本身条件是主键,那么会按照主键加行锁如果sql语句本身是辅助索引,那么会先给辅助索引加锁,再给辅助索引对应的主键加锁,同时还有gap lock范围锁
2 mysql的加锁顺序如果多个事务内的多个操作同时对同一资源申请加锁,是根据事务内操作申请先后有序申请的,比如事务1申请X锁->事务2申请X锁->事务1又申请X锁
3 获取相关分析
1 是2个事务等待被授予的锁和具体的sql语句和表结构
2 是全部的事务操作语句,因为死锁日志记录并不包含全部的事务操作,只会记录发生死锁的最近的两个sql操作
3 sql语句的explain 一定要根据sql语句的explain进行具体分析,不同的索引扫描行数是不同的,而mysql是逐行扫描加锁的
这里说一个update语句的加锁顺序
当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁 (current read-> for update)。待MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录(update -> X 锁)。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。因此，Update操作内部，就包含了一个当前读。同理，Delete操作也一样。
四死锁的案例
场景 1
1 insert并发导致的死锁
事务1 insert into value(1) 事务1 rollback
事务2 insert into value(1)
事务3 insert into value(1)
2 死锁日志分析
1 insert在进行唯一值检测的时候会加一个唯一性Insert Intention锁,这个其实是(S)锁,对发生冲突的事务X锁,当事务1回滚后事务2 和3 都申请了S锁,后续需要申请了X锁,但是由于事务2和3都拥有S锁,互相等待不释放,导致了死锁,1个事务回滚释放了S锁,另一个事物申请成功
2 插入意向锁需要加的锁依次为 S NK, X IK, X RK
3 锁等待两个事务都在等待 insert intention lock 锁,也即是2阶段的IK锁
场景 2
1 update并发导致的死锁
事务 1 update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)
事务 2 update tab_test set state=1067,time=now () where id in (9921180)
2 死锁日志分析
1 加锁可能应用在主键或者辅助索引上,此问题场景于高并发下的update导致的死锁,有几率会出现
2 造成死锁的原因是由于事务1 在执行期间先在辅助索引加锁,再加主键事务2 先加主键先在主键上加锁,再在辅助索引加锁,最后造成互相持有资源不释放
场景 3
1 混合事务导致的死锁
事务 1 delete from table_1 where id=1 事务2 update table_2 set message='aa' where token='aaa'
update table_2 set message='aa' where token='aaa' delete from table_1 where id=1
2 死锁日志分析
1 mysql锁申请是有序的
2 事务1 delete语句先拿到ID=1的行锁需要获取token的辅助索引事务2拥有token的辅助索引需要获取ID=1的行锁互相等待持有不释放
五解决死锁问题的通用方法
1 并发insert导致的死锁(单事务)
1 减少唯一值检测
2 降低重复值插入
3 降低插入频率
2 并发update导致的死锁 (单事务)
1 尽量采用主键进行更新
2 降低更新频率
3 混合事务导致的死锁 (混合事务)
1 尽量采用主键进行操作
2 分析事务内加锁顺序,改造程序,调整业务逻辑
3 降低事务操作频率
4 特殊操作导致的死锁
1 insert into select from 在目标表低峰业务期做
2 pt-osc同样可能导致死锁, 在目标表低峰业务期做