SQL Server Performance

Deadlock issue in SQL Server 2005

Discussion in 'ALL SQL SERVER QUESTIONS' started by DilliGrg, Mar 16, 2012.

  1. DilliGrg Member

    Hello Everyone,
    I have this deadlock graph from one of our systems. The deadlock seem to be happening with dbo.ScoreNote table. I can use all the help I can get on identifying and resolving this deadlock issue.

    Code:
    <deadlock-list>
 <deadlock victim="process8e31d68">
  <process-list>
   <process id="process3c092e8" taskpriority="0" logused="768" waitresource="OBJECT: 6:277576027:8 " waittime="4140" ownerId="899661708" 
    transactionname="implicit_transaction" lasttranstarted="2012-03-14T21:17:58.287" XDES="0x13f19fd130" lockMode="IS" schedulerid="9" kpid="8124" status="suspended" 
    spid="72" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2012-03-14T21:17:58.287" lastbatchcompleted="2012-03-14T21:17:58.283" clientapp="jTDS" 
    hostname="PLAXORIN003" hostpid="123" loginname="GBuser" isolationlevel="read committed (2)" xactid="899661708" currentdb="6" lockTimeout="4294967295" clientoption1="539099168" clientoption2="128058">
    <executionStack>
     <frame procname="Gradebook.dbo.ut_Score_Update_ScoreHistory" line="9" stmtstart="248" stmtend="992" sqlhandle="0x03000600d64334038c2b1500949900000000000000000000">
INSERT INTO ScoreHistory(
            ScoreID,
            Score,    
            InstructorNote,
            StudentNote
            )
        SELECT
            ScoreID,
            Score,
            (SELECT InstructorNote FROM ScoreNote WHERE ScoreID = deleted.ScoreID),
            (SELECT StudentNote FROM ScoreNote WHERE ScoreID = deleted.ScoreID)
        FROM deleted

        WHERE
            (Select StatusID from Score where ScoreID = deleted.ScoreID) = 1     </frame>
     <frame procname="adhoc" line="1" stmtstart="68" sqlhandle="0x020000009a464b032ceb25e89690e3370e8b5daa50655776">
     
UPDATE [Score] set [Score] = @1  WHERE [ScoreID]=@2     </frame>
     <frame procname="adhoc" line="1" stmtend="116" sqlhandle="0x0200000043f132175f8903b116b6e4fd01571c1032098b0c">
     
update Score set Score = 3.58 where ScoreID = 142601340998     </frame>
    </executionStack>
    <inputbuf>
    
update Score set Score = 3.58 where ScoreID = 142601340998 

update ScoreNote set StudentNote = &apos;No assignment submitted. Please see course information for due dates. The COO is an old form that is no longer being used by the university. 
        Please always submit the most current forms for your courses. - 3/14/12 - Assignment submitted two days late. Please see comments on document. 
        A Turnitin report is required for every paper assignment.&apos;, InstructorNote = &apos;&apos; 
where ScoreID = 142601340998    </inputbuf>
   </process>
   <process id="process8e1d2e8" taskpriority="0" logused="20012" waittime="3515" schedulerid="3" kpid="5480" status="suspended" spid="289" sbid="0" ecid="35" priority="0" 
   transcount="0" lastbatchstarted="2012-03-14T21:17:41.343" lastbatchcompleted="2012-03-14T21:17:41.343" clientapp="SQLAgent - TSQL JobStep (Job 0x254C17E890E5BE4393FDB27CD58F2E37 : Step 1)" 
   hostname="PWXDSQLV105" hostpid="5968" isolationlevel="read committed (2)" xactid="899645666" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="Gradebook.dbo.usp_DeleteOlderGradeBookRecords_TenWeeks" line="104" stmtstart="6336" stmtend="6930" sqlhandle="0x0300060065a2f55062d00c00289f00000100000000000000">
     
DELETE TOP (@BatchSize) FROM sn
        FROM dbo.ScoreNote sn 
        JOIN dbo.Score s 
        ON s.ScoreID = sn.ScoreID 
        JOIN dbo.StudentCourseOffering sco 
        ON sco.StudentCourseOfferingID = s.StudentCourseOfferingID
        JOIN #tempCourseOffering tmp 
        ON tmp.CourseOfferingID = sco.CourseOfferingID     </frame>
     <frame procname="adhoc" line="1" sqlhandle="0x0100060028047a02f0511560110000000000000000000000">
     
