Deadlocks "again"

Hi
I am getting the same deadlock on our SQL 2K server but with different SPID
Ids throughout the day.
I know what tables and Index information from the KEY: information , and
that is it performing an exclusive lock. What i cannot figure out is what
to do to help resolve this.
Do anyone have any tips? Here is the deadlock results: -
Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
Grant List 1::
Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64 ECID:0
SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
Input Buf: RPC Event: sp_execute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0 Ec0x77D6D550)
Value:0x46951760 Cost0/F08)
Node:2
KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
Grant List 2::
Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61 ECID:0
SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: RPC Event: sp_execute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec0x7E9EF550)
Value:0x51983620 Cost0/714)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec0x7E9EF550)
Value:0x51983620 Cost0/714)
Many Thanks
Hi Hush
Have you identified the SQL statements that cause the deadlock and tried to
optimize the queries/indexes so that the locks resolve faster. The dump
specify the RPC event as sp_execute,1. Are you executing dynamic SQL ?
Regards
Charl
http://www.sqlserver.co.za
"Hush" wrote:

> Hi
> I am getting the same deadlock on our SQL 2K server but with different SPID
> Ids throughout the day.
> I know what tables and Index information from the KEY: information , and
> that is it performing an exclusive lock. What i cannot figure out is what
> to do to help resolve this.
> Do anyone have any tips? Here is the deadlock results: -
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 1::
> Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64 ECID:0
> SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0 Ec0x77D6D550)
> Value:0x46951760 Cost0/F08)
> Node:2
> KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 2::
> Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61 ECID:0
> SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec0x7E9EF550)
> Value:0x51983620 Cost0/714)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec0x7E9EF550)
> Value:0x51983620 Cost0/714)
>
> Many Thanks
>
>
|||See if these help:
http://msdn.microsoft.com/library/de...tabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
Andrew J. Kelly SQL MVP
"Hush" <hushdontspamme@.hotmail.com> wrote in message
news:uzWvhdOSGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Hi
> I am getting the same deadlock on our SQL 2K server but with different
> SPID Ids throughout the day.
> I know what tables and Index information from the KEY: information , and
> that is it performing an exclusive lock. What i cannot figure out is what
> to do to help resolve this.
> Do anyone have any tips? Here is the deadlock results: -
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 1::
> Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64 ECID:0
> SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0
> Ec0x77D6D550) Value:0x46951760 Cost0/F08)
> Node:2
> KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 2::
> Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61 ECID:0
> SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec0x7E9EF550)
> Value:0x51983620 Cost0/714)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec0x7E9EF550)
> Value:0x51983620 Cost0/714)
>
> Many Thanks
>
|||Hi Charl
Thanks for your reply:
I am currently running a SQL Profiler - How do i look for the RPC event? I
have included Excution Plan and the SQL locks events in the profiler.
What i am alittle concerned about is that the table in question has on index
(Clustered Index) and out of the 7 Columns in the table 6 of them is
assigned to clustered index. Is that good practice?
I am a non SQL Developer DBA....so i am a little stuck with this.
Many Thanks
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:F38AA9E6-F580-4F62-A3D6-93C70375D145@.microsoft.com...[vbcol=seagreen]
> Hi Hush
> Have you identified the SQL statements that cause the deadlock and tried
> to
> optimize the queries/indexes so that the locks resolve faster. The dump
> specify the RPC event as sp_execute,1. Are you executing dynamic SQL ?
> Regards
> Charl
> --
> http://www.sqlserver.co.za
>
> "Hush" wrote:
|||While there are always exceptions having that many columns in a clustered
index is usually not a good idea. Since the leaf level of the clustered
index is the actual data there is little to gain by clustering on so many
columns. Usually you can make the index selective enough on just a few and
it is always a good idea to keep the clustered index narrow since the
clustered index key is appended to the end of any nonclustered indexes as
well. If you are updating any of the 6 columns in the clustered index there
is a prime candidate for deadlocks since it has to physically move the
entire row.
Andrew J. Kelly SQL MVP
"Hush" <hushdontspamme@.hotmail.com> wrote in message
news:ekTgocPSGHA.424@.TK2MSFTNGP12.phx.gbl...
> Hi Charl
> Thanks for your reply:
> I am currently running a SQL Profiler - How do i look for the RPC event?
> I have included Excution Plan and the SQL locks events in the profiler.
> What i am alittle concerned about is that the table in question has on
> index (Clustered Index) and out of the 7 Columns in the table 6 of them is
> assigned to clustered index. Is that good practice?
> I am a non SQL Developer DBA....so i am a little stuck with this.
> Many Thanks
>
> "Charl" <Charl@.discussions.microsoft.com> wrote in message
> news:F38AA9E6-F580-4F62-A3D6-93C70375D145@.microsoft.com...
>

