Re: Transaction problem

In response to "x asasaxax" <xanaruto(at)gmail(dot)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(dot)alvarezx66(at)gmail(dot)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