Re: Am I locking more than I need to?

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

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

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?

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

Thanks,
Jeff Davis

client1=> BEGIN;
-- test to see if there's already a record there. If so, UPDATE
-- if not, INSERT
client1=> SELECT * from cart_items where cart_id=X AND prod_id=Y;
-- no record, so INSERT
client1=> INSERT into cart_items(cart_id,prod_id,quantity)
VALUES(X,Y,1);
client2=> SELECT * from cart_items where cart_id=X AND prod_id=Y;
-- still no record, since client1 didn't commit yet
client1=> COMMIT;
-- now client2 needs to insert
client2=> INSERT into cart_items(cart_id,prod_id,quantity)
VALUES(X,Y,1);
client2=> COMMIT;
-- Oops, now there are two records in there.