Deadlocks "again"

Hi
I am getting the same deadlock on our SQL 2K server but with different SPID
Ids throughout the day.
I know what tables and Index information from the KEY: information , and
that is it performing an exclusive lock. What i cannot figure out is what
to do to help resolve this.
Do anyone have any tips? Here is the deadlock results: -
Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
Grant List 1::
Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64 ECID:0
SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
Input Buf: RPC Event: sp_execute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0 Ec:(0x77D6D550)
Value:0x46951760 Cost:(0/F08)
Node:2
KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
Grant List 2::
Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61 ECID:0
SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: RPC Event: sp_execute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
Value:0x51983620 Cost:(0/714)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
Value:0x51983620 Cost:(0/714)
Many ThanksHi Hush
Have you identified the SQL statements that cause the deadlock and tried to
optimize the queries/indexes so that the locks resolve faster. The dump
specify the RPC event as sp_execute,1. Are you executing dynamic SQL ?
Regards
Charl
--
http://www.sqlserver.co.za
"Hush" wrote:
> Hi
> I am getting the same deadlock on our SQL 2K server but with different SPID
> Ids throughout the day.
> I know what tables and Index information from the KEY: information , and
> that is it performing an exclusive lock. What i cannot figure out is what
> to do to help resolve this.
> Do anyone have any tips? Here is the deadlock results: -
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 1::
> Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64 ECID:0
> SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0 Ec:(0x77D6D550)
> Value:0x46951760 Cost:(0/F08)
> Node:2
> KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 2::
> Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61 ECID:0
> SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
> Value:0x51983620 Cost:(0/714)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
> Value:0x51983620 Cost:(0/714)
>
> Many Thanks
>
>|||See if these help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
--
Andrew J. Kelly SQL MVP
"Hush" <hushdontspamme@.hotmail.com> wrote in message
news:uzWvhdOSGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Hi
> I am getting the same deadlock on our SQL 2K server but with different
> SPID Ids throughout the day.
> I know what tables and Index information from the KEY: information , and
> that is it performing an exclusive lock. What i cannot figure out is what
> to do to help resolve this.
> Do anyone have any tips? Here is the deadlock results: -
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 1::
> Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64 ECID:0
> SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0
> Ec:(0x77D6D550) Value:0x46951760 Cost:(0/F08)
> Node:2
> KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 2::
> Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61 ECID:0
> SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
> Value:0x51983620 Cost:(0/714)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
> Value:0x51983620 Cost:(0/714)
>
> Many Thanks
>|||Hi Charl
Thanks for your reply:
I am currently running a SQL Profiler - How do i look for the RPC event? I
have included Excution Plan and the SQL locks events in the profiler.
What i am alittle concerned about is that the table in question has on index
(Clustered Index) and out of the 7 Columns in the table 6 of them is
assigned to clustered index. Is that good practice?
I am a non SQL Developer DBA....so i am a little stuck with this.
Many Thanks
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:F38AA9E6-F580-4F62-A3D6-93C70375D145@.microsoft.com...
> Hi Hush
> Have you identified the SQL statements that cause the deadlock and tried
> to
> optimize the queries/indexes so that the locks resolve faster. The dump
> specify the RPC event as sp_execute,1. Are you executing dynamic SQL ?
> Regards
> Charl
> --
> http://www.sqlserver.co.za
>
> "Hush" wrote:
>> Hi
>> I am getting the same deadlock on our SQL 2K server but with different
>> SPID
>> Ids throughout the day.
>> I know what tables and Index information from the KEY: information , and
>> that is it performing an exclusive lock. What i cannot figure out is
>> what
>> to do to help resolve this.
>> Do anyone have any tips? Here is the deadlock results: -
>> Deadlock encountered ... Printing deadlock information
>> Wait-for graph
>> Node:1
>> KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
>> Grant List 1::
>> Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64
>> ECID:0
>> SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
>> Input Buf: RPC Event: sp_execute;1
>> Requested By:
>> ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0
>> Ec:(0x77D6D550)
>> Value:0x46951760 Cost:(0/F08)
>> Node:2
>> KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
>> Grant List 2::
>> Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61
>> ECID:0
>> SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
>> Input Buf: RPC Event: sp_execute;1
>> Requested By:
>> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
>> Value:0x51983620 Cost:(0/714)
>> Victim Resource Owner:
>> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
>> Value:0x51983620 Cost:(0/714)
>>
>> Many Thanks
>>|||While there are always exceptions having that many columns in a clustered
index is usually not a good idea. Since the leaf level of the clustered
index is the actual data there is little to gain by clustering on so many
columns. Usually you can make the index selective enough on just a few and
it is always a good idea to keep the clustered index narrow since the
clustered index key is appended to the end of any nonclustered indexes as
well. If you are updating any of the 6 columns in the clustered index there
is a prime candidate for deadlocks since it has to physically move the
entire row.
--
Andrew J. Kelly SQL MVP
"Hush" <hushdontspamme@.hotmail.com> wrote in message
news:ekTgocPSGHA.424@.TK2MSFTNGP12.phx.gbl...
> Hi Charl
> Thanks for your reply:
> I am currently running a SQL Profiler - How do i look for the RPC event?
> I have included Excution Plan and the SQL locks events in the profiler.
> What i am alittle concerned about is that the table in question has on
> index (Clustered Index) and out of the 7 Columns in the table 6 of them is
> assigned to clustered index. Is that good practice?
> I am a non SQL Developer DBA....so i am a little stuck with this.
> Many Thanks
>
> "Charl" <Charl@.discussions.microsoft.com> wrote in message
> news:F38AA9E6-F580-4F62-A3D6-93C70375D145@.microsoft.com...
>> Hi Hush
>> Have you identified the SQL statements that cause the deadlock and tried
>> to
>> optimize the queries/indexes so that the locks resolve faster. The dump
>> specify the RPC event as sp_execute,1. Are you executing dynamic SQL ?
>> Regards
>> Charl
>> --
>> http://www.sqlserver.co.za
>>
>> "Hush" wrote:
>> Hi
>> I am getting the same deadlock on our SQL 2K server but with different
>> SPID
>> Ids throughout the day.
>> I know what tables and Index information from the KEY: information , and
>> that is it performing an exclusive lock. What i cannot figure out is
>> what
>> to do to help resolve this.
>> Do anyone have any tips? Here is the deadlock results: -
>> Deadlock encountered ... Printing deadlock information
>> Wait-for graph
>> Node:1
>> KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
>> Grant List 1::
>> Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64
>> ECID:0
>> SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
>> Input Buf: RPC Event: sp_execute;1
>> Requested By:
>> ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0
>> Ec:(0x77D6D550)
>> Value:0x46951760 Cost:(0/F08)
>> Node:2
>> KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
>> Grant List 2::
>> Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61
>> ECID:0
>> SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
>> Input Buf: RPC Event: sp_execute;1
>> Requested By:
>> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
>> Value:0x51983620 Cost:(0/714)
>> Victim Resource Owner:
>> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
>> Value:0x51983620 Cost:(0/714)
>>
>> Many Thanks
>>
>

