Re: Am I locking more than I need to?

> I'm not sure what potential race condition you see since you haven't said
> much about how your transactions fit in here.  But I would suggest you go
> with your first design and don't worry about any explicit locking
> unless/until it clearly becomes a problem.  I've built numerous things
> similar to this, and in my experience, PostgreSQL is very good about
> managing the locking in an intelligent manner if your transactions are
> reasonably grouped.
>
> HTH.
>

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.

That's the condition I was worried about.

Thanks,
    Jeff Davis