mysql holdlock_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 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同样可能导致死锁, 在目标表低峰业务期做

六 总结

1 死锁问题经常爆发于高并发场景下,所以业务场景要考虑

2 不建议根据区分度较低的辅助索引值进行事务操作

3  排查死锁问题最好能模拟死锁场景问题

4  请注意 一定要依靠死锁日志打印进行锁的判断,explain出现的rows并不一定代表锁定的行

七 补充

1 高并发场景下多事务加锁的顺序可能会有变化,针对于事务内的混和操作

2 锁的申请是按照申请顺序的,并不是按照事务分类