Deadlocked on the same resource (same index)

I'm seeing a deadlock issue that traces out the following 1204 report
below. You can see that one process is granted a shared lock (Mode: S)
on the index and another process is granted an exclusive lock on the
same index.
How is that possible? What scenarios could lead to this? I know that
deadlocks can happen over the same resource when one or two processes
are trying to raise the isolation level, but that doesn't seem to be
the case here.
It almost seems like the two processes are requesting locks (that they
already have?) and waiting for the other to release. What scenarios
could lead to this?
Unfortunately I can't show any code. Here is the trace file:
Michael Swart
Wait-for graph
Node:1
KEY: 7:2133582639:3 (180223bc5cb5) CleanCnt:1 Mode: X Flags: 0x0
Grant List 3::
Owner:0x52e00720 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:98
ECID:0
SPID: 98 ECID: 0 Statement Type: UPDATE Line #: 34
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec0x7C1615D8)
Value:0x52dd7340 Cost0/0)
Node:2
KEY: 7:2133582639:3 (a80172417f28) CleanCnt:1 Mode: S Flags: 0x0
Grant List 0::
Owner:0x52e2e7c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:93
ECID:0
SPID: 93 ECID: 0 Statement Type: INSERT Line #: 2
Input Buf: Language Event: EXEC LoadDataPartitions
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec0x5A2E5578)
Value:0x52fa6780 Cost0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec0x7C1615D8)
Value:0x52dd7340 Cost0/0)
Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.

> It almost seems like the two processes are requesting locks (that they
> already have?) and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.

> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robert

Deadlocked on the same resource (same index)

I'm seeing a deadlock issue that traces out the following 1204 report
below. You can see that one process is granted a shared lock (Mode: S)
on the index and another process is granted an exclusive lock on the
same index.
How is that possible? What scenarios could lead to this? I know that
deadlocks can happen over the same resource when one or two processes
are trying to raise the isolation level, but that doesn't seem to be
the case here.
It almost seems like the two processes are requesting locks (that they
already have') and waiting for the other to release. What scenarios
could lead to this?
Unfortunately I can't show any code. Here is the trace file:
Michael Swart
Wait-for graph
Node:1
KEY: 7:2133582639:3 (180223bc5cb5) CleanCnt:1 Mode: X Flags: 0x0
Grant List 3::
Owner:0x52e00720 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:98
ECID:0
SPID: 98 ECID: 0 Statement Type: UPDATE Line #: 34
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec0x7C1615D8)
Value:0x52dd7340 Cost0/0)
Node:2
KEY: 7:2133582639:3 (a80172417f28) CleanCnt:1 Mode: S Flags: 0x0
Grant List 0::
Owner:0x52e2e7c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:93
ECID:0
SPID: 93 ECID: 0 Statement Type: INSERT Line #: 2
Input Buf: Language Event: EXEC LoadDataPartitions
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec0x5A2E5578)
Value:0x52fa6780 Cost0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec0x7C1615D8)
Value:0x52dd7340 Cost0/0)Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.

