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:
-- 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
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.