On Dec 3, 2007 12:27 PM, x asasaxax <xanaruto@gmail.com> 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.