高级InnoDB死锁故障排除,SHOW INNODB STATUS可没有告诉你这些 |

8月 24, 2014 | Nix.Huang

Show Engine Innodb Status[输出解读]：参考
mysql7以后，select * from sys.innodb_lock_waits更得心应手，参考

这个场景在mysql8上是不会死锁！！！！

InnoDB死锁的一个常见诱因是外键约束，以及这些外键约束从引用行获取的共享锁.

我之所以要详细地探讨这个主题是因为它们经常难以诊断，特别是你仅仅使用“SHOW ENGINE INNODB STATUS”的输出（看起来不是很直观的，因为人们希望它包含这些信息）。

演示一个死锁错误（以下内容来自SHOW ENGINE INNODB STATUS\G）:

------------------------
LATEST DETECTED DEADLOCK
------------------------
111109 20:10:03
*** (1) TRANSACTION:
TRANSACTION 65839, ACTIVE 19 sec, OS thread id 4264 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 1
MySQL thread id 3, query id 74 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1 WHERE id=1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6833 page no 3 n bits 72 index `PRIMARY` of table
`test`.`parent` trx id 65839 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
*** (2) TRANSACTION:
TRANSACTION 65838, ACTIVE 26 sec, OS thread id 768 starting index read,
thread declared inside InnoDB 500
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1024, 4 row lock(s), undo log entries 2
MySQL thread id 4, query id 75 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1 WHERE id=2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6833 page no 3 n bits 72 index `PRIMARY` of table
`test`.`parent` trx id 65838 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6833 page no 3 n bits 72 index `PRIMARY` of table
`test`.`parent` trx id 65838 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
*** WE ROLL BACK TRANSACTION (1)

现在我们的确可以看出是什么诱发了上面的死锁，但是这只是冰山一角而已。

让我们逐行解析 ..

首先，注意事务1已经运行了19秒，同时事务2运行了26秒，输出是将事务1作为新事务，事务2作为老事务（虽然不是很直观，最好意识到有这么回事）

我们能清晰地获取如下信息：

事务 #1 (“UPDATE parent .. WHERE id=1″) 等待来自事务 #2 (“UPDATE parent .. WHERE id=2″)的锁.

事务2占有一个锁（RECORD LOCKS space id 6833 page no 3 n bits 72 index, heap no 2）但是它等待（RECORD LOCKS space id 6833 page no 3 n bits 72 index, heap no 3 – held by TX #1）。

显然这两个更新语句本身并不会有什么冲突，一定是在事务的早期发生了什么。

以下是如何重现，供您参考：

