Re: ensuring row/table locking

[Aaron Westendorf <awestendorf@xxxxxxxxx>]

> I would prefer using a table that works as a semaphore between different
> process. Use this table to signal when you are "reserving" a record, and
> clear the flag when you are done. Use a batch process to clear flags that
> were left "dirty" by unfinished processes.

This is one of the methods we have discussed, and it seems that it might 
work the best.  Additionally, we could add the 'dirty' flag to the row 
and perform our locking in a more granular fashion.  The only problem is 
that there is still a very small chance that two processes might see the 
'clean' state at the same time.  I suppose that this could be handled by 
only breaking out of the wait loop when the record is clean and a query 
like "UPDATE X WHERE dirty=0" returns  greater than 0.

To widen the scope of my inquiry, consider the following situation. 
There are accounts in the system, and a table of properties maps the 
remaining quantity of various items purchased for each account.  We need 
a reliable way of querying the current quantity, performing an operation 
if quantity > 0, and then decrementing it.  This would be easy but our 
process may be load-balanced in a large-scale environment, and so 
several processes may be handling the requests.

I'm sure someone else has come across this problem before, please share 
your insights if you can.

cheers,
Aaron

-- 

Titan Corporation
1020 Bay Area Boulevard - Suite 200
Houston, TX  77058
(281) 461-2100 x130
(281) 488-0191 Fax