如何查看MySQL数据库的死锁信息

如何查看MySQL数据库的死锁日志

创新互联公司是一家专业提供潍坊企业网站建设,专注与网站设计制作、网站建设、H5建站、小程序制作等业务。10年已为潍坊众多企业、政府机构等服务。创新互联专业网络公司优惠进行中。

1. 使用终端或命令提示符登录到MySQL，输入命令：mysql -h xxxx.xxx.xxx -P 3306 -u username -p

解释：xxxx.xxx.xxx是数据库IP地址，username是数据库用户名，输入命令后，会让你输入username对应的密码，就可以登录了

2. 如何查看MySQL数据库的死锁信息

在MySQL客户端下输入命令：

show engine innodb status \G;

3. 如何定位MySQL数据库的死锁信息

在打印出来的信息中找到“LATEST DETECTED DEADLOCK”一节内容，看图中红线

能否将死锁信息保存到日志中

在之前的版本，要查看死锁，你要show engine innodb status\G;

在MySQL5.6版本，在my点吸烟 f配置文件里，加入

innodb_print_all_deadlocks = 1

就可以把死锁信息打印到错误日志里。

下面是一个死锁演示：。。。。略

# more mysql5_6.err

121012 10:33:40 [Note]

/usr/local/mysql/bin/mysqld: ready for connections.

Version:

'5.6.6-m9-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server

（GPL）

InnoDB: transactions deadlock detected, dumping detailed

information.

121012 10:35:07

*** （1） TRANSACTION:

TRANSACTION 304904, ACTIVE 49 sec starting index read

mysql tables in use

1, locked 1

LOCK WAIT 3 lock struct（s）， heap size 320, 2 row lock（s），

undo log entries 1

MySQL thread id 1, OS thread handle 0x9106cb90, query

id 13 localhost root updating

update t2 set name='cc1' where id=3

*** （1） WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1201

page no 3 n bits 80 index `PRIMARY` of table `test`.`t2` trx id 304904 lock_mode

X locks rec but not gap waiting

Record lock, heap no 12 PHYSICAL RECORD:

n_fields 4; compact format; info bits 0

0: len 4; hex 80000003; asc

1: len 6; hex 00000004a709; asc

2: len 7; hex

09000002f401ca; asc

3: len 2; hex 6363; asc cc

*** （2）

TRANSACTION:

TRANSACTION 304905, ACTIVE 15 sec starting index read

mysql tables in use 1, locked 1

3 lock struct（s）， heap size 320, 2 row

lock（s）， undo log entries 1

MySQL thread id 2, OS thread handle

0x9103bb90, query id 14 localhost root updating

update t2 set name='bb1'

where id=2

*** （2） HOLDS THE LOCK（S）：

RECORD LOCKS space id 1201

page no 3 n bits 80 index `PRIMARY` of table `test`.`t2` trx id 304905 lock_mode

X locks rec but not gap

Record lock, heap no 12 PHYSICAL RECORD: n_fields

4; compact format; info bits 0

0: len 4; hex 80000003; asc

1: len 6; hex 00000004a709; asc

2: len 7; hex 09000002f401ca;

asc

3: len 2; hex 6363; asc cc

*** （2） WAITING FOR

THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1201 page no 3 n bits 80

index `PRIMARY` of table `test`.`t2` trx id 304905 lock_mode X locks rec but not

gap waiting

Record lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact

format; info bits 0

0: len 4; hex 80000002; asc

1: len 6;

hex 00000004a708; asc

2: len 7; hex 08000002522e5c; asc

R.\

3: len 2; hex 6262; asc bb

*** WE ROLL BACK TRANSACTION

（2）

参见手册：

解决一次mysql死锁问题

多线程开启事务处理。每个事务有多个update操作和一个insert操作（都在同一张表）。

默认隔离级别：Repeatable Read

只有hotel_id=2和hotel_id=11111的数据

逻辑删除原有数据

插入新的数据

根据现有数据情况，update的时候没有数据被更新

报了非常多一样的错

发现居然有死锁。

根据常识考虑，我每个线程（事务）更新的数据都不冲突，为什么会产生死锁？

带着这个问题，打印mysql最近一次的死锁信息

show engine innodb status

显示如下

发现事务1在等待一个锁

事务2也在等待一个锁

而且事物2持有了事物1需要的锁

关于锁的描述，出现了 lock_mode ， gap before rec ， insert intention 等字眼，看不懂说明了什么？说明我关于mysql的锁相关的知识储备还不够。那就开始调查mysql的锁相关知识。

通过搜索引擎，

锁的持有兼容程度如下表

那么再回到死锁日志，可以知道 :

事务1正在获取插入意向锁

事务2正在获取插入意向锁，持有排他gap锁

再看我们上面的锁兼容表格，可以知道， gap lock和insert intention lock是不兼容的

那么就可以推断出： 事务1持有gap lock，等待事务2的insert intention lock释放；事务2持有gap lock，等待事务1的insert intention lock释放，从而导致死锁。

那么新的问题就来了，事务1的intention lock 为什么会和事务2的gap lock 有交集，或者说，事务1要插入的数据的位置为什么会被事务2给锁住？

让我回顾一下gap lock的定义：

间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况

那为什么是gap lock，gap lock到底是基于什么逻辑锁的记录？发现自己相关的知识储备还不够。那就开始调查。

调查后发现，当当前索引是一个 普通索引 的时候，会加一个gap lock来防止幻读， 此gap lock 会锁住一个左开右闭的区间。 假设索引为xx_idx（xx_id），数据分布为1,4,6,8,12，当更新xx_id=9的时候，这个时候gap lock的锁定记录区间就是(8，12]，也就是锁住了xxid in (9,10,11,12)的数据，当有其他事务要插入xxid in (9,10,11,12)的数据时，就会处于等待获取锁的状态。

ps：当前索引不是普通索引，而且是唯一索引等其他情况，请参考下面资料

MySQL 加锁处理分析

回到我自己的案例中，重新屡一下事务1的执行过程：

因为普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

的关系 这段sql会获取一个gap lock，范围(2,11111]

这段sql会获取一个insert intention lock （waiting）

再看事务2的执行过程

因为普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

的关系 这段sql也会获取一个gap lock，范围也是(2,11111]（根据前面的知识，gap lock之间会互相兼容，可以一起持有锁的）

这段sql也会获取一个insert intention lock （waiting）

看到这里，基本也就破案了。因为普通索引的关系，事务1和事务2的gap lock的覆盖范围太广，导致其他事务无法插入数据。

重新梳理一下：

所以从结果来看，一堆事务被回滚，只有10007数据被更新成功

gap lock 导致了并发处理的死锁

在mysql默认的事务隔离级别（repeatable read）下，无法避免这种情况。只能把并发处理改成同步处理。或者从业务层面做处理。

共享锁、排他锁、意向共享、意向排他

record lock、gap lock、next key lock、insert intention lock

show engine innodb status

本文名称：mysql死锁怎么打印 mysql解决死锁的4种基本方法
URL分享：http://lswzjz.com/article/ddgeoog.html