Update with CTE & OUTPUT blocks on concurrent calls
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.