Concurrency Questions

Hi!

I have read chapter "Multiversion Concurrency
Control" of the online document. I still need
inspirations from you.

Regards,

CN
===================
Please try imagining the following block of code.

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.
Hence, the first idea comes to me is to implement a
mechanism to defer user2's read once any user enters
the above transaction block.
Now, a lousy version to fix my own problem is
"invented":

BEGIN TRANSACTION;
LOCK TABLE inventory IN ACCESS EXCLUSIVE MODE;
SELECT ItemId FROM inventory WHERE ItemId=NewItemId;
IF FOUND THEN
UPDATE inventory SET qty=NewItemQty WHERE
ItemId=NewItemId;
ELSE
INSERT INTO inventory VALUES
(NewItemId,NewItemQty);
END IF;
UNLOCK TABLE inventory;
END TRANSACTION;

My questions are:
Q1. Does the above "improved" version completely
solve my problem? Is it buggy?
Q2. Since inventory table is one of the core tables
which is heavily accessed in most organizations and
the above transcation requires some time to finish,
will all users be very angry at the slow application
response caused by the lock commands?
Q3. Any better solutions and examples?

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