Re: Transaction problem

On Dec 4, 2007 7:45 AM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
> 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

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.