MySQLのデフォルトのトランザクション分離レベルは SELECT がスナップショットを参照する

MySQLInnoDB のデフォルトのトランザクション分離レベルは REPEATABLE READ で、ファジーリードしないようにトランザクション中の SELECT はトランザクションを開始してから最初の SELECT の時点のスナップショットから行われます。

そのため、次のやり方だと一意制約エラーが発生する可能性があります。

テーブル定義と初期データ

/* なんか関係ないテーブル */
CREATE TABLE t_dummy
(
  dummy_id INT NOT NULL PRIMARY KEY
);

/* カテゴリ */
CREATE TABLE t_category
(
  category_id INT NOT NULL PRIMARY KEY
);

/* カテゴリごとのデータ */
CREATE TABLE t_data
(
  category_id INT NOT NULL,
  data_id INT NOT NULL,
  PRIMARY KEY (category_id, data_id)
);

INSERT INTO t_category VALUES (100);

データ挿入のトランザクション

t_data に行を 挿入/更新/削除 するときは必ずその行の category_id に対応する t_category の行を FOR UPDATE でロックするという開発上のルールがあるものとします。このルールがあれば t_data に特別なロックを行わなくても同じ category_id を持つ t_data が並列に処理されないことが保証されます。

よって data_id の最大値 +1 を取りたければ単純に MAX+1 で大丈夫そうですが・・・

BEGIN;

/* (1) 関係ないテーブルをなにかの事情で SELECT している */
SELECT dummy_id FROM t_dummy LIMIT 1;

/* (2) カテゴリをロック */
SELECT 1 FROM t_category WHERE category_id = 100 FOR UPDATE;

/* (3) カテゴリ内のデータの id の最大値+1 を得る */
SELECT @data_id := IFNULL(MAX(data_id) + 1, 1) FROM t_data WHERE category_id = 100;

/* (4) データを挿入 */
INSERT INTO t_data VALUES (100, @data_id);

/* (5) コミット */
COMMIT;

問題点

確かに (2) で t_category をロックしてから (5) の COMMIT まで、同じ category_id の処理は排他制御されるのですが、(3) で参照される t_data は (1) の時点のスナップショットのため (1) ~ (2) の間に同じ処理が並列に実行されると、(3) で同じ data_id になり、(4) で一意制約エラーが発生します。

解決方法

下記のいずれかで対応出来ます。

(3) を LOCK IN SHARE MODE にする

SELECT ~ LOCK IN SHARE MODE なら他のトランザクションがコミットした行が見えるので (3) で同じ data_id になることはなくなります。ただ、ギャップロックによって無駄に広い範囲のロックが獲得されるので、今度はデッドロックの可能性が出てきます。

トランザクション分離レベルを READ COMMITTED にする

トランザクション分離レベルを READ COMMITTED にすれば只の SELECT でも他のトランザクションがコミットした行が見えます。ロックも獲得されないのでデッドロックの心配もありません。

(1) の SELECTトランザクション外に出す

(1) をトランザクション内に書いている意味は何なのか・・・意味がないなら外に出してしまえば良い。

最後に

最初に触った RDBMSPostgreSQL だったので、トランザクション中の SELECT がスナップショットを参照するというのがどうもシックリ来ません(PostgreSQL はデフォルトのトランザクション分離レベルが READ COMMITTED)。

Oracle Database も Microsoft SQL ServerPostgreSQL もデフォルトのトランザクション分離レベルが READ COMMITTED なので、いっそのこと InnoDB でも READ COMMITTED にしてしまった方が面倒が少ないのかもしれません(「MySQL (InnoDB) でデッドロック検知される条件について - QA@IT」の SH2 さんの回答より)。

次の新規案件では思い切って my.cnf で transaction-isolation = READ-COMMITTED にしてしまおうか?(InnoDB と TRANSACTION ISOLATION LEVEL