deadlock - 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

New to SQL Server Programming

deadlock

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-06-19 : 09:12:48

Can anyone help me to solve this deadlock?

2007-06-17 22:42:34.18 spid4 Wait-for graph
2007-06-17 22:42:34.18 spid4
2007-06-17 22:42:34.18 spid4 Node:1
2007-06-17 22:42:34.18 spid4 PAG: 66:1:26187 CleanCnt:1 Mode: IX Flags: 0x2
2007-06-17 22:42:34.18 spid4 Grant List 2::
2007-06-17 22:42:34.18 spid4 Owner:0x69f19520 Mode: IX Flg:0x0 Ref:2 Life:02000000 SPID:349 ECID:0
2007-06-17 22:42:34.18 spid4 SPID: 349 ECID: 0 Statement Type: UPDATE Line #: 1
2007-06-17 22:42:34.18 spid4 Input Buf: RPC Event: sp_prepexec;1
2007-06-17 22:42:34.18 spid4 Requested By:
2007-06-17 22:42:34.18 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:348 ECID:33 Ec:(0x5996C098) Value:0x2f8d2aa0 Cost:(0/0)
2007-06-17 22:42:34.18 spid4
2007-06-17 22:42:34.18 spid4 Node:2
2007-06-17 22:42:34.18 spid4 PAG: 66:1:3051 CleanCnt:1 Mode: SIU Flags: 0x2
2007-06-17 22:42:34.18 spid4 Grant List 1::
2007-06-17 22:42:34.18 spid4 Owner:0x65c57560 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:348 ECID:33
2007-06-17 22:42:34.18 spid4 SPID: 348 ECID: 33 Statement Type: UPDATE Line #: 22
2007-06-17 22:42:34.18 spid4 Input Buf: Language Event: exec [Extracts].dbo.asp_SPCUpdateDRPFieldsIR @Name = 'SPCExecStep B05-ASP'
2007-06-17 22:42:34.18 spid4 Grant List 2::
2007-06-17 22:42:34.18 spid4 Requested By:
2007-06-17 22:42:34.18 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:349 ECID:0 Ec:(0x5425B500) Value:0x674a2160 Cost:(0/5FD8)
2007-06-17 22:42:34.18 spid4 Victim Resource Owner:
2007-06-17 22:42:34.18 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:348 ECID:33 Ec:(0x5996C098) Value:0x2f8d2aa0 Cost:(0/0)

=============================
http://www.sqlserverstudy.com

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-06-19 : 09:20:08

not really, you haven't given us the information we need. I suggest you read this article as it will walk you step-by-step through the process http://support.microsoft.com/kb/832524

-ec

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-06-19 : 09:40:00

i have read it. But its different from my problem..on that page they use key. My problem is PAG

=============================
http://www.sqlserverstudy.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-19 : 10:44:31

You have to review your update statements in app code.

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-07-11 : 11:12:15

