2-2-5 条件付きinsert(1レコードのみ)

SQLパズル

UniqTableのCol1,Col2に、以下のレコードを、一意制約違反が発生しない場合のみ追加する。
レコード1(1,2)
UniqTableのプライマリキーは、Col1とする。

データ作成スクリプト

create table UniqTable(Col1 primary key,Col2) as
select 1,2 from dual;

SQL

insert into UniqTable(Col1,Col2)
select 1,2 from dual
 where not exists(select 1 from UniqTable b
                   where b.Col1=1);

解説

select文を発行して一意制約違反が発生しないかチェックしてからinsertする方法だと、
ファントムリードやアンリピータブルリードが発生した場合に一意制約違反が発生します。

ファントムリードやアンリピータブルリードを防ぐには、
for updateを使って、ロックをかければよいのですが、
ロックをかけると他のトランザクションに影響を与える上に、
SQLの発行回数もselectで1回、insertで1回の計2回となってしまいます。

上記のinsert文で一意制約違反をチェックしつつinsertする方法だと、
ファントムリードやアンリピータブルリードが発生しづらくなる上に、
SQLの発行回数が一回で済みます。
ただし、ファントムリードやアンリピータブルリードによる一意制約違反を完全に防ぐには、
for updateを使って、ロックをかける必要があります。

insertに限らず、update,delete,mergeも条件つきで実行することが可能です。

トランザクションの並列処理時に発生する問題

Oracle11gR2からは、IGNORE_ROW_ON_DUPKEY_INDEXヒントを使って、一意制約違反を完全に防げるようです。

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(UniqTable(Col1)) */ into UniqTable(Col1,Col2) values(1,2);

INSERT文のIGNORE_ROW_ON_DUPKEY_INDEXヒント

INSERT INTO TARGET...SELECT...FROM SOURCEでは、
挿入する行の一意キーが既存の行と衝突することがあります。
IGNORE_ROW_ON_DUPKEY_INDEXにより、警告なしに衝突を無視して、衝突のない行を挿入できます。

---