CREATE TABLE `parent` (
`id` INT NOT NULL AUTO_INCREMENT,
`age` INT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `child` (
`id` INT NOT NULL AUTO_INCREMENT,
`age` INT NOT NULL,
`parent_id` INT NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB;

INSERT INTO parent (id, age) VALUES (1, 50);
INSERT INTO parent (id, age) VALUES (2, 60);
INSERT INTO child (id, age, parent_id) VALUES (1, 20, 1);
INSERT INTO child (id, age, parent_id) VALUES (2, 20, 1);

CREATE TABLE `parent` (

`id` INT NOT NULL AUTO_INCREMENT,

`age` INT NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

CREATE TABLE `child` (

`id` INT NOT NULL AUTO_INCREMENT,

`age` INT NOT NULL,

`parent_id` INT NOT NULL,

PRIMARY KEY (`id`),

KEY `parent_id` (`parent_id`),

CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)

) ENGINE=InnoDB;

INSERT INTO parent (id, age) VALUES (1, 50);

INSERT INTO parent (id, age) VALUES (2, 60);

INSERT INTO child (id, age, parent_id) VALUES (1, 20, 1);

INSERT INTO child (id, age, parent_id) VALUES (2, 20, 1);

然后打开两个连接， (T1 and T2 – 注意顺序和SHOW INNODB STATUS相反):

T1:

BEGIN;
UPDATE child SET age=age+1, parent_id=2 WHERE id=1;
UPDATE parent SET age=age+1 WHERE id=1;

BEGIN;

UPDATE child SET age=age+1, parent_id=2 WHERE id=1;

UPDATE parent SET age=age+1 WHERE id=1;

T2:

BEGIN;
UPDATE child SET age=age+1, parent_id=2 WHERE id=2;
UPDATE parent SET age=age+1 WHERE id=1;

BEGIN;

UPDATE child SET age=age+1, parent_id=2 WHERE id=2;

UPDATE parent SET age=age+1 WHERE id=1;

<-- T2 等待

T1:

UPDATE parent SET age=age+1 WHERE id=2;

1	UPDATE parent SET age=age+1 WHERE id=2;

<-- 死锁 (T1 完成, T2 回滚)
为什么会有这个死锁，原因在于外键。事实上，如果没有定义`parent`.`id`，外键是不会有死锁。
那到底是什么阻止了T2顺利执行呢？
以下是后台发生的事：->我们来看看后台发生了什么：
T1#1语句:

获取以下的两个锁:

child 的id=1 行的排他锁， <--由于 "UPDATE child" 语句本身

parent的id=2行的共享锁S <-- 由于parent.id外键

(注意共享锁意味着可以在该行继续获取共享锁，但是排他锁不行，这是本问题的关键).

T1#2语句:

获取如下锁:

在parent的id=1上的排他锁由于 "UPDATE parent" 语句本身

T2#1:

获取如下锁:

`child`的 id=2行的排他锁，缘自于"UPDATE child" 语句本身

`parent` 的 id=2行共享锁缘自外键（重申一下，这个是没有问题的因为它也是共享锁）

T2#2:

尝试获取以下锁，由于T1#2在parent的id=1行的排他锁而挂起。

T1#3:

尝试获取位于parent id=2行的排他锁，然而由于该行已经存在2个共享锁，且T1需要一个排他锁，冲突产生了。

通常（文中用的是normally）通常只需要等待共享锁释放（有很多地方我觉得都不需要用“的”，比如：共享锁的释放，尝试获取一下的锁中的“的”都不需要），但是由于T2因为等待T1释放其他的锁已经挂起，死锁！！！

最后T1赢得了竞争，T2回滚且释放了它占有的锁，T1执行完成。

简而言之，后台发生了很多事，但是你只能从SHOW ENGINE INNODB STATUS结果的LATEST DETECTED DEADLOCK节看到这些信息的一半（放后面更符合中国人说话习惯），如果（这是个条件句哦）我没有贴出SHOW CREATE TABLE status的结果（以及之前tx的声明），很难说的清究竟发生了什么。

当死锁问题发生时你如何找出到底是什么造成的？

SHOW INNODB STATUS只会告诉你这些，此外，一旦死锁发生，赢者继续执行，输者回滚。这意味着在输出中不会有更多这些事务的信息，因为它们都过去了。

因此，一般如果你遇到任何锁问题（死锁，锁超时，等待信号量挂起，等等），如果可能的话同时获取以下全部输出，这样可以给你最大的可能性来跟踪问题。

SHOW ENGINE INNODB STATUS

这一般非常好，但是它会被截断，它可能不会含有你需要的全部信息。

Enable InnoDB Lock Monitor

通过创建任意一个名为innodb_lock_monitor innodb 的表来启用InnoDB锁监视

在SHOW ENGINE INNODB STATUS 输出中会记录很多额外的锁信息，但是也可能被截断

Run "mysqladmin debug"

记录锁的所有信息到错误日志。非常棒！因为它记录了所有的锁（即（i.e不是比如的意思哦）没有被截断）且它记录锁表锁，锁表锁不会显示在in SHOW INNODB STATUS中，即使在innodb表中也不会出现，因为锁表锁是在innodb存储引擎以外的，唯一的不足是不便于阅读。我不会仅仅依靠它，因为和其他的详细信息联合使用能获取更多的帮助。

SHOW FULL PROCESSLIST

这将显示所有连接的线程，特别是说到隐藏锁时，它会呈现给用户（除了已经发起锁表指令的用户）的是连接已经建立，但是实际是空闲的。（when it comes to...是当提到..的时候的意思，另外，括号里面的部分补充User的）

Error log

当然，请经常检查错误日志，日志中记录了信息以及任何不同寻常的东西（更别提（not to mention是这个意思）"mysqladmin debug" and innodb_lock_monitor中的额外数据都会记录在错误日志中。）

SELECT * FROM INFORMATION_SCHEMA INNODB_LOCKS, INNODB_LOCK_WAITS, 和 INNODB_TRX

在MySQL 5.1及以后的版本可用（且必须使用innodb插件），由于大多数人将使用这个，他们包含非常棒的信息，事实上我已经写了一篇博文关于“使用Innodb的information schema 表来调试锁”，它含有关于使用这3张表来排除故障的更详尽的介绍

原文出处：

http://www.chriscalender.com/advanced-innodb-deadlock-troubleshooting-what-show-innodb-status-doesnt-tell-you-and-what-diagnostics-you-should-be-looking-at/

Posted in: MySQL practise | Tags: mysql死锁故障排除

知行合一，止于至善

高级InnoDB死锁故障排除,SHOW INNODB STATUS可没有告诉你这些

站内搜索