概要
タイトルのままです。
「いや、当たり前じゃないか」と思うかもしれません。
自分も最初にこのことを知ったとき、そう思いました。
しかしどうやらこれはOracleに特有の挙動のようでした。
ロック対象行がないときにロックがかからないとは？
一応どういうことかという説明です。
普通、DMLとか SELECT ... FOR UPDATE みたいなSQLを発行するとき、ロックがかかると思います。対象行があれば。
例: ロックを取得するとき
例示するまでもないかもですが。
RESERVATIONSというテーブルがあるとします。
| カラム名 | データ型 | 制約 | 説明 |
|---|---|---|---|
| RESERVE_ID | NUMBER | PRIMARY KEY | 予約番号（ユニークキー） |
| USER_NAME | VARCHAR2(50) | - | 予約者名 |
| EVENT_CODE | VARCHAR2(10) | - | イベントコード |
そして以下のようなレコードがあるとします。
| RESERVE_ID | USER_NAME | EVENT_CODE |
|---|---|---|
| 101 | 田中 | EV001 |
| 102 | 佐藤 | EV002 |
このとき、DELETE FROM RESERVATIONS WHERE RESERVE_ID = 101みたいに、存在するレコードに対してDELETE文を発行すると対象行にロックがかかります。
ここまでは大丈夫かと思います。
DELETE FROM RESERVATIONS WHERE RESERVE_ID = 101が発行されるとき
| RESERVE_ID | USER_NAME | EVENT_CODE | 行ロック |
|---|---|---|---|
| 101 | 田中 | EV001 | あり。コミットされるまで他トランザクションから更新されない。 |
| 102 | 佐藤 | EV002 | なし |
では、RESERVE_ID = 999に対してDELETE文を発行するとどうなるでしょうか？
そうですよね。ロックなどかかりようがありません。対象の行がないのですから。
DELETE FROM RESERVATIONS WHERE RESERVE_ID = 999が発行されるとき
| RESERVE_ID | USER_NAME | EVENT_CODE | 行ロック |
|---|---|---|---|
| 101 | 田中 | EV001 | なし |
| 102 | 佐藤 | EV002 | なし |
だからどうしたというのか？
操作したい行がないときはロックが取得できない。
とてもシンプルです。
しかし注意すべきなのは、存在する行しかロックを取得できない、ということ。
例: 途中でレコードが追加されたとき
先ほどのテーブルの再掲です。
| RESERVE_ID | USER_NAME | EVENT_CODE |
|---|---|---|
| 101 | 田中 | EV001 |
| 102 | 佐藤 | EV002 |
ここで、以下のようなときはどのようになるでしょう？
- トランザクション1で
RESERVATIONSを全件取得(SELECT ... FOR UPDATE) - トランザクション2で
RESERVE_ID = 103をINSERT - トランザクション1でもう一度
RESERVATIONSを全件取得(SELECT ... FOR UPDATE)
実は2回目のSELECT時、他トランザクションからINSERTされたレコードも取得されます。
| # | T1 | T2 | 結果 |
|---|---|---|---|
| 1 | SELECT * FROM RESERVATIONS FOR UPDATE |
- |
RESERVE_ID = 101,102が取得される |
| 2 | - | INSERT INTO ... VALUES(103, ...) |
RESERVE_ID = 103が登録される |
| 3 | SELECT * FROM RESERVATIONS FOR UPDATE |
- |
RESERVE_ID = 101,102,103が取得される★ |
SELECT ... FOR UPDATEなので1回目のSELECT時、存在する行にはロックがかかります。
しかしここでまた繰り返すようですが、Oracleのデフォルトにおいては、存在する行しかロックを取得できない挙動になってます。
つまり、ロックがかかる行はRESERVE_ID = 101,102のみ。
それゆえに、トランザクション2にてRESERVE_ID = 103の行はそのままINSERTされ、2回目のSELECTで取得できてしまうのですね。
いわゆるファントムリードというやつです。
他のDBはどうなのか？
では他のDBの場合はどうなのでしょうか？
実は、他のDBでは存在していない行にもロックがかかるような挙動になってます。
MySQLを例に見てみましょう。
MySQLの仕様
MySQL(InnoDB)には、先ほど挙げたファントムリードのような状況が起こりにくいような設定をデフォルトにしているようです。
例えば、ギャップロックという仕組み。
ギャップロックは、インデックスレコード間のギャップのロック、または最初のインデックスレコードの前または最後のインデックスレコードの後のギャップのロックです。 たとえば、SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;では、範囲内の既存のすべての値間のギャップがロックされているため、カラムにそのような値がすでに存在するかどうかにかかわらず、他のトランザクションが 15 の値をカラム t.c1 に挿入できなくなります。
つまり、RESERVE_ID = 101 ~ 105でロックを取得するような操作を行うとき、行の存在如何にかかわらず、間の行はすべてロックするということらしいのです。
例: 範囲内にINSERTを試みる
以下の事例を想定してみましょう。
- トランザクション1: 範囲指定でロックを取得
- トランザクション2: 隙間にINSERTを試みる
初期データ状態
分かりやすくするために、今度のデータはIDの間に「102, 103, 104」という空き（ギャップ）がある状態。
| RESERVE_ID | USER_NAME | EVENT_CODE |
|---|---|---|
| 101 | 田中 | EV001 |
| 105 | 佐藤 | EV002 |
MySQL (InnoDB) での挙動例
SELECT * FROM RESERVATIONS
WHERE RESERVE_ID BETWEEN 101 AND 105
FOR UPDATE;
このとき、MySQLは以下のすべてをロックします。
-
既存の行:
101と105 -
ギャップ（隙間）:
102, 103, 104
トランザクション2：隙間にINSERTを試みる
-- 103番は現在存在しないが...
INSERT INTO RESERVATIONS (RESERVE_ID, USER_NAME, EVENT_CODE)
VALUES (103, '鈴木', 'EV003');
【結果】
セッションBは 待機（Lock Wait） 状態になります。
103番というデータはまだこの世に存在しませんが、セッションAが「101から105の範囲」にギャップロックを張っているため、割り込むことができません。
| # | T1 | T2 | 結果・状態 |
|---|---|---|---|
| 1 | SELECT * FROM RESERVATIONS FOR UPDATE |
- |
101, 105 を取得。同時に105の間の範囲もロック。 |
| 2 | - | INSERT INTO ... VALUES(103, ...) |
待機 (Lock Wait)。 T1が「103番の場所」を予約しているため。 |
| 3 | SELECT * FROM RESERVATIONS FOR UPDATE |
- |
101, 105 のみ。 (T2が未完了のためデータが増えない) |
Oracle での挙動例
同じことをOracleで行うと、全く異なる結果になります。
トランザクション1: 範囲指定でロックを取得
SELECT * FROM RESERVATIONS
WHERE RESERVE_ID BETWEEN 101 AND 105
FOR UPDATE;
このとき、Oracleがロックするのは以下の「実在する行」だけです。
-
既存の行:
101と105 - ギャップ（隙間）: ロックされません。
トランザクション2: 隙間にINSERTを試みる
INSERT INTO RESERVATIONS (RESERVE_ID, USER_NAME, EVENT_CODE)
VALUES (103, '鈴木', 'EV003');
【結果】
セッションBは 即座に成功 します。
Oracleには「隙間をロックする」という概念がないため、103番という空いている場所への書き込みを誰も邪魔しません。
| # | T1 | T2 | 結果・状態 |
|---|---|---|---|
| 1 | SELECT * FROM RESERVATIONS FOR UPDATE |
- |
101, 102 を取得し、その2行のみをロック。 |
| 2 | - | INSERT INTO ... VALUES(103, ...) |
成功。 103番は誰にもロックされていないため。 |
| 3 | SELECT * FROM RESERVATIONS FOR UPDATE |
- |
101, 102, 103 が取得される ★ファントム・リード発生
|
MySQLは存在しない行もロックする
ここまで見てきたように、MySQLはOracleとは異なり、存在しない行についても変更がないようにすることで、データの一貫性を保とうとしています。
最初にあげた例では、「RESERVE_ID = 999に対してDELETE文を発行するとどうなるでしょうか？」という問いを発して、「ロックなどかかりようがありません。対象の行がないのですから。」と続けたのですが、実はこれがOracleに特有のことで、MySQLなどではロックがかかっていることになっていたのですね。
DELETE FROM RESERVATIONS WHERE RESERVE_ID = 999が発行されるとき
| RESERVE_ID | USER_NAME | EVENT_CODE | 行ロック |
|---|---|---|---|
| 101 | 田中 | EV001 | なし |
| 102 | 佐藤 | EV002 | なし |
| (999) | (未登録) | (未登録) | あり（999番の場所を予約）(*1) |
また、ここで上げたギャップロック以外にもネクストキーロックやインテンションロックなるものもあるそうなので気になる方は調べてみてください。
それだけでなくSQL Serverも独自の仕組みによって同様のことを行っているそうでした。
DBごとにいろいろあるんですね（子供みたいな感想）。
(*1) 正確には実在する「行」ではなく、インデックス上の「その値が入るはずの場所」をロックしていますが、イメージしやすさを優先してここでは行としています。
まとめ: Oracleは「存在している行」しかロックしない！
まとめというか繰り返し。
Oracleはデフォルト設定では存在している行しかロックしません。
これはMySQLなどがデータ一貫性を担保するのを重要視しているのに対して、Oracleは同時実行性（並行性）を重要視しているからなんでしょうね。
ユーザー数が多い大規模アプリケーションとかだとDB処理が爆速なのは助かりますが、それゆえにアプリケーションの方で気を配ってあげないと、思わぬ事故に遭うかもです。
こわやこわや。
参考