Deadlocks "again"

Hi
I am getting the same deadlock on our SQL 2K server but with different SPID
Ids throughout the day.
I know what tables and Index information from the KEY: information , and
that is it performing an exclusive lock. What i cannot figure out is what
to do to help resolve this.
Do anyone have any tips? Here is the deadlock results: -
Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
Grant List 1::
Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64 ECID:0
SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
Input Buf: RPC Event: sp_execute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0 Ec0x77D6D550)
Value:0x46951760 Cost0/F08)
Node:2
KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
Grant List 2::
Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61 ECID:0
SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: RPC Event: sp_execute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec0x7E9EF550)
Value:0x51983620 Cost0/714)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec0x7E9EF550)
Value:0x51983620 Cost0/714)
Many ThanksHi Hush
Have you identified the SQL statements that cause the deadlock and tried to
optimize the queries/indexes so that the locks resolve faster. The dump
specify the RPC event as sp_execute,1. Are you executing dynamic SQL ?
Regards
Charl
http://www.sqlserver.co.za
"Hush" wrote:

> Hi
> I am getting the same deadlock on our SQL 2K server but with different SPI
D
> Ids throughout the day.
> I know what tables and Index information from the KEY: information , and
> that is it performing an exclusive lock. What i cannot figure out is what
> to do to help resolve this.
> Do anyone have any tips? Here is the deadlock results: -
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 1::
> Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64 ECID:0
> SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0 Ec0x77D6D550
)
> Value:0x46951760 Cost0/F08)
> Node:2
> KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 2::
> Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61 ECID:0
> SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec0x7E9EF550)
> Value:0x51983620 Cost0/714)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec0x7E9EF550)
> Value:0x51983620 Cost0/714)
>
> Many Thanks
>
>|||See if these help:
http://msdn.microsoft.com/library/d...
tabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
Andrew J. Kelly SQL MVP
"Hush" <hushdontspamme@.hotmail.com> wrote in message
news:uzWvhdOSGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Hi
> I am getting the same deadlock on our SQL 2K server but with different
> SPID Ids throughout the day.
> I know what tables and Index information from the KEY: information , and
> that is it performing an exclusive lock. What i cannot figure out is what
> to do to help resolve this.
> Do anyone have any tips? Here is the deadlock results: -
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 1::
> Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64 ECID:0
> SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0
> Ec0x77D6D550) Value:0x46951760 Cost0/F08)
> Node:2
> KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 2::
> Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61 ECID:0
> SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec0x7E9EF550)
> Value:0x51983620 Cost0/714)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec0x7E9EF550)
> Value:0x51983620 Cost0/714)
>
> Many Thanks
>|||Hi Charl
Thanks for your reply:
I am currently running a SQL Profiler - How do i look for the RPC event? I
have included Excution Plan and the SQL locks events in the profiler.
What i am alittle concerned about is that the table in question has on index
(Clustered Index) and out of the 7 Columns in the table 6 of them is
assigned to clustered index. Is that good practice?
I am a non SQL Developer DBA....so i am a little stuck with this.
Many Thanks
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:F38AA9E6-F580-4F62-A3D6-93C70375D145@.microsoft.com...[vbcol=seagreen]
> Hi Hush
> Have you identified the SQL statements that cause the deadlock and tried
> to
> optimize the queries/indexes so that the locks resolve faster. The dump
> specify the RPC event as sp_execute,1. Are you executing dynamic SQL ?
> Regards
> Charl
> --
> http://www.sqlserver.co.za
>
> "Hush" wrote:
>|||While there are always exceptions having that many columns in a clustered
index is usually not a good idea. Since the leaf level of the clustered
index is the actual data there is little to gain by clustering on so many
columns. Usually you can make the index selective enough on just a few and
it is always a good idea to keep the clustered index narrow since the
clustered index key is appended to the end of any nonclustered indexes as
well. If you are updating any of the 6 columns in the clustered index there
is a prime candidate for deadlocks since it has to physically move the
entire row.
Andrew J. Kelly SQL MVP
"Hush" <hushdontspamme@.hotmail.com> wrote in message
news:ekTgocPSGHA.424@.TK2MSFTNGP12.phx.gbl...
> Hi Charl
> Thanks for your reply:
> I am currently running a SQL Profiler - How do i look for the RPC event?
> I have included Excution Plan and the SQL locks events in the profiler.
> What i am alittle concerned about is that the table in question has on
> index (Clustered Index) and out of the 7 Columns in the table 6 of them is
> assigned to clustered index. Is that good practice?
> I am a non SQL Developer DBA....so i am a little stuck with this.
> Many Thanks
>
> "Charl" <Charl@.discussions.microsoft.com> wrote in message
> news:F38AA9E6-F580-4F62-A3D6-93C70375D145@.microsoft.com...
>

