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會在重複的索引記錄上設定一把共用讀鎖，最終導致了本例中的死結。