Re: Concurrency Questions

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 - 22:48:13 CEST