レコードが存在しなければINSERTする

データベースプログラミングで「レコードが存在しなければINSERTする」という処理が必要になることありますよね。この処理、簡単なようで意外と難しいんです。今日は、この処理を安全に実装するための方法と注意点を解説したいと思います。

本記事はSQL Serverについて記載していますが、他のデータベース製品にも当てはまるデータベース一般のお話です。

はじめに以下の3レコードを持つPersonテーブルを用意しておきます。

Personテーブル
Id (PK) Name Age
1 Taro 20
2 Jiro 19
3 Saburo 17

UPDATEの場合

INSERTではなくUPDATEの場合は難しくありません。「レコードを取得して」「レコードを更新する」という2つの操作をトランザクションにするのが基本です。

UPDATE
BEGIN TRAN SELECT * FROM Person WITH (UPDLOCK) WHERE Id = 2 UPDATE Person SET Age = 18 WHERE Id = 2 COMMIT

レコード取得時にUPDLOCKを指定しているので、トランザクション終了まで Id = 2 のレコードには更新ロックがかかります。これにより他のトランザクションが Id = 2 のレコードを書き換えることを防ぎ、取得したレコードを確実に更新することができます。

INSERTの場合

INSERTの場合、行ロックを使う方法は上手くいきません。

INSERT
-- あらかじめ Personテーブルから Id = 2 を削除してあります。 BEGIN TRAN SELECT * FROM Person WITH (UPDLOCK) WHERE Id = 2 IF @@ROWCOUNT = 0 BEGIN INSERT INTO Person (Id, Name, Age) VALUES (2, 'Jiro', 19) END COMMIT

上記のコードはUPDLOCKを指定してレコードを取得して @@ROWCOUNT0 であること、つまり、行が存在しないことを確認してからINSERTしています。

残念ながら、上記のコードは排他制御が正しくできていません。 SELECTしてからINSERTするまでの間に、他のトランザクションによって Id = 2 のレコードが挿入されてしまう可能性があります。そして、それが実際に起こると、INSERTは一意制約違反で失敗します。

UPDLOCKを指定しているのになぜ? と思うかもしれませんね。それは「存在しない行には行ロックをかけることはできない」からです。

トランザクション分離レベル

「レコードが存在しなければINSERTする」この処理を確実に成功させるためには、トランザクション分離レベルを変更してファントムリードが発生しないようにする必要があります。

ファントムリードの説明をする前に、トランザクション分離レベルについて少しおさらいをしておきましょう。

 ダーティリード   ファジーリード   ファントムリード
READ UNCOMMITTED
READ COMMITTED 発生しない
REPEATABLE READ 発生しない 発生しない
SERIALIZABLE 発生しない 発生しない 発生しない

READ UNCOMMITTED

READ UNCOMMITTEDは文字通りコミットされていないデータが読める分離レベルです。他のトランザクションがデータを更新してコミットする前の状態が読み取れてしまいます。

NOLOCKに気を付けよう
SQL ServerではロックヒントとしてNOLOCKを指定すると、READ UNCOMMITTED分離レベルと同様に未コミットのデータを読み取ってしまいます。クエリー実行がブロックされるのを避けるため安易にNOLOCKを指定しているケースが見られますが、不用意にNOLOCKを付けるのは危険です。要件にもよりますが未コミットのデータを処理対象にしなければならないケースは多くないはずです。クエリー実行がブロックされることなくコミット済のデータを読み取りたいのであれば、代わりにREADPASTヒントの使用を検討してください。READPASTヒントを指定すると、ロックが獲得できない行をスキップして読み取り可能な行だけを返します。NOLOCKを指定して未コミットのデータを処理対象にしてしまうよりも安全です。

READ COMMITTED

READ COMMITTEDは文字通りコミットされているデータが読める分離レベルです。多くのデータベースでこの分離レベルが既定値になっています。

REPEATABLE READ

REPEATABLE READは反復可能読み取りができる分離レベルです。反復可能読み取り? REPEATABLE READを直訳した分かりにくい言葉ですね。これは、読み取りが反復可能、つまり読み取った値が変化しないことが保証される分離レベルです。

REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT * FROM Person WHERE Id = 1 SELECT * FROM Person WHERE Id = 1 COMMIT

上記のようにREPEATABLE READ分離レベルのトランザクションを指定すると、Id = 1 のレコードを2回読み取ってもレコードの値が同じであることが保証されます。 1回目の読み取りで Age = 20 だったのに、2回目の読み取りで Age = 21 になってしまうということがありません。

繰り返し同じ値を読み取ることができる、これが反復可能読み取りです。

