Re: Am I locking more than I need to?

At 07:19 PM 5/20/2004 -0700, Jeff Davis wrote:
>Assuming my logic above is correct, there are two other ways I thought
>to do it, but both seem considerably more redundant:
>
>(1) I could just get rid of the "quantity" attribute and just insert a
>record for each product, then do a view that aggregates the products of
>the same prod_id and cart_id with count().
>
>(2) Every time I add a product I could add a record with a quantity of 0
>for each cart in existance, and every time I add a cart I could add a
>record with a quantity of 0 for each product.
>
>Is there some better solution that I'm missing? It seems like a simple
>problem, but right now I'm doing the full table lock to be on the safe
>side. Maybe there's some solution involving check constraints?

Full table lock works but blocks normal selects.

If you can manage to use a uniqueness enforcement then that works too (but
you'll have to deal with the errors).

Alternatively you can use a table lock mode that doesn't lock plain selects
but locks select for updates and similar stuff (you may still wish to have
uniqueness enforcement just in case).

e.g.
pseudosub putrow (tablename,whereclause,namevaluepairs)
LOCK TABLE tablename IN SHARE ROW EXCLUSIVE MODE
select ... from tablename where whereclause for update
if found
update tablename ....
else
insert into tablename
endif

I'm not aware of a standard SQL command to do this, which seems like a
common enough requirement. And the bright sparks made the syntax for
updates different from inserts.

Oh well, maybe it's just me.

Link.