Deadlock on TAB level

I have a small database and a smalll table ( Table ID=565577053,with two
indexes on this table). when more than one user connected, I got the deadlock
on the index KEY and PAGE lock. I setup index with "DisallowRowLock" and
"DisallowPageLock" , seems kill the index KEY and PAGE lock problem, but I
get to this TAB lock situation instead as following:
2006-01-18 09:51:37.87 spid4 --
2006-01-18 09:51:37.87 spid4 Starting deadlock search 15
Deadlock encountered ... Printing deadlock information
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Wait-for graph
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Node:1
2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
Mode: S Flags: 0x0
2006-01-18 09:51:37.87 spid4 Grant List 0::
2006-01-18 09:51:37.87 spid4 Owner:0x42c03ba0 Mode: S Flg:0x0
Ref:2 Life:02000000 SPID:77 ECID:0
2006-01-18 09:51:37.87 spid4 SPID: 77 ECID: 0 Statement Type: DELETE
Line #: 1
2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
2006-01-18 09:51:37.87 spid4 Requested By:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:64 ECID:0 Ec:(0x4AEAF530) Value:0x42c0df00 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Node:2
2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
Mode: S Flags: 0x0
2006-01-18 09:51:37.87 spid4 Grant List 0::
2006-01-18 09:51:37.87 spid4 Owner:0x42c03e00 Mode: S Flg:0x0
Ref:2 Life:02000000 SPID:64 ECID:0
2006-01-18 09:51:37.87 spid4 SPID: 64 ECID: 0 Statement Type: DELETE
Line #: 1
2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
2006-01-18 09:51:37.87 spid4 Requested By:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4 Victim Resource Owner:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 End deadlock search 15 ... a deadlock was
found.
2006-01-18 09:51:37.87 spid4 --
How can I get ride of this deadlock without changing the application
code(without using set the isolation level or NOLOCK hint). When I load more
data, will this problem goes away?
Anybody can help to solve this TAB deadlock will be appreciate.
HansenAnybody any suggestion please?
"HG" wrote:
> I have a small database and a smalll table ( Table ID=565577053,with two
> indexes on this table). when more than one user connected, I got the deadlock
> on the index KEY and PAGE lock. I setup index with "DisallowRowLock" and
> "DisallowPageLock" , seems kill the index KEY and PAGE lock problem, but I
> get to this TAB lock situation instead as following:
>
> 2006-01-18 09:51:37.87 spid4 --
> 2006-01-18 09:51:37.87 spid4 Starting deadlock search 15
> Deadlock encountered ... Printing deadlock information
> 2006-01-18 09:51:37.87 spid4
> 2006-01-18 09:51:37.87 spid4 Wait-for graph
> 2006-01-18 09:51:37.87 spid4
> 2006-01-18 09:51:37.87 spid4 Node:1
> 2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
> Mode: S Flags: 0x0
> 2006-01-18 09:51:37.87 spid4 Grant List 0::
> 2006-01-18 09:51:37.87 spid4 Owner:0x42c03ba0 Mode: S Flg:0x0
> Ref:2 Life:02000000 SPID:77 ECID:0
> 2006-01-18 09:51:37.87 spid4 SPID: 77 ECID: 0 Statement Type: DELETE
> Line #: 1
> 2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
> 2006-01-18 09:51:37.87 spid4 Requested By:
> 2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:64 ECID:0 Ec:(0x4AEAF530) Value:0x42c0df00 Cost:(0/D4)
> 2006-01-18 09:51:37.87 spid4
> 2006-01-18 09:51:37.87 spid4 Node:2
> 2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
> Mode: S Flags: 0x0
> 2006-01-18 09:51:37.87 spid4 Grant List 0::
> 2006-01-18 09:51:37.87 spid4 Owner:0x42c03e00 Mode: S Flg:0x0
> Ref:2 Life:02000000 SPID:64 ECID:0
> 2006-01-18 09:51:37.87 spid4 SPID: 64 ECID: 0 Statement Type: DELETE
> Line #: 1
> 2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
> 2006-01-18 09:51:37.87 spid4 Requested By:
> 2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
> 2006-01-18 09:51:37.87 spid4 Victim Resource Owner:
> 2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
> 2006-01-18 09:51:37.87 spid4
> 2006-01-18 09:51:37.87 spid4 End deadlock search 15 ... a deadlock was
> found.
> 2006-01-18 09:51:37.87 spid4 --
> How can I get ride of this deadlock without changing the application
> code(without using set the isolation level or NOLOCK hint). When I load more
> data, will this problem goes away?
> Anybody can help to solve this TAB deadlock will be appreciate.
> Hansen
>|||Have you considered just disallowing page locks but allowing row locks? With
rowlocks disallowed, for your application to do an insert/update/delete it
seems that it would be left with no choice but to take out a table lock.
Question for others: when is a "good" time to disallow row locks? I can't
think of one.
"HG" wrote:
> Anybody any suggestion please?
> "HG" wrote:
> > I have a small database and a smalll table ( Table ID=565577053,with two
> > indexes on this table). when more than one user connected, I got the deadlock
> > on the index KEY and PAGE lock. I setup index with "DisallowRowLock" and
> > "DisallowPageLock" , seems kill the index KEY and PAGE lock problem, but I
> > get to this TAB lock situation instead as following:
> >
> >
> > 2006-01-18 09:51:37.87 spid4 --
> > 2006-01-18 09:51:37.87 spid4 Starting deadlock search 15
> >
> > Deadlock encountered ... Printing deadlock information
> > 2006-01-18 09:51:37.87 spid4
> > 2006-01-18 09:51:37.87 spid4 Wait-for graph
> > 2006-01-18 09:51:37.87 spid4
> > 2006-01-18 09:51:37.87 spid4 Node:1
> > 2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
> > Mode: S Flags: 0x0
> > 2006-01-18 09:51:37.87 spid4 Grant List 0::
> > 2006-01-18 09:51:37.87 spid4 Owner:0x42c03ba0 Mode: S Flg:0x0
> > Ref:2 Life:02000000 SPID:77 ECID:0
> > 2006-01-18 09:51:37.87 spid4 SPID: 77 ECID: 0 Statement Type: DELETE
> > Line #: 1
> > 2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
> > 2006-01-18 09:51:37.87 spid4 Requested By:
> > 2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
> > SPID:64 ECID:0 Ec:(0x4AEAF530) Value:0x42c0df00 Cost:(0/D4)
> > 2006-01-18 09:51:37.87 spid4
> > 2006-01-18 09:51:37.87 spid4 Node:2
> > 2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
> > Mode: S Flags: 0x0
> > 2006-01-18 09:51:37.87 spid4 Grant List 0::
> > 2006-01-18 09:51:37.87 spid4 Owner:0x42c03e00 Mode: S Flg:0x0
> > Ref:2 Life:02000000 SPID:64 ECID:0
> > 2006-01-18 09:51:37.87 spid4 SPID: 64 ECID: 0 Statement Type: DELETE
> > Line #: 1
> > 2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
> > 2006-01-18 09:51:37.87 spid4 Requested By:
> > 2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
> > SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
> > 2006-01-18 09:51:37.87 spid4 Victim Resource Owner:
> > 2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
> > SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
> > 2006-01-18 09:51:37.87 spid4
> > 2006-01-18 09:51:37.87 spid4 End deadlock search 15 ... a deadlock was
> > found.
> > 2006-01-18 09:51:37.87 spid4 --
> >
> > How can I get ride of this deadlock without changing the application
> > code(without using set the isolation level or NOLOCK hint). When I load more
> > data, will this problem goes away?
> > Anybody can help to solve this TAB deadlock will be appreciate.
> >
> > Hansen
> >