Deadlock in SQL 2008 on insert and update

Question

I am getting a deadlock on concurrent users for insertion on my table. The sp which I am firing is relatively simple

SET NOCOUNT ON;

begin try
    begin tran

    DECLARE @idoc INT
    declare @ProcessedResponse table (
      candidateInstanceID  int,
      assessmetID          int,
      sectionID            int,
      itemID               int,
      clientID             int,
      displayTypeID        int,
      respondedAt          datetime,
      responseTime         float,
      marksObtained        float,
      processedresponseXML xml,
      resultDescription    varchar(255),
      reportedIssue        bit )

    -- insert into @PartialResponse 
    EXEC sp_xml_preparedocument
      @idoc OUTPUT,
      @Processed_Responses

    insert into @ProcessedResponse
    SELECT *
    FROM   OPENXML(@idoc, '/Processed_Responses/Processed_Response', 1)
              WITH (Candidate_Instance_ID int,
                    Assessment_ID         int,
                    Section_ID            int,
                    Item_ID               int,
                    Client_ID             int,
                    Display_Type_ID       int,
                    Responded_At          datetime,
                    Response_Time         float,
                    Marks_Obtained        float,
                    Processed_XML         text,
                    Result_Description    text,
                    Reported_Issue        bit)

    INSERT INTO Processed_Response
                (Candidate_Instance_ID,
                 Assessment_ID,
                 Section_ID,
                 Client_ID,
                 Item_ID,
                 Display_Type_ID,
                 Responded_At,
                 Response_Time,
                 Marks_Obtained,
                 Processed_XML,
                 Result_Description,
                 Reported_Issue)
    SELECT pr.candidateInstanceID,
           pr.assessmetID,
           pr.sectionID,
           pr.clientID,
           pr.itemID,
           pr.displayTypeID,
           cast(pr.respondedAt as datetime),
           pr.responseTime,
           case pr.marksObtained
             when 0 then null
             else pr.marksObtained
           end,
           CAST(pr.processedresponseXML AS XML),
           pr.resultDescription,
           pr.reportedIssue
    FROM   @ProcessedResponse pr

    update pr
    set    pr.Client_ID = a.Assessment_Owner
    from   Processed_Response pr
           join Assessment a
             on pr.Assessment_ID = a.Assessment_Id
    where  pr.Candidate_Instance_ID = @Candidate_Instance_ID

    update Candidate_Instance
    set    Instance_Status = 'Completed',
           Instance_End_Time = GETDATE()
    where  Candidate_Instance_Id = @Candidate_Instance_ID

    commit tran
end try

begin catch
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();

    rollback tran

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage,-- Message text.
               @ErrorSeverity,-- Severity.
               @ErrorState -- State.
    );
end catch

Can anyone help me out thanks.

Does `Processed_Response` have a foreign key relationship with `Candidate_Instance`? What indexes are on these tables? If there is no index on `Processed_Response.Client_ID` you should probably add one. — Martin Smith, Mar 25 '11 at 14:08
Is this really the code? Because in the code you've shown, ` @Candidate_Instance_ID` is never initialized. — Damien_The_Unbeliever, Mar 25 '11 at 15:54
@Damien The OP mentions this is an sp - I assumed that was a parameter — Martin Smith, Mar 25 '11 at 16:12

score 0 · Accepted Answer · answered Mar 25 '11 at 14:15

it takes two bits of code to deadlock, so is this same code deadlocking itself? It is hard to diagnose this without knowing the table structures and index usage. is your update-select (update pr... ) table scanning and locking the entire table? etc.

without a doubt, I'd move the BEGIN TRANS to just before the INSERT INTO Processed_Response, you don't need to put all the local variable stuff in the transaction. keep your transactions as short as possible.

set up SQL Server Profiler and get a dead lock graph trace of this and you'll see the two pieces of SQL that are hitting each other, and go from there.

Deadlock in SQL 2008 on insert and update

1 Answers1