Re: Concurrency Questions

From: Lauri Pietarinen <lauri.pietarinen_at_atbusiness.com>
Date: 26 Jul 2002 04:58:09 -0700
Message-ID: <e9d83568.0207260358.1a18652d@posting.google.com>

cnliou_at_eurosport.com (cnliou) wrote in message news:<eaa50a08.0207252122.3976fd6d_at_posting.google.com>...
> Thank you very much! Lauri,
>
> The solution you provided is so far the best one I think. The only
> problem with it is that the database (postgresql) will abort the
> transaction when trying to insert a record with duplicate key value.

I am not familiar with Postgres but that does sound strange!

You could try this small variant. It should get you past the duplicate-problem:

 BEGIN TRANSACTION;
   UPDATE inventory SET qty=NewItemQty WHERE ItemId=NewItemId;    IF <no-rows-updated> THEN

     INSERT INTO inventory VALUES (NewItemId,NewItemQty);
     <here a slight possibility of duplicate-error problems>

   END IF
   SELECT qty FROM inventory where ItemId=NewItemId;  END TRANSACTION; There is a slight possibility that two (or more) separate transactions will all try the UPDATE first and then proceed to the INSERT. In that case one of the INSERT-statements will succeed and the other(s) will fail. But that should be a remote possibility (depending of course on transaction volume) and if it sometime does happen then you will get the occasional aborted transaction.

Lauri Pietarinen Received on Fri Jul 26 2002 - 06:58:09 CDT