死锁排查监控

死锁监控

• 可以在任意库下创建innodb_lock_monitor表，这样innodb status信息会完整且每15s一次被记录到错误日志中。如：create table innodb_lock_monitor(a int)engine=innodb;，不需要记录到错误日志中时就删掉这个表即可。

部分回滚

1. 原因1：

show variables like 'autocommit';
+-----------------+---------+  
| Variable_name   | Value   |
|-----------------+---------|
| autocommit      | ON      |
+-----------------+---------+

在多个 update 或 insert 语句情况下，每执行完一条 SQL，innodb 就立即 commit 一次以持久化变更，同时释放锁，这也正是本例中死锁回滚事务后只有极个别语句失败的原因。

2. 原因2：

show VARIABLES LIKE 'innodb_rollback_on_timeout'
+----------------------------+---------+
| Variable_name              | Value   |
|----------------------------+---------|
| innodb_rollback_on_timeout | OFF     |
+----------------------------+---------+

解释：这个参数关闭或不存在的话遇到超时只回滚事务最后一个Query，打开的话事务遇到超时就回滚整个事务。

降低死锁的几率

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

• 尽量使用较低的隔离级别，比如如果发生了间隙锁，你可以把会话或者事务的事务隔离级别更改为 RC(read committed)级别来避免，但此时需要把 binlog_format 设置成 row 或者 mixed 格式
• 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
• 选择合理的事务大小，小事务发生锁冲突的几率也更小；
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响；
• 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

Lock wait time out（长时间的行锁等待, !=死锁）

查看数据库默认的锁等待超时时间

show variables like 'innodb_lock_wait_timeout';
+--------------------------+---------+
| Variable_name            |   Value |
|--------------------------+---------|
| innodb_lock_wait_timeout |      50 |
+--------------------------+---------+

数据库的日志文件组成

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