Deadlocks

Hello everyone,

For a while I have been experiencing deadlocks on my tables.
I am not sure how to get this fixed.

I have done lots of research but havent come to a result as yet.

Here is a bit of background on the apps that are cause these deadlocks,

I work at an ISP, we run about 8 radius servers, which open up a connection to our database to authenticate users and to insert accounting records. We also use radius (radiator) to give us information about our users online, meaning that our modem box servers where users dial in to send update packets every 5 minutes for each user which radiator handles and updates our database.

We then have another application which goes through all these account records and "processes" them, Meaning that it will find the stop records for a particular user, then grab the start record , then bill the customer, insert a record to our history table and then delete those records. These 2 (well in actual fact 9 programs because of our 8 radius servers) programs are causing deadlocks, I dont know how to fix this,

Are there any steps I can take to stop this, minimise this. Any help will be very helpful :)

thanks guysRefer to this link (http://www.sql-server-performance.com/lock_contention_tamed_article.asp) about lock contentions and how to tame them.

Deadlock within select?

My SQL Server has kicked out a deadlocked process, which should only be
running a select statement, though there is another select on one of
the tables in the WHERE clause (see code below). Can anyone tell me
whether this is possible, or is it that my system, which is re-using
connections, is trying to complete an earlier statement? I've looked
through the system and think I'm committing all transactions.

The query I'm running (simplified, I don't use daft names like 'table1'
or 'date_col', honest :) is:

