>Here's a simplified example of our current caching strategy:
>
>Assume products table with Onhand field (a cached field because it's not practical to calculate sum of total purchased-total sold).
>At any time, buyers can place order for product A
>
>Pseudo-code:
>Web Process 1 starts processing order for Product A from Buyer Y
>Web Process 2 starts processing order for Product A from Buyer Z
>at this time both Process 1 and Process 2 see there are 4 onhand
>So they both simultaneously submit an update
>Update products onhand=3 where product=A
>
>(The actual real world application is much more complex so I can't just issue 'update products set onhand=onhand-1 where product=A')
>
>I'm looking to use Semaphore Locking, but I wanted to check with the gurus here for any other strategies I should consider for ensuring the cache is up-to-date.

I've heard of several strategies, including signal stock levels (so extra locking is applied if stock is below that level). One that came to mind recently (I was helping a younger colleague, so I don't know the whole context, except that it's stock and case like yours is likely) is to have a table of touched items and their status. During the save of any document, all items mentioned in it are written into this table. When saving the document, this table is saved as well and transaction is closed. Then a separate transaction is launched for each item in the table - calculating the stock for each, and locking the record for the duration of it. Then the item is removed from the table if successful, or just left there. Any other document being saved at the time checks its items against this table (which is a list of suspects)... now what it does if anyone tries to sell something on the suspect list is not decided yet, there'll probably be a wait loop and an extra call to try to recalculate any of these items that are still suspect.

This is just a theory, though, the guy isn't through it yet. The real life examples I may have didn't really involve any realtime stock management, but were rather just recording paperwork already done manually. We did recalculate stock on each save of a document, but didn't have enough traffic on any item to be anywhere near this kind of embrace, so of course it worked flawlessly.