On Wed, Sep 7, 2011 at 10:45 AM, Lincoln Yeoh <lyeoh@xxxxxxxxxxxxx> wrote: > At 05:23 AM 9/7/2011, Merlin Moncure wrote: >> >> On Tue, Sep 6, 2011 at 3:45 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: >> >> > b) doesn't block reads if you lock in EXCLUSIVE mode. a) is the best >> > way to go if you prefer to handle errors on the client and/or >> > concurrency is important...c) otherwise. >> >> whoops! meant to say b) otherwise! As far as c) goes, that is >> essentially an advisory lock for the purpose -- using advisory locks >> in place of mvcc locks is pretty weak sauce -- they should be used >> when what you are locking doesn't follow mvcc rules. >> >> merlin > > 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. > From what I see this (UPSERT/MERGE) has been a common problem/query over the > years but it's not in a Postgresql FAQ and many people seem to be using > methods that don't actually work. Google shows that many are even > recommending those methods to others. Postgresql might still get blamed for > the resulting problems. yeah -- there are two basic ways to do upsert -- a) table lock b) row lock with loop/retry (either in app or server side via procedure). I greatly prefer a) for simplicity's sake unless you are shooting for maximum possible concurrency. @andrew s: going SERIALIZABLE doesn't help if you trying to eliminate cases that would push you into retrying the transaction. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
