Re: Am I locking more than I need to?

On Friday May 21 2004 12:50, Jeff Davis wrote:
>
> 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.

Ah, I see. I second Christopher Browne's comments on the unique index (I
assumed you were doing that) and the ease of checking errors in the app.
If you don't have transactions spanning multiple pageviews and you don't
have multiple people modifying the same shopping cart at the same time, it
would seem this is a non-issue. But I guess you could try to explicitly
lock the table. I've never done it that way, instead preferring like C.B.
to enforce integrity at the schema level with the unique index and having
the app handle return values, errors, etc. (In DBI, you need to set a flag
to have it allow you to handle the error vs. aborting. RaiseError,
maybe?). Maybe its wise to systematically handle all DB errors, but I
suspect you'll never see this one occur.