deadlock Question

HI,
below is the deadlock i am frequently getting in my application. In the
below error log, SPID 100 is blocked from its request for an Shared(S) lock
on object 258099960 because SPID 93 already has an exclusive lock on it. In
Node 2, SPID 93 is blocked from its request for an exclusive lock on Table
258099960
because SPID 100 has an Shared lock on it. SQL Server chose SPID 100 as the
deadlock victim to break the deadlock, as indicated by the Victim Resource
Owner entry.
I understood why it is happening but not completely and not able to resolve
this issue, can any one help.
================================================== =
2005-06-14 13:46:48.00 spid4ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4Victim Resource Owner:
2005-06-14 13:46:48.00 spid4ResType:LockOwner Stype:'OR' Mode: X SPID:93
ECID:0 Ec0x543E3548) Value:0x1ff
2005-06-14 13:46:48.00 spid4Requested By:
2005-06-14 13:46:48.00 spid4Grant List 3::
2005-06-14 13:46:48.00 spid4Input Buf: RPC Event: FindCustomer;1
2005-06-14 13:46:48.00 spid4SPID: 100 ECID: 0 Statement Type: SELECT Line
#: 1
2005-06-14 13:46:48.00 spid4Owner:0x52f99720 Mode: S Flg:0x0 Ref:1
Life:02000000 SPID:100 ECID:0
2005-06-14 13:46:48.00 spid4Grant List 1::
2005-06-14 13:46:48.00 spid4KEY: 13:836250084:1 (8e0060ab9a82) CleanCnt:1
Mode: U Flags: 0x0
2005-06-14 13:46:48.00 spid4Node:2
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4Requested By:
2005-06-14 13:46:48.00 spid4Input Buf: RPC Event: SPAR42B04.dbo.Search;1
2005-06-14 13:46:48.00 spid4SPID: 93 ECID: 0 Statement Type: UPDATE Line
#: 95
2005-06-14 13:46:48.00 spid4Owner:0x55bbd100 Mode: X Flg:0x0 Ref:1
Life:02000000 SPID:93 ECID:0
2005-06-14 13:46:48.00 spid4Grant List 3::
2005-06-14 13:46:48.00 spid4KEY: 13:258099960:1 (8e0060ab9a82) CleanCnt:1
Mode: X Flags: 0x0
2005-06-14 13:46:48.00 spid4Node:1
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4Wait-for graph
Could you post the code that caused it?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:89F25B5D-11F5-433D-8BD9-C4734010684B@.microsoft.com...
HI,
below is the deadlock i am frequently getting in my application. In the
below error log, SPID 100 is blocked from its request for an Shared(S) lock
on object 258099960 because SPID 93 already has an exclusive lock on it. In
Node 2, SPID 93 is blocked from its request for an exclusive lock on Table
258099960
because SPID 100 has an Shared lock on it. SQL Server chose SPID 100 as the
deadlock victim to break the deadlock, as indicated by the Victim Resource
Owner entry.
I understood why it is happening but not completely and not able to resolve
this issue, can any one help.
================================================== =
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Victim Resource Owner:
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:93
ECID:0 Ec0x543E3548) Value:0x1ff
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: FindCustomer;1
2005-06-14 13:46:48.00 spid4 SPID: 100 ECID: 0 Statement Type: SELECT Line
#: 1
2005-06-14 13:46:48.00 spid4 Owner:0x52f99720 Mode: S Flg:0x0 Ref:1
Life:02000000 SPID:100 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 1::
2005-06-14 13:46:48.00 spid4 KEY: 13:836250084:1 (8e0060ab9a82) CleanCnt:1
Mode: U Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:2
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: SPAR42B04.dbo.Search;1
2005-06-14 13:46:48.00 spid4 SPID: 93 ECID: 0 Statement Type: UPDATE Line
#: 95
2005-06-14 13:46:48.00 spid4 Owner:0x55bbd100 Mode: X Flg:0x0 Ref:1
Life:02000000 SPID:93 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 KEY: 13:258099960:1 (8e0060ab9a82) CleanCnt:1
Mode: X Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:1
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 Wait-for graph

deadlock Question

