Re: Am I locking more than I need to?

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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Jeff Davis
Sent: Friday, May 21, 2004 1:25 PM
To: Christopher Browne
Cc: PostgreSQL General
Subject: Re: [GENERAL] 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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly