On Dec 3, 2007 12:27 PM, x asasaxax <[EMAIL PROTECTED]> wrote: > Hi everyone, > > I would like to know how can i do a simple transaction for this > situation: > > I have n products in certain row of a table. When the user buys a product, > the quantity of this product will be decreased. The user can only buy a > product that has a quantity n > 0. This means that when the user send the > product confirmation to the system, the bd will decrease the product > quantity with a transaction if the number of product in stock is greater > than zero.
First, set a constraint on quantity that it must be 0 or greater. Here's a contained example: create table items (id int primary key, quant int, dsc text); alter table items add constraint min_quant check (quant>=0); insert into items values(1,1,'widget'); Now, two transactions: T1: begin; T2: begin; T1: select * from items; id | quant | dsc ----+-------+-------- 1 | 1 | widget T1: update items set quant=quant-1 where id=1; (succeeds) T2: select * from items; id | quant | dsc ----+-------+-------- 1 | 1 | widget (to T2 they're still there) T2: update items set quant=quant-1 where id=1; (waits for T1) Two possibilites: T1 commits, then T2 says: ERROR: new row for relation "items" violates check constraint "min_quant" T1 rolls back, then T2 says: UPDATE 1 Get an error, you can't have the item, get no error, you're gold. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
