mysql死锁日志阅读

前言

在开发中，我们经常会遇到死锁问题，我们会查看数据库的死锁日志来查看死锁出现的原因，但是死锁日志如果不会阅读的话，可能就导致我们难以进行问题的排查

环境介绍

1、数据库场景

MySQL 5.6 引擎为innodb 事务隔离级别为RR

CREATE TABLE `ty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idxa` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4
insert into ty(a,b) values(2,3),(5,4),(6,7);

说明：一个ty表，主键id，自增，在a字段上加上一个普通索引

2、测试两事务并发

事务1：

• 首先删除 a 为5的记录
• 然后插入一条新记录

事务2：

• 首先删除a为5的记录
• 再次尝试删除a为5的记录，会出现死锁

T1	T2
begin;
delete from ty where a=5;	begin;
	delete from ty where a=5;
insert into ty(a,b) values(2,10);
	delete from ty where a=5;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

下面通过数据库的日志来进行分析：

3、日志分析

在数据库查看最近的死锁日志情况：

show engine innodb status

日志情况：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-09 22:34:13 7f78eab82700
*** (1) TRANSACTION: #事务1
TRANSACTION 462308399, ACTIVE 33 sec starting index read 
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 3525577, OS thread handle 0x7f896cc4b700, query id 780039657 localhost root updating
delete from ty where a=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308399 lock_mode X waiting

*** (2) TRANSACTION: #事务2
TRANSACTION 462308398, ACTIVE 61 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 2
MySQL thread id 3525490, OS thread handle 0x7f78eab82700, query id 780039714 localhost root update
insert into ty(a,b) values(2,10)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

日志字段分析介绍：

(1) TRANSACTION: #事务1

a、TRANSACTION 462308399, ACTIVE 33 sec starting index read
事务编号为 462308399 ，活跃33秒，starting index read 表示事务状态为根据索引读取数据。常见的其他状态:

• fetching rows 表示事务状态在row_search_for_mysql中被设置，表示正在查找记录。
• updating or deleting 表示事务已经真正进入了Update/delete的函数逻辑（row_update_for_mysql） thread
• declared inside InnoDB 说明事务已经进入innodb层。通常而言 不在innodb层的事务大部分是会被回滚的。

b、mysql tables in use 1, locked 1

use 1 说明当前的事务使用一个表。

locked 1 表示表上有一个表锁,对于DML语句为LOCK_IX

c、LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
LOCK WAIT表示正在等待锁

2 lock struct(s) 表示trx->trx_locks锁链表的长度为2，每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及auto_inc锁等。本案例中2locks 表示IX锁和 lock_mode X (Next-key lock)

heap size 360 表示事务分配的锁堆内存大小,一般没有什么具体的用处。

1 row lock(s)表示当前事务持有的行记录锁/gap 锁的个数。

d、delete from ty where a=5

表示事务1在执行的sql ，show engine innodb status 是查看不到完整的事务的sql 的，通常显示当前正在等待锁的sql。

e、 (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index idxa of table test.ty trx id 462308399 lock_mode X waiting

• lock type : 锁的类型 这里指RECORD LOCKS（记录锁）
• space id : 锁对象的space id
• page no ：事务锁定页的数量，若是表锁，该值为null
• index ：锁住的索引，这里指的是idxa索引
• table：要加锁的表，这里是test数据库的ty表
• trx id：事务id，这里是6FBF8
• lock_mode : 锁的模式，这边指的是加了一个Next-Key lock

事务2的log 分析一样，

f、(2) HOLDS THE LOCK(S):
RECORD LOCKS space id 219 page no 4 n bits 72 index idxa of table test.ty trx id 462308398 lock_mode X

显示了事务2 insert into ty(a,b) values(2,10) 持有了a=5 的Lock mode X |LOCK_GAP ，不过我们从日志里面看不到 事务2 执行的 delete from ty where a=5;这点也是造成DBA 仅仅根据日志难以分析死锁的问题的根本原因。

e、(2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index idxa of table test.ty trx id 462308398 lock_mode X locks gap before rec insert intention waiting

表示事务2的insert 语句正在等待插入意向锁 lock_mode X locks gap before rec insert intention waiting (LOCK_X + LOCK_REC_GAP )

需要注意的是锁组合，类似lock_mode X waiting ,lock_mode X,lock_mode X locks gap before rec insert intention waiting 是我们分析死锁的核心重点

首先我们要知道对于MySQL有两种常规锁模式

LOCK_S（读锁，共享锁）

LOCK_X（写锁，排它锁）

最容易理解的锁模式，读加共享锁，写加排它锁.

有如下几种锁的属性

• LOCK_REC_NOT_GAP （锁记录）
• LOCK_GAP （锁记录前的GAP）
• LOCK_ORDINARY （同时锁记录+记录前的GAP 。传说中的Next Key锁
• LOCK_INSERT_INTENTION（插入意向锁，其实是特殊的GAP锁）

lock_mode介绍：

• 单纯的record lock，显示的是：lock mode xx locks rec but not gap
• 单纯的gap lock， 显示的是：lock mode xx locks gap before rec
• 插入意向锁，显示的是lock_mode X locks gap before rec insert intention
• 若是gap+record，也就是next key lock，显示的是：lock mode X <空>
• waiting 表示锁等待