Navision SQL & Deadlocks

megawavez
megawavez Member Posts: 133
edited 2005-09-30 in NAV/Navision Classic Client
Has anybody had any experiences working with Navision and fixing deadlocks problems? I have a routine which more than one people can run at the same time - the routine basically deletes a number of records from about 5 tables. Unfortunately, it deadlocks and from what I've determined, it's deadlocking on the keys. It anybody has some pointers, it would be greatly appreciate.

Chris

Comments

  • fl
    fl Member Posts: 184
    Hi Chris,

    Just pass me your mail address. I will send you then a doc with recomandations on this point.

    Francois
    Francois
    Consultant-Developper

    http://www.CreaChain.com
  • megawavez
    megawavez Member Posts: 133
    megawavez@yahoo.com

    many thanks!

    Chris
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    See also

    http://www.mibuso.com/forum/viewtopic.p ... highlight=
    Marije Brummel | Freelance Business Central Specialist
  • bbrown
    bbrown Member Posts: 3,268
    To help track down deadlock issues, turn on traceflag 1024 in SQL. This will place detailed information about the deadloacks in the SQL error log. This information may help you isolate the issue.
    There are no bugs - only undocumented features.
  • DeSp
    DeSp Member Posts: 105
    Chris, what is the title of this document?
    I have here some docs: "Performance troubleshooting guide.pdf", "SQL Server resource kit.pdf" and "Tuning Navision for better performance" (Thanks to Mark Brummel). All of them contain some useful info about solving the deadlock problem. Do you have something else?
    Nil desperandum
  • kriki
    kriki Member, Moderator Posts: 9,121
    Why not put all those documents in the download-section?
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    kriki wrote:
    Why not put all those documents in the download-section?


    Agree. :D

    Maybe a new category "Tuning Navision/SQL" ??
    Marije Brummel | Freelance Business Central Specialist
  • kriki
    kriki Member, Moderator Posts: 9,121
    Mark Brummel wrote:
    Maybe a new category "Tuning Navision/SQL" ??
    Best keep it in the download-section, because otherwise we also need to create "Upgrading Navision", "How to use automation/OCX",...
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    OK, fine with me. :D


    Lets leave it to Luc. He's the webmaster.
    Marije Brummel | Freelance Business Central Specialist
  • megawavez
    megawavez Member Posts: 133
    Here's an example of 1024 tracing:

    ===============================================================================
    Node:1
    KEY: 7:1060198827:1 (fa0049244034) CleanCnt:2 Mode: Range-X-X Flags: 0x0
    Grant List 0::
    Owner:0x9540ad00 Mode: Range-X-X Flg:0x0 Ref:1 Life:02000000 SPID:54 ECID:0
    SPID: 54 ECID: 0 Statement Type: DELETE Line #: 1
    Input Buf: RPC Event: sp_execute;1
    Requested By:
    ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:52 ECID:0 Ec [Sad] 0x5703F560) Value:0xb1240e60 Cost [Sad] 0/16B7D80)

    Node:2
    KEY: 7:160719625:8 (0604204a640e) CleanCnt:2 Mode: Range-S-U Flags: 0x0
    Grant List 0::
    Owner:0x35e3a5c0 Mode: Range-S-U Flg:0x0 Ref:311 Life:02000000 SPID:52 ECID:0
    SPID: 52 ECID: 0 Statement Type: SELECT Line #: 1
    Input Buf: RPC Event: sp_execute;1
    Requested By:
    ResType:LockOwner Stype:'OR' Mode: X SPID:54 ECID:0 Ec [Sad] 0x36CF1560) Value:0x8003f960 Cost [Sad] 0/2C8FC)
    Victim Resource Owner:
    ResType:LockOwner Stype:'OR' Mode: X SPID:54 ECID:0 Ec [Sad] 0x36CF1560) Value:0x8003f960 Cost [Sad] 0/2C8FC)
    ==============================================

    It looks to me like it getting locked out when trying to maintain table keys, but I'm not sure how to debug and fix it. The two clients are running the same routine to delete records.

    If anybody could post the SQL & Deadlocking document, that would be appreciated.
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    megawavez wrote:
    If anybody could post the SQL & Deadlocking document, that would be appreciated.

    I've sent you "Tuning Navision for better performance.pdf" which I got from Francois :D
    Marije Brummel | Freelance Business Central Specialist
  • DeSp
    DeSp Member Posts: 105
    megawavez, I can send you the "Performance troubleshooting guide.pdf" and "SQL Server resource kit.pdf", just give me your email.
    Nil desperandum

Categories