Results 1 to 10 of 10
  1. #1
    Join Date
    May 2002
    Posts
    55

    Unanswered: 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 Ec0x4AEAF530) Value:0x42c0df00 Cost0/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 Ec0x4951D530) Value:0x42c03da0 Cost0/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 Ec0x4951D530) Value:0x42c03da0 Cost0/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.

    Hanson

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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?
    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!

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    May 2002
    Posts
    55
    Quote Originally Posted by tomh53
    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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by guyang2000
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Mar 2004
    Posts
    12

    DisAllowRowLock

    Why would using this ever be advantageous? I can see where DisAllowPageLock might be helpful, but not this one.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    hmmm...load more data so this won't happen...

    You're hired!

    Make sure you keep the deep fryers clean when you clock out
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2006
    Posts
    27
    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.

  9. #9
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by John Grubb
    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?

    -- This is all just a Figment of my Imagination --

  10. #10
    Join Date
    Jan 2006
    Posts
    27
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •