Re: Am I locking more than I need to? - Mailing list pgsql-general

Clinging to sanity, jdavis-pgsql@empires.org (Jeff Davis) mumbled into her beard:
>> Various sorts of race conditions are possible in multi-user
>> multi-tasking systems; what _actual_ problem are you expecting to have
>> here?
>
> I posted the condition as a reply to Ed L., and copied it to the bottom
> of this message.

I saw that, yes.

>> What I would expect is that putting a unique index onto cart_items
>> based on (cart_id, prod_id) would prevent getting the confusing
>> situation of having multiple quantities of a single product in a
>> single cart.
>
> It looks like you knew what I was referring to anyway, and the
> UNIQUE constraint looks like another good solution. It would make
> the second transaction unable to commit, allowing the application to
> detect the error and send an update.

Right.

> One thing though, it would seem that it would have to be in the
> application code, since if I make a user-defined function I couldn't
> have a transaction inside it (at least until the 2PC patch makes it
> into a release). So, in a user-defined function I couldn't detect
> the error, because it would abort the outer transaction, right?

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...

> So, it seems a little back-and-forth with the application would be
> required if using a unique constraint. It certainly seems like a
> performance win for concurrent access though (not that performance
> is currently a problem for me).

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 :-).
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org').
http://www.ntlug.org/~cbbrowne/sap.html
"You can only  examine 10 levels  of pushdown, because that's  all the
fingers you have to stick in the listing."
-- Anonymous programmer - "TOPS-10 Crash Analysis Guide"