Deadlock encountered .... Printing deadlock information
2007-07-10 17:07:02.57 spid4
2007-07-10 17:07:02.57 spid4 Wait-for graph
2007-07-10 17:07:02.57 spid4
2007-07-10 17:07:02.57 spid4 Node:1
2007-07-10 17:07:02.57 spid4 RID: 68:1:34476:2 CleanCnt:1 Mode: X Flags: 0x2
2007-07-10 17:07:02.57 spid4 Grant List 0::
2007-07-10 17:07:02.57 spid4 Owner:0x2abb53a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:102 ECID:0
2007-07-10 17:07:02.57 spid4 SPID: 102 ECID: 0 Statement Type: DELETE Line #: 10
2007-07-10 17:07:02.57 spid4 Input Buf: RPC Event: dbo.spdeletesession;1
2007-07-10 17:07:02.57 spid4 Requested By:
2007-07-10 17:07:02.57 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:1090 ECID:0 Ec:(0x4EE57568) Value:0x28dff560 Cost:(0/0)
2007-07-10 17:07:02.57 spid4
2007-07-10 17:07:02.57 spid4 Node:2
2007-07-10 17:07:02.57 spid4 KEY: 68:661577395:3 (1104e952839b) CleanCnt:1 Mode: U Flags: 0x0
2007-07-10 17:07:02.57 spid4 Grant List 0::
2007-07-10 17:07:02.57 spid4 Owner:0x28dff660 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:1090 ECID:0
2007-07-10 17:07:02.57 spid4 SPID: 1090 ECID: 0 Statement Type: DELETE Line #: 10
2007-07-10 17:07:02.57 spid4 Input Buf: RPC Event: dbo.spdeletesession;1
2007-07-10 17:07:02.57 spid4 Requested By:
2007-07-10 17:07:02.57 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:102 ECID:0 Ec:(0x559D3568) Value:0x2a5b6d40 Cost:(0/114)
2007-07-10 17:07:02.57 spid4 Victim Resource Owner:
2007-07-10 17:07:02.57 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:1090 ECID:0 Ec:(0x4EE57568) Value:0x28dff560 Cost:(0/0)
2007-07-10 17:36:11.95 spid4
Deadlock encountered .... Printing deadlock information
2007-07-10 17:36:11.95 spid4
2007-07-10 17:36:11.95 spid4 Wait-for graph
2007-07-10 17:36:11.95 spid4
2007-07-10 17:36:11.95 spid4 Node:1
2007-07-10 17:36:11.95 spid4 RID: 68:1:19814:2 CleanCnt:1 Mode: X Flags: 0x2
2007-07-10 17:36:11.95 spid4 Grant List 2::
2007-07-10 17:36:11.95 spid4 Owner:0x71390580 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:1124 ECID:0
2007-07-10 17:36:11.95 spid4 SPID: 1124 ECID: 0 Statement Type: DELETE Line #: 10
2007-07-10 17:36:11.95 spid4 Input Buf: RPC Event: dbo.spdeletesession;1
2007-07-10 17:36:11.95 spid4 Requested By:
2007-07-10 17:36:11.95 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:1123 ECID:0 Ec:(0x642B1568) Value:0x63eed100 Cost:(0/0)
2007-07-10 17:36:11.95 spid4
2007-07-10 17:36:11.95 spid4 Node:2
2007-07-10 17:36:11.95 spid4 KEY: 68:661577395:3 (4a040db0f196) CleanCnt:1 Mode: U Flags: 0x0
2007-07-10 17:36:11.95 spid4 Grant List 1::
2007-07-10 17:36:11.95 spid4 Owner:0x2bcf16e0 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:1123 ECID:0
2007-07-10 17:36:11.95 spid4 SPID: 1123 ECID: 0 Statement Type: DELETE Line #: 10
2007-07-10 17:36:11.95 spid4 Input Buf: RPC Event: dbo.spdeletesession;1
2007-07-10 17:36:11.95 spid4 Requested By:
2007-07-10 17:36:11.95 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:1124 ECID:0 Ec:(0x64211568) Value:0x30d6a220 Cost:(0/114)
2007-07-10 17:36:11.95 spid4 Victim Resource Owner:
2007-07-10 17:36:11.95 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:1123 ECID:0 Ec:(0x642B1568) Value:0x63eed100 Cost:(0/0)
2007-07-10 17:38:04.45 spid4
Deadlock encountered .... Printing deadlock information
2007-07-10 17:38:04.45 spid4
2007-07-10 17:38:04.45 spid4 Wait-for graph
2007-07-10 17:38:04.45 spid4
2007-07-10 17:38:04.45 spid4 Node:1
2007-07-10 17:38:04.45 spid4 RID: 68:1:487:3 CleanCnt:1 Mode: X Flags: 0x2
2007-07-10 17:38:04.45 spid4 Grant List 1::
2007-07-10 17:38:04.45 spid4 Owner:0x4cba6080 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:690 ECID:0
2007-07-10 17:38:04.45 spid4 SPID: 690 ECID: 0 Statement Type: DELETE Line #: 10
2007-07-10 17:38:04.45 spid4 Input Buf: RPC Event: dbo.spdeletesession;1
2007-07-10 17:38:04.45 spid4 Requested By:
2007-07-10 17:38:04.45 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:1111 ECID:0 Ec:(0x7BCA5568) Value:0x6ab98d00 Cost:(0/0)
2007-07-10 17:38:04.45 spid4
2007-07-10 17:38:04.45 spid4 Node:2
2007-07-10 17:38:04.45 spid4 KEY: 68:661577395:3 (4e0495ea0e22) CleanCnt:1 Mode: U Flags: 0x0
2007-07-10 17:38:04.45 spid4 Grant List 3::
2007-07-10 17:38:04.45 spid4 Owner:0x6a9651c0 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:1111 ECID:0
2007-07-10 17:38:04.45 spid4 SPID: 1111 ECID: 0 Statement Type: DELETE Line #: 10
2007-07-10 17:38:04.45 spid4 Input Buf: RPC Event: dbo.spdeletesession;1
2007-07-10 17:38:04.45 spid4 Requested By:
2007-07-10 17:38:04.45 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:690 ECID:0 Ec:(0x713D1568) Value:0x4cba7020 Cost:(0/114)
2007-07-10 17:38:04.45 spid4 Victim Resource Owner:
2007-07-10 17:38:04.45 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:1111 ECID:0 Ec:(0x7BCA5568)

----------------------------------------------------------------------------------------------------
SQL server shows me new deadlocks. Any ideas how to solve them?

=============================
http://www.sqlserverstudy.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-11 : 11:20:03

what do your statements that get deadlocked look like?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-07-11 : 11:45:43

how do you find the statement?

=============================
http://www.sqlserverstudy.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-11 : 11:58:58

do you have a stored procedure named dbo.spdeletesession?
and what is on line 10 of it?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-07-11 : 13:09:56

no, i cant find spdeletesession on sysobjects.

=============================
http://www.sqlserverstudy.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-11 : 14:28:11

Did you look at correct db? How about dbo.asp_SPCUpdateDRPFieldsIR?

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-07-11 : 16:54:10

found it.

but its encrypted

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Encrypted object is not transferable, and script can not be generated. ******/

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

=============================
http://www.sqlserverstudy.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-11 : 22:13:12

Contact vendor if it's third party app.

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-09-20 : 13:27:32

ALTER PROCEDURE [dbo].[spdeletesession](@sessionid [nvarchar](128))
AS
BEGIN

-- Copyright 2002-2006 SourceGear LLC. --
-- All rights reserved. See Vault License Agreement --
-- for more information. --

SET NOCOUNT ON
DELETE FROM tblsessions WHERE sessionid=@sessionid
SET NOCOUNT OFF
RETURN @@ERROR
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

=============================
http://www.sqlserverstudy.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources