Serializable vs. Repeatable Read

Tobe
Tobe Member Posts: 4
edited 2011-08-04 in SQL Performance
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

Comments

  • bbrown
    bbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • Tobe
    Tobe Member Posts: 4
    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
    bbrown Member Posts: 3,268
    You may find this post fo some interest:

    http://www.mibuso.com/forum/viewtopic.p ... it=4194304
    There are no bugs - only undocumented features.
  • johanna
    johanna Member Posts: 369
    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..
    Best regards,

    Johanna
  • Tobe
    Tobe Member Posts: 4
    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

Categories