Showing posts with label inherited. Show all posts
Showing posts with label inherited. Show all posts

Thursday, March 29, 2012

Deadlocks on Queries

Hi,
I have a situation where I have inherited a system that is being
stress tested at the moment. The main table used in the DB has a
number of indexes. The problem I am having is that under load, I keep
getting deadlocks on iwhat appears to be the indexes on the tables.
More specifically a row appears to be inserted / updated, and another
select query is being locked out when trying to query that table.
I haven't touched fill factors (they are still the default zero). Any
pointers / reading material would be appreciated.
Check if your query includes BEGIN TRAN and COMMIT TRAN at proper place
"Spondishy" wrote:

> Hi,
> I have a situation where I have inherited a system that is being
> stress tested at the moment. The main table used in the DB has a
> number of indexes. The problem I am having is that under load, I keep
> getting deadlocks on iwhat appears to be the indexes on the tables.
> More specifically a row appears to be inserted / updated, and another
> select query is being locked out when trying to query that table.
> I haven't touched fill factors (they are still the default zero). Any
> pointers / reading material would be appreciated.
>
sql
Posted by limes at 7:38 PM 0 comments
Labels: , , , , , , , , , , , , , ,

Deadlocks on Queries

Hi,
I have a situation where I have inherited a system that is being
stress tested at the moment. The main table used in the DB has a
number of indexes. The problem I am having is that under load, I keep
getting deadlocks on iwhat appears to be the indexes on the tables.
More specifically a row appears to be inserted / updated, and another
select query is being locked out when trying to query that table.
I haven't touched fill factors (they are still the default zero). Any
pointers / reading material would be appreciated.Check if your query includes BEGIN TRAN and COMMIT TRAN at proper place
"Spondishy" wrote:
> Hi,
> I have a situation where I have inherited a system that is being
> stress tested at the moment. The main table used in the DB has a
> number of indexes. The problem I am having is that under load, I keep
> getting deadlocks on iwhat appears to be the indexes on the tables.
> More specifically a row appears to be inserted / updated, and another
> select query is being locked out when trying to query that table.
> I haven't touched fill factors (they are still the default zero). Any
> pointers / reading material would be appreciated.
>
Posted by limes at 7:38 PM 0 comments
Labels: , , , , , , , , , , , , ,

Deadlocks on Queries

Hi,
I have a situation where I have inherited a system that is being
stress tested at the moment. The main table used in the DB has a
number of indexes. The problem I am having is that under load, I keep
getting deadlocks on iwhat appears to be the indexes on the tables.
More specifically a row appears to be inserted / updated, and another
select query is being locked out when trying to query that table.
I haven't touched fill factors (they are still the default zero). Any
pointers / reading material would be appreciated.Check if your query includes BEGIN TRAN and COMMIT TRAN at proper place
"Spondishy" wrote:

> Hi,
> I have a situation where I have inherited a system that is being
> stress tested at the moment. The main table used in the DB has a
> number of indexes. The problem I am having is that under load, I keep
> getting deadlocks on iwhat appears to be the indexes on the tables.
> More specifically a row appears to be inserted / updated, and another
> select query is being locked out when trying to query that table.
> I haven't touched fill factors (they are still the default zero). Any
> pointers / reading material would be appreciated.
>
Posted by limes at 7:37 PM 0 comments
Labels: , , , , , , , , , , , , , ,

