Using RowLock to enforce Once-And-Only-Once

Question

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.

Answer 1

You can use the OUTPUT clause to see if the chain of events should be "fired":

My test-bed:

CREATE TABLE dbo.OrderedChainFlag
(
    IsChainFired BIT NOT NULL
        CONSTRAINT DF_OrderedChainFlag_IsChainFired
        DEFAULT ((0))
    , OrderID INT NOT NULL
);

CREATE INDEX IX_OrderedChainFlag_Firing
ON dbo.OrderedChainFlag(OrderID, IsChainFired);

GO

INSERT INTO dbo.OrderedChainFlag (OrderID)
VALUES (123456);

Run this to see the output:

UPDATE OrderedChainFlag 
SET IsChainFired = 1 
OUTPUT inserted.IsChainFired, inserted.OrderID
WHERE OrderId = 123456 
    and IsChainFired = 0;

Use a DataReader to read the results of the UPDATE statement; if a row is returned by .Read() you know the table was updated, and effectively, you can run the chain of events.

Running the UPDATE statement above will only ever provide output if it actually updated the IsChainFired column to 1. You can prove that by running the update statement twice. The first one shows output, the second one does not.

Since this one statement is atomic, one-and-only-one update will succeed, without affecting lock escalation, assuming you have a good index on OrderId and IsChainFired, and that index is configured WITH (ALLOW_ROW_LOCKS = ON).

---

Second option, which is a slight variation on the above, would be to not specify the next OrderID to be processed; instead simply select the next one that has IsChainFired = 0:

CREATE INDEX IX_OrderedChainFlag_IsFired
ON dbo.OrderedChainFlag(IsChainFired);

UPDATE TOP(1) OrderedChainFlag 
SET IsChainFired = 1 
OUTPUT inserted.IsChainFired, inserted.OrderID
WHERE IsChainFired = 0;

The UPDATE statement in this case returns the OrderID for a single row directly from the IX_OrderedChainFlag_IsFired index.

I inserted over 750,000 rows into the OrderedChainFlag table in my test-bed, and ran the above UPDATE TOP(1) statement, with the index I mention in place, and got the following plan:

A (arguable) bonus here is you could potentially return more than TOP(1) rows for processing, perhaps using multiple threads in your Web Service.

You should probably also read this question, along with the excellent answers on concurrency.