Re: Concurrency Questions
From: Lauri Pietarinen <lauri.pietarinen_at_atbusiness.com>
Date: 26 Jul 2002 04:58:09 -0700
Message-ID: <e9d83568.0207260358.1a18652d_at_posting.google.com>
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.
Date: 26 Jul 2002 04:58:09 -0700
Message-ID: <e9d83568.0207260358.1a18652d_at_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 - 13:58:09 CEST