Scenarios that could cause deadlocks in MySQL

1 Overview

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

1. 真正的事务间循环等待。
2. 在进行死锁检测的过程中，如果InnoDB认为检测的代价过大（例如需要递归检查超过200个事务等），那么InnoDB放弃死锁检测，并认为死锁发生。

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

 

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的记录也被设置了共享读锁。

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

 

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会在重复的索引记录上设置一把共享读锁，最终导致了本例中的死锁。

评论

5 楼 trydofor 2011-04-01  

javaeye，以下变成了iteye。 眼神变了

4 楼 trydofor 2011-04-01  

鬼子说，要有PostgreSQL

3 楼 fujohnwang 2011-04-01  

whitesock 写道

目前对Postgresql的理解仅限于4，5年前credit用的那个前端申请库。当时配置的slony貌似不少问题。VoltDB倒是可以关注一下。

pgsql９之后有质的飞跃

2 楼 whitesock 2011-04-01  

目前对Postgresql的理解仅限于4，5年前credit用的那个前端申请库。当时配置的slony貌似不少问题。VoltDB倒是可以关注一下。

1 楼 fujohnwang 2011-04-01  

有机会看推广一下Pgsql如何？呵呵
太长没看完