Обсуждение: Transaction problem

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.


Did anyone knows how can i do that with postgre?

Thanks a lot.

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.

What are you programing with?.
are you using npgsql?

Regards Cesar Alvarez.
> 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.
>
>
> Did anyone knows how can i do that with postgre?
>
> Thanks a lot.

Its just use a constraint then? there´s no problem id two sessions decrease the number, and this number goes to less then or equals as zero?
I´m programming with php.


Thanks

In response to "x asasaxax" <xanaruto@gmail.com>:

> Its just use a constraint then? there´s no problem id two sessions decrease
> the number, and this number goes to less then or equals as zero?
> I´m programming with php.

BEGIN;
SELECT quantity FROM products WHERE productid=[productid] FOR UPDATE;
[Check in PHP to ensure enough product exists for this purchase]
UPDATE products SET quantity=[new quantity after purchase]
    WHERE productid=[productid];
[... any other table updates you need to do for this transaction ...]
COMMIT WORK;

SELECT ... FOR UPDATE will prevent other transactions from locking this
row until this transaction completes.  It guarantees that only 1
transaction can modify a particular row at a time.  See the docs for
more details:
http://www.postgresql.org/docs/8.1/static/sql-select.html#SQL-FOR-UPDATE-SHARE
http://www.postgresql.org/docs/8.1/static/explicit-locking.html

> 2007/12/3, Cesar Alvarez <c.alvarezx66@gmail.com>:
> >
> > What are you programing with?.
> > are you using npgsql?
> >
> > Regards Cesar Alvarez.
> > > 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.
> > >
> > >
> > > Did anyone knows how can i do that with postgre?
> > >
> > > Thanks a lot.
> >
> >
> >
>


--
Bill Moran
http://www.potentialtech.com

On Dec 4, 2007 7:45 AM, Bill Moran <wmoran@potentialtech.com> wrote:
> In response to "x asasaxax" <xanaruto@gmail.com>:
>
> > Its just use a constraint then? there´s no problem id two sessions decrease
> > the number, and this number goes to less then or equals as zero?
> > I´m programming with php.
>
> BEGIN;
> SELECT quantity FROM products WHERE productid=[productid] FOR UPDATE;
> [Check in PHP to ensure enough product exists for this purchase]
> UPDATE products SET quantity=[new quantity after purchase]
>     WHERE productid=[productid];
> [... any other table updates you need to do for this transaction ...]
> COMMIT WORK;
>
> SELECT ... FOR UPDATE will prevent other transactions from locking this
> row until this transaction completes.  It guarantees that only 1
> transaction can modify a particular row at a time.  See the docs for
> more details:
> http://www.postgresql.org/docs/8.1/static/sql-select.html#SQL-FOR-UPDATE-SHARE
> http://www.postgresql.org/docs/8.1/static/explicit-locking.html

Seems like a lot more work than my method of using a check constraint
on quantity >=0.  The advantage to doing it my way is you use a single
statement with no race conditions and no "for update" locking of the
row required.  If the update succeeds there was one, and you have
"checked it out".  If it fails there weren't any.  It's race proof and
far simpler.