Thursday, March 22, 2012

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 Ec0x53BF3570) Value:0x2c2a82a0 Cost0/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 Ec0x76F51570) Value:0x78fb0520 Cost0/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 Ec0x76F51570) Value:0x78fb0520 Cost0/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
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 Ec0x53BF3570) Value:0x2c2a82a0 Cost0/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 Ec0x76F51570) Value:0x78fb0520 Cost0/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 Ec0x76F51570) Value:0x78fb0520 Cost0/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.
Posted by limes at 10:24 PM 0 comments
Labels: , , , , , , , , , , , , , ,

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?
Posted by limes at 10:21 PM 0 comments
Labels: , , , , , , , , , , , , ,

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 Ec0x53BF3570) Value:0x2c2a82a0 Cost0/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 Ec0x76F51570) Value:0x78fb0520 Cost0/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 Ec0x76F51570) Value:0x78fb0520 Cost0/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,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
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 Ec0x53BF3570) Value:0x2c2a82a0 Cost0/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 Ec0x76F51570) Value:0x78fb0520 Cost0/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 Ec0x76F51570) Value:0x78fb0520 Cost0/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,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
>|||Good question. I should have mentioned that. It doesn't have any
indexes at all.sql
Posted by limes at 10:17 PM 0 comments
Labels: , , , , , , , , , , , , , ,

Wednesday, March 21, 2012

Deadlock on SQL SELECT statement

I have inherited the maintenance of a product which includes the snipet
of code below. Every 10 seconds the code is executed. It is causing a
deadlock in some instances, but I am undable to reproduce the problem
on my machine. The "PC" table contains a list of PCs seen on a
network, so isn't very large. Since I dont have much background in
database programming, I was wondering if there is some simple answer to
the deadlock issue...but from reading on deadlocks, there rarely seems
to be a simple solution.
// ****************************************
// Find PCs to restart
CString strQuery;
strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
_RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
PrepareSQLDate(CTime::GetCurrentTime()))
;
try
{
for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
{
list.Add(rs.GetColInt(0));
}
rs.Close();
}
catch (CDBException * e)
{
HandleException (e, strQuery);
}
return list.GetCount();
// ****************************************
**
Thanks in advance.In message <1138983056.041276.84650@.g47g2000cwa.googlegroups.com>,
bigcoops@.hotmail.com writes
>network, so isn't very large. Since I dont have much background in
>database programming, I was wondering if there is some simple answer to
>the deadlock issue...but from reading on deadlocks, there rarely seems
>to be a simple solution.
You may want to give Thread Validator a whirl.
http://www.softwareverify.com
Stephen
--
Stephen Kellett
Object Media Limited http://www.objmedia.demon.co.uk/software.html
Computer Consultancy, Software Development
Windows C++, Java, Assembler, Performance Analysis, Troubleshooting|||Try this:
select _ID from PC WITH (NOLOCK) ... and so forth
HTH,
Tom Dacon
Dacon Software Consulting
<bigcoops@.hotmail.com> wrote in message
news:1138983056.041276.84650@.g47g2000cwa.googlegroups.com...
>I have inherited the maintenance of a product which includes the snipet
> of code below. Every 10 seconds the code is executed. It is causing a
> deadlock in some instances, but I am undable to reproduce the problem
> on my machine. The "PC" table contains a list of PCs seen on a
> network, so isn't very large. Since I dont have much background in
> database programming, I was wondering if there is some simple answer to
> the deadlock issue...but from reading on deadlocks, there rarely seems
> to be a simple solution.
> // ****************************************
> // Find PCs to restart
> CString strQuery;
> strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
> _RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
> PrepareSQLDate(CTime::GetCurrentTime()))
;
> try
> {
> for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
> {
> list.Add(rs.GetColInt(0));
> }
> rs.Close();
> }
> catch (CDBException * e)
> {
> HandleException (e, strQuery);
> }
> return list.GetCount();
> // ****************************************
**
> Thanks in advance.
>|||Doesn't NOLOCK have the potential of getting dirty data?
Since the 10 second timer is set after the code above is executed, is
it possible the CRecordSet::Close() method did not close properly and
is holding a lock on the table? So when the next timer goes off the
deadlock occurs.
Thanks,
bigcoops|||It appears that this is not the place where deadlocks are occurring.
There is another SELECT statement, "select _NAME from PC where _ID =
....", and I suspect all other statements accessing the PC table will
cause a deadlock. Has anyone seen a similar issue where access to a
table will cause a deadlock?|||In addition to the deadlocks, there are now "Timeout expired (S1T00)"
errors occuring, which is more than likely a releated issue.
Posted by limes at 12:46 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)