SQL Server Deadlock – Trace Flag Running but No Message

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.

First of all, the process is holding IX and want to convert to U. This is expected with the hint in the query.

Both queries will take the IX lock during the select and then convert to U when the change (if any) needs to happen.

This can be solved by adding: WITH (XLOCK) as a hint to the temporary table.