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? Link. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
