SOLUTION: SQL Server deadlocks not automatically resolved

FDickschat

Dear all,

this is an explanation and solution to the problem of unresolved deadlocks with SQL server. If you would like to see a previous discussion about this problem follow this link: http://www.mibuso.com/forum/viewtopic.php?t=8775

Problem
Clients: NAV 4.0 SP3 on XP/32
Server: SQL 2005 Enterprise /64 on W2003/64.
Other combinations (e.g. NAV 3.70/SQL 2000) show the same problem

A client starts a transaction (in our case posting of a sales/service order, CU80) while some other users do the same. One of the clients is no longer responding, while having locks set. When you look in the session list you can see that this session is the headlock of a list of locked sessions. The list just grows longer and longer. The headlock session will never recover. The only solution is to kill the session.

When looking at this on SQL server side in the server log you can see that the server encountered a deadlock (if you have switched on the appropriate traceflags). When running dbcc showoutputbuffer on the headlock session you will already see the message "Transaction (Process ID. xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." but this message never reaches the client. The server still has all locks set for this session and it looks like an unresolved deadlock.

Error tracking
We were never able to reproduce this problem by will. It just happened sometimes. It very much depends on the number of simultaneously posted orders whether this problem occurs or not. I programmed some deadlock generating codeunits with crosslocks but these were always resolved.

We were in close contact to MS trying to solve the problem and all focus was set on finding the unresolved deadlock in the SQL server but this was the wrong place to look at.

After reading above linked forum message I reprogrammed the 2 mentioned codeunits and was immediately able to reproduce the phenomenon. The forum message somewhere also states that this is a problem of MDAC and neither of NAV client nor SQL server. For Server 2003 there is an MDAC fix available (2.8 SP2), but XP is still on MDAC 2.8 SP1. I checked MDAC versions on our systems:
XP: 2.81.1117.0
Server 2003: 2.82.3959.0

I tested my 2 CUs in 2 clients running on Server 2003 and there these deadlocks were automatically resolved. So it is indeed a problem of MDAC.

Solution
MS confirms this to be a problem of MDAC and the MDAC people at MS are working on it. Since MDAC today is part of the OS the modification has to be synchronized with the windows teams. At the moment the fix is scheduled for end of July.

I will keep you informed.

Best regards, Frank

bobby1970

Dear All,

In the last reply posted, there was mention about hotfix release towards the end of July. Is there any update on the hotfix release.

Thanks

Bobby

colin_leversuch-roberts

I asked my microsoft contact and got the rather vague response " with com+ you should always have the most up to date mdac"

FDickschat

Dear all,

Navision has released an MDAC Update for XP to solve this problem. The Update is not publicly available.
You can access the update via this kb-article: http://support.microsoft.com/kb/890470/en-us

You can not download the update. Instead you have to contact the Microsoft Product Support Services to obtain the hotfix.

Best regards,
Frank

thaug

You can now request hotfixes through an online form. They will send you the link and password to download within a couple of hours. Really quite handy.

https://support.microsoft.com/contactus ... &WS=hotfix