Navision SQL & Deadlocks

megawavez

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

fl

Hi Chris,

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

Francois

megawavez

megawavez@yahoo.com

many thanks!

Chris

Marije_Brummel

See also

http://www.mibuso.com/forum/viewtopic.p ... highlight=

bbrown

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.

DeSp

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?

kriki

Why not put all those documents in the download-section?

Marije_Brummel

kriki wrote:

Why not put all those documents in the download-section?

Agree.

Maybe a new category "Tuning Navision/SQL" ??

kriki

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",...

Marije_Brummel

OK, fine with me.


Lets leave it to Luc. He's the webmaster.

megawavez

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

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

DeSp

megawavez, I can send you the "Performance troubleshooting guide.pdf" and "SQL Server resource kit.pdf", just give me your email.