Am I locking more than I need to?

[Jeff Davis]

Right now performance isn't a problem, but this question has me curious:

Let's say I have a shopping cart system where there is a "products"
table that contains all possible products, and an "cart_items " table
that stores how many of each product are in each cart.

The obvious (or the first thing that came to my mind) would look
something like this:

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.

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?

Regards,
Jeff Davis
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Christopher Browne]

The world rejoiced as jd**********@em pires.org (Jeff Davis) wrote:

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.

Various sorts of race conditions are possible in multi-user
multi-tasking systems; what _actual_ problem are you expecting to have
here?

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.

I imagine that is the best thing to try to prevent, and that is
readily done without any "locks" by adding a UNIQUE constraint. But
perhaps I am imagining a different error condition.

Can you describe the nature of the error condition that you are
thinking about? That may help indicate what foreign key checks and/or
uniqueness constraints might be worth adding.
--
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/internet.html
This login session: only $23.95!

[Ed L.]

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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Jeff Davis]

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,qua ntity)
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,qua ntity)
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Jeff Davis]

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,qua ntity)
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,qua ntity)
VALUES(X,Y,1);
client2=> COMMIT;
-- Oops, now there are two records in there.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Christopher Browne]

Clinging to sanity, jd**********@em pires.org (Jeff Davis) mumbled into her beard:

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.

I saw that, yes.

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.

Right.
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?
That seems to be the right understanding. The exception handling does
need to be in the application. And the right response may be, for a
web app, to, at that point, simply stop, pull the "cart" contents as
they are now, and then report back to the user:

- Problem: Attempt to simultaneously request multiple quantities of
Product Foo (Could someone be messing with your cart???)

- Here's what's in your cart right now...
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).

Well, I'm not sure what the likely alternatives are, without, let's
say, creating a lockable table for each 'cart.' And that would seem
likely to have pretty heavy effects on the application, too.

Whether you "lock" or "detect errors" seems like a "six of one, half a
dozen of the other" to me, and the latter is likely to be WAY more
efficient :-).
--
wm(X,Y):-write(X),write( '@'),write(Y). wm('cbbrowne',' acm.org').
http://www.ntlug.org/~cbbrowne/sap.html
"You can only examine 10 levels of pushdown, because that's all the
fingers you have to stick in the listing."
-- Anonymous programmer - "TOPS-10 Crash Analysis Guide"

[Ed L.]

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,qua ntity)
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,qua ntity)
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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Chris Browne]

pg***@bluepolka .net ("Ed L.") writes:

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,qua ntity)
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,qua ntity)
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.

I think it's just wishful thinking to hope that there's anything that
is _fundamentally_ a lot better than having the UNIQUE index, and
recovering from the "not unique" errors that may arise.

- If you lock the table, then that adds some _new_ error conditions
that can occur, namely that an update may get blocked because the
table is locked. Coping with that requires some application code.

- The other approach one might _imagine_ would be useful would be to
run queries that are always checking to see if the inserts look like
they'll be unique. Unfortunately, that CAN'T work, because multiple
connections involve multiple transaction contexts.

I can think of three other approaches:

1. You create a temp table for each cart, and somehow tie the cart to
a single persistent connection. It is _impossible_ for another
connection to interfere, because other connections can't even
see the cart.

If you can associate a process with each cart, and can accept the
overheads of having a DB connection for each cart that is in
progress, this ought to be pretty slick. Cart tables pass in and
out of existence, cleaning themselves up as needed. Quite cool.
But you can't ever use connection pooling, which may be
unacceptable...

2. You don't insert directly into the cart/product table; you insert
into a "product request" table, that is a queue of requests.
There's a big literature on this; look up "Message Queueing,"
and perhaps look at IBM's product MQSeries. (Microsoft made a
clone called "MSMQ.")

A single serial process periodically goes through that queue, and
cleanly moves the data in the queue into the cart/product table.

That means there's some asynchronicity; data may stay in the queue
for a while, which may be a problem. Furthermore, there is an
efficiency loss because every insert has to be done twice; once
into the queue, and then once into the "real" table.

3. Look up the notion of "Opportunis tic locking."

