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