MySQL DEADLOCK

几个文章参考

浅谈MySQL的七种锁 link
MYSQL自增列引起死锁 link
MySQL innodb_autoinc_lock_mode设置 link
两个INSERT发生死锁原因剖析link
Innodb 锁的介绍 link
Innodb Next-Key Lock 浅谈 link
innodb 先删除再插入引发的死锁 link

show global variables where variable_name=‘xxx’;//查询mysqlglobal变量

show status like ‘innodb_row_lock%’;//可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况;
Innodb_row_lock_current_waits:当前正在等待锁的数量；
Innodb_row_lock_time:从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间长度；
Innodb_row_lock_waits:系统启动到现在总共等待的次数；
如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高
通过设置InnoDBMonitors来进一步观察发生锁冲突的表、数据行

show engine innodb status;//查看mysql的死锁日志

mysql5.6以后版本
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;
set GLOBAL innodb_status_output_locks=OFF;//关闭监控
show variables like ‘log_error’;
设 置监视器后，在SHOW INNODB STATUS的显示内容中，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等，便于进行进一步的分析和问题的确定。打开监视器以后，默认情况下每15秒会向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常的巨大，确认问题原因之后，要记得删除监控表以关闭监视器

MySQL如何处理死锁

• 等待 直到超时
• 开启死锁检测 主动回滚一条uodo长度小的事务

处理普通索引和无索引情况下当前读的导致deadlock问题

1.取消mysql的gap锁
global变量中将innodb_locks_unsafe_for_binlog设置成ON(但是在mysql默认隔离级别可重复读的隔离级别下会产生幻读)
2.程序先查询后删除(利用主键去删除)

处理自增id导致deadlock问题

1.global变量中将innodb_autoinc_lock_mode 设置成2(但是产生的id将会不连续)
2.换成uuid程序生成(默认java或者mysql的uuid在毫秒级产生不同值是没问题的如果并发特别高可以考虑引入 雪花算法处理这个问题)

处理id重复导致deadlock问题

1.global变量中将innodb_deadlock_detect(死锁检测)设置成OFF
解释：mysql默认会在检测到死锁时不会利用innodb_lock_wait_timeout(默认50s)这个值 而是检测到就会直接回滚两个中的一个事务 使用与秒杀高并发的场景
当需要进行唯一性冲突检测时，需要先加一个S lock 所以会导致deadlock.
mysql 事务锁超时时间 innodb_lock_wait_timeout
#查询全局等待事务锁超时时间
SHOW GLOBAL VARIABLES LIKE ‘innodb_lock_wait_timeout’;
#设置全局等待事务锁超时时间
SET GLOBAL innodb_lock_wait_timeout=100;
#查询当前会话等待事务锁超时时间
SHOW VARIABLES LIKE ‘innodb_lock_wait_timeout’;
2.采用雪花算法 业务控制不会出现多个事务对同一条或者多个记录进行更新操作

处理删除不存在记录导致deadlock问题

处理方案：先查询有没有记录在执行相关逻辑

处理inser into select导致deadlock问题

inser into settlement_cost_detail select xxx… FROM settlement_specialfee_up
WHERE work_order_code = ‘xxx’;
这样会锁住settlement_specialfee_up表中相关的记录(有可能是普通索引或者没有索引导致间隙锁锁住一些意想不到的记录)
在这个过程中夹杂这对某些记录的修改操作则很容易发生
处理方案：先查询出来相关记录组装好再进行插入