Search Postgresql Archives

Re: Transaction problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux