Don't you still have the possibility for a race-condition? 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; Once client commits - assuming UNIQUE is enforced - one of the INSERT transactions with fail. Again, have to be handled client-side. <|};-)> -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jeff Davis Sent: Friday, May 21, 2004 1:25 PM To: Christopher Browne Cc: PostgreSQL General Subject: Re: Am I locking more than I need to? > That seems to be the right understanding. The exception handling does > need to be in the application. And the right response may be, for a > web app, to, at that point, simply stop, pull the "cart" contents as > they are now, and then report back to the user: > > - Problem: Attempt to simultaneously request multiple quantities of > Product Foo (Could someone be messing with your cart???) > > - Here's what's in your cart right now... > Interesting. I suppose in my application it probably is a good idea to give an error, seeing as one physical person can't do anything quickly enough to violate the UNIQUE. What if postgres were to have nested transactions (I misstated above as 2PC for some reason)? Would it be desirable to do the checking on the server side in a function (attempt to insert, and if we get a unique constraint violation we update) rather than the application? > Well, I'm not sure what the likely alternatives are, without, let's > say, creating a lockable table for each 'cart.' And that would seem > likely to have pretty heavy effects on the application, too. > > Whether you "lock" or "detect errors" seems like a "six of one, half a > dozen of the other" to me, and the latter is likely to be WAY more > efficient :-). One thing that I didn't think of before is this: if I have a table of all the carts, then could I do a "SELECT ... WHERE cart_id=X FOR UPDATE" right before I did the test to see whether I should insert or update? That would basically be a row lock on just the cart I'm modifying, preventing other concurrent accesses (assuming that they are also trying to "SELECT ... FOR UPDATE") from locking the same cart, right? But it would allow other carts to be modified without waiting. Is this a viable solution? Thanks, Jeff ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
