UPDATE – ORDER BY and Deadlocking

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:

1. Find the first item in the queue
2. Lock the item the was found
3. 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?