EXEC dbo.usp_DeleteOlderGradeBookRecords_TenWeeks
    @CutOffDate = NULL
    ,@BatchSize = 1000     </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
   </process>
   <process id="process8e31d68" taskpriority="0" logused="0" waitresource="KEY: 6:297569039745024 (3400381188ae)" waittime="3656" ownerId="899645666" transactionname="DELETE" 
   lasttranstarted="2012-03-14T21:17:48.510" XDES="0x91e01a760" lockMode="S" schedulerid="5" kpid="9148" status="suspended" spid="289" sbid="0" ecid="52" priority="0" transcount="0" 
   lastbatchstarted="2012-03-14T21:17:41.343" lastbatchcompleted="2012-03-14T21:17:41.343" clientapp="SQLAgent - TSQL JobStep (Job 0x254C17E890E5BE4393FDB27CD58F2E37 : Step 1)" 
   hostname="PWXDSQLV105" hostpid="5968" isolationlevel="read committed (2)" xactid="899645666" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="Gradebook.dbo.usp_DeleteOlderGradeBookRecords_TenWeeks" line="104" stmtstart="6336" stmtend="6930" sqlhandle="0x0300060065a2f55062d00c00289f00000100000000000000">
     
DELETE TOP (@BatchSize) FROM sn
        FROM dbo.ScoreNote sn 
        JOIN dbo.Score s 
        ON s.ScoreID = sn.ScoreID 
        JOIN dbo.StudentCourseOffering sco 
        ON sco.StudentCourseOfferingID = s.StudentCourseOfferingID
        JOIN #tempCourseOffering tmp 
        ON tmp.CourseOfferingID = sco.CourseOfferingID     </frame>
     <frame procname="adhoc" line="1" sqlhandle="0x0100060028047a02f0511560110000000000000000000000">
     
EXEC dbo.usp_DeleteOlderGradeBookRecords_TenWeeks
    @CutOffDate = NULL
    ,@BatchSize = 1000     </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
   </process>
   <process id="process8e4f048" taskpriority="0" logused="10014" waittime="3593" schedulerid="8" kpid="2472" status="suspended" spid="289" sbid="0" ecid="18" priority="0" 
   transcount="0" lastbatchstarted="2012-03-14T21:17:41.343" lastbatchcompleted="2012-03-14T21:17:41.343" clientapp="SQLAgent - TSQL JobStep (Job 0x254C17E890E5BE4393FDB27CD58F2E37 : Step 1)" 
   hostname="PWXDSQLV105" hostpid="5968" isolationlevel="read committed (2)" xactid="899645666" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="Gradebook.dbo.usp_DeleteOlderGradeBookRecords_TenWeeks" line="104" stmtstart="6336" stmtend="6930" sqlhandle="0x0300060065a2f55062d00c00289f00000100000000000000">
     
DELETE TOP (@BatchSize) FROM sn
        FROM dbo.ScoreNote sn 
        JOIN dbo.Score s 
        ON s.ScoreID = sn.ScoreID 
        JOIN dbo.StudentCourseOffering sco 
        ON sco.StudentCourseOfferingID = s.StudentCourseOfferingID
        JOIN #tempCourseOffering tmp 
        ON tmp.CourseOfferingID = sco.CourseOfferingID     </frame>
     <frame procname="adhoc" line="1" sqlhandle="0x0100060028047a02f0511560110000000000000000000000">
     
