Below is the detail of deadlock from errorlog.
Deadlock encountered .... Printing deadlock information
2007-05-30 12:26:44.89 spid3 Wait-for graph
2007-05-30 12:26:44.89 spid3 Node:1
2007-05-30 12:26:44.89 spid3 PAG: 7:1:655581 CleanCnt:2
Mode: IX Flags: 0x2
2007-05-30 12:26:44.89 spid3 Grant List 2::
2007-05-30 12:26:44.89 spid3 Owner:0x599f16a0 Mode: IX Flg:0x0
Ref:3 Life:02000000 SPID:96 ECID:0
2007-05-30 12:26:44.89 spid3 SPID: 96 ECID: 0 Statement Type: INSERT
Line #: 16
2007-05-30 12:26:44.89 spid3 Input Buf: RPC Event: UP_TRANS_CHARGES;1
2007-05-30 12:26:44.89 spid3 Requested By:
2007-05-30 12:26:44.89 spid3 ResType:LockOwner Stype:'OR' Mode: S
SPID:79 ECID:0 Ec:(0x48C35580) Value:0x66849c40
2007-05-30 12:26:44.89 spid3 Node:2
2007-05-30 12:26:44.89 spid3 PAG: 7:1:672402 CleanCnt:2
Mode: UIX Flags: 0x2
2007-05-30 12:26:44.89 spid3 Grant List 2::
2007-05-30 12:26:44.89 spid3 Owner:0x66814c40 Mode: UIX Flg:0x0
Ref:1 Life:02000000 SPID:79 ECID:0
2007-05-30 12:26:44.91 spid3 SPID: 79 ECID: 0 Statement Type: SELECT
Line #: 255
2007-05-30 12:26:44.91 spid3 Input Buf: RPC Event:
UP_CANCEL_INVENTORY;1
2007-05-30 12:26:44.91 spid3 Requested By:
2007-05-30 12:26:44.91 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:96 ECID:0 Ec:(0x1FCB7580) Value:0x5bcadca0
2007-05-30 12:26:44.91 spid3 Victim Resource Owner:
2007-05-30 12:26:44.91 spid3 ResType:LockOwner Stype:'OR' Mode: IX
SPID:96 ECID:0 Ec:(0x1FCB7580) Value:0x5bcadca0
I found the table of pages specified in above details and found as below.
PAG: 7:1:655581 belongs to table - INVENTORY_SOLD that is not used in stored
procedure - UP_TRANS_CHARGES but used in UP_CANCEL_INVENTORY.
PAG: 7:1:672402 belongs to table - T_TR_TRANS that is not used in stored
procedure - UP_CANCEL_INVENTORY but used in UP_TRANS_CHARGES.
There is not relation between INVENTORY_SOLD and T_TR_TRANS tables. How
these two stored procedure are deadlocking each other.
Please give your ideas about possible causes of this deadlock.
Thanks & Regards,
Subhash
-----Original Message-----
From: Holly Allen
[mailto:mssqldba-ezmlmshield-x23117059.[Email address protected]
Sent: Thursday, May 31, 2007 1:10 AM
To: LazyDBA Discussion
Subject: RE: Deadlock Detection
Also check your foreign keys. For example, if table Foo has a column Bar_ID,
where Bar_ID has a foreign key constraint to table Bar, then inserting into
Foo will also lock Bar as the engine verifies that the new value of Bar_ID
is valid. Without more info I've no idea if this is your problem, but it's
one I've run into.
> -----Original Message-----
> From: Subhash Chandra
> [mailto:mssqldba-ezmlmshield-x25525151.[Email address protected]
> Sent: Saturday, May 26, 2007 12:32 AM
> To: LazyDBA Discussion
> Subject: Deadlock Detection
>
>
> Hi All,
>
> Using trace 1204, 1305 I found details of many deadlocks in errorlog.
> But in some deadlock table that is locked is nowhere used by stored
> procedure executed by participating SPIDs.
> If there is no operation on a table by specified stored proceudre then
> how this table is causing deadlock.
>
> Thanks & Regards,
> Subhash
>
> -----Original Message-----
> From: Subhash Chandra [mailto:[Email address protected]
> Sent: Wednesday, May 23, 2007 3:20 PM
> To: 'Ashish Mohite '; 'LazyDBA Discussion'
> Subject: RE: query-urgent
>
> Hi Ashish,
>
> following query return the desired resultset.
>
> select distinct base.a, base.b,
> (select top 1 c from test1 where d = 'Telephone' and a = base.a and b
> = base.b order by c) as Telephone1, (select top 1 c from test1 where d
> = 'Telephone' and a = base.a and b = base.b and c not in (select top
> 1 c from
> test1 where d = 'Telephone' and a = base.a and b = base.b order by c)
> order by c) as Telephone2, (select top 1 c from test1 where d =
> 'Telephone' and a = base.a and b = base.b and c not in (select top 2
> c from
> test1 where d =
> 'Telephone' and a = base.a and b = base.b order by c) order by c) as
> Telephone3, (select top 1 c from test1 where d = 'Fax' and a = base.a
> and b = base.b order by c) as Fax1, (select top 1 c from test1 where d
> = 'Fax'
> and a = base.a and b = base.b and c not in (select top 1 c from test1
> where d = 'Fax' and a = base.a and b = base.b order by c) order by
> c) as Fax2,
> (select top 1 c from test1 where d = 'Fax' and a = base.a and b =
> base.b and c not in (select top 2 c from test1 where d = 'Fax' and a
> = base.a and b = base.b order by c) order by c) as Fax3 from test1
> base
>
> The numbers of columns depends on your requirement.
> You can use other way to return 2nd and 3rd (or more if
> required) highest
> values.
>
> Thanks & Regards,
> Subhash
>
> -----Original Message-----
> From: Ashish Mohite
> [mailto:mssqldba-ezmlmshield-x63997230.[Email address protected]
> Sent: Wednesday, May 23, 2007 2:22 PM
> To: LazyDBA Discussion
> Subject: query-urgent
>
>
>
>
> Hi, <http://www.dnb.co.in>
>
> <http://www.dnb.co.in>
>
> I have some problem while writing query. I am providing you sample
> data can you help me? <http://www.dnb.co.in>
>
> <http://www.dnb.co.in>
>
> Sample data <http://www.dnb.co.in>
>
> Column a b c d e
> f <http://www.dnb.co.in>
>
> 19 356 91 22 25243269
> Telephone <http://www.dnb.co.in>
>
> 19 356 91 22 25227847
> Telephone <http://www.dnb.co.in>
>
> 19 356 91 22 25222994
> Fax <http://www.dnb.co.in>
>
> 22 356 91 4111 256031
> Telephone <http://www.dnb.co.in>
>
> And output like <http://www.dnb.co.in>
>
> A b c d phone1
> phone2 fax1 <http://www.dnb.co.in>
>
> 19 356 91 22 25243269
> 25227847 25222994 <http://www.dnb.co.in>
>
> <http://www.dnb.co.in>
>
> And strictly I want use query only. <http://www.dnb.co.in>
>
> <http://www.dnb.co.in>
>
> <http://www.dnb.co.in>
>
> Waiting for your reply <http://www.dnb.co.in>
>
> Thanks in advance. <http://www.dnb.co.in>
>
> <http://www.dnb.co.in>
>
> Best Regards, <http://www.dnb.co.in>
>
> <http://www.dnb.co.in>
>
> Ashish <http://www.dnb.co.in>
>
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post
> a dba
> job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com To
> unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post
> a dba job: http://jobs.lazydba.com To subscribe :
> http://www.LazyDBA.com To unsubscribe:
> http://www.lazydba.com/unsubscribe.html
>
>
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a dba
job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page