SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Dead Lock problem		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sanjnep
Posting Yak Master

USA
190 Posts
Posted - 02/08/2010 :  09:48:18  Show Profile  Visit sanjnep's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3438 Posts
Posted - 02/08/2010 :  10:33:50  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

sanjnep
Posting Yak Master

USA
190 Posts
Posted - 02/08/2010 :  10:49:23  Show Profile  Visit sanjnep's Homepage  Reply with Quote
I am using SQL server 2000. Is there any other place to try?
Thanks


Sanjeev Shrestha
12/17/1971
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3438 Posts
Posted - 02/08/2010 :  11:08:12  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 02/08/2010 :  11:50:02  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000