Well it looks like the process is trying to insert and the select
statement is stepping on it, could be an index creation on the insert.
You should write some error trapping into the procedure I think its 1205
and then re-run the statement.
You should run a profiler session to catch some better information on
what is occuring (timeline)
Christopher Bellizzi
SQL DBA/Systems Analyst
240 Fall Street
SFO Main Office
Seneca Falls, NY 13148
315-568-7886
-----Original Message-----
From: Subhash Chandra
[mailto:mssqldba-ezmlmshield-x29781002.[Email address protected]
Sent: Thursday, May 31, 2007 2:36 AM
To: LazyDBA Discussion
Subject: RE: Deadlock Detection
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
---------------------------------------------------------------------
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
*****************************************************************
This e-mail and any files transmitted with it may be proprietary
and are intended solely for the use of the individual or entity to
whom they are addressed. If you have received this e-mail in
error please notify the sender. Please note that any views or
opinions presented in this e-mail are solely those of the author
and do not necessarily represent those of ITT Corporation. The
recipient should check this e-mail and any attachments for the
presence of viruses. ITT accepts no liability for any damage
caused by any virus transmitted by this e-mail.
*******************************************************************
MS Sql Server LazyDBA home page