wisdomone1
作者wisdomone12019-11-13 11:37
软件架构设计师, 东华

避免或最小化mysql死锁deadlock一些方法

字数 1946阅读 4388评论 0赞 3

前言

数据库如果死锁过多,对数据库的并发访问会造成比较严重影响,甚至严重时会造成整个数据库无法访问,业务系统停滞,对于企业信誉有不良影响。虽然mysql死锁检测机制定时会扫描是否产生死锁,并根据相关算法选择某个影响最小的事务进行回滚。死锁产生有很多因素。如何减少不必要的死锁,保持数据库运行在一下合理的性能范围内,是我们数据从业者必须考虑的重要问题。

数据库死锁是数据库内部的一种底层处理机制,为了提升数据库应对并发资源访问的能力,减少资源访问冲突。伴随企业业务体量不断扩张及变更数据访问逻辑也会产生相应调整,在迭代期间,难免某些业务模块的设计会产生资源的交叉访问,产生死锁的可能性也会增加。所以调整业务访问逻辑重构业务模型也是减少死锁一种重要方式。

某些mysql数据库版本由于本身存在代码逻辑或引擎设计,在一些特殊情况之下,也会产生死锁。当然一般情况下不会出现。基本这种情况是数据库特定版本出现bug。最好解决方法就是选择一个稳定的mysql版本,并且在上线之前进行广泛压力及性能测试。

数据库死锁产生的场景非常多。下面依次罗列出来,供大家参考。

  • 执行show engine innodb status\G数据库了解最新的死锁信息

如果想全面捕获数据库运行期间的所有死锁,可以配置系统变量

innodb_ print_all_deadlocks,对应信息在错误日志中

  • 一般应用系统会配置重试事务功能
执行事务不成功时(包括死锁事务),会再次重试事务。请与应用方同事确认。
  • 尽量让你的事务小而短

因为只有事务小,产生事务冲突的可能就会少,自然死锁也就少了。比如:在

OLTP中尽量少用全表扫描,多用主键约束或索引,减少扫描数据的可能。

  • 对于数据库的一些操作,避免中间停顿
比如:事务包括大量的insert,delete,update。注意尽量马上提交,不要让事务运行时间过久
  • 假如你的事务采用select for update或者select lock in shared mode这

种锁表读,强烈建议采用事务隔离级别read-committed.

mysql 5.6或mysql 6.7事务隔离级别是repeatable read,这种隔离级别要高于read-commited, 建议进行调整。
  • 如果你的事务包括非常复杂的DML处理逻辑,尽量把它们封装在一个函数或

    存储 过程中 。不要在应用系统编写DML语句。

试想一下,你的应用系统在不同的代码地方,使用不同顺序的DML语句。产生死锁的可能会大大增加。而采用统一的函数或存储过程,它们访问资源的顺序是相同,产生死锁的几率会大大减少。
  • 特别是对于一些核心业务的表,一定要设计或构建索引
对于核心表构建索引,有几个原则:一为统一评估不同SQL查询条件,优选频度高 的SQL查询构建索引。二为索引列一定要选择性好。当然,对于一些超大表,也可以考虑分区表或分区索引
  • 尽量在业务中减少使用不必要的锁

比如:如果你的数据库事务允许从旧版本读取数据,不要使用select for update或者select lock in shared mode。在这种情形下,可以采用事务隔离级别read-committed,因为每个事务的一致性读可以获取到最新的数据版本

(注:是最新的数据一致性版本)

  • 如果上述的一系列方法效果仍旧不明显,可以采用表级锁

代码示例

set autocommit=0;

lock tables t1 write,t2 read,...;

具体事务操作代码

commit;

unlock table;

这样虽然最大程度减少了死锁,但基本上并发就没有了哟 。

表锁的锁定粒度或范围更 大,整个表被一个事务锁住了,其它事务就只能等待了,不会产生死锁了.业务的吞吐量会显少减少。金融系统行业,产生的后果就是交易量明显下降所以是最后的方法,一般不要这样使用。一种这种方法,多用于mysqldump或xtrabackup备份
  • mysql还提供一种方法,可以创建一个信号表,此表只包括1条记录。
具体实现就是让每个数据库事务必须先要更新此表。通过这种方法,实现顺序访问资 源。减少死锁。

小结

  • 数据库死锁的认识,必须要大量经历工作案例,才会有更进一步的了解。本文只是普及一些知识点及概念
  • 事务隔离级别和数据库死锁有密切相关的关系,需要大家全面理解,也再次说明,数据库死锁是一个涵盖面极广的技术概念,它由事务,锁,事务隔离级别,应用设计逻辑等组成。必须全部掌握,才能对于死锁有充分的理解
  • 表锁尽量不要用,可以思考一下,表锁一般用于什么业务场景下,需要思考表锁和行锁的差别在哪儿。
  • 为何会产生不同的事务隔离级别,它们的基本概念是什么,各自解决什么问题,如何为自己运维的数据库选择合适的事务隔离级别,这是一个好问题。

如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!

3

添加新评论0 条评论

Ctrl+Enter 发表

作者其他文章

相关文章

相关问题

相关资料

X社区推广