EXEC dbo.usp_DeleteOlderGradeBookRecords_TenWeeks
    @CutOffDate = NULL
    ,@BatchSize = 1000     </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <objectlock lockPartition="8" objid="277576027" subresource="FULL" dbid="6" objectname="Gradebook.dbo.ScoreNote" id="lockaa0e66280" mode="X" associatedObjectId="277576027">
    <owner-list>
     <owner id="process8e1d2e8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process3c092e8" mode="IS" requestType="wait"/>
    </waiter-list>
   </objectlock>
   <keylock hobtid="297569039745024" dbid="6" objectname="Gradebook.dbo.Score" indexname="PK_Score" id="lock12830f3d00" mode="X" associatedObjectId="297569039745024">
    <owner-list>
     <owner id="process3c092e8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process8e31d68" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <exchangeEvent id="port8025fd40" nodeId="14">
    <owner-list>
     <owner event="e_waitNone" type="producer" id="process8e31d68"/>
    </owner-list>
    <waiter-list>
     <waiter event="e_waitPipeGetRow" type="consumer" id="process8e4f048"/>
    </waiter-list>
   </exchangeEvent>
   <exchangeEvent id="port8025e480" nodeId="12">
    <owner-list>
     <owner event="e_waitPipeGetRow" type="consumer" id="process8e4f048"/>
    </owner-list>
    <waiter-list>
     <waiter event="e_waitPipeNewRow" type="producer" id="process8e1d2e8"/>
    </waiter-list>
   </exchangeEvent>
  </resource-list>
 </deadlock>
