Re: Transaction problem - Mailing list pgsql-general

From Scott Marlowe
Subject Re: Transaction problem
Date
Msg-id dcc563d10712031042r4b5ccd5crfbca5429b060b8b1@mail.gmail.com
Whole thread Raw
In response to Transaction problem  ("x asasaxax" <xanaruto@gmail.com>)
List pgsql-general
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.

pgsql-general by date:

Previous
From: "x asasaxax"
Date:
Subject: Transaction problem
Next
From: Richard Huxton
Date:
Subject: Re: stored procedures and dynamic queries