はじめまして、株式会社ECナビ システム本部 情報システムグループの三浦と申します。
私は主にデータベースの運用、管理を行っています。
ECナビでは様々なサービスを展開しています。そしてそれと同じ数と言っても良い程のデータベースが稼動しています。
リレーショナルデータベースがメインでサービスを支えていますが、それを補う形でキーバリューストア的なデータベースも多数存在しています。
メインで活躍しているリレーショナルデータベースは用途によりOracle、MySQL、Netezza等と多岐に渡っています。
今回は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)でトランザクションを構成する場合にこういう動きがあることを念頭に置くことが大事だと思います。
