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

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

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

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

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 を指定してレコードを取得して @@ROWCOUNT が 0 であること、 つまり、 行が存在しないことを確認してから INSERT しています。

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

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

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

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

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

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 分離レベルよりも更にデータベースの同時実行性能を下げてしまう要因になります。十分に注意してトランザクション分離レベルを指定するようにしましょう。