MySQL の InnoDB のデフォルトのトランザクション分離レベルは 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) をトランザクション内に書いている意味は何なのか・・・意味がないなら外に出してしまえば良い。
最後に
最初に触った RDBMS が PostgreSQL だったので、トランザクション中の SELECT
がスナップショットを参照するというのがどうもシックリ来ません(PostgreSQL はデフォルトのトランザクション分離レベルが READ COMMITTED
)。
Oracle Database も Microsoft SQL Server も PostgreSQL もデフォルトのトランザクション分離レベルが READ COMMITTED
なので、いっそのこと InnoDB でも READ COMMITTED
にしてしまった方が面倒が少ないのかもしれません(「MySQL (InnoDB) でデッドロック検知される条件について - QA@IT」の SH2 さんの回答より)。
次の新規案件では思い切って my.cnf で transaction-isolation = READ-COMMITTED
にしてしまおうか?(InnoDB と TRANSACTION ISOLATION LEVEL)