</deadlock-list>
    DilliGrg, Mar 16, 2012
    #1

  2. Vivek Singh New Member

    Hello,

    Deadlock Graph analysis:

    spid="72 which is for Stored Proc {procname="Gradebook.dbo.ut_Score_Update_ScoreHistory"}

    The SPID is waiting for resource on the object {waitresource="OBJECT: 6:277576027:8}
    NOTE: {waitresource="OBJECT: 6:277576027:8} 6 is the database id and 277576027 is the object id.

    currentdb="6"

    DBCC input buffer statement is as mentioned below.

    INSERT INTO ScoreHistory(
    ScoreID,
    Score,
    InstructorNote,
    StudentNote
    )
    SELECT
    ScoreID,
    Score,
    (SELECT InstructorNote FROM ScoreNote WHERE ScoreID = deleted.ScoreID),
    (SELECT StudentNote FROM ScoreNote WHERE ScoreID = deleted.ScoreID)
    FROM deleted

    WHERE
    (Select StatusID from Score where ScoreID = deleted.ScoreID) = 1 </frame>
    <frame procname="adhoc" line="1" stmtstart="68" sqlhandle="0x020000009a464b032ceb25e89690e3370e8b5daa50655776">

    UPDATE [Score] set [Score] = @1 WHERE [ScoreID]=@2 </frame>
    <frame procname="adhoc" line="1" stmtend="116" sqlhandle="0x0200000043f132175f8903b116b6e4fd01571c1032098b0c">

    update Score set Score = 3.58 where ScoreID = 142601340998 </frame>
    </executionStack>
    <inputbuf>

    update Score set Score = 3.58 where ScoreID = 142601340998

    update ScoreNote set StudentNote = &apos;No assignment submitted. Please see course information for due dates. The COO is an old form that is no longer being used by the university.
    Please always submit the most current forms for your courses. - 3/14/12 - Assignment submitted two days late. Please see comments on document.
    A Turnitin report is required for every paper assignment.&apos;, InstructorNote = &apos;&apos;
    where ScoreID = 142601340998

    The Deadlock victim is SPID 289 which is for stored proc "Gradebook.dbo.usp_DeleteOlderGradeBookRecords_TenWeeks

    I see there is a SQLAGENT job which is invoking the Stored Proc.
    clientapp="SQLAgent - TSQL JobStep (Job 0x254C17E890E5BE4393FDB27CD58F2E37 : Step 1)"
    Convert 0x254C17E890E5BE4393FDB27CD58F2E37 to Decimal and you will get the Step id .
    Please use the Step id to identify the job id by quering sysjobsteps table in msdb database.

    You should reschedule the job to some other time so that it doesn’t waits and further create Deadlocks in your system.

    The job is waiting on the resource 6:297569039745024

    6 above is the database id and remaining is the object id , Query against sysobjects to see which object it is waiting for resource.

    You can analyze the query in the first proc Gradebook.dbo.ut_Score_Update_ScoreHistory to see if we have some missing indexes in question, Use Database Tuning advisor for the same.

    Creating Missing Indexes in the tables where these Stored procs retreive the data will resolve your issue.

    Please let me know if you need further help
    Cheers
    Vivek
    Vivek Singh, Mar 19, 2012
    #2

  3. DilliGrg Member

    Vivek,
    Appreciated for your reply. After doing further analysis, the issue may not be on missing indexes (both tables have proper indexes) but the constraint on dbo.ScoreNote table where it has both UPDATE CASCADE and DELETE CASCADE. Just to clarify, "Gradebook.dbo.ut_Score_Update_ScoreHistory" is a FOR UPDATE trigger and Gradebook.dbo.usp_DeleteOlderGradeBookRecords_TenWeeks is a batch (1000) delete job runs every week. We tried running it in different windows and still fails. Rest of the update/insert are coming directly from the java based application. I have attached the table structure and trigger code to see if you have a different opinion on this.

    Code:
    CREATE TABLE [dbo].[ScoreNote](
    [ScoreID] [numeric](28, 0) NOT NULL,
    [StudentNote] [varchar](3000) NULL,
    [InstructorNote] [varchar](3000) NULL,
    [DateCreated] [smalldatetime] NOT NULL CONSTRAINT [DF_ScoreNote_DateCreated]  DEFAULT (getdate()),
    [DateModified] [smalldatetime] NOT NULL CONSTRAINT [DF_ScoreNote_DateModified]  DEFAULT (getdate()),
    [UserCreated] [varchar](15) NOT NULL CONSTRAINT [df_ScoreNote_UserCreated]  DEFAULT (host_name()),
    [UserModified] [varchar](15) NOT NULL CONSTRAINT [df_ScoreNote_UserModified]  DEFAULT (host_name()),
CONSTRAINT [PK_ScoreNote] PRIMARY KEY CLUSTERED
(
    [ScoreID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON
    , ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Score](
    [ScoreID] [numeric](28, 0) NOT NULL,
    [StudentCourseOfferingID] [int] NOT NULL,
    [AssessmentItemWeekID] [numeric](28, 0) NOT NULL,
    [Score] [float] NOT NULL,
    [StatusID] [tinyint] NOT NULL,
    [DateCreated] [smalldatetime] NOT NULL CONSTRAINT [DF_Score_DateCreated]  DEFAULT (getdate()),
    [DateModified] [smalldatetime] NOT NULL CONSTRAINT [DF_Score_DateModified]  DEFAULT (getdate()),
    [UserCreated] [varchar](15) NOT NULL CONSTRAINT [df_Score_UserCreated]  DEFAULT (host_name()),
    [UserModified] [varchar](15) NOT NULL CONSTRAINT [df_Score_UserModified]  DEFAULT (host_name()),
CONSTRAINT [PK_Score] PRIMARY KEY CLUSTERED
(
    [ScoreID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON
    , ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IDX_Score_AssessmentItemWeekID] ON [dbo].[Score]
(
    [AssessmentItemWeekID] ASC
)
GO

CREATE NONCLUSTERED INDEX [IDX_Score_SCOF_AIW_ScoreID] ON [dbo].[Score]
(
    [StudentCourseOfferingID] ASC,
    [AssessmentItemWeekID] ASC,
    [ScoreID] ASC
)
INCLUDE ( [Score],
[StatusID],
[DateModified])
GO

CREATE NONCLUSTERED INDEX [idx_score_statusid] ON [dbo].[Score]
(
    [StatusID] ASC
)
GO

CREATE NONCLUSTERED INDEX [IDX_Score_StudentCourseOfferingID] ON [dbo].[Score]
(
    [StudentCourseOfferingID] ASC
)
GO

CREATE UNIQUE NONCLUSTERED INDEX [IDX_Score_StudentCourseOfferingId_AssessmentItemWeekID] ON [dbo].[Score]
(
    [StudentCourseOfferingID] ASC,
    [AssessmentItemWeekID] ASC
)
GO

/****** Object:  ForeignKey [FK_Score_AssessmentItemWeek]    Script Date: 03/15/2012 11:05:18 ******/
ALTER TABLE [dbo].[Score]  WITH NOCHECK ADD  CONSTRAINT [FK_Score_AssessmentItemWeek] FOREIGN KEY([AssessmentItemWeekID])
REFERENCES [dbo].[AssessmentItemWeek] ([AssessmentItemWeekID])
NOT FOR REPLICATION
GO

ALTER TABLE [dbo].[Score] CHECK CONSTRAINT [FK_Score_AssessmentItemWeek]
GO
/****** Object:  ForeignKey [FK_Score_Status]    Script Date: 03/15/2012 11:05:18 ******/
ALTER TABLE [dbo].[Score]  WITH NOCHECK ADD  CONSTRAINT [FK_Score_Status] FOREIGN KEY([StatusID])
REFERENCES [dbo].[Status] ([StatusID])
NOT FOR REPLICATION
GO

ALTER TABLE [dbo].[Score] CHECK CONSTRAINT [FK_Score_Status]
GO
/****** Object:  ForeignKey [FK_Score_StudentCourseOffering]    Script Date: 03/15/2012 11:05:18 ******/
ALTER TABLE [dbo].[Score]  WITH NOCHECK ADD  CONSTRAINT [FK_Score_StudentCourseOffering] FOREIGN KEY([StudentCourseOfferingID])
REFERENCES [dbo].[StudentCourseOffering] ([StudentCourseOfferingID])
NOT FOR REPLICATION
GO

ALTER TABLE [dbo].[Score] CHECK CONSTRAINT [FK_Score_StudentCourseOffering]
GO
/****** Object:  ForeignKey [FK_ScoreNote_Score]    Script Date: 03/15/2012 11:05:18 ******/
ALTER TABLE [dbo].[ScoreNote]  WITH NOCHECK ADD  CONSTRAINT [FK_ScoreNote_Score] FOREIGN KEY([ScoreID])
REFERENCES [dbo].[Score] ([ScoreID])
ON UPDATE CASCADE
ON DELETE CASCADE
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[ScoreNote] CHECK CONSTRAINT [FK_ScoreNote_Score]
GO

ALTER TRIGGER [dbo].[ut_Score_Update_ScoreHistory] ON [dbo].[Score]
FOR UPDATE
AS
SET NOCOUNT ON

IF UPDATE(Score)
    BEGIN
        INSERT INTO ScoreHistory(
            ScoreID,
            Score,
            InstructorNote,
            StudentNote
            )
        SELECT
            ScoreID,
            Score,
            (SELECT InstructorNote FROM ScoreNote WHERE ScoreID = deleted.ScoreID),
            (SELECT StudentNote FROM ScoreNote WHERE ScoreID = deleted.ScoreID)
        FROM deleted

        WHERE
            (Select StatusID from Score where ScoreID = deleted.ScoreID) = 1
    END
    DilliGrg, Mar 19, 2012
    #3

  4. Shehap Moderator

    Basically , without jumping to index parts , you have to bear in mind that any Bulk DML transactions with enabling triggers might end up with a culprit point of performance for:

    · Long running execution time

    · Much heavy locks on relevant tables.

    · Dirty data since all of these Bulk data would be uncommitted till triggers finish their job

    So I do recommend either :

    · To make a job to disable triggers while your job is run and return them back again .

    · Or to put a criteria at triggesr level to exclude one account only which is here the job owner from firing that if any update transaction undertaken by that account
    Shehap, Mar 21, 2012
    #4

  5. DilliGrg Member

    Thanks Shehap for your response. I can certainly look at those options. You don't think cascade delete and cascade update on ScoreNote table is contributing to the deadlock issue?
    DilliGrg, Mar 22, 2012
    #5

  6. Shehap Moderator

    For More legibility , deadlock incidents falls down under the accidental incidents category thereby its probability might increase by cascade update /delete option due to increasing the contention possibilities between DML transactions.

    Therefore , if you have intensive DML transactions ( OLTP system) , you have to look after :

    1. Indexing efficiency on relevant tables from 2 main angels:

    · Indexes size to reduce it to the least possible to reduce IO cost of DML transactions

    · Indexes de-fragmentation to maintain it periodically through index rebuild jobs (if index fragmentation >30%) and index re-organize (if index fragmentation <30%)

    2. Apply read committed snapshot isolation level using row versioning which might cut off a lot of locks + deadlocks issues

    Shehap, Mar 29, 2012
    #6

Share This Page