>"The main purposes of explicit locks are (1) to prevent expensive handling of
>update conflict errors in heavily loaded application"
>
>this is the exact reason I try to avoid conflicts. My threads can wait but
>they should not raise exceptions.
>
>I always though that it is possible to avoid such conflicts on the database
>level.
In some cases it might be possible to avoid lock conflickts by using INSERT
rather than UPDATE. Typically, you may have a trigger or something similar that
inserts into a summary table, and then you have a separate process running at
regular intervals that update the summary tables. E.g. you could have an
inventory table originally with three items, and then insert into this table
when things are sold or bought.
Inventory
Product InStock
1 75 --Items originally in stock
2 350 --Items originally in stock
3 54 --Items originally in stock
1 -5 --Sold five items
1 -2 --Sold two items
3 -8 --Sold eight items
1 100 --Bought hundred more items
1 -3 --Sold three items
This does require programs using SUM(InStock) rather than just InStock when
selecting, but you should never get lock conflicts. Every night (or every hour
or week or whenever, just make sure it doesn't run several instances
concurrently) you could run the following procedure:
CREATE PROCEDURE UpdateStock AS
DECLARE VARIABLE id INTEGER;
DECLARE VARIABLE NoOfItems INTEGER;
BEGIN
FOR SELECT Product, sum(InStock)
FROM Inventory
GROUP BY 1
INTO :id, :NoOfItems DO
BEGIN
DELETE FROM INVENTORY
WHERE Product = :id;
INSERT INTO INVENTORY(Product, InStock)
VALUES (:id, :NoOfItems);
END
END
Don't know whether this is applicable to your situation or not, but
HTH,
Set
