Re: Concurrency Questions

Hi!

I have got no answers for this question from this
list. However, someone in another newsgroup pointed
me to a very good way. So, I am here again looking
for postgresql people's help.

Thank you in advance!

CN
===========
Bad version follows:

BEGIN TRANSACTION;
SELECT ItemId FROM inventory WHERE ItemId=NewItemId;
--ItemId is primary key.
IF FOUND THEN
  --Prepare value for NewItemQty. This takes some
time.
  UPDATE inventory SET qty=NewItemQty WHERE
ItemId=NewItemId;
ELSE
  --Prepare value for NewItemQty. This takes some
time.
  INSERT INTO inventory VALUES
(NewItemId,NewItemQty);
END IF;
END TRANSACTION;

Explanation:
The code first lookups table inventory when any item
is coming. If that item already exists, then add the
coming quantity to the existing quantity in the
table. Otherwise, that coming item is a brand new one
and should be inserted to the table.

I wonder conflict may happen in this sequence:

Both user 1 and user 2 enter this transaction.
Both users find that record for NewItemId does not
exist.
Now, user 1 does the insert.
Then, later, user 2 tries to do the insert, too.
Obviousely, user 2 will abort.

I don't want to write programs which can cause
abortions because I think it is complicate to clean
up things when abortions arise.

The suggested improved version follows:

BEGIN --PL/PGSQL function
--Prepare value for NewItemQty. This takes some time.
INSERT INTO inventory VALUES (NewItemId,NewItemQty);
IF <key violation> THEN
  UPDATE inventory SET qty=NewItemQty WHERE
ItemId=NewItemId;
END IF;
END --PL/PGSQL function

I am almost there. The last (I hope) question is: How
do I make this block of PL/PGSQL function keep going
even when key violation, caused by INSERT, occurs and
detect the happening of key violation?

--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com