InnoDB死锁检测

1 Overview

    InnoDB自动检测死锁。如果死锁发生，那么InnoDB会回滚权重相对小的事务。实际上，InnoDB中存在以下两种类型的死锁：

1. 真正的事务间循环等待。
2. 在进行死锁检测的过程中，如果InnoDB认为检测的代价过大（例如需要递归检查超过200个事务等），那么InnoDB放弃死锁检测，并认为死锁发生。为解决200个事物的限制，可以对mysql源代码进行修改，修改文件lock0lock.c的变量 #define LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK 200

     本文中使用的MySQL版本: 5.1.42，InnoDB plugin版本: 1.0.6。

     其中为了检查当前事务发生的数目，可以参考 Innodb_row_lock_current_waits参数。

2 Scenarios

    如果死锁发生，除了应用程序的日志之外，最有价值的信息恐怕就是show innodb status的输出了，然而show innodb status的输出中死锁相关的信息并不完整（例如只记录导致死锁的最后两个事务，以及最后执行的两个SQL等）。    基于在日常工作中的经验，笔者总结了以下一定/可能会导致死锁的场景。

2.1 Scenario 1

    CREATE TABLE test(id INT PRIMARY KEY, name VARCHAR(10)) ENGINE=InnoDB;
    INSERT INTO test VALUES(1, ’1′), (2, ’2′);

    SET @@tx_isolation = ‘READ-COMMITTED’;

Session A	Session B
START TRANSACTION;	START TRANSACTION;
UPDATE test SET name = ’11′ WHERE id = 1;
	UPDATE test SET name = ’22′ WHERE id = 2;
UPDATE test SET name = ‘21′ WHERE id = 2;# BLOCKED
	UPDATE test SET name = ‘12′ WHERE id = 1;# DEADLOCK

    点评：这是最常见的死锁场景之一，解决方法就是resource ordering，即确保所有关联事务均以相同的顺序持有锁。

2.2 Scenario 2

    CREATE TABLE t (id INT PRIMARY KEY, count INT) ENGINE = InnoDB;
    INSERT INTO t VALUES(1, 1);

    SET @@tx_isolation = ‘READ-COMMITTED’;

Session A	Session B
START TRANSACTION;	START TRANSACTION;
SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
	SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE;
UPDATE t SET count = 2 WHERE id = 1;# BLOCKED
	UPDATE t SET count = 3 WHERE id = 1;# DEADLOCK

    点评：在这种场景下，resource ordering也无济于事，SELECT … LOCK IN SHARE MODE 调整为SELECT … FOR UPDATE即可。

2.3 Scenario 3

    CREATE TABLE parent(id int PRIMARY KEY, count INT) ENGINE=InnoDB;
    CREATE TABLE child(id int PRIMARY KEY, parent_id INT, FOREIGN KEY (parent_id) REFERENCES     parent(id)) ENGINE=InnoDB;
    INSERT INTO parent VALUES(1, 0);

    SET @@tx_isolation = ‘READ-COMMITTED’;

Session A	Session B
START TRANSACTION;	START TRANSACTION;
INSERT INTO child VALUES(1, 1);
	INSERT INTO child VALUES(2, 1);
UPDATE parent SET count = count + 1 WHERE id = 1;# BLOCKED
	UPDATE parent SET count = count + 1 WHERE id = 1;# DEADLOCK

    点评：在进行外键完整性检查时，InnoDB会在被检查的记录上设置一把共享读锁。本例中，在对child进行插入时，parent表中id为1的记录也被设置了共享读锁。

    需要注意的是，OpenSSO在登录时进行了类似的数据库操作，因此也存在潜在的死锁可能性。

2.4 Scenario 4

    CREATE TABLE parent(id int PRIMARY KEY, count INT) ENGINE=InnoDB;
    CREATE TABLE child(id int PRIMARY KEY, parent_id INT) ENGINE=InnoDB;
    INSERT INTO parent VALUES(1, 0);

    SET @@tx_isolation = ‘READ-COMMITTED’;

