Update with CTE & OUTPUT blocks on concurrent calls

I was having deadlock issues, so I started using this CTE trick that I read somewhere. No more deadlocks.

But now each client that I add slows down (blocks?) the stored procedure. E.g. a 1-2 second update with 1 client becomes 2-4 seconds for two clients. (This simple metaphor query runs at 0.001 seconds, but gets up to like 0.03 after a handful of clients -- so the issue is independent of my real-world implementation.)

Is it a locking issue? Do I need to wrap it in a (some kind of) transaction?

WITH UpdateView AS (
    SELECT TOP 1 W.*
    FROM [WidgetSandbox].[dbo].[Widgets] W
    INNER JOIN [WidgetSandbox].[dbo].[Sizes] S ON W.SizeId = S.Id
    WHERE W.StatusId = @availableStatusId
    AND W.ColorCode = @colorCode
    ORDER BY S.DiameterInches
)

UPDATE UpdateView 
SET StatusId = @soldOutStatusId
OUTPUT INSERTED.Id INTO @outputIds;

SET @singleUpdatedId = (SELECT TOP 1 Id FROM @outputIds);

SELECT * FROM [WidgetSandbox].[dbo].[Widgets]
WHERE Id = @singleUpdatedId;

I wish I had a better idea of what the specific problem is, but as it is I'm just stuck....

DDL, if it helps: https://gist.github.com/RobertBaldini/3740c7bb85eea47d7fe63cb8602ac2d6

Repo: https://github.com/RobertBaldini/WidgetSandbox

Many of the issues you see are being caused by an inefficient execution plan:

Not that the supplied plan and query matches the question, but even so, I'm working with what was provided.

Anyway, you should implement the Name column data type changes (from nvarchar(max)) that I mentioned in your previous question. More importantly, you need to add the indexes I recommended, so the row to update can be found without sorting:

CREATE NONCLUSTERED INDEX IX_dbo_Sizes__DiameterInches
ON dbo.Sizes (DiameterInches);

CREATE NONCLUSTERED INDEX IX_dbo_Widgets__SizeId_ColorCode_StatusId__Name
ON dbo.Widgets (SizeId, ColorCode, StatusId)
INCLUDE (Name);

You can then update the status of the selected item and return the row affected with:

DECLARE
    -- Constant values guessed, replace with the real ones
    @availableStatusId integer = 1,
    @soldOutStatusId integer= 9,
    @colorCode nvarchar(6) = N'Red';

WITH UpdateView AS 
(
    SELECT TOP (1)
        W.Id,
        W.Name,
        W.StatusId,
        W.ColorCode,
        W.SizeId
    FROM dbo.Widgets AS W WITH (UPDLOCK, ROWLOCK, READPAST)
    JOIN dbo.Sizes AS S 
        ON W.SizeId = S.Id
    WHERE 
        W.StatusId = @availableStatusId
        AND W.ColorCode = @colorCode
    ORDER BY 
        S.DiameterInches
)
UPDATE UpdateView 
SET StatusId = @soldOutStatusId
OUTPUT
    Inserted.Id,
    Inserted.Name,
    Inserted.StatusId,
    Inserted.ColorCode,
    Inserted.SizeId;

Note the UPDLOCK, ROWLOCK, READPAST hints there. These are the standard hints needed for concurrent FIFO access to queue-type tables. See Using Tables as Queues by Remus Rusanu for more information.

The execution plan shape you should get (with no sorts!) is:

As an aside, unless you have very good reasons, your database should not be using SET AUTO_CLOSE ON.

StackExchange Database Administrators Q#135842, answer score: 8