Scope Scope - 6 months ago 8
SQL Question

How to execute part of the code only once while running multiple instances in T-SQL

I have a stored procedure that is called from business code. This code uses parallelism, so multiple instances of this SP could be running at the same time depending on some conditions.

There is some logic in this SP that I want to execute only once. I have a table (let's call it HISTORY) that holds a UID for the run and a DATETIME when this portion of the code is executed. Here's my flow:

SP BEGIN
-- some logic
IF certain conditions are met, check if HISTORY does not have an entry for the UID
1. Add an entry in HISTORY for the current UID
2. Run the once only code
SP END


The issue is that, at times, the logic above still gets executed multiple times if different instances reach that part at the same time. What can I do to ensure that it only runs once?

Thank you!

Answer
BEGIN TRANSACTION;
    INSERT [HISTORY](UID, ...)
    SELECT @UID, ...
    WHERE NOT EXISTS (
        SELECT * FROM [HISTORY] WITH (HOLDLOCK) WHERE UID = @UID
    );

    IF @@ROWCOUNT = 1 BEGIN;
      -- we inserted, do logic that should run only once
    END;
COMMIT;

HOLDLOCK (equivalent to running the transaction under SERIALIZABLE, but more granular) ensures that no other transaction running in parallel can insert an entry in HISTORY for that UID; any transaction that tries so will block until the first INSERT is finished and then return (since a row already exists). Ensure that an index on UID exists, otherwise it will lock a lot more than is healthy for performance.

Getting code like this right is always tricky, so make sure to test it in practice as well by stress-testing concurrent inserts for the same (and different) UID.