Hi, > Instead, I was thinking more like: > > ... > ELSE > UPDATE ... Sorry: I neglected that for the nut of the gnarly part. Now you have to factor how MVCC behaves. My understanding is that, depending on the connection's transaction-isolation level, READ_COMMITTED transactions will only see those records committed at the START of the transaction (actually, I think it's before any modifications - such as UPDATE, INSERT, etc. - are made in the transaction). I'm presuming here that READ_SERIALIZABLE is way too heavy-handed for your application. So it's possible that you can have one or more transactions - clients trying to add a cart, select a cart, whatever - and not see any changes in any other transaction until the COMMIT of an INSERTed cart. Then the backends have to resolve WHO actually gets to INSERT the UNIQUE'ly qualified cart: only one should win and the others should throw "uniqueness violation" exceptions. Since postgres doesn't do nested-transactions the client has to rollback and submit the query again; the new transaction should see the newly committed record and on you chug. For support of this line of thinking, view the conversation where Tom Lane described the overall problem much more illustratively than I can. http://archives.postgresql.org/pgsql-general/2004-04/msg01153.php Carl <|};-)> -----Original Message----- From: Jeff Davis [mailto:jdavis-pgsql@empires.org] Sent: Friday, May 21, 2004 3:24 PM To: Carl E. McMillin Cc: 'PostgreSQL General' Subject: Re: Am I locking more than I need to? On Fri, 2004-05-21 at 14:33, Carl E. McMillin wrote: > Scenario: > > SELECT ... WHERE cart_id=X FOR UPDATE > > IF (NOT FOUND) THEN > BEGIN > --Here is where nothing is locked. > --No way to guarantee no one else will create a record before we do. > INSERT ... > END; > END IF; > Instead, I was thinking more like: BEGIN SELECT ... WHERE cart_id=X FOR UPDATE IF (NOT FOUND) THEN --Here is where nothing is locked. --No way to guarantee no one else will create a record before we do. INSERT ... ELSE UPDATE ... END IF; END; Won't that "SELECT ... FOR UPDATE" block out a concurrent access to the same cart until the first one finishes? Of course this assumes all concurrent accesses also try to "SELECT ... FOR UPDATE" before inserting. Thanks, Jeff Davis ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
