Re: Concurrency Questions

From: Lauri Pietarinen <lauri.pietarinen_at_atbusiness.com>
Date: 17 Jul 2002 13:48:13 -0700
Message-ID: <e9d83568.0207171248.6665e51e@posting.google.com>

You could try to "shoot first and ask questions later" like this:

BEGIN TRANSACTION;
INSERT INTO inventory VALUES (NewItemId,NewItemQty); IF <duplicate error> THEN
UPDATE inventory SET qty=NewItemQty WHERE ItemId=NewItemId; SELECT qty FROM inventory where ItemId=NewItemId; END TRANSACTION; My understanding is that this is both safe and (reasonably) efficient (you will usually get a duplicate error, but this is probably a very small overhead that can be well tolerated).

Lauri

>
> 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?
>
> Thank you in advance!
>
> CN
Received on Wed Jul 17 2002 - 15:48:13 CDT