InnoDBでのデッドロック検出

概要

MySQLのInnoDBを使っている場合、トランザクション間でデッドロックが発生した場合は、デッドロックが検出されロールバックが行われるのだが、実際にデッドロックを発生させてみて挙動を確認した。

準備

適当な名前で空のデータベースを作成し、以下のSQLを実行。

-- テスト用テーブル
DROP TABLE IF EXISTS test;
CREATE TABLE test (
        id BIGINT NOT NULL AUTO_INCREMENT UNIQUE,
        data INT NOT NULL) ENGINE = InnoDB;
-- テスト用データ挿入
INSERT INTO test (id, data) VALUES (1, 1000), (2, 2000);

この状態でtestテーブルに以下のようなデータが格納されている。

testテーブルの内容

id	data
1	1000
2	2000

デッドロックを発生させてみる

Shellを二つ立ち上げ、それぞれからmysqlコマンドでデータベースに接続し、図１のような順番でSQLを実行してデッドロックを発生させる。

図1 SQLの実行順

トランザクションA,Bそれぞれのログは以下のとおり。

トランザクションAのログ

(A-1)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

(A-2)
mysql> UPDATE test SET data = data + 1 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(A-3)
mysql> UPDATE test SET data = data + 1 WHERE id = 2;
Query OK, 1 row affected (6.66 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql>

トランザクションBのログ

(B-1)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

(B-2)
mysql> UPDATE test SET data = data + 100 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(B-3)
mysql> UPDATE test SET data = data + 100 WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql>

(B-3)を実行するとデッドロックが発生し、すぐにデッドロックが検出され、トランザクションBがロールバックされたのが確認できる。トランザクションBがロールバックされてロックが解除されたので、トランザクションAは(A-3)でブロックしていたSQLが実行される。

デッドロック検出時にどちらのトランザクションがロールバックされるかは、http://dev.mysql.com/doc/refman/5.1/ja/innodb-deadlock-detection.html によると、「InnoDB は、トランザクションのサイズが挿入、更新、または削除された行数によって決定される小さいトランザクションを選んでロールバックしようとします。」とある。