MS SQL Server - deadlock problem

Author

deadlock problem

chris.nolan@feltex.com

2006-04-03, 3:23 am

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),@JobDes
cription varchar(512),
@JobUserName varchar(60),@JobEmai
lType int,@JobEmailTo varchar(1000),
@JobEmailCC varchar(255),@JobCla
ssName varchar(60),@JobPara
meters
varbinary(5500),
@JobDLLName varchar(60),@JobDLLP
athName varchar(60),@Categor
yId int,
@FrequencyType int,@UpdateBy varchar(30),@UpdateD
ate 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,2
1)
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

Alejandro Mesa

2006-04-03, 9:23 am

chris,

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),@JobDes
cription varchar(512),
> @JobUserName varchar(60),@JobEmai
lType int,@JobEmailTo varchar(1000),
> @JobEmailCC varchar(255),@JobCla
ssName varchar(60),@JobPara
meters
> varbinary(5500),
> @JobDLLName varchar(60),@JobDLLP
athName varchar(60),@Categor
yId int,
> @FrequencyType int,@UpdateBy varchar(30),@UpdateD
ate 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,2
1)
> 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
>
>

Liverpool fan

2006-04-03, 8:23 pm

Good question. I should have mentioned that. It doesn't have any
indexes at all.

Sponsored Links