I have a Web Service that can fire off a chain of events for an order. It does this when it gets a message telling it to (max of about 2 per second).
The problem I have is that I have more than one instance of the Web Service, and sometimes different parts of the order can reach each instance at the exact same time. This causes the chain of events to fire off more than once for the order (bad).
So, I was thinking, I could make a table that is just the ID of the order and a flag saying if the "chain of events" has been fired off yet.
I could then start a transaction in my code. Something like this:
mySqlConnection.BeginTransaction(); // C# Code
I could select the row for the order in question like this:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT OrderId, IsChainFired FROM OrderedChainFlag WITH ( ROWLOCK, XLOCK, HOLDLOCK ) WHERE OrderId = 123456
Then, if IsChainFired is false, fire off the "Chain of Events". I would then run:
UPDATE OrderedChainFlag SET IsChainFired = 1 WHERE OrderId = 123456
and then run (even if IsChainFired is true):
myConnection.CommitTransaction(); // C# Code
This would prevent more than one instance of the service from reading that the chain is free to be run at the same time.
My worry (and my question) is lock escalation. If this stays on row locks then it will be a fantastic solution to my problem. But if the locks escalate to page or table, then I will have just created a bottleneck in my system.
So, is there anything I can do to ensure that this stays at the row lock level? (or is it even a good idea to use the database locking system as a semaphore like this?)
NOTE: OrderId would be the Primary Key, Clustered Index and Partitioning Index of the table.