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

dbo option only

Hi,
Can you change the mode to dbo option only if there are other users connected? Will they automatically get booted out?
MeeraHowdy

If you try & change to db_use only, an error will come up saying people are using the database & the task will fail.

Cheers

SG

dbnull

Is there an easy way in Reporting service to handle dbull in dataset?

I am using the local mode of the new reportviewer control, when I feed the dataset to the report, if there are some dbull column, an exception will be thrown.

To those string column, I can set NULLVALUE property to empty, but to other datatype, like integer, datetime, money, what should I do?

ThanksDBNull is translated to a unitialized object in the report. You can use IsNothing, i.e. IsNothing(Fields!Foo.Value).|||Hey, you know what, it is very strange.

If I use BindingDatasource.datasource = ds.tables(0), (BindDatasource is bound to ReportViewer), I will have exception if any field is null.

But if I use
BindingDatasource.datasource = ds
BindingDatasource.datamember = "Table1"

I don't get exception any more.

Don't why, but it works like charm.

Wei|||

If this solves your problem, please mark the question answered. Thanks. :)

|||In addition to =IsNothing(Fields!Foo.Value), you could use the IS keyword: (Fields!Foo.Value is Nothing)

-- Robert