On Thu, Sep 8, 2011 at 9:14 AM, Lincoln Yeoh <lyeoh@xxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