SELECT table1.*, table2.*, table3.*
FROM table1, table2, table3
WHERE table2.col1 = table1.col1 AND table3.col1 = table1.col2
AND table1.col3 = 'xyz'
AND (table1.date_col >= '2006-02-01' OR table1.date_col IN
(SELECT date_col FROM table1 WHERE (col4 = 'A' OR col4 = 'B') AND col3
= 'xyz'))
ORDER BY table1.date_col

Thanks

J(jw_guildford@.yahoo.co.uk) writes:
> My SQL Server has kicked out a deadlocked process, which should only be
> running a select statement, though there is another select on one of
> the tables in the WHERE clause (see code below). Can anyone tell me
> whether this is possible, or is it that my system, which is re-using
> connections, is trying to complete an earlier statement? I've looked
> through the system and think I'm committing all transactions.

Presumably, there was an insert/update/delete operation that your SELECT
clashed with.

Have you looked at the deadlock trace?

If you don't have deadlock trace enabled, open Enterprise Manager, and
edit the startup parameters to include "-T 1204 -T 3605", and restart
the server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland, I've added those parameters, I'm afraid I don't have a
way of reproducing it at the moment so I'll just have to wait until it
happens again.

J

Deadlock on communication buffer, Thread

In our environment, a large number of users are
simultaneously running same select query against database.
The query involves around 7 tables and one view that has
millions of rows. In summary, the query is resource
intensive. Sometimes this query fails with the error
message
"Your transaction has been chosen as a victim of deadlock
on Communication Buffer,Thread" .
My question is why should there be a deadlock involved in
a SELECT statement. Deadlock should happen in
update/insert statements that use transactions.
Any insights on this will be helpful.
Thanks.there is an article at BOL that can helpl with this issue.. TROUBLESHOOTING
DEADLOCKS
One thing is the communication buffer in your error message, the bol sugests
that this can be related to query paralelism
Try to start the DBCC TRACEON(1204) to isolate the deadlock cause.
HTH
--
Wandenkolk T. Neto
MCDBA , MCSE
Fundação Abrinq
www.fundabrinq.org.br
"Vinod" <vinoddua2000@.yahoo.com> escreveu na mensagem
news:01c301c3674e$6b278d10$a501280a@.phx.gbl...
> In our environment, a large number of users are
> simultaneously running same select query against database.
> The query involves around 7 tables and one view that has
> millions of rows. In summary, the query is resource
> intensive. Sometimes this query fails with the error
> message
> "Your transaction has been chosen as a victim of deadlock
> on Communication Buffer,Thread" .
> My question is why should there be a deadlock involved in
> a SELECT statement. Deadlock should happen in
> update/insert statements that use transactions.
> Any insights on this will be helpful.
> Thanks.

Deadlock issues

I'm trying to eliminate (or at least reduce) deadlock issues. I've already
ensured all stored procedures are accessing tables in the same order and now
I am looking at locks and transaction levels. Are row level locks the
default for stored procedures in SQL Server 2000 or do I need to issue some
command to set the default to row level? Should implementing row level
locking and reducing my current transaction isolation levels from
Serializable to RepeatableRead yield any noticable difference in reducting
deadlocks? I realize this is a bit vague but I'm a VB developer tasked
with running the new SQL Server so any best practices or suggestions on this
subject are very welcome.Row level locking is the norm as long as you have proper indexes to access
the rows by. But you will definitely see more deadlocks if you are using
serializable isolation level. Read Committed is the default and should be
used where ever possible. There are very few times when you should actually
need serializable or even repeatable read isolation levels.
Andrew J. Kelly SQL MVP
"John Cobb" <john.cobb@.acxiom.com> wrote in message
news:u2oQRNPTFHA.2676@.TK2MSFTNGP10.phx.gbl...
> I'm trying to eliminate (or at least reduce) deadlock issues. I've
> already
> ensured all stored procedures are accessing tables in the same order and
> now
> I am looking at locks and transaction levels. Are row level locks the
> default for stored procedures in SQL Server 2000 or do I need to issue
> some
> command to set the default to row level? Should implementing row level
> locking and reducing my current transaction isolation levels from
> Serializable to RepeatableRead yield any noticable difference in reducting
> deadlocks? I realize this is a bit vague but I'm a VB developer tasked
> with running the new SQL Server so any best practices or suggestions on
> this
> subject are very welcome.
>
>
>|||In addition to Andrew's comment, you might want to take a look at these:
http://support.microsoft.com/kb/169960
http://support.microsoft.com/kb/224453
http://support.microsoft.com/kb/75722
-oj
"John Cobb" <john.cobb@.acxiom.com> wrote in message
news:u2oQRNPTFHA.2676@.TK2MSFTNGP10.phx.gbl...
> I'm trying to eliminate (or at least reduce) deadlock issues. I've
> already
> ensured all stored procedures are accessing tables in the same order and
> now
> I am looking at locks and transaction levels. Are row level locks the
> default for stored procedures in SQL Server 2000 or do I need to issue
> some
> command to set the default to row level? Should implementing row level
> locking and reducing my current transaction isolation levels from
> Serializable to RepeatableRead yield any noticable difference in reducting
> deadlocks? I realize this is a bit vague but I'm a VB developer tasked
> with running the new SQL Server so any best practices or suggestions on
> this
> subject are very welcome.
>
>
>|||Some of my tables have primary keys defined but not explicit indexes. Will
the PKs allow row level locking or do I explicitly need to define indexes.
Probably will do this anyway for performance but just curious.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Osp2YVPTFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Row level locking is the norm as long as you have proper indexes to access
> the rows by. But you will definitely see more deadlocks if you are using
> serializable isolation level. Read Committed is the default and should be
> used where ever possible. There are very few times when you should
actually
> need serializable or even repeatable read isolation levels.
> --
> Andrew J. Kelly SQL MVP
>
> "John Cobb" <john.cobb@.acxiom.com> wrote in message
> news:u2oQRNPTFHA.2676@.TK2MSFTNGP10.phx.gbl...
reducting
>|||PK constraints will build an index to enforce the constraint and will
function just like any other index (plus the constraint part). So they will
allow row level locking if you are using the PK in the WHERE clause as your
SARG. Rarely is the table accessed solely by the PK. You may require other
indexes to access and lock the table properly.
Andrew J. Kelly SQL MVP
"John Cobb" <john.cobb@.acxiom.com> wrote in message
news:uQ28GYAUFHA.4092@.TK2MSFTNGP12.phx.gbl...
> Some of my tables have primary keys defined but not explicit indexes. Will
> the PKs allow row level locking or do I explicitly need to define indexes.
> Probably will do this anyway for performance but just curious.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:Osp2YVPTFHA.3176@.TK2MSFTNGP09.phx.gbl...
> actually
> reducting
>sql