This is pretty big these days in Java and Smalltalk applications;
I won't bother explaining it Yet Again. If your application is
getting hammered because big long expensive transactions doing
lots of updates are failing at COMMIT point due to uniqueness
constraints, OL can cut the cost.

All these approaches have a big impact on application design. And I
don't see them being _fundamentally_ better than just using the UNIQUE
index.
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/advocacy.html
"Ahhh. A man with a sharp wit. Someone ought to take it away from him
before he cuts himself." -- Peter da Silva

[Jeff Davis]

That seems to be the right understanding. The exception handling does
need to be in the application. And the right response may be, for a
web app, to, at that point, simply stop, pull the "cart" contents as
they are now, and then report back to the user:

- Problem: Attempt to simultaneously request multiple quantities of
Product Foo (Could someone be messing with your cart???)

- Here's what's in your cart right now...

Interesting. I suppose in my application it probably is a good idea to
give an error, seeing as one physical person can't do anything quickly
enough to violate the UNIQUE.

What if postgres were to have nested transactions (I misstated above as
2PC for some reason)? Would it be desirable to do the checking on the
server side in a function (attempt to insert, and if we get a unique
constraint violation we update) rather than the application?
Well, I'm not sure what the likely alternatives are, without, let's
say, creating a lockable table for each 'cart.' And that would seem
likely to have pretty heavy effects on the application, too.

Whether you "lock" or "detect errors" seems like a "six of one, half a
dozen of the other" to me, and the latter is likely to be WAY more
efficient :-).

One thing that I didn't think of before is this: if I have a table of
all the carts, then could I do a "SELECT ... WHERE cart_id=X FOR UPDATE"
right before I did the test to see whether I should insert or update?
That would basically be a row lock on just the cart I'm modifying,
preventing other concurrent accesses (assuming that they are also trying
to "SELECT ... FOR UPDATE") from locking the same cart, right? But it
would allow other carts to be modified without waiting. Is this a viable
solution?

Thanks,
Jeff

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Carl E. McMillin]

Don't you still have the possibility for a race-condition?

Scenario:

SELECT ... WHERE cart_id=X FOR UPDATE

IF (NOT FOUND) THEN
BEGIN
--Here is where nothing is locked.
--No way to guarantee no one else will create a record before we do.
INSERT ...
END;
END IF;

Once client commits - assuming UNIQUE is enforced - one of the INSERT
transactions with fail. Again, have to be handled client-side.

<|};-)>

-----Original Message-----
From: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org] On Behalf Of Jeff Davis
Sent: Friday, May 21, 2004 1:25 PM
To: Christopher Browne
Cc: PostgreSQL General
Subject: Re: [GENERAL] Am I locking more than I need to?

That seems to be the right understanding. The exception handling does
need to be in the application. And the right response may be, for a
web app, to, at that point, simply stop, pull the "cart" contents as
they are now, and then report back to the user:

- Problem: Attempt to simultaneously request multiple quantities of
Product Foo (Could someone be messing with your cart???)

- Here's what's in your cart right now...

Interesting. I suppose in my application it probably is a good idea to give
an error, seeing as one physical person can't do anything quickly enough to
violate the UNIQUE.

What if postgres were to have nested transactions (I misstated above as 2PC
for some reason)? Would it be desirable to do the checking on the server
side in a function (attempt to insert, and if we get a unique constraint
violation we update) rather than the application?
Well, I'm not sure what the likely alternatives are, without, let's
say, creating a lockable table for each 'cart.' And that would seem
likely to have pretty heavy effects on the application, too.

Whether you "lock" or "detect errors" seems like a "six of one, half a
dozen of the other" to me, and the latter is likely to be WAY more
efficient :-).

One thing that I didn't think of before is this: if I have a table of all
the carts, then could I do a "SELECT ... WHERE cart_id=X FOR UPDATE" right
before I did the test to see whether I should insert or update? That would
basically be a row lock on just the cart I'm modifying, preventing other
concurrent accesses (assuming that they are also trying to "SELECT ... FOR
UPDATE") from locking the same cart, right? But it would allow other carts
to be modified without waiting. Is this a viable solution?

Thanks,
Jeff

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org