March 15, 2012 at 11:43 am
Hello Everyone,
This is the deadlock graph I have from one of our systems. I am not able to identify the actual cause of the deadlock even though there seem to have a lot of activities going on. Can you please help identify where the actual issue is happening and how can we resolve this issue. I will use all the help I can get.
<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 = '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.', InstructorNote = ''
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>
Thanks in advance.
March 15, 2012 at 11:51 am
Can you please post the definition of the ScoreNote and Score tables along with all indexes that they have. Also the definitions of the following procedures and triggers: ut_Score_Update_ScoreHistory, usp_DeleteOlderGradeBookRecords_TenWeeks
Is your Java app using implicit transactions?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 15, 2012 at 12:07 pm
GilaMonster (3/15/2012)
Can you please post the definition of the ScoreNote and Score tables along with all indexes that they have. Also the definitions of the following procedures and triggers: ut_Score_Update_ScoreHistory, usp_DeleteOlderGradeBookRecords_TenWeeksIs your Java app using implicit transactions?
Thank you Gail for the quick response. Here is structure for both tables including a trigger and indexes:
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
/****** Object: Trigger [ut_Score_Update_ScoreHistory] Script Date: 03/15/2012 11:05:19 ******/
CREATE 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
The DELETE operation is a background/maintenance job running a batch of 1000 every week during midnight. Do you want me to include the actual code for this delete?
Also, yes java application uses some of these smaller updates directly from the application.
March 15, 2012 at 2:46 pm
The trigger and procedure code please?
Does the java app use implicit transactions (will likely be a setting on the driver somewhere)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 15, 2012 at 3:09 pm
GilaMonster (3/15/2012)
The trigger and procedure code please?Does the java app use implicit transactions (will likely be a setting on the driver somewhere)?
Gail,
Here is the trigger code(mentioned in previous post as well 🙂 )
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
And the procedure:
ALTER PROCEDURE [dbo].[usp_DeleteOlderGradeBookRecords_TenWeeks]
(
@CutOffDate datetime = NULL
,@BatchSize int = 1000 --default batch size but can be adjusted as needed to delete
,@debug bit = 0 --set this to 1 to print the debug info
)
AS
BEGIN
SET NOCOUNT ON
--|declare local variables
DECLARE @RowCount int
,@msg varchar(128)
--|set the default cutoff date to 10 weeks
IF @CutOffDate IS NULL OR @CutOffDate = ''
BEGIN
SET @CutOffDate = CONVERT(VARCHAR(10) , DATEADD (WK , -10, GETDATE()) , 101)
END
ELSE
BEGIN
IF @CutOffDate > CONVERT(VARCHAR(10) , DATEADD (WK , -10, GETDATE()) , 101)
BEGIN
RAISERROR ('Cannot delete records from last ten weeks', 16, 1)
RETURN -1
END
END
--|create a temp table to capture courseofferingid used in delete
IF OBJECT_ID('tempdb..#tempCourseOffering') IS NOT NULL
DROP TABLE #tempCourseOffering
CREATE TABLE #tempCourseOffering
( CourseOfferingID numeric(20,0) NOT NULL PRIMARY KEY )
--|get the list of courseofferingid to be deleted
INSERT INTO #tempCourseOffering(CourseOfferingID)
SELECTDISTINCT CourseOfferingID
FROMdbo.CourseOffering WITH(nolock)
WHERE CourseEndDate < @CutOffDate
IF @debug=1
BEGIN
SET @msg = 'Start ScoreNote delete: ' + convert(varchar(60), getdate(), 109)
RAISERROR(@msg, 10,1) WITH NOWAIT
END
-- Loop through all records matching in the temporary table
-- using the WHILE loop construct
SET @RowCount = 1
WHILE (@RowCount > 0)
BEGIN
--Delete from ScoreNote
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
SET @RowCount = @@ROWCOUNT
IF @debug=1
BEGIN
SET @msg = 'Rows affected by ScoreNote delete = ' + str(@RowCount)
RAISERROR(@msg, 10,1) WITH NOWAIT
PRINT ' '
END
END
IF @debug=1
BEGIN
SET @msg = 'Start ScoreHistory delete: ' + convert(varchar(60), getdate(), 109)
RAISERROR(@msg, 10,1) WITH NOWAIT
END
-- Loop through all records matching in the temporary table
-- using the WHILE loop construct
SET @RowCount = 1
WHILE (@RowCount > 0)
BEGIN
--Delete from ScoreHistory
DELETE TOP (@BatchSize) FROM sh
FROM dbo.ScoreHistory sh
JOIN dbo.Score s
ON s.ScoreID = sh.ScoreID
JOIN dbo.StudentCourseOffering sco
ON sco.StudentCourseOfferingID = s.StudentCourseOfferingID
JOIN #tempCourseOffering tmp
ON tmp.CourseOfferingID = sco.CourseOfferingID
SET @RowCount = @@ROWCOUNT
IF @debug=1
BEGIN
SET @msg = 'Rows affected by ScoreHistory delete = ' + str(@RowCount)
RAISERROR(@msg, 10,1) WITH NOWAIT
PRINT ' '
END
END
IF @debug=1
BEGIN
SET @msg = 'Start Score delete: ' + convert(varchar(60), getdate(), 109)
RAISERROR(@msg, 10,1) WITH NOWAIT
END
-- Loop through all records matching in the temporary table
-- using the WHILE loop construct
SET @RowCount = 1
WHILE (@RowCount > 0)
BEGIN
--Delete from Score
DELETE TOP (@BatchSize) FROM s
FROM dbo.Score s
JOIN dbo.StudentCourseOffering sco
ON sco.StudentCourseOfferingID = s.StudentCourseOfferingID
JOIN #tempCourseOffering tmp
ON tmp.CourseOfferingID = sco.CourseOfferingID
SET @RowCount = @@ROWCOUNT
IF @debug=1
BEGIN
SET @msg = 'Rows affected by Score delete = ' + str(@RowCount)
RAISERROR(@msg, 10,1) WITH NOWAIT
PRINT ' '
END
END
IF @debug=1
BEGIN
SET @msg = 'Start StudentFinalGrade delete: ' + convert(varchar(60), getdate(), 109)
RAISERROR(@msg, 10,1) WITH NOWAIT
END
IF OBJECT_ID('tempdb..#tempCourseOffering') IS NOT NULL
DROP TABLE #tempCourseOffering
RETURN --end logic
END
As for the information on java application side, I am not very positive as I can't see how the app team is using these update statements.
March 15, 2012 at 8:24 pm
Do you think this cascading update/delete may have contributed to the deadlock issue?
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
March 16, 2012 at 4:37 am
Might do.
This is going to take a fair amount of analysis to work through. Will try to make time, but won't be today.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2012 at 12:13 pm
GilaMonster (3/16/2012)
Might do.This is going to take a fair amount of analysis to work through. Will try to make time, but won't be today.
Thank you Gail for your time. I was expecting some other members to step in but looks like no one wants to take a stab at it.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply