削除フラグなどを用いた論理削除でデータ削除を表現する場合、外部キー制約では参照整合性が保てないことがあるのでその解決方法。
次のようなテーブルがあったとします。
CREATE TABLE t1
(
id INT NOT NULL,
del BOOLEAN NOT NULL, /* 削除フラグ */
PRIMARY KEY (id)
);
CREATE TABLE t2
(
no INT NOT NULL,
id INT NOT NULL,
PRIMARY KEY (no),
CONSTRAINT FOREIGN KEY (id) REFERENCES t1 (id) ON UPDATE NO ACTION ON DELETE NO ACTION
);
- t2 の id は t1 を参照しています
- t2 から参照されている t1 のレコードは削除出来ない仕様です
- 論理削除のときも同様です（外部キー制約は役に立ちません）
初期データ
すごく単純な例になりますが次のようなデータが登録されているとします。
INSERT INTO t1 VALUES (1, 0);
参照先を論理削除するトランザクション (A)
BEGIN;
/* t1 を排他ロック */
SELECT id FROM t1 WHERE id = 1 FOR UPDATE;
/* t2 の存在を確認 */
/* t2 に該当するレコードがあればロールバック */
SELECT id FROM t2 WHERE id = 1 LOCK IN SHARE MODE;
/* t1 を論理削除 */
UPDATE t1 SET del = 1 WHERE id = 1;
COMMIT;
参照元に挿入するトランザクション (B)
BEGIN;
/* t1 を共有ロックして論理削除を確認 */
/* 該当するレコードが見つからない or 論理削除されている場合はロールバック */
SELECT del FROM t1 WHERE id = 1 LOCK IN SHARE MODE;
/* t2 に t1 を参照するレコードを挿入 */
INSERT INTO t2 VALUES (1, 1);
COMMIT;
説明
外部キー制約によって行われることを自前でやっている感じです。参照元(t2)にレコードを挿入あるいは更新するときのトランザクション中に参照先(t1)の該当レコードが論理削除されないことを保証するために参照先を共有ロックしています。
実行例
「参照先を論理削除するトランザクション」を A、「参照元に挿入するトランザクション」を B とします。
ケース１ A が先の場合
/* A */ BEGIN;
/* A */ SELECT id FROM t1 WHERE id = 1 FOR UPDATE;
/* B */ BEGIN;
/* B */ SELECT del FROM t1 WHERE id = 1 LOCK IN SHARE MODE; /* 競合する (1) */
/* A */ SELECT id FROM t2 WHERE id = 1 LOCK IN SHARE MODE; /* 結果は空なので削除可能 */
/* A */ UPDATE t1 SET del = 1 WHERE id = 1;
/* A */ COMMIT;
/* B */ ROLLBACK; /* (1) の結果で 1 が返ってきたのでロールバック */
ケース２ B が先の場合
/* B */ BEGIN;
/* B */ SELECT del FROM t1 WHERE id = 1 LOCK IN SHARE MODE; /* 結果は 0 なので挿入可能 */
/* A */ BEGIN;
/* A */ SELECT id FROM t1 WHERE id = 1 FOR UPDATE; /* 競合する */
/* B */ INSERT INTO t2 VALUES (1, 1); /* レコードを挿入 (2) */
/* B */ COMMIT;
/* A */ SELECT id FROM t2 WHERE id = 1 LOCK IN SHARE MODE; /* (2) で挿入したレコードが返る */
/* A */ ROLLBACK; /* 参照元が存在するのでロールバック */
t2 の存在確認で LOCK IN SHARE MODE を付けない場合の問題
「参照先を論理削除するトランザクション(A)」の t2 の SELECT の LOCK IN SHARE MODE は無くても良いようにも見えます。トランザクション B で t1 を必ず共有ロックしていれば、トランザクション A は t2 を共有ロックしなくても t2 が挿入や更新されないことが保証できるからです。
実際↑のケースであれば LOCK IN SHARE MODE は無くても動作は変わりません。が、それでも LOCK IN SHARE MODE を付けておいたほうがいいだろうと思います。
何故なら、A で SELECT * FROM t1 ... FOR UPDATE の前に別のテーブルを SELECT したりすると次のようなことが起こる可能性があるためです。
/* B */ BEGIN;
/* B */ SELECT del FROM t1 WHERE id = 1 LOCK IN SHARE MODE;
/* B */ INSERT INTO t2 VALUES (1, 1);
/* A */ BEGIN;
/* A */ SELECT * FROM t3; /* (1) なんらかの理由で別のテーブルを SELECT する必要があった */
/* A */ SELECT id FROM t1 WHERE id = 1 FOR UPDATE; /* 競合 */
/* B */ COMMIT;
/* A */ SELECT id FROM t2 WHERE id = 1; /* 結果は空 (2) */
/* A */ UPDATE t1 SET del = 1 WHERE id = 1; /* 論理削除 */
/* A */ COMMIT;
t2 に id=1 のレコードを挿入したにも関わらず t1 の id=1 のレコードを論理削除してしまいました。
MySQL のデフォルトのトランザクション分離レベルである「リピータブルリード」ではトランザクション内で同じレコードを 2 回 SELECT したときに同じ結果が得られることが保証されます。MySQL はそれを実現するために普通の SELECT はトランザクションが開始してから最初の SELECT 時点のスナップショットから結果を得るようになっています。
↑の例の場合、(2) で得られる結果は (1) の時点のスナップショットになるのです。(1) の時点では B がまだコミットされていないため、B が INSERT した t2 のレコードは A には見えません。そのため (2) の結果は空になります。
「LOCK IN SHARE MODE」を付けておけば分離レベルが「リピータブルリード」でもスナップショットからではなく他のトランザクションがコミットした最新のレコードが得られます。
おまけ
t2 に t1 の削除フラグ (t2ではなく) を設けて、そのフィールドも含めて外部キー制約にする方法を最初に思いつきましたが、なんか気持ち悪かったので止めました。次のような感じです。
CREATE TABLE t1
(
id INT NOT NULL,
del BOOLEAN NOT NULL, /* 削除フラグ */
PRIMARY KEY (id),
UNIQUE (id, del)
);
CREATE TABLE t2
(
no INT NOT NULL,
id INT NOT NULL,
del BOOLEAN NOT NULL, /* id が参照しているレコードの削除フラグ */
PRIMARY KEY (no),
CONSTRAINT FOREIGN KEY (id, del) REFERENCES t1 (id, del) ON UPDATE NO ACTION ON DELETE NO ACTION
);
これなら t1 を論理削除しようとしたときに参照元の t2 があれば外部キー制約にかかります。
INSERT INTO t1 VALUES (1, 0);
INSERT INTO t2 VALUES (1, 1, 0);
UPDATE t1 SET del = 1 WHERE id = 1; /* エラー */
DELETE FROM t2 WHERE id = 1;
UPDATE t1 SET del = 1 WHERE id = 1; /* 成功 */
ただ、t2 の del は 必ず 0 のなんか微妙なフィールドです・・・