MySQL DEADLOCK

原创已于 2024-11-28 19:18:35 修改 · 498 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #deadlock #死锁 #锁

于 2019-07-24 19:58:45 首次发布

DB 专栏收录该内容

8 篇文章

订阅专栏

本文深入探讨MySQL中的锁机制，包括不同类型的锁、如何诊断及解决死锁问题，并提供了一些具体的配置建议和最佳实践。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参考

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

Mysql锁参数

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如何处理死锁

等待,直到超时(Innodb_lock_wait_timeout默认50s也是事务超时事件)
开启死锁检测主动回滚一条uodo长度小的事务(检测比较费性能，如果业务确保不会死锁可关闭)

锁兼容

相同区间的区间锁可以共存但是区间锁和插入意向锁不能共存

案例

案例1:先删除后插入
在这里插入图片描述
案例2:先删除后插入删除参数值为null会全表扫描锁

案例3:
1.先更新A再更新B
2.先更新B再更新A

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

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

并发插入自增id导致deadlock问题

当需要进行唯一性冲突检测时，需要先加一个S lock 所以会导致deadlock.
1.global变量中将innodb_autoinc_lock_mode 设置成2(但是产生的id将会不连续)
2.换成uuid程序生成(默认java或者mysql的uuid在毫秒级产生不同值是没问题的如果并发特别高可以考虑引入雪花算法处理这个问题)

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

删除不存在的记录会产生间隙锁
在这里插入图片描述
处理方案：先查询有没有记录在执行相关逻辑

处理inser into select导致deadlock问题

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