今回はMySQL InnoDBで実装されているネクストキーロックの落とし穴をデッドロックと絡めて説明したいと思います。
評価環境のMySQLのバージョンは5.1.39、トランザクション分離レベルはデフォルトのREPEATABLE READ、InnoDB Pluginは未導入にて今回は行いました。
下記のテーブル、データにて実施します。
mysql> SHOW CREATE TABLE HOGE\G; *************************** 1. row *************************** Table: HOGE Create Table: CREATE TABLE `HOGE` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `COL_INT` int(11) NOT NULL, `COL_CHAR` char(1) NOT NULL, PRIMARY KEY (`ID`), KEY `COL_INT` (`COL_INT`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> SELECT * FROM HOGE; +----+---------+----------+ | ID | COL_INT | COL_CHAR | +----+---------+----------+ | 1 | 35 | A | | 2 | 40 | B | | 3 | 20 | C | | 4 | 25 | D | | 5 | 50 | A | | 6 | 10 | B | | 7 | 45 | C | | 8 | 15 | D | | 9 | 30 | A | +----+---------+----------+ 9 rows in set (0.00 sec)テストケース１
テーブル「HOGE」に存在するレコードをDELETEした後に新規レコードをテーブル「HOGE」に INSERT
トランザクションA トランザクションB mysql> DELETE FROM HOGE WHERE COL_INT = 25;
Query OK, 1 row affected (0.00 sec)mysql> DELETE FROM HOGE WHERE COL_INT = 45;
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO HOGE (COL_INT,COL_CHAR) VALUES ( 44,’B’);
WAIT状態mysql> INSERT INTO HOGE (COL_INT,COL_CHAR) VALUES ( 29,’C’);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionトランザクションBがエラーとなったことを受け
Query OK, 1 row affected (7.51 sec)テストケース２
テーブル「HOGE」に存在しないレコードをDELETEした後に新規レコードをテーブル「HOGE」に INSERT
トランザクションA トランザクションB mysql> DELETE FROM HOGE WHERE COL_INT = 28;
Query OK, 1 row affected (0.00 sec)mysql> DELETE FROM HOGE WHERE COL_INT = 43;
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO HOGE (COL_INT,COL_CHAR) VALUES ( 44,’B’);
WAIT状態mysql> INSERT INTO HOGE (COL_INT,COL_CHAR) VALUES ( 29,’C’);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionトランザクションBがエラーとなったことを受け
Query OK, 1 row affected (11.76 sec)テストケース３
テストケース１と同じくテーブル「HOGE」に存在するレコードをDELETEした後に新規レコードをテーブル「HOGE」に INSERT
但し、COL_INTを非UNIQUEからUNIQUEに変更
mysql> SHOW CREATE TABLE HOGE\G; *************************** 1. row *************************** Table: HOGE Create Table: CREATE TABLE `HOGE` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `COL_INT` int(11) NOT NULL, `COL_CHAR` char(1) NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `COL_INT` (`COL_INT`) ★UNIQUEに変更 ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
トランザクションA トランザクションB mysql> DELETE FROM HOGE WHERE COL_INT = 25;
Query OK, 1 row affected (0.00 sec)mysql> DELETE FROM HOGE WHERE COL_INT = 45;
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO HOGE (COL_INT,COL_CHAR) VALUES ( 44,’B’);
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO HOGE (COL_INT,COL_CHAR) VALUES ( 29,’C’);
Query OK, 1 row affected (0.00 sec)エラーとならず正常終了 エラーとならず正常終了 まとめ
非UNIQUE、UNIQUE INDEXの違いだけでも動きが違いました。
また、トランザクション分離レベルがREAD COMMITTEDの場合ですとテストケース１、２共にトランザクション間で干渉は起こらず正常終了します。
このようにINDEXの種類やトランザクション分離レベル、今回は実施していませんがデータの並び等のその他の要素によっても動きは違ってきます。
今回のような単一行単位でのDELETE&INSERTのようなアプリケーションはどこにでもあると思います。
まずは複数のDML(Data Manipulation Language)でトランザクションを構成する場合にこういう動きがあることを念頭に置くことが大事だと思います。
ECナビ エンジニアブログ : MySQL InnoDBでのネクストキーロックの落とし穴
「ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction」というエラーが起きたときのメモ
