I was working on a deadlocking issue a few days ago at a client The transaction that was continuously getting deadlocked utilized three SQL statements to checkout and return rows from a queue for the client’s application.
Before going into the problem here’s a little code to setup the tables for the examples:
USE tempdb GO IF OBJECT_ID('tempdb.dbo.SalesOrderDetailQueue') IS NOT NULL DROP TABLE dbo.SalesOrderDetailQueue SELECT *, CAST(0 as bit) as IsVerified INTO dbo.SalesOrderDetailQueue FROM AdventureWorks.Sales.SalesOrderDetail
The SQL statements that was causing the deadlocking looked similar to the following:
DECLARE @CarrierTrackingNumber nvarchar(50) ,@IsVerified bit SELECT @CarrierTrackingNumber = 'AnAwesomeTrackingNumber' ,@IsVerified = 1 DECLARE @Queue table ( SalesOrderID int ,SalesOrderDetailID int ,IsVerified bit ) INSERT INTO @Queue (SalesOrderID, SalesOrderDetailID, IsVerified) SELECT TOP 1 SalesOrderID ,SalesOrderDetailID ,IsVerified FROM dbo.SalesOrderDetailQueue WHERE IsVerified = 0 ORDER BY SalesOrderDetailID DESC UPDATE s SET IsVerified = @IsVerified ,CarrierTrackingNumber = @CarrierTrackingNumber ,ModifiedDate = GETDATE() FROM dbo.SalesOrderDetailQueue s INNER JOIN @Queue q ON s.SalesOrderID = q.SalesOrderID AND s.SalesOrderDetailID = q.SalesOrderDetailID SELECT SalesOrderID ,SalesOrderDetailID ,@IsVerified AS IsVerified ,@CarrierTrackingNumber FROM @Queue
This is obviously not the clients production code, but a close facsimile. There are three basic components to the transaction:
- Find the first item in the queue
- Lock the item the was found
- Return the item to the application
With some features that were released with SQL Server 2005, I was able to rewrite the above SQL statement into a single statement that resolved the deadlocking issues. Before going into the final solution, I’ll start by showing a rewrite that didn’t work that I’ve accidentally tried to use a few times in the past.
Instead of a finding the first item in a separate SQL statement, use the TOP and ORDER BY clauses to get the first item. And then use the OUTPUT clause to return the desired information rather than using the a third SQL statement to return the data selected.
UPDATE TOP (1) UpdateVolumeSimReaders SET IsVerified = @IsVerified ,CarrierTrackingNumber = @CarrierTrackingNumber ,ModifiedDate = GETDATE() OUTPUT INSERTED.SalesOrderID ,INSERTED.SalesOrderDetailID ,@IsVerified AS IsVerified ,@CarrierTrackingNumber WHERE IsVerified = 0 ORDER BY SalesOrderDetailID DESC
If you look at the sample code, it looks like a good solution. The TOP feature is used to get the first row, then OUTPUT clause is used to return the data, and finally an ORDER BY clause is used to sort the results so that the requested row would be returned.
Unfortunately, ORDER BY doesn’t work with an UPDATE statement. But before hope is lost, there is an alternative. First, the ROW_NUMBER function can be used to provide a sequential number for each row in the table across the SalesOrderDetailID. And since we are only concerned about the first sorted row in the table the UPDATE only needs to be applied to the row where the ROW_NUMBER function returns the value 1.
Of course, there is another catch. Windowed functions, in this case ROW_NUMBER, can only appear in SELECT or ORDER BY statements. To get around this, a Common Table Expressions (CTE) can be used. The CTE allows a column with the ROW_NUMBER function to be added to the table which can then be used in the UPDATE statement in the WHERE clause.
The final statement looks like this:
DECLARE @CarrierTrackingNumber nvarchar(50) ,@IsVerified bit SELECT @CarrierTrackingNumber = 'AnAwesomeTrackingNumber' ,@IsVerified = 1 ;WITH UpdateSalesOrderDetailQueue AS ( SELECT SalesOrderID ,SalesOrderDetailID ,IsVerified ,CarrierTrackingNumber ,ModifiedDate ,ROW_NUMBER() OVER(ORDER BY SalesOrderDetailID DESC) as RowID FROM dbo.SalesOrderDetailQueue WHERE IsVerified = 0 and ROW_NUMBER() OVER(ORDER BY SalesOrderDetailID DESC) = 1 ) UPDATE UpdateSalesOrderDetailQueue SET IsVerified = @IsVerified ,CarrierTrackingNumber = @CarrierTrackingNumber ,ModifiedDate = GETDATE() OUTPUT INSERTED.SalesOrderID ,INSERTED.SalesOrderDetailID ,@IsVerified AS IsVerified ,@CarrierTrackingNumber WHERE RowID = 1
The end result for the client was that all deadlocking by the application was resolved and performance was obviously lifted significantly.
Anyone have any other solutions to this same issue?