NAV 2013: SERIALIZABLE or READ UNCOMMITTED?

azerty74

In Dynamics NAV 2013, RTC, when I run the following code:
MESSAGE('%1',CURRENTTRANSACTIONTYPE);
it always returns: UpdateNoLocks.

So I suppose the READ UNCOMMITTED is no longer the default isolation level and has become SERIALIZABLE?

Is that correct? Is there any information/documentation on this?

azerty74

hmmm, I better double checked before creating this post, when I do the same test in NAV 2009 it also returns UpdateNoLocks :oops:

So, can I presume it will begin with a READ UNCOMMITTED until the first INSERT/MODIFY/LOCKTABLE statement and then the SERIALIZABLE starts untill the end of the transaction?

So can I presume there are no changes in concurrency behaviour between NAV 2009 and NAV 2013 except the fact that no cursors are used anymore ofcourse?

holger1076

Very interesting question. I have the same and a futher more regarding that topic. What is the Standard in NAV2013 or NAV2009? I think "READ UNCOMMITED"? But, if that is so, who can explain that post that decribes a way to change the Isolation to "Repeatable Read":

http://blogs.msdn.com/b/nav/archive/2011/05/12/microsoft-dynamics-nav-changes-by-version.aspx

What sense makes that?

As far as ein understand it is only switches to "SERIALIZABLE" during a LOCKTABLE or FINDSET(TRUE).

Belias

Just a side note: it's not "locktable" that sets the isolation level, but it's the "find" instruction AFTER the locktable instruction. (or FINDSET(TRUE) of course, because it "includes" both statements).

stryk

"UpdateNoLocks" is indeed the default Transaction-Type and just defines when NAV switches from READUNCOMMITTED (Dirty Reads) to SERIALIZABLE (Clean Reads but Range Locks).

Thus in NAV we end up in either reading dirty data or facing blocking issues ... ](*,)

Especially the Range Locks in SERIALIZED transaction are the major issue, and this was changed in NAV 5.0 SP1 Build 30482 (or higher) or NAV 2009 SP1 Build 30609 (or higher) - see: https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb$en-us$979135&wa=wsignin1.0

If you apply this "diagnostics" switch ...

update [$ndo$dbproperty] set diagnostics = diagnostics + 4194304
go

... then instead of SERIALIZABLE now REPEATABLE READ isolation is used. This still grants clean reads, but does not establish any Range Locks - and this will dramatically reduce blocking issues!

In NAV 2013 this REPEATBALE READ isolation is used out of the box.