Deadlock Issue when dropping/creating tables

We are getting deadlock errors (sporadically) on a batch job we've created.

This job runs against a SQL Server 2000 back-end.

The first step of the batch job is to run a DDL script to drop and create 4 tables that are used in the job. The tables are only used during this job and are not accessed by any other process or application.

The second step of the batch job is to make an OSQL call to run the stored procedures associated with the job.

The deadlocks occur during the first step in the job, during the drop/create table statements. A sample follows:

Msg 1205, Level 13, State 54, Server SQL\APP_PROD, Line 7
Transaction (Process ID 78) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.

I am no DBA but can't understand how we can be getting a deadlock while dropping and creating tables that are used by no other processes or applications.

Any thoughts or help would be greatly appreciated.

Quote:

Originally Posted by DWiggin

We are getting deadlock errors (sporadically) on a batch job we've created.

This job runs against a SQL Server 2000 back-end.

The first step of the batch job is to run a DDL script to drop and create 4 tables that are used in the job. The tables are only used during this job and are not accessed by any other process or application.

The second step of the batch job is to make an OSQL call to run the stored procedures associated with the job.

The deadlocks occur during the first step in the job, during the drop/create table statements. A sample follows:

Msg 1205, Level 13, State 54, Server SQL\APP_PROD, Line 7
Transaction (Process ID 78) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.

I am no DBA but can't understand how we can be getting a deadlock while dropping and creating tables that are used by no other processes or applications.

Any thoughts or help would be greatly appreciated.

if you ran your stored proc and then run it again, you'll have problem since it's still being used by the first one. possible locks will happen. try to create a tempoary table with randomly-generated table names...

Deadlock issue SQLServer2000

