Need Help Troubleshooting Sql Server 2005 Deadlock Scenario

I am running into a deadlock scenario where the only participants in the deadlock appear to be a single table and a single stored procedure that deletes from that table. I drew that conclusion based on my analysis of the sql error log at the time of several of these deadlocks, using the MSDN article below as a guideline to decipher the trace in the error log.

The table DEXTable and the stored procedure ClearDEXTableRows are defined below. There is another stored procedure InsertDEXTableRow that inserts rows into DEXTable, but that proc does not seem to be involved in the deadlock based on the entries in the sql error log.

The DEXTable has ~8.3 million rows in it, and tends to grow steadily. The Respondent table is also large and tends to grow steadily.

It is accessed from a high traffic volume website with pages that frequently call ClearDEXTableRows and InsertDEXTableRow in quick succession.

The deadlock has occurred between 0 and 9 times per day for the past 10 days.

I've enabled sql trace for 1222 (using DBCC TRACEON 1222) and just recently enabled flag 1204. There's a good description of the output for these flags on Detecting and Ending Deadlocks
My questions are:

Does it make sense that only this one stored procedure ClearDEXTableRows is the cause of the deadlock?

If so, can anyone offer a good explanation of how this can happen and recommend a way to fix it?

My suspicion is that the DELETE statements are causing contention on the PK for DEXTable which needs to be rebuilt frequently.

If not, what additional trace should I enable to dig deeper into the cause of the deadlock? (I do want to learn here)

```
-- Table definition
CREATE TABLE [dbo].DEXTableWITH (PAD_INDEX = OFF, STATISTICS_NORECOMP

Three things leap out:

-
Your DELETE is on the 2nd column (RespondentID) of the current PK which means a scan, not a seek.

-
Pointless ROWLOCK hint

-
Your "UPSERT" pattern is not concurrency safe. The test for existence may pass for 2 overlapping (in time) concurrent threads giving an error.

To fix

-
Reverse your PK order in DEXTable to (RespondentID, ExportID). Or add a separate index on RespondentID alone. Personally, I'd probably reverse the PK order.

-
Remove ROWLOCK hint. If it continues after index and UPSERT changes suggested here, try UPDLOCK, but only after checking for parallelism

-
Check for parallelism in the plan: try MAXDOP 1 to restrict. Try this before UPDLOCK

-
Use the "JFDI" UPSERT pattern. That is, the INSERT will check uniqueness anyway so just INSERT, if it fails then UPDATE.

For SQL Server 2005 (you'd use MERGE on SQL Server 2008+)

BEGIN TRY

    BEGIN TRY
        INSERT DEXTable (ExportID, RespondentID, Exported)
        VALUES (@ExportID, @RespondentID, getdate())
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 2627
            UPDATE DEXTable
            SET Exported = getdate()
            WHERE ExportID = @ExportID AND RespondentID = @RespondentID
        ELSE
        BEGIN
           ..process real error
           RAISERROR (...)
        END
    END CATCH

END TRY
BEGIN CATCH
    ..process
    RAISERROR (...)
END CATCH

Finally, MSDN recommends that you trap deadlock errors and re-try on the client side. This should part of your data access layer that handles all your SQL calls.

StackExchange Database Administrators Q#8351, answer score: 7