mysql 死锁和死锁检测的实现

何艳娥

1、死锁的定义

当mysql请求发生并发时，不同线程执行的事务操作需要获取相同资源的锁，涉及的线程都在等待别的线程释放锁，几个线程都进入无限等待的状态时，就出现死锁了。

2、锁等待的最大时长

当出现死锁时，事务会一直等待，直到时间达到innodb_lock_wait_timeout设置的值，默认值是50秒。

3、死锁检测

可以设置innodb_deadlock_detect=on 来开启死锁检测。死锁检测在发生死锁的时候，能够快速发现并进行处理，回滚并重新启动。但是死锁检测会比较好资源。当每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作可能就是 100 万的量级。虽然最终检测的结果可能没有死锁，但是这期间要消耗大量的 CPU 资源。

4、innodb死锁的监控和查看

innodb引擎，查看死锁信息，可以通过以下两种方式：
查看

1. SHOW ENGINE INNODB STATUS

复制代码

命令的输出：

1. SHOW ENGINE INNODB STATUS;

复制代码

这将显示InnoDB的当前状态，包括最近的死锁信息（如果发生过的话）。死锁信息包括受影响的事务、相关的SQL语句、锁等待信息等。
借助错误日志：
如果启用了InnoDB监控(

1. innodb_print_all_deadlocks

复制代码

)，所有的死锁信息都会被记录到MySQL的错误日志文件中。要启用该功能，你可以在MySQL配置文件中设置：

1. [mysqld] innodb_print_all_deadlocks=1

复制代码

然后重启MySQL服务。注意，频繁的死锁可能导致日志文件迅速膨胀，因此在生产环境中应谨慎使用。

5、防止死锁的一些策略

虽然不能完全避免死锁，但可以采取一些措施来最小化其发生的频率：
保持一致的锁定顺序：确保所有的事务按照相同的顺序请求锁。
使用锁超时：通过

1. innodb_lock_wait_timeout

复制代码

设置合理的锁等待超时时间。
尽量减少事务大小：大事务更容易导致死锁。
快速提交事务：进行一系列相关更改后，应立即提交事务以减少冲突风险。特别是要避免在mysql会话中长时间保持未提交的事务。
使用较低隔离级别的锁定读取：若使用了

1. SELECT ... FOR UPDATE

复制代码

或

1. SELECT ... FOR SHARE

复制代码

，可以考虑采用

1. READ COMMITTED

复制代码

这样更低的隔离级别。
尽量使用索引：通过确保SQL语句使用到索引以减少锁的范围。
减少锁的使用：如果允许SELECT返回非最新数据，则无需在其上添加

1. FOR UPDATE

复制代码

或

1. FOR SHARE

复制代码

子句。

1. READ COMMITTED

复制代码

隔离级别在此场景下非常有效。
使用表级锁来串行化事务：如所有其他方法都无效，可以使用表级锁。正确地使用

1. LOCK TABLES

复制代码

应先设置

1. SET autocommit = 0

复制代码

开启事务，然后再执行

1. LOCK TABLES

复制代码

，并在显式提交事务之前不要解锁。例如：

1. SET autocommit=0;
   
2. LOCK TABLES t1 WRITE, t2 READ, ...;
   
3. ... 在这里对表 t1 和 t2 进行操作 ...
   
4. COMMIT;
   
5. UNLOCK TABLES;

复制代码

表级锁可以阻止表的并发更新，避免死锁，但代价是减少系统忙碌时的响应能力。

• 分析和优化查询：分析查询性能，优化长时间运行的查询以减少它们持有锁的时间。
  
• 创建“信号量”表来序列化事务：在事务开始前，它首先尝试更新信号量表中的唯一记录。如果某个事务已经锁定了这条记录以执行更新，其他事务必须等待直到该记录被解锁才能继续，从而实现强制的顺序执行。这样做确保了每次只有一个事务可以执行更新操作，防止了多个事务同时运行，可能导致死锁的情况发生。
  

适当设计和调优数据库操作，特别是确保正确使用事务，可以显著减少死锁的发生。
到此这篇关于mysql 死锁和死锁检测的实现的文章就介绍到这了,更多相关mysql 死锁和死锁检测内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

来源:https://www.jb51.net/database/326817ahm.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！