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

8月 24, 2014 |

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);

然后打开两个连接, (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;

T2:

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;

<-- 死锁 (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:

Comments are closed.