Session A	Session B	…	Session N
START TRANSACTION;	START TRANSACTION;		START TRANSACTION;
INSERT INTO child VALUES(1, 1);	INSERT INTO child VALUES(2, 1);		INSERT INTO child VALUES(n, 1);
UPDATE parent SET count = count + 1 WHERE id = 1;	UPDATE parent SET count = count + 1 WHERE id = 1;		UPDATE parent SET count = count + 1 WHERE id = 1;
Deadlock may occur in some sessions.

    点评：以上场景中，如果N>200，并且这些事务并发执行，那么可能会导致死锁，并且一部分事务被会滚。这是第二种类型死锁的典型场景。在show innodb status的输出中会包含如下内容：“TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH”。

    需要注意的是，在UPDATE parent SET count = count + 1 WHERE id = 1;语句之前进行过何种操作并不重要，关键是这些事务都并发更新同一条记录，最终导致InnoDB放弃了死锁检测。

2.5 Scenario 5

    CREATE TABLE test(id varchar(10) primary key, count int) ENGINE=InnoDB;
    INSERT INTO test values(‘ID00000001′, 0), (‘ID00000002′, 0), (‘ID00000003′, 0);

    SET @@tx_isolation = ‘READ-COMMITTED’;

Session A	Session B
START TRANSACTION;	START TRANSACTION;
update test inner join (select *, sleep(15) from test where id <= ‘ID00000002′) t on test.id = t.id set test.count = 1;# SLEEPING
	update test set count = 3 where id = ‘ID00000001′;# BLOCKED
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction # 15 × 2 （2 records selected）seconds later

    点评：由于InnoDB采用了MVCC，因此在通常情况下（非SERIALIZABLE事务隔离级别），普通的SELECT语句不会对查询结果集中的记录加锁，也不会被已有的锁阻塞住。但是，InnoDB会在update语句的select子句的查询结果集的每条记录上设置一把共享读锁。这是本例中导致死锁的原因。

    需要注意的是，本例中select子句中的sleep函数调用只是为了更容易地重现死锁，并没有其它特殊作用。 针对这种类型的死锁，最好还是调整业务逻辑，正如本例中Session A的update语句试图有条件的更新test表的部分记录，应该调整该update语句以避免死锁。

2.6 Scenario 6

    CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(10)) ENGINE = InnoDB;

    SET @@tx_isolation = ‘SERIALIZABLE’;

Session A	Session B
START TRANSACTION;	START TRANSACTION;
select * from t1 where id = 1;
	select * from t1 where id = 1;
insert into test values(1, ‘a’);# BLOCKED
	insert into test values(1, ‘a’);# DEADLOCK

    点评：在SERIALIZABLE事务隔离级别下，如果autocommit被禁用，那么InnoDB会隐式地将普通的SELECT语句转换为SELECT … LOCK IN SHARE MODE，即在查询结果集的每条记录上设置共享读锁。

    需要注意的是，如果完全采用默认配置，那么Spring Batch 2.0.0会在SERIALIZABLE事务隔离级别下进行类似的数据库操作，最终可能导致死锁。如果使用MySQL存储Spring Batch相关的数据库表，那么需要调整Spring Batch的配置，将事务隔离级别从默认的SERIALIZABLE调整为REPEATABLE READ。

2.7 Scenario 7

    CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

    SET @@tx_isolation = ‘READ-COMMITTED’;

Session A	Session B	Session C
START TRANSACTION;	START TRANSACTION;	START TRANSACTION;
INSERT INTO t1 VALUES(1);
	INSERT INTO t1 VALUES(1); # BLOCKED	INSERT INTO t1 VALUES(1); # BLOCKED
ROLLBACK;
	Deadlock occurs in either Session B or Session C

    点评：这种类型的死锁不常见，如果发生duplicate-key error，那么InnoDB会在重复的索引记录上设置一把共享读锁，最终导致了本例中的死锁。