Dead Lock problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Dead Lock problem

Author

Topic

sanjnep
Posting Yak Master

191 Posts

Posted - 2010-02-08 : 09:48:18

we have front end .NET code and four parallel threads running at a same time. Only two threads able to update the table another two threads became the deadlock victim.
Following is the table definition:

ALTER TABLE [dbo].[LP_Load](
[RowKey] [int] IDENTITY(1,1) NOT NULL,
[MainKey] [varchar](32) NULL,
[ProcessID] [varchar](30) NULL,
[Completed] [varchar](1) NULL,
CONSTRAINT [PK_LP_Load] PRIMARY KEY CLUSTERED
(
[RowKey] 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]
..................................................................................................................................................................................
Index
index_name description index_keys
....................................................................................................................................................................
IX_PID_Com nonclustered located on PRIMARY ProcessID, Completed
PK_LP_Load clustered, unique, primary key located on PRIMARY RowKey

I rebuilt all indexes every day before running the update process and following is the error log for deadlock. I am getting this error from one week and unable to resolve it. Do you have any idea?

Deadlock encountered .... Printing deadlock information 0
2010-02-04 07:21:30.90 spid4 0
2010-02-04 07:21:30.90 spid4 Wait-for graph 0
2010-02-04 07:21:30.90 spid4 0
2010-02-04 07:21:30.90 spid4 Node:1 0
2010-02-04 07:21:30.92 spid4 PAG: 16:1:19476306 CleanCnt:2 Mode: U Flags: 0x2 0
2010-02-04 07:21:30.92 spid4 Grant List 0:: 0
2010-02-04 07:21:30.92 spid4 Owner:0x1b3dcb40 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:79 ECID:0 0
2010-02-04 07:21:30.92 spid4 SPID: 79 ECID: 0 Statement Type: UPDATE Line #: 1 0
2010-02-04 07:21:30.92 spid4 Input Buf: Language Event: UPDATE LP_Load SET Completed = 'Y' WHERE ProcessID = 'A2_2860' AND Completed = 'N' 0
2010-02-04 07:21:30.92 spid4 Requested By: 0
2010-02-04 07:21:30.92 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec:(0x68515588) Value:0x741d2c40 Cost:(0/4B1A8) 0
2010-02-04 07:21:30.92 spid4 0
2010-02-04 07:21:30.92 spid4 Node:2 0
2010-02-04 07:21:30.92 spid4 PAG: 16:1:19322786 CleanCnt:2 Mode: X Flags: 0x2 0
2010-02-04 07:21:30.92 spid4 Grant List 0:: 0
2010-02-04 07:21:30.92 spid4 Owner:0x38cbd540 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:63 ECID:0 0
2010-02-04 07:21:30.92 spid4 SPID: 63 ECID: 0 Statement Type: UPDATE Line #: 1 0
2010-02-04 07:21:30.92 spid4 Input Buf: Language Event: UPDATE LP_Load SET Completed = 'Y' WHERE ProcessID = 'A1_2860' AND Completed = 'N' 0
2010-02-04 07:21:30.92 spid4 Requested By: 0
2010-02-04 07:21:30.92 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:79 ECID:0 Ec:(0x3E101588) Value:0x76012e20 Cost:(0/519A8) 0
2010-02-04 07:21:30.92 spid4 Victim Resource Owner: 0
2010-02-04 07:21:30.92 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec:(0x68515588) Value:0x741d2c40 Cost:(0/4B1A8) 0
2010-02-04 07:21:35.92 spid4 0
Deadlock encountered .... Printing deadlock information 0
2010-02-04 07:21:35.92 spid4 0
2010-02-04 07:21:35.92 spid4 Wait-for graph 0
2010-02-04 07:21:35.92 spid4 0
2010-02-04 07:21:35.92 spid4 Node:1 0
2010-02-04 07:21:35.92 spid4 PAG: 16:1:19476308 CleanCnt:2 Mode: U Flags: 0x2 0
2010-02-04 07:21:35.92 spid4 Grant List 1:: 0
2010-02-04 07:21:35.92 spid4 Owner:0x2fb98260 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:84 ECID:0 0
2010-02-04 07:21:35.92 spid4 SPID: 84 ECID: 0 Statement Type: UPDATE Line #: 1 0
2010-02-04 07:21:35.92 spid4 Input Buf: Language Event: UPDATE LP_Load SET Completed = 'Y' WHERE ProcessID = 'A3_2860' AND Completed = 'N' 0
2010-02-04 07:21:35.92 spid4 Requested By: 0
2010-02-04 07:21:35.92 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:79 ECID:0 Ec:(0x3E101588) Value:0x4ec3bf60 Cost:(0/53DA8) 0
2010-02-04 07:21:35.92 spid4 0
2010-02-04 07:21:35.92 spid4 Node:2 0
2010-02-04 07:21:35.92 spid4 PAG: 16:1:19476754 CleanCnt:2 Mode: X Flags: 0x2 0
2010-02-04 07:21:35.92 spid4 Grant List 0:: 0
2010-02-04 07:21:35.92 spid4 Owner:0x1a1d7c20 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:79 ECID:0 0
2010-02-04 07:21:35.92 spid4 SPID: 79 ECID: 0 Statement Type: UPDATE Line #: 1 0
2010-02-04 07:21:35.92 spid4 Input Buf: Language Event: UPDATE LP_Load SET Completed = 'Y' WHERE ProcessID = 'A2_2860' AND Completed = 'N' 0
2010-02-04 07:21:35.92 spid4 Requested By: 0
2010-02-04 07:21:35.92 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:84 ECID:0 Ec:(0x1D157588) Value:0x36ce87e0 Cost:(0/38DC8) 0
2010-02-04 07:21:35.92 spid4 Victim Resource Owner: 0
2010-02-04 07:21:35.92 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:84 ECID:0 Ec:(0x1D157588) Value:0x36ce87e0 Cost:(0/38DC8)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-08 : 10:33:50

what version of SQLSERVER are you using.

If it's 2005 or later you may find that setting the default isolation level to READ_COMMITTED_SNAPSHOT may remove any deadlocking with reads vs writes.

From the dump it looks like both updates are deadlocking with each other though. :(

You seem to have a nice index over processId, Completed.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sanjnep
Posting Yak Master

191 Posts

Posted - 2010-02-08 : 10:49:23

I am using SQL server 2000. Is there any other place to try?
Thanks

Sanjeev Shrestha
12/17/1971

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-08 : 11:08:12

I guess you could try passing the (ROWLOCK) hint through the UPDATE statement. just in case you are getting some sort of escalated lock. I've not got a great deal of experiences with 2000 and deadlocks.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 11:50:02

Can SQL Profiler be used to find what the culprits are, when it happens, and from there decide on a course of action to prevent it?

It might be that Task-A is doing Lock-Table-A then Lock-Table-B

and Task-B is doing Lock-Table-B then Lock-Table-A

and if that is the case just re-ordering the way the updates are done may fix the problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources