[pgsql-jp: 40431] 「行が存在していなかったらinsert」の処理

[武田 憲太郎]

　武田と申します。

※先日、無作為一行取り出しの件で質問した者です。その節はお世話になりました。

　今回も、もしかするととても些細な箇所で悩んでしまっているのかもしれません
が、掲題のSQLがどうしても上手くいかず悩んでしまっています。

・行が存在しなかった場合のみinsertする。
・存在した場合は何もしない。
・アプリケーション側の処理は行ないたくない。SQLのみで完結したい。
・ストアドプロシージャを使うのはOK


■準備
create table t(pkey int primary key);


■案１（NG）
====================================
create or replace function func(_pkey int) returns void as
$$
begin
perform 1 from t where pkey = _pkey ;
if not found then
 insert into t values(_pley);
end if ;
end
$$
language plpgsql ;
====================================
→完全に同じタイミングで同じ_pkeyが入力された際に一意性制約違反でエラー、
NG。


■案２（OKだが少々厳しい）
====================================
create or replace function func(_pkey int) returns void as
$$
begin
-- ロックで処理を直接か。自分自身と競合する中で最もレベルの低いロック
lock table t in share update exclusive mode ;
perform 1 from t where pkey = _pkey ;
if not found then
 insert into t values(_pley);
end if ;
end
$$
language plpgsql ;
====================================
→これでエラーは回避できます、が、案件によってはロックが重く使い物になりませ
ん。（ロックの要件は「自分自身と競合」なので、ロックレベルはこれ以上下げられ
ない気がします。）


■案３（これがNGなのが痛い）
====================================
insert into t(pkey) select [_pkey]
 where not exists(select 1 from t where pkey = [_pkey]) ;
====================================
→文字通りです。ロックはかけません。アクセスが集中してサーバが重くなっている
際などは、この処理でも稀に主キー制約違反が発生します。この方法でも上手く行か
ないのが正直痛いのですが、よくよく考えると、insertはin row exclusve modeなの
でそう考えると当然です。


■案４（これならOK、でもまだ心もとない）
====================================
begin ;
lock table t in share update exclusive mode ;
insert into t(pkey) select [_pkey]
 where not exists(select 1 from t where pkey = [_pkey]) ;
commit ;
====================================
→「案２」よりは軽いですが、それでも重くて使い物になりません。このロックが必
要ない程度のtpsの案件であればこのロックをかけてもほぼ動きます。しかし逆に、
「案３」でエラーが発生する程度にアクセス量の多いシステムだと、この方法だとや
はり動きません…。


　参考までに案件の内容ですが、serial型を主キーに出来る案件であればこのような
ことで悩まなくて良いのですが、携帯電話向けシステムの案件（キャリア側の付与す
るuidを主キーにする）やソーシャルアプリの案件（SNSが採番したユーザーidを主
キーにする）の場合、どうしてもこの問題が発生します。

 本来は発生しない（同じユーザーが同時に同じURLにアクセスしてくることは有り得
ない）はずなのですが、途中経路（キャリアのインフラやSNS側のプロクシ）が重く
なっている最中にユーザーがページをリロードすると、以外と普通かつ高頻度に発生
するんです。少々困ります…。

　何か良いテクニックをご存じの方いらっしゃいましたらアドバイス頂けませんで
しょうか。

武田憲太郎
takeda ＠ youmind.jp