Re: conditional insert

On Thu, Sep 8, 2011 at 9:14 AM, Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> wrote:
> At 03:51 AM 9/8/2011, Merlin Moncure wrote:
>>
>> > Don't you have to block SELECTs so that the SELECTs get serialized?
>> > Otherwise concurrent SELECTs can occur at the same time, find no
>> > existing
>> > rows, then "all" the inserts proceed and you get errors (or dupes).
>> >
>> > That's how Postgresql still works right? I haven't really been keeping
>> > up.
>>
>> yeah -- but you only need to block selects if you are selecting in the
>> inserting transaction (this is not a full upsert).  if both writers
>> are doing:
>> begin;
>> lock table foo exclusive;
>> insert into foo select ... where ...;
>> commit;
>>
>> is good enough.  btw even if you are doing upsert pattern
>> (lock...select for update...insert/update), you'd be fine with
>> straight exclusive locks because the 'for update' lock takes a higher
>> lock that is blocked by exclusive.  A basic rule of thumb is to try
>> and not fully block readers unless absolutely necessary...basically
>> maintenance operations.
>
> Oh wait, now I think I get it. "lock table foo exclusive" will block the
> inserts too, so I wouldn't get dupe errors even if other transactions
> "blindly" insert dupes at the same time. The other transactions might get
> the dupe errors, but mine won't as long as it selects first and only inserts
> if there are no rows at that point.
>
> Is that correct?

correct -- your transactions never get dup errors and external
transactions only get them if they, say, select without update before
the upsert (which is a bug any way you slice it).

fully blocking readers on a high traffic table is a good way to crash
your application. bring this issue up to any 'sql server admin' and
they'll start to develop a nervous tic...

merlin