> It almost seems like the two processes are requesting locks (that they
> already have') and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.

> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robertsql

Deadlocked on the same resource (same index)

I'm seeing a deadlock issue that traces out the following 1204 report
below. You can see that one process is granted a shared lock (Mode: S)
on the index and another process is granted an exclusive lock on the
same index.
How is that possible? What scenarios could lead to this? I know that
deadlocks can happen over the same resource when one or two processes
are trying to raise the isolation level, but that doesn't seem to be
the case here.
It almost seems like the two processes are requesting locks (that they
already have') and waiting for the other to release. What scenarios
could lead to this?
Unfortunately I can't show any code. Here is the trace file:
Michael Swart
Wait-for graph
Node:1
KEY: 7:2133582639:3 (180223bc5cb5) CleanCnt:1 Mode: X Flags: 0x0
Grant List 3::
Owner:0x52e00720 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:98
ECID:0
SPID: 98 ECID: 0 Statement Type: UPDATE Line #: 34
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec:(0x7C1615D8)
Value:0x52dd7340 Cost:(0/0)
Node:2
KEY: 7:2133582639:3 (a80172417f28) CleanCnt:1 Mode: S Flags: 0x0
Grant List 0::
Owner:0x52e2e7c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:93
ECID:0
SPID: 93 ECID: 0 Statement Type: INSERT Line #: 2
Input Buf: Language Event: EXEC LoadDataPartitions
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec:(0x5A2E5578)
Value:0x52fa6780 Cost:(0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec:(0x7C1615D8)
Value:0x52dd7340 Cost:(0/0)Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.
> It almost seems like the two processes are requesting locks (that they
> already have') and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.
> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robert

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

deadlock between select (shared) and update (intent exclusive)

I regularly have deadlocks on my sql-server 2000. Using the 1204 trace
I got the following info about the problem:
Wait-for graph
Node:1
PAG: 7:1:251381 CleanCnt:2 Mode: S Flags: 0x2
Grant List 0::
Owner:0x2c959e00 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:61
ECID:3
Requested By:
ResType:LockOwner Stype:'OR' Mode: IX SPID:72 ECID:0 Ec0x50871568)
Value:0x76375e60 Cost0/5580)
Node:2
PAG: 7:1:230822 CleanCnt:2 Mode: IX Flags: 0x2
Grant List 3::
Owner:0x4bba49e0 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:72
ECID:0
SPID: 72 ECID: 0 Statement Type: INSERT Line #: 1
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:3 Ec0x2D8EA0C0)
Value:0x75df1780 Cost0/0)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:3 Ec0x2D8EA0C0)
Value:0x75df1780 Cost0/0)
As I understand it, one statement owns an IX-lock and requests another
one while another statement owns a shared-lock and requests another
one. I know, I should always access tables in the same order but it's
too late for this now.
How can I tell the select statement to read the last commited data and
not to lock anything? IMHO we did not give any lock-hints with our
statements so the default lock levels should be used. Does it make
sense that a select blocks an update?Update: it is not a select and an UPDATE but a select count and an
insert.|||
> As I understand it, one statement owns an IX-lock and requests another
> one while another statement owns a shared-lock and requests another
> one. I know, I should always access tables in the same order but it's
> too late for this now.
> How can I tell the select statement to read the last commited data and
> not to lock anything? IMHO we did not give any lock-hints with our
> statements so the default lock levels should be used. Does it make
> sense that a select blocks an update?
>
I do not pretend to understand your locking situation.
And although I thought in the past that a select should Not be partner
in a deadlock. This proved to be wrong.
My situation.
Update transaction (standard isolation), two updates on one single row.
The select was a very simple select which resulted in a single row of a
single table.
The combination could result in a deadlock.
The probable cause of 'my' problem.
Both updates used different where clauses, which resulted in the same row,
but
resulted in different locking situations.
In this situation you can NOT tel to read the last commited data, because
that is
locked at the moment. In SQL-server 2005 you can opt for snapshot isolation,
where the last commited data is read. So with snapshot isolation reads do
not block
write and writes do not block reads.
Be carefull with snapshot isolation because this does not implement
serializability.
Good luck with your situation,
If you have more information please post it here,
If you have more questions please post it here.
ben brugman|||mhuhn.de@.gmail.com,
This feature has been implemented in SQL Server 2005 (Snapshot Isolation).
If you are using 2000 and do not want to change the order in which you
access your tables, consider using a table_hint in your "select" statement,
specifically ROWLOCK based on the info you posted (the lock seems to be at
the page level). See BOL for more info.
AMB
"mhuhn.de@.gmail.com" wrote:

> Update: it is not a select and an UPDATE but a select count and an
> insert.
>|||Thanks for answering. Anyway, 2005 is not an option because our
solution is already used from lots of customers. Do you think a ROWLOCK
makes sense if I do a select count? If the where-clause in the select
count includes the updated row, I'll have the same problem, right?
Furthermore, it will slow down my selects!?|||As I wrote in my other mail :
I do not pretend to understand your locking situation.
But I doubt very much that a ROWLOCK in the select will
solve the problem. The select (without a rowlock) is allready
waiting for another process to finish, this waiting can (I think)
not be solved by using a ROWLOCK, the rowlock will
(probably) prevent the other process on locking on the read
process.
A (row)lock in the update might claim enough resources that
the select is not capable of applying a lock which can stop the
update. So the update can finish after which the select can finish.
ben brugman
<mhuhn.de@.gmail.com> wrote in message
news:1147960900.879319.169740@.j55g2000cwa.googlegroups.com...
> Thanks for answering. Anyway, 2005 is not an option because our
> solution is already used from lots of customers. Do you think a ROWLOCK
> makes sense if I do a select count? If the where-clause in the select
> count includes the updated row, I'll have the same problem, right?
> Furthermore, it will slow down my selects!?
>

