Re: Am I locking more than I need to?

On Thursday May 20 2004 8:19, Jeff Davis wrote:
>
> create table products (
>   id serial primary key,
>   ...
> );
>
> create table cart_items (
>   id serial primary key,
>   cart_id int references ...,
>   prod_id int references product(id),
>   quantity int
> );
>
> The problem is, when you add the first item to "cart_items" you have to
> do an INSERT with a quantity of 1, but after that you need to do
> UPDATEs. That would seem to create a potential race condition, so in
> order for that to work it would seem you would need to do an ACCESS
> EXCLUSIVE lock on the table to make sure no other process was reading
> the table at the same time.

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.