MySQL死锁与日志分析(2)-木庄网络博客

当前第2页返回上一页

3、怎样降低 innodb 死锁几率？

死锁在行锁及事务场景下很难完全消除，但可以通过表设计和SQL调整等措施减少锁冲突和死锁，包括：

尽量使用较低的隔离级别，比如如果发生了间隙锁，你可以把会话或者事务的事务隔离级别更改为 RC(read committed)级别来避免，但此时需要把 binlog_format 设置成 row 或者 mixed 格式

精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；

选择合理的事务大小，小事务发生锁冲突的几率也更小；

给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；

不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；

尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响；

不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；

对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

Case2：诡异的 Lock wait timeout

连续几天凌晨6点和早上8点都分别有一个任务失败，load data local infile 的时候报 Lock wait timeout exceeded try restarting transaction innodb 的 Java SQL 异常，和平台的同学沟通得知，这是我们自己的业务数据库的 Lock 时间太短或者锁冲突的问题。但是回头一想不应该啊？这不一直好好的吗？而且基本都是单表单任务，不存在多人冲突。

甭管谁的问题，那咱们还是先看自己的数据库有没有问题：

默认 lock 超时时间 50s，这个时间真心不短了，估计调了也没用，事实上确实死马当活马医的试了下没用。。。

而且这次 SHOW ENGINE INNODB STATUS\G 也没出现任何死锁信息，然后又将目光转向 MySQL-server 日志，希望能从日志里看一看那个时刻前后数据究竟在做什么操作。这里先简单的介绍下MySQL日志文件系统的组成：

(a) error 日志：记录启动、运行或停止 mysqld 时出现的问题，默认开启。
(b) general 日志：通用查询日志，记录所有语句和指令，开启数据库会有 5% 左右性能损失。
(c) binlog 日志：二进制格式，记录所有更改数据的语句，主要用于 slave 复制和数据恢复。
(d) slow 日志：记录所有执行时间超过 long_query_time 秒的查询或不使用索引的查询，默认关闭。
(e) Innodb日志：innodb redo log、undo log，用于恢复数据和撤销操作。

从上面的介绍可以看到，目前这个问题的日志可能在 d 和 b 中，看了下 d 中没有，那就只能开启 b 了，但 b 对数据库的性能有一定损耗，由于是全量日志，量非常巨大，所以开启一定要谨慎：

我这里只是每天在出问题的前后半小时开启下全量日志，结果没有发现任何 MySQL-client 请求到我们的业务数据库！该日志格式如下，记录了所有的连接与命令：

那问题基本确定了，客户端请求都没到我们这边就抛出了上述的异常，和平台方再三沟通确认下，最后平台方查证是因为在执行插入前他们需要先从 SQL task 表取出 SQL 和更新 task 状态，结果这张表由于在整点存在大量 insert 和 update 并发，导致部分 SQL 等待 lock 超时了。。。

MySQL 日志分析脚本

由于凌晨是数据仓库的业务高峰，很多问题都是在这个时候爆发，一些诡异的问题往往是过了这个村就没这个店了，白天无法复现。如何能捕获我们关心的日志，便于快速的定位问题，这个是重中之重，这里我写了个小脚本，crontab 部署，可以选择时间范围开启，每分钟采样一次日志，需要说明的是 general log 没事别轻易开启，否则对数据库性能损耗较大。