> 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,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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
