deadlock problem

I have inherited a problem from the guy I have taken over from. Below
is the log error message followed by the procedure in question. Anybody
have any ideas why this is deadlocking?
2006-04-03 15:00:40.14 spid4 Node:1
2006-04-03 15:00:40.14 spid4 RID: 8:1:89142:1
CleanCnt:1 Mode: X Flags: 0x2
2006-04-03 15:00:40.14 spid4 Grant List 3::
2006-04-03 15:00:40.14 spid4 Owner:0x3571b2c0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
2006-04-03 15:00:40.14 spid4 SPID: 63 ECID: 0 Statement Type:
SELECT Line #: 71
2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
FeltexJob_Update;1
2006-04-03 15:00:40.14 spid4 Requested By:
2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
U SPID:58 ECID:0 Ec:(0x53BF3570) Value:0x2c2a82a0 Cost:(0/B4)
2006-04-03 15:00:40.14 spid4
2006-04-03 15:00:40.14 spid4 Node:2
2006-04-03 15:00:40.14 spid4 RID: 8:1:70483:3
CleanCnt:1 Mode: X Flags: 0x2
2006-04-03 15:00:40.14 spid4 Grant List 1::
2006-04-03 15:00:40.14 spid4 Owner:0x2c2a8b60 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:58 ECID:0
2006-04-03 15:00:40.14 spid4 SPID: 58 ECID: 0 Statement Type:
UPDATE Line #: 38
2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
FeltexJob_Update;1
2006-04-03 15:00:40.14 spid4 Requested By:
2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:63 ECID:0 Ec:(0x76F51570) Value:0x78fb0520 Cost:(0/B4)
2006-04-03 15:00:40.14 spid4 Victim Resource Owner:
2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:63 ECID:0 Ec:(0x76F51570) Value:0x78fb0520 Cost:(0/B4)
CREATE PROCEDURE Job_Update(@.JobID int,@.JobName
varchar(128),@.JobDescription varchar(512),
@.JobUserName varchar(60),@.JobEmailType int,@.JobEmailTo varchar(1000),
@.JobEmailCC varchar(255),@.JobClassName varchar(60),@.JobParameters
varbinary(5500),
@.JobDLLName varchar(60),@.JobDLLPathName varchar(60),@.CategoryId int,
@.FrequencyType int,@.UpdateBy varchar(30),@.UpdateDate datetime,
@.RowTS varbinary(8) Output )
-- WITH ENCRYPTION
AS
--
-- Update table Job
-- Uses Optimistic locking via RowTS. New RowTS is returned in @.RowTS
-- BEGIN & Commit Transaction is done in proc. Can also be done in VB.
-- If Update fails it returns an error and does a RaisError (Will force
VB error)
--
BEGIN
Declare @.OldTs VarBinary(8)
DECLARE @.nRowCount INT
-- Begin the transaction
BEGIN TRANSACTION
-- Retrieve and check timestamp. Update lock held until Commit (or
Rollback)
SELECT @.OldTs = RowTS from Job WHERE JobID = @.JobID
SELECT @.nRowCount = @.@.ROWCOUNT
IF @.nRowCount = 0
BEGIN
RaisError 50302 'Update failed - Job record was deleted by another
user'
GOTO PROC_ROLLBACK
END
IF @.OldTs <> @.RowTS
BEGIN
RaisError 50303 'Update failed - Job record was updated by another
user'
GOTO PROC_ROLLBACK
END
UPDATE dbo.Job Set JobName = @.JobName,
JobDescription = @.JobDescription,
JobUserName = @.JobUserName,
JobEmailType = @.JobEmailType,
JobEmailTo = @.JobEmailTo,
JobEmailCC = @.JobEmailCC,
JobClassName = @.JobClassName,
JobParameters = @.JobParameters,
JobDLLName = @.JobDLLName,
JobDLLPathName = @.JobDLLPathName,
CategoryId = @.CategoryId,
FrequencyType = @.FrequencyType,
UpdateBy = @.UpdateBy,
UpdateDate = @.UpdateDate,
RowTS = Convert(VarBinary(8),CURRENT_TIMESTAMP,21)
WHERE JobID = @.JobID
AND RowTS = @.OldTs
SELECT @.nRowCount = @.@.ROWCOUNT
IF @.@.error <> 0
BEGIN
RaisError 50301 'Job Update Failed'
GOTO PROC_ROLLBACK
END
IF @.nRowCount = 0
BEGIN
RaisError 50303 'Update failed - Job record was updated by another
user'
GOTO PROC_ROLLBACK
END
-- Get the new timestamp
SELECT @.RowTS = RowTS FROM Job WHERE JobID = @.JobID
-- Commit the Transaction
COMMIT TRANSACTION
RETURN(0)
PROC_ROLLBACK:
-- Rollback on Error
ROLLBACK TRANSACTION
RETURN (-301)
END
GOchris,
Can you check if this table has a clustered index?
Can you check if this table has a nonclustered index by [JobID]?
INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/kb/q169960/
AMB
"chris.nolan@.feltex.com" wrote:
> I have inherited a problem from the guy I have taken over from. Below
> is the log error message followed by the procedure in question. Anybody
> have any ideas why this is deadlocking?
>
> 2006-04-03 15:00:40.14 spid4 Node:1
> 2006-04-03 15:00:40.14 spid4 RID: 8:1:89142:1
> CleanCnt:1 Mode: X Flags: 0x2
> 2006-04-03 15:00:40.14 spid4 Grant List 3::
> 2006-04-03 15:00:40.14 spid4 Owner:0x3571b2c0 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
> 2006-04-03 15:00:40.14 spid4 SPID: 63 ECID: 0 Statement Type:
> SELECT Line #: 71
> 2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
> FeltexJob_Update;1
> 2006-04-03 15:00:40.14 spid4 Requested By:
> 2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
> U SPID:58 ECID:0 Ec:(0x53BF3570) Value:0x2c2a82a0 Cost:(0/B4)
> 2006-04-03 15:00:40.14 spid4
> 2006-04-03 15:00:40.14 spid4 Node:2
> 2006-04-03 15:00:40.14 spid4 RID: 8:1:70483:3
> CleanCnt:1 Mode: X Flags: 0x2
> 2006-04-03 15:00:40.14 spid4 Grant List 1::
> 2006-04-03 15:00:40.14 spid4 Owner:0x2c2a8b60 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:58 ECID:0
> 2006-04-03 15:00:40.14 spid4 SPID: 58 ECID: 0 Statement Type:
> UPDATE Line #: 38
> 2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
> FeltexJob_Update;1
> 2006-04-03 15:00:40.14 spid4 Requested By:
> 2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:63 ECID:0 Ec:(0x76F51570) Value:0x78fb0520 Cost:(0/B4)
> 2006-04-03 15:00:40.14 spid4 Victim Resource Owner:
> 2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:63 ECID:0 Ec:(0x76F51570) Value:0x78fb0520 Cost:(0/B4)
>
> CREATE PROCEDURE Job_Update(@.JobID int,@.JobName
> varchar(128),@.JobDescription varchar(512),
> @.JobUserName varchar(60),@.JobEmailType int,@.JobEmailTo varchar(1000),
> @.JobEmailCC varchar(255),@.JobClassName varchar(60),@.JobParameters
> varbinary(5500),
> @.JobDLLName varchar(60),@.JobDLLPathName varchar(60),@.CategoryId int,
> @.FrequencyType int,@.UpdateBy varchar(30),@.UpdateDate datetime,
> @.RowTS varbinary(8) Output )
> -- WITH ENCRYPTION
> AS
> --
> -- Update table Job
> -- Uses Optimistic locking via RowTS. New RowTS is returned in @.RowTS
> -- BEGIN & Commit Transaction is done in proc. Can also be done in VB.
> -- If Update fails it returns an error and does a RaisError (Will force
> VB error)
> --
> BEGIN
> Declare @.OldTs VarBinary(8)
> DECLARE @.nRowCount INT
> -- Begin the transaction
> BEGIN TRANSACTION
> -- Retrieve and check timestamp. Update lock held until Commit (or
> Rollback)
> SELECT @.OldTs = RowTS from Job WHERE JobID = @.JobID
> SELECT @.nRowCount = @.@.ROWCOUNT
> IF @.nRowCount = 0
> BEGIN
> RaisError 50302 'Update failed - Job record was deleted by another
> user'
> GOTO PROC_ROLLBACK
> END
> IF @.OldTs <> @.RowTS
> BEGIN
> RaisError 50303 'Update failed - Job record was updated by another
> user'
> GOTO PROC_ROLLBACK
> END
> UPDATE dbo.Job Set JobName = @.JobName,
> JobDescription = @.JobDescription,
> JobUserName = @.JobUserName,
> JobEmailType = @.JobEmailType,
> JobEmailTo = @.JobEmailTo,
> JobEmailCC = @.JobEmailCC,
> JobClassName = @.JobClassName,
> JobParameters = @.JobParameters,
> JobDLLName = @.JobDLLName,
> JobDLLPathName = @.JobDLLPathName,
> CategoryId = @.CategoryId,
> FrequencyType = @.FrequencyType,
> UpdateBy = @.UpdateBy,
> UpdateDate = @.UpdateDate,
> RowTS = Convert(VarBinary(8),CURRENT_TIMESTAMP,21)
> WHERE JobID = @.JobID
> AND RowTS = @.OldTs
> SELECT @.nRowCount = @.@.ROWCOUNT
> IF @.@.error <> 0
> BEGIN
> RaisError 50301 'Job Update Failed'
> GOTO PROC_ROLLBACK
> END
> IF @.nRowCount = 0
> BEGIN
> RaisError 50303 'Update failed - Job record was updated by another
> user'
> GOTO PROC_ROLLBACK
> END
> -- Get the new timestamp
> SELECT @.RowTS = RowTS FROM Job WHERE JobID = @.JobID
> -- Commit the Transaction
> COMMIT TRANSACTION
> RETURN(0)
> PROC_ROLLBACK:
> -- Rollback on Error
> ROLLBACK TRANSACTION
> RETURN (-301)
> END
> GO
>|||Good question. I should have mentioned that. It doesn't have any
indexes at all.|||Does anybody have any suggestions please?