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