Hi,
I'm facing a deadlock issue in a stored procedure which only deletes
records from multiple tables.When i run this stotred proc. multiple
times I get deadlock between two SPIDs running the same stored
procedure's code.On drilling down in SQL trace using flag 1205 and the
SQL server trace I found that these are conversion deadlocks.The
records are deleted using PK in follwing sequence of
tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
TbSFUsage -> TbCID -> TbCustomer.
As is evident from names of tables - TbUserService
,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFU
sage,TbUser
depends(FK) on TbCID
tables -
TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
tables -
TbCID depends(FK) on TbCustomer.
This is SQL trace I get...
Deadlock encountered ... Printing deadlock information
2004-10-04 19:06:25.27 spid4
2004-10-04 19:06:25.27 spid4 Wait-for graph
2004-10-04 19:06:25.27 spid4
2004-10-04 19:06:25.27 spid4 Node:1
2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
CleanCnt:2 Mode: X Flags: 0x0
2004-10-04 19:06:25.27 spid4 Wait List:
2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageExpertInitializeRollba
ck;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:78 ECID:0 Ec0x1B58F568) Value:0x193ff980 Cost0/614)
2004-10-04 19:06:25.32 spid4
2004-10-04 19:06:25.32 spid4 Node:2
2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
CleanCnt:2 Mode: X Flags: 0x0
2004-10-04 19:06:25.32 spid4 Grant List 0::
2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageExpertInitializeRollba
ck;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:85 ECID:0 Ec0x1D915568) Value:0x1940e180 Cost0/518)
2004-10-04 19:06:25.32 spid4
2004-10-04 19:06:25.32 spid4 Node:3
2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
CleanCnt:1 Mode: X Flags: 0x0
2004-10-04 19:06:25.32 spid4 Grant List 0::
2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageProfiInitializeRollbac
k;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:83 ECID:0 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/518)
2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:83 ECID:0 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/518)
2004-10-04 19:06:30.34 spid4
the resource 277576027 above is table TbCIDAdmin!
Until and unless i change the sequence of deletes I get the deadlock
in same table.
Can anyone figure out why is this happening?
I feel the problem lies in FK constraint checking while deleting rows.
Because SQL server must be reading(Shared Lock) the child tables
before deleting row from a parent table. Also, when I disabled all
foreign key constraint checking I stopped getting the deadlock errors!
If this is the reason can anyone pl. tell me how can I fix this?
Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
this stored procedure and at the same time other stored
procedures/transaction can work with the constraint checking as
ususal.
There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
. Can we somehow replicate this functionlaity in SQL server 2000?
Pl. help because this problem has become a real pain ....
Thanks in advance
BipulConstranits are good so do not turn them off so eagerly.
You might want to try select .. with (updlock) to avoid conversion related
deadlocks.
Here is an example of a deadlock free sequence.
begin tran
select ... from dept-table with (updlock) where dept_id = 99
delete employee-table where dept_id = 99
delete dept-table where dept_id = 99
commit
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bipul" <itsbipul@.gmail.com> wrote in message
news:f280e5a9.0410112211.2245022e@.posting.google.com...
> Hi,
> I'm facing a deadlock issue in a stored procedure which only deletes
> records from multiple tables.When i run this stotred proc. multiple
> times I get deadlock between two SPIDs running the same stored
> procedure's code.On drilling down in SQL trace using flag 1205 and the
> SQL server trace I found that these are conversion deadlocks.The
> records are deleted using PK in follwing sequence of
> tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
> TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
> TbSFUsage -> TbCID -> TbCustomer.
> As is evident from names of tables - TbUserService
> ,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
> tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFU
sage,TbUser
> depends(FK) on TbCID
> tables -
> TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
> tables -
> TbCID depends(FK) on TbCustomer.
> This is SQL trace I get...
> Deadlock encountered ... Printing deadlock information
> 2004-10-04 19:06:25.27 spid4
> 2004-10-04 19:06:25.27 spid4 Wait-for graph
> 2004-10-04 19:06:25.27 spid4
> 2004-10-04 19:06:25.27 spid4 Node:1
> 2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
> CleanCnt:2 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.27 spid4 Wait List:
> 2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
> Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
> 2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageExpertInitializeRollba
ck;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:78 ECID:0 Ec0x1B58F568) Value:0x193ff980 Cost0/614)
> 2004-10-04 19:06:25.32 spid4
> 2004-10-04 19:06:25.32 spid4 Node:2
> 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
> CleanCnt:2 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.32 spid4 Grant List 0::
> 2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
> 2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageExpertInitializeRollba
ck;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:85 ECID:0 Ec0x1D915568) Value:0x1940e180 Cost0/518)
> 2004-10-04 19:06:25.32 spid4
> 2004-10-04 19:06:25.32 spid4 Node:3
> 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
> CleanCnt:1 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.32 spid4 Grant List 0::
> 2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
> 2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageProfiInitializeRollbac
k;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:83 ECID:0 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/518)
> 2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:83 ECID:0 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/518)
> 2004-10-04 19:06:30.34 spid4
> the resource 277576027 above is table TbCIDAdmin!
> Until and unless i change the sequence of deletes I get the deadlock
> in same table.
> Can anyone figure out why is this happening?
> I feel the problem lies in FK constraint checking while deleting rows.
> Because SQL server must be reading(Shared Lock) the child tables
> before deleting row from a parent table. Also, when I disabled all
> foreign key constraint checking I stopped getting the deadlock errors!
> If this is the reason can anyone pl. tell me how can I fix this?
> Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
> this stored procedure and at the same time other stored
> procedures/transaction can work with the constraint checking as
> ususal.
> There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
> . Can we somehow replicate this functionlaity in SQL server 2000?
> Pl. help because this problem has become a real pain ....
> Thanks in advance
> Bipul|||Hi xiao,
I dont have any SELECT statements inside the transaction in my stored
procedure.
I have only DELETE statements with WHERE clause on the PK(whihc is
clustered index).
I'm basically not able to understand why deadlock chain is happening?
If you see the SQL Log I have provided in the first mail, all the
SPIDs are locking on the same Table and on the same IndId(index)and
each are having X lock and requesting for S lock. How is this possible
that each have been granted a X lock on same resource (since hash
values are different I guess they corresond to different records in
same table)? Should not they Block instead of deadlock? Will TABLOCK
help? But,then I would have to have TABLOCK on all tables from which
I'm deleteing in the transaction, which won't be a good idea?
Awaiting your comments?
Regds,
Bipul
yahoo/MSN id : itsbipul
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message news:<umxXUROsEHA.3564@.tk
2msftngp13.phx.gbl>...[vbcol=seagreen]
> Constranits are good so do not turn them off so eagerly.
> You might want to try select .. with (updlock) to avoid conversion related
> deadlocks.
> Here is an example of a deadlock free sequence.
> begin tran
> select ... from dept-table with (updlock) where dept_id = 99
> delete employee-table where dept_id = 99
> delete dept-table where dept_id = 99
> commit
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Bipul" <itsbipul@.gmail.com> wrote in message
> news:f280e5a9.0410112211.2245022e@.posting.google.com...|||On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
>Awaiting your comments?
Are you doing joins in your delete statements?
Can you show your code?
It is curious, since you own the locks on early deletes, but I'd like
to try to figure out what SQLServer *thinks* it's doing!
Can you break the transaction into several independent pieces - quick
workaround, probably.
J.|||Hi,
This indeed is an intriguing problem. I don't have any joins in the
Delete queries. These are just pure delete statements with 'where'
clause on PK of the table from which I delete.
But,yes as you can see from my first mail there is child parent
relationship between the tables I'm deleting.
I can not break the transaction into smaller pieces.
What do you think will be the problem?
What I think is that when SQL server is deleting from the parent table
it searches the child tables for checking FK violations. For this it
takes locks on the indexes of these child tables and then 2 pids doing
the same get deadlocked on index resource.
Am I thinking on right lines ? or there is something else?
Awaiting responses...
Regds,
Bipul
JXStern <JXSternChangeX2R@.gte.net> wrote in message news:<cd9in0h4qtg9g9n63buuhoh85qte4asjvh
@.4ax.com>...
> On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
> Are you doing joins in your delete statements?
> Can you show your code?
> It is curious, since you own the locks on early deletes, but I'd like
> to try to figure out what SQLServer *thinks* it's doing!
> Can you break the transaction into several independent pieces - quick
> workaround, probably.
> J.|||Hi Bipul,
Is this problem resolved ? I was going thru the newsgroup to learn more
about deadlocks. Reading the thread and the response from Wei Xiao, I have a
feeling that he had the select statement with updlock in his transaction to
prevent sql server from allowing any sharing of those rows. so make your
delete work, you can try this:
begin tran
get upd lock on childTab
delete from childTab
delete from parentTab
commit tran
One thing that is confusing is that Wei had a select on the parentTab with
updlock, whereas your deadlock was due to contention on a child table. My
above suggestion is based on your assumption that sql server is not able to
establish the shared lock on the child table while checking the constraint.
Regards,
Mani.
"Bipul" wrote:

> Hi,
> This indeed is an intriguing problem. I don't have any joins in the
> Delete queries. These are just pure delete statements with 'where'
> clause on PK of the table from which I delete.
> But,yes as you can see from my first mail there is child parent
> relationship between the tables I'm deleting.
> I can not break the transaction into smaller pieces.
> What do you think will be the problem?
> What I think is that when SQL server is deleting from the parent table
> it searches the child tables for checking FK violations. For this it
> takes locks on the indexes of these child tables and then 2 pids doing
> the same get deadlocked on index resource.
> Am I thinking on right lines ? or there is something else?
> Awaiting responses...
> Regds,
> Bipul
>
>
> JXStern <JXSternChangeX2R@.gte.net> wrote in message news:<cd9in0h4qtg9g9n6
3buuhoh85qte4asjvh@.4ax.com>...
>