Deadlock on TAB lock

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 got 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 rid 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?
Any kind of help will be appreciate.

HansonHow can I get rid 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?


The short answers ... You can't and no

More information:
It is the application causing the deadlock, but you only exacerbated the problem by disallowing the row and page locking mechanism. SQL Server will try to take the lowest level lock it needs to accomplish the task. If I have a table with 1 million rows and I need to update 1 row, SQL Server will lock the row (in most cases). If I disallow row locks, it will have to lock the page ... so if there are 100 rows on an 8K page, 100 rows are locked instead of 1. If I then disallow page locks, the next level is a TABLE lock (TAB). So you have really screwed yourself by doing that.

Now to the deadlock ... spid XX locks row 12345 in Table A and needs to lock row 23456 in Table B. spif YY has locked row 23456 in Table B and needs to lock row 12345 in Table A. Each spid is competing for the exact same resource. SQL Server resolves the problem by killing and rolling back one of the spids.

Now your problem could be a spid locking a resource longer than needed, or allowing a user to hold a lock while going off to lunch, or maybe the app accesses the tables in two different sequences ... whatever the problem, it's the app and not the database.

Long term solution ... allow sqlserver to determine the proper locking mechanism and fix the app!|||The short answers ... You can't and no

More information:
It is the application causing the deadlock, but you only exacerbated the problem by disallowing the row and page locking mechanism. SQL Server will try to take the lowest level lock it needs to accomplish the task. If I have a table with 1 million rows and I need to update 1 row, SQL Server will lock the row (in most cases). If I disallow row locks, it will have to lock the page ... so if there are 100 rows on an 8K page, 100 rows are locked instead of 1. If I then disallow page locks, the next level is a TABLE lock (TAB). So you have really screwed yourself by doing that.

Now to the deadlock ... spid XX locks row 12345 in Table A and needs to lock row 23456 in Table B. spif YY has locked row 23456 in Table B and needs to lock row 12345 in Table A. Each spid is competing for the exact same resource. SQL Server resolves the problem by killing and rolling back one of the spids.

Now your problem could be a spid locking a resource longer than needed, or allowing a user to hold a lock while going off to lunch, or maybe the app accesses the tables in two different sequences ... whatever the problem, it's the app and not the database.

Long term solution ... allow sqlserver to determine the proper locking mechanism and fix the app!

tomh53:

Your information is very helpful.
I agree with you that the proper fix should be done on the application rather than database. it is a third party application so I don't have any source code, now the only thing I can do is escalate to them and force them to update the code.

Another approch I like to do is load more data in, make the deadlock less chance happen.

Thanks,

HANSON|||Smells like Access and you're returning all the rows to a form...which should be a shared lock.

We need more background on the application and what you're doing...which doesn't sound good...|||it is a third party application so I don't have any source code, now the only thing I can do is escalate to them and force them to update the code.

You have a 3rd party code that you bought, and it causes deadlocks? Make them fix the damn code.

Can you let us know who they are? They got a home page?|||Why would using this ever be advantageous? I can see where DisAllowPageLock might be helpful, but not this one.|||hmmm...load more data so this won't happen...

You're hired!

Make sure you keep the deep fryers clean when you clock out|||DisAllowRowLock would use fewer lock resources. Systems that have correctly designed applications hitting them and don't have deadlock issues can really benefit from this.|||DisAllowRowLock would use fewer lock resources. Systems that have correctly designed applications hitting them and don't have deadlock issues can really benefit from this.

Really? Would you post your sources for review?|||I don't have current sources, or hard number, but some experience back when it was debated about row-level locking entering SQL Server, and when to use it. Search for "lock escalation" in BOL. Each lock is a small amount of memory, and is something for the server to manage. Normally, the server handles lock escalation in a fairly intelligent manner, but the option is there if you need it. In almost all circumstances letting the server manage the overhead is acceptable. Looking at my post, I shouldn't have implied a big gain. Still the post is correct in that that:

1) DisAllowRowLock would use fewer lock resources.

But

2) Correctly designed applications must be used, or you will have deadlocking issues.

You can easily (as the initial poster did) cause more problems by fiddling with the lock level.

Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers