Concurrency Questions
Date: 15 Jul 2002 18:03:29 -0700
Message-ID: <eaa50a08.0207151703.51b72ab_at_posting.google.com>
Greetings!
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:
CN Received on Tue Jul 16 2002 - 03:03:29 CEST