Serializable vs. Repeatable Read

Tobe

Hi,

has someone changed the default locking level from serializable to repeatable read?
What are your experiences with repeatable read?
Does it really reduce blocking?
Did you have problems with phantom reads?
Do you use it as a default for your customers (if you are working for a nsc)?

How can I check in a test enviroment, if it causes any problems because of phantom reads?
What are the standard locking levels in other ERPs (AX, GP, SAP, Oracle ...)?

So many questions ...

regards
tobe

bbrown

The default transaction isolation level used by NAV is Read Uncommitted. Serializable is only used during update transactions. SQL default behavior is Read Committed and tends to be more common but allows less concurrency. The downside of Read Uncommitted is dirty reads in exchange for better concurrency.

Tobe

Yes, but you can change the default for update transactions from serializable to repeatable read.
It's described in this KB article (I should have mentoined it in my first post):

Blocking problems caused by Microsoft Dynamics NAV 5.0 SP1 using Microsoft SQL Server SERIALIZABLE isolation level
https://mbs2.microsoft.com/knowledgebase/KbDisplay.aspx?scid=kb;en-us;979135

bbrown

You may find this post fo some interest:

http://www.mibuso.com/forum/viewtopic.p ... it=4194304

johanna

Tobe wrote:

Yes, but you can change the default for update transactions from serializable to repeatable read.
It's described in this KB article (I should have mentoined it in my first post):

Blocking problems caused by Microsoft Dynamics NAV 5.0 SP1 using Microsoft SQL Server SERIALIZABLE isolation level
https://mbs2.microsoft.com/knowledgebase/KbDisplay.aspx?scid=kb;en-us;979135

Hi Tobe,

Is there any hotfix for blocking problems in Microsoft Dynamics NAV 4.0 SP3 like the article above? Thanks..

Tobe

johanna wrote:

Tobe wrote:

Yes, but you can change the default for update transactions from serializable to repeatable read.
It's described in this KB article (I should have mentoined it in my first post):

Blocking problems caused by Microsoft Dynamics NAV 5.0 SP1 using Microsoft SQL Server SERIALIZABLE isolation level
https://mbs2.microsoft.com/knowledgebase/KbDisplay.aspx?scid=kb;en-us;979135

Hi Tobe,

Is there any hotfix for blocking problems in Microsoft Dynamics NAV 4.0 SP3 like the article above? Thanks..

I'm not aware of one.
Anyway, the hotfix is not for general blocking problems, but only for those special cases, where i.e. blocking all sales lines for one sales order, also blocks last line of previous sales order and first line of next order.

In most situations blocking problems occur from other reasons.

Tobe