SQL ServerではREPEATABLE READを実現するために共有ロックが使われます。 1度読み取ったデータに共有ロックをかけることで、他のトランザクションからデータが変更されるのを防いでいます。この共有ロックはトランザクション終了まで保持されるため、REPEATABLE READは同時実行性能が下がる要因にもなります。

REPEATABLE READで注意すべきことがあります。それは「読み取ったデータを繰り返し読めることが保証される」ということです。 まだ読み取っていないデータについては言及していません。

REPEATABLE READ
-- あらかじめ Personテーブルから Id = 2 を削除してあります。 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT * FROM Person   -- ここで他のトランザクションが Id = 2 を追加してコミットすると… SELECT * FROM Person COMMIT

REPEATABLE READ分離レベルで上記コードを実行してみます。 WHERE句が指定されていないのですべてのレコードが返されます。最初のSELECTでは、Id = 1 と Id = 3 の2行が返されます。そして、この2つのレコードの値が変わらないことが保証されています。

ここで、他のトランザクションによって Id = 2 のレコードが追加されたらどうなるでしょうか?

次のSELECTでは、他のトランザクションで追加された Id = 2 を含めた 3レコードが返されます。REPEATABLE READでは、読み取っていない Id = 2 のレコードが、トランザクションの途中で発生してしまうことを防ぐことはできません。

1回目の読み取りでは返されなかった Id = 2 のレコードが、2回目の読み取りでは発生してしまいました。このようなレコードをファントム、そして、途中発生したデータを読み取ってしまうこの現象をファントムリードと言います。

SERIALIZABLE

SERIALIZABLE分離レベルでは、前述したファントムリードが発生しないことも保証されます。

SERIALIZABLE
-- あらかじめ Personテーブルから Id = 2 を削除してあります。 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT * FROM Person   -- ここで他のトランザクションは Id = 2 を追加することができません SELECT * FROM Person COMMIT

SERIALIZABLE分離レベルで上記コードを実行すると、最初のSELECTが Id = 1 と Id = 3 の 2レコードを返した場合、その状態がトランザクション終了まで維持されます。 SELECTを繰り返し実行してもレコード数が 3 になってしまうことはありません。

このSERIALIZABLEではファントムリードが発生しない、つまり、他のトランザクションがレコードを追加するのを防ぐことができます。


これで、冒頭の「レコードが存在しなければINSERTする」問題に戻ることができます。

  • 他のトランザクションによるUPDATEは更新ロック(UPDLOCK)で防ぐことができる

  • 他のトランザクションによるINSERTはSERIALIZABLE分離レベルで防ぐことができる

ということになります。

レコードが存在しなければINSERTする
-- あらかじめ Personテーブルから Id = 2 を削除してあります。 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT * FROM Person WHERE Id = 2   -- ここで他のトランザクションは Id = 2 を追加することができません IF @@ROWCOUNT = 0 BEGIN INSERT INTO Person (Id, Name, Age) VALUES (2, 'Jiro', 19) END COMMIT

SERIALIZABLE分離レベルを指定してファントムリードが発生しないようにすると、 SELECTを実行してから、INSERTを実行するまでの間に他のトランザクションによって Id = 2 のレコードが追加されることはなくなります。

「レコードが存在しなければINSERTする」を確実に成功させるためには、トランザクション分離レベルを SERIALIZABLE にする必要があるわけです。

キー範囲ロック

存在しない行には行ロックをかけることができないのに、SQL Serverはどのようにしてファントムの挿入を防いでいるのでしょうか? SQL Serverはファントムリードを防ぐためにキー範囲ロックというものを使っています。存在しない Id = 2 のレコードに行ロックをかけることはできないので、代わりに Id = 1 から Id = 3 までの範囲をロックします。

このキー範囲ロックは広い範囲をロックしてしまう可能性があることに注意してください。

レコードが存在しなければINSERTする
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT * FROM Person WHERE Id = 200 IF @@ROWCOUNT = 0 BEGIN INSERT INTO Person (Id, Name, Age) VALUES (200, 'Jiro', 19) END COMMIT

たとえば、Id = 1 と Id = 3 ではなく、Id = 100 と Id = 300 の 2レコードがあるとします。

Id = 200 のレコード追加を防ぐことができれば十分なのですが、実際には、Id = 100 ~ Id = 300 がキー範囲ロックの対象になります。

このとき、Id = 200 のレコード追加だけでなく、Id = 250 や Id = 299 のレコード追加もブロックされます。 Id = 100 ~ Id = 300 の範囲外である Id = 350 のレコード追加は阻害されません。

SERIALIZABLE分離レベルはREPEATABLE READ分離レベルよりも更にデータベースの同時実行性能を下げてしまう要因になります。十分に注意してトランザクション分離レベルを指定するようにしましょう。