HI,
below is the deadlock i am frequently getting in my application. In the
below error log, SPID 100 is blocked from its request for an Shared(S) lock
on object 258099960 because SPID 93 already has an exclusive lock on it. In
Node 2, SPID 93 is blocked from its request for an exclusive lock on Table
258099960
because SPID 100 has an Shared lock on it. SQL Server chose SPID 100 as the
deadlock victim to break the deadlock, as indicated by the Victim Resource
Owner entry.
I understood why it is happening but not completely and not able to resolve
this issue, can any one help.
===================================================
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec:(0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Victim Resource Owner:
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:93
ECID:0 Ec:(0x543E3548) Value:0x1ff
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: FindCustomer;1
2005-06-14 13:46:48.00 spid4 SPID: 100 ECID: 0 Statement Type: SELECT Line
#: 1
2005-06-14 13:46:48.00 spid4 Owner:0x52f99720 Mode: S Flg:0x0 Ref:1
Life:02000000 SPID:100 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 1::
2005-06-14 13:46:48.00 spid4 KEY: 13:836250084:1 (8e0060ab9a82) CleanCnt:1
Mode: U Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:2
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec:(0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: SPAR42B04.dbo.Search;1
2005-06-14 13:46:48.00 spid4 SPID: 93 ECID: 0 Statement Type: UPDATE Line
#: 95
2005-06-14 13:46:48.00 spid4 Owner:0x55bbd100 Mode: X Flg:0x0 Ref:1
Life:02000000 SPID:93 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 KEY: 13:258099960:1 (8e0060ab9a82) CleanCnt:1
Mode: X Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:1
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 Wait-for graphCould you post the code that caused it?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:89F25B5D-11F5-433D-8BD9-C4734010684B@.microsoft.com...
HI,
below is the deadlock i am frequently getting in my application. In the
below error log, SPID 100 is blocked from its request for an Shared(S) lock
on object 258099960 because SPID 93 already has an exclusive lock on it. In
Node 2, SPID 93 is blocked from its request for an exclusive lock on Table
258099960
because SPID 100 has an Shared lock on it. SQL Server chose SPID 100 as the
deadlock victim to break the deadlock, as indicated by the Victim Resource
Owner entry.
I understood why it is happening but not completely and not able to resolve
this issue, can any one help.
===================================================
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec:(0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Victim Resource Owner:
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:93
ECID:0 Ec:(0x543E3548) Value:0x1ff
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: FindCustomer;1
2005-06-14 13:46:48.00 spid4 SPID: 100 ECID: 0 Statement Type: SELECT Line
#: 1
2005-06-14 13:46:48.00 spid4 Owner:0x52f99720 Mode: S Flg:0x0 Ref:1
Life:02000000 SPID:100 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 1::
2005-06-14 13:46:48.00 spid4 KEY: 13:836250084:1 (8e0060ab9a82) CleanCnt:1
Mode: U Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:2
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec:(0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: SPAR42B04.dbo.Search;1
2005-06-14 13:46:48.00 spid4 SPID: 93 ECID: 0 Statement Type: UPDATE Line
#: 95
2005-06-14 13:46:48.00 spid4 Owner:0x55bbd100 Mode: X Flg:0x0 Ref:1
Life:02000000 SPID:93 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 KEY: 13:258099960:1 (8e0060ab9a82) CleanCnt:1
Mode: X Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:1
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 Wait-for graph

deadlock Question

HI,
below is the deadlock i am frequently getting in my application. In the
below error log, SPID 100 is blocked from its request for an Shared(S) lock
on object 258099960 because SPID 93 already has an exclusive lock on it. In
Node 2, SPID 93 is blocked from its request for an exclusive lock on Table
258099960
because SPID 100 has an Shared lock on it. SQL Server chose SPID 100 as the
deadlock victim to break the deadlock, as indicated by the Victim Resource
Owner entry.
I understood why it is happening but not completely and not able to resolve
this issue, can any one help.
========================================
===========
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Victim Resource Owner:
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:93
ECID:0 Ec0x543E3548) Value:0x1ff
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: FindCustomer;1
2005-06-14 13:46:48.00 spid4 SPID: 100 ECID: 0 Statement Type: SELECT Line
#: 1
2005-06-14 13:46:48.00 spid4 Owner:0x52f99720 Mode: S Flg:0x0 Ref:1
Life:02000000 SPID:100 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 1::
2005-06-14 13:46:48.00 spid4 KEY: 13:836250084:1 (8e0060ab9a82) CleanCnt:1
Mode: U Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:2
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: SPAR42B04.dbo.Search;1
2005-06-14 13:46:48.00 spid4 SPID: 93 ECID: 0 Statement Type: UPDATE Line
#: 95
2005-06-14 13:46:48.00 spid4 Owner:0x55bbd100 Mode: X Flg:0x0 Ref:1
Life:02000000 SPID:93 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 KEY: 13:258099960:1 (8e0060ab9a82) CleanCnt:1
Mode: X Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:1
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 Wait-for graphCould you post the code that caused it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:89F25B5D-11F5-433D-8BD9-C4734010684B@.microsoft.com...
HI,
below is the deadlock i am frequently getting in my application. In the
below error log, SPID 100 is blocked from its request for an Shared(S) lock
on object 258099960 because SPID 93 already has an exclusive lock on it. In
Node 2, SPID 93 is blocked from its request for an exclusive lock on Table
258099960
because SPID 100 has an Shared lock on it. SQL Server chose SPID 100 as the
deadlock victim to break the deadlock, as indicated by the Victim Resource
Owner entry.
I understood why it is happening but not completely and not able to resolve
this issue, can any one help.
========================================
===========
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Victim Resource Owner:
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:93
ECID:0 Ec0x543E3548) Value:0x1ff
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: FindCustomer;1
2005-06-14 13:46:48.00 spid4 SPID: 100 ECID: 0 Statement Type: SELECT Line
#: 1
2005-06-14 13:46:48.00 spid4 Owner:0x52f99720 Mode: S Flg:0x0 Ref:1
Life:02000000 SPID:100 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 1::
2005-06-14 13:46:48.00 spid4 KEY: 13:836250084:1 (8e0060ab9a82) CleanCnt:1
Mode: U Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:2
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: SPAR42B04.dbo.Search;1
2005-06-14 13:46:48.00 spid4 SPID: 93 ECID: 0 Statement Type: UPDATE Line
#: 95
2005-06-14 13:46:48.00 spid4 Owner:0x55bbd100 Mode: X Flg:0x0 Ref:1
Life:02000000 SPID:93 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 KEY: 13:258099960:1 (8e0060ab9a82) CleanCnt:1
Mode: X Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:1
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 Wait-for graph

dead lock

Hi,
Yestoday I checked the SQL SERVER management information, it showed one
process blocked the others, the last TSQL command batch of the blocking
process was SET NOEXEC OFF, what's the reason that this process blocked
other processes, how to solve it?
Thanks
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>
|||Actually, 1204 only forces a full scan debug of the deadlock chain. 3604
sends output to the client and 3605 sends output to the server error log.
However, at some pointI believe SP3the 3605 flag was no longer required,
but it has gone back and forth a few times. So, I usually explicitly set
3605 as well.
Sincerely,
Anthony Thomas

"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7iETYPYFHA.3584@.TK2MSFTNGP12.phx.gbl...
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>

dead lock

Hi,
Yestoday I checked the SQL SERVER management information, it showed one
process blocked the others, the last TSQL command batch of the blocking
process was SET NOEXEC OFF, what's the reason that this process blocked
other processes, how to solve it?
ThanksHi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>|||Actually, 1204 only forces a full scan debug of the deadlock chain. 3604
sends output to the client and 3605 sends output to the server error log.
However, at some pointI believe SP3the 3605 flag was no longer required,
but it has gone back and forth a few times. So, I usually explicitly set
3605 as well.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7iETYPYFHA.3584@.TK2MSFTNGP12.phx.gbl...
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>

dead lock

Hi,
Yestoday I checked the SQL SERVER management information, it showed one
process blocked the others, the last TSQL command batch of the blocking
process was SET NOEXEC OFF, what's the reason that this process blocked
other processes, how to solve it?
ThanksHi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>|||Actually, 1204 only forces a full scan debug of the deadlock chain. 3604
sends output to the client and 3605 sends output to the server error log.
However, at some point?I believe SP3?the 3605 flag was no longer required,
but it has gone back and forth a few times. So, I usually explicitly set
3605 as well.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7iETYPYFHA.3584@.TK2MSFTNGP12.phx.gbl...
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>