MySQLのInnoDBはトランザクションが使えたり、行ロックが使えたりして、データの整合性の点でMyISAMに比べて優れています。 業務系アプリになると、データの整合性が重視されることも多く、トランザクションを使うことも増えます。

今回、そのトランザクションを使っていて、思いもよらないクエリがきっかけで、テーブルにロックが掛かってハマりました。

テーブル構造はこのような感じ...。

CREATE TABLE `working` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `col` (`col`)
) ENGINE=InnoDB;

もう一個まったく同じテーブルを作ります。

CREATE TABLE `backup` LIKE `working`;

workingにデータを入れます。

INSERT INTO `working` (`col`) VALUES ('1'), ('2'), ('3');

SELECT * FROM `working`;

+----+-----+
| id | col |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
+----+-----+

ちなみに、分離レベルを見ておきます。

SELECT @@tx_isolation;

+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

さて、もう察しが付いているかもしれませんが、これからworkingからbackupにデータを移すクエリを実行したいと思います。

クライアント1

クライアント1は、BEGINしてトランザクションを開始し、INSERT INTO t SELECT ... FROM s WHERE...を使って、データコピーのクエリを実行します。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `backup` (`col`) SELECT `col` FROM `working` WHERE `col` = 3;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

この処理は一瞬で終わりますが、COMMITやROLLBACKせず、トランザクションはそのままにしておきます。

クライアント2

クライアント2では、普通にworkingにINSERTします。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `backup`;
Empty set (0.00 sec)

mysql> INSERT INTO `working` (`col`) VALUES ('4');

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

まず、backupテーブルはまだ空です。これはクライアント1がコミットせずトランザクションを維持しているからです。しかし、ここでworkへのINSERTが長く待たされます。挙句に、「Lock wait timeout exceeded」というエラーが出て、クライアント2のトランザクションが終了してしまいます。

一見すると、とても奇妙な挙動です。クライアント1は、サブクエリーでworkingテーブルにSELECTしているだけです。UPDATEやSELECT ... FOR UPDATEをかけているわけではないからです。

ところが、良く調べてみると、INSERT INTO SELECT FROMの形でSELECTされたテーブルにロックが掛かることがあるとマニュアルに書いてありました。

INSERT INTO T SELECT ... FROM S WHERE ... は T に挿入された各行に、ギャップロックなしの排他インデックスレコードロックを設定します。innodb_locks_unsafe_for_binlog が有効であるかトランザクション遮断レベルが READ COMMITTED である場合には、InnoDB は S での検索を一貫性読み取り (ロックなし) として行います。それ以外の場合、InnoDB は S から取得した行に共有ネクストキーロックを設定します。InnoDB は後者の場合にロックを設定する必要があります。バックアップからの前進復旧では、すべての SQL ステートメントはそれが元々行われたのとまったく同じ方法で実行されなければいけません。

これを読む限りでは、workingテーブルにネクストキーロックがかかってしまったようです。注目して欲しいのは、クライアント1のクエリの`col` = 3と、クライアント2の(`col`) VALUES ('4')です。ネクストキーロックの範囲や発動条件はよく調べていないのでなんとも言えませんが、colの数字が3と4で隣り合っています。これによってロックが発動したようです。

ためしに、下のように隣接していない場合はロックが掛かりません。

# クライアント1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `backup` (`col`) SELECT `col` FROM `working` WHERE `col` = 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

# クライアント2
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `working` (`col`) VALUES ('4');
Query OK, 1 row affected (0.00 sec)

また比較として、単なるSELECTやINSERTでは、ロックはかかりません。

# クライアント1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `working` (`col`) VALUES ('3');
Query OK, 1 row affected (0.00 sec)

# クライアント2
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `working` (`col`) VALUES ('4');
Query OK, 1 row affected (0.00 sec)

もし、以下の条件に当てはまる処理は、INSERT INTO SELECT FROMのサブクエリを独立させてPHPで結果をINSERTに渡すような実装にしたほうが良いかもしれません。

• workingは頻繁にINSERTされる。(そして待たされたり、タイムアウトしては困る。)
• INSERT INTO SELECT FROMのあるトランザクションが非常に長い。
• クライアント2のINSERTはクライアント1のトランザクションに全く関係ないデータである。

余談

今回はトランザクションにはまりましたが、ROLLBACKは便利そうです。大量のテストデータとテストパターンがあって、毎回DBをリストアしてテストしなおすような計画では、テストが完了するごとにROLLBACKすると良さそうです。一瞬でDBがもとに戻ります。