SQL Server Deadlocks – Monitoring Deadlocks in SQL Server 2014

Three things leap out:

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

2. Pointless ROWLOCK hint

3. 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

1. 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.

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

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

4. 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.

Something wrong with the viewer (looks like the bug in SSMS), but you always can open ERRORLOG in any other viewer, such as notepad and get exactly the same info