Re: Transaction problem

On Dec 3, 2007 12:27 PM, x asasaxax <xanaruto(at)gmail(dot)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.