dbinit(), dblogin(), how often?

I'm a MS SQL newbie and am programming SQL using MS DS C++ 2003.

I'm writing sql code that will reside in a shared dll, used by many
processes and many threads in those processes.

So how often do I need to call dbinit()? Only the first time the DLL is
loaded, once per new process, once per thread, or once per database open?

Same question for dblogin().

Thanks very much for any help.
Bruce.Bruce. (noone@.nowhere.com) writes:

Quote:

Originally Posted by

I'm a MS SQL newbie and am programming SQL using MS DS C++ 2003.
>
I'm writing sql code that will reside in a shared dll, used by many
processes and many threads in those processes.
>
So how often do I need to call dbinit()? Only the first time the DLL is
loaded, once per new process, once per thread, or once per database open?
>
Same question for dblogin().

Zero times. At least unless you have some very special reason to use
DB-Library at all, like the need to support a legacy application. To wit,
DB-Library is a deprecated client API, and it lacks support for new features
added since SQL7, as Microsoft has not touched it for the last 8-10 years.

The recommended choice for a C++ application are ODBC and OLE DB. Of these
the ODBC is probably a lot easier to work with.

--
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|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99425A6E7696FYazorman@.127.0.0.1...

Quote:

Originally Posted by

The recommended choice for a C++ application are ODBC and OLE DB. Of these
the ODBC is probably a lot easier to work with.

Not an option in this case but thanks for your reply anyway.

Bruce.|||Bruce. (noone@.nowhere.com) writes:

Quote:

Originally Posted by

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99425A6E7696FYazorman@.127.0.0.1...

Quote:

Originally Posted by

>The recommended choice for a C++ application are ODBC and OLE DB. Of
>these the ODBC is probably a lot easier to work with.

>
Not an option in this case but thanks for your reply anyway.

I'm sorry I was not able to answer your actual question at the time, but
I did not have access to some old source code that I have. Having looked
at that one, I see that I have this:

// Init DB-Library if we are the first player.
EnterCriticalSection(&CS);
if (no_of_threads++ == 0) {
if(dbinit() == FAIL) {
croak("Can't initialize dblibrary...");
}
// Set up the error handlers once for all.
dberrhandle(err_handler);
dbmsghandle(msg_handler);
}
LeaveCriticalSection(&CS);

// Set up LOGINREC struct for this thread.
td->login = dblogin();
DBSETLUSER(td->login, NULL);
DBSETLPWD(td->login, NULL);
DBSETLHOST(td->login, getenv("COMPUTERNAME"));

That is, call dbinit() when the DLL is initiated, but call dblogin once
for each thread. Then again, I guess the reason I did it this way was
to permit different threads to use the different login information. If
all threads will use the same login details, I can't see anything else
than that it would be sufficient to call dblogin() once, since LOGINREC
appears to only hold static data.

But permit me again to point the unsuitable in using DB-Library for new
development. Or to be more blunt: it's sheer silliness. If nothing else,
it's a waste of time for your professional development. The likelyhood
that you will get the oppurtunity to reuse the knowledge of DB-Library
programming are slim, whereas learning to master the ODBC API can be very
useful.

Why would ODBC or OLE DB not be an option in your case?

--
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|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99436DC7B5A82Yazorman@.127.0.0.1...

Quote:

Originally Posted by

That is, call dbinit() when the DLL is initiated, but call dblogin once
for each thread. Then again, I guess the reason I did it this way was
to permit different threads to use the different login information. If
all threads will use the same login details, I can't see anything else
than that it would be sufficient to call dblogin() once, since LOGINREC
appears to only hold static data.

That's very interesting and helpful. Thanks for the information.

Bruce.