Microsoft Dynamics NAV has had multiple changes to the underlying code in order to optimize performance of NAV on SQL Server. The chart below shows the changes by version.
In order to use the REPEATABLEREAD, changes are needed after installing the latest hotfix to enable the option. The following information outlines this and should be used ONLY after testing and discussion with your SQL DBA. REPEATABLEREAD support was adding on the following hotfix version and later for NAV, it would be recommended to use the latest hotfix builds, the information below is to give a reference of when the support was added, to better understand if you are currently on a version that supports this change:
5.0 SP1 – (Build 30482 / KB 979135)
6.0 SP1 – (Build 30609 / KB 978100)
update [$ndo$dbproperty] set diagnostics = diagnostics | 4194304
for more information about Microsoft SQL Server transaction isolation levels, visit the topic 'Isolation Levels in the Database Engine' on Microsoft MSDN.
Enabling the REPEATABLE READ isolation level in NAV will improve general performance in situations where multiple users are experiencing blocking when they are entering journal entries, sales order entries, purchase order entries and similar tasks.The difference between the SERIALIZABLE transaction isolation level and the REPEATABLE READ transaction isolation level is that SERIALIZABLE transaction isolation level protects against phantom reads. Therefore, enabling REPEATABLE READ transaction isolation level introduces a theoretical risk of phantom reads. The following example shows what this means from a C/AL perspective. Note: Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
MYTABLE.LOCKTABLE; MYTABLE.SETCURRENTKEY("Document No.", "Line No."); MYTABLE.SETRANGE(MYTABLE."Document No.", '1'); MYTABLE.SETRANGE(MYTABLE."Line No.", 1, 10); IF MYTABLE.FIND('-') THEN REPEAT UNTIL (MYTABLE.NEXT()=0); IF MYTABLE.FIND('-') THEN REPEAT UNTIL (MYTABLE.NEXT()=0); COMMIT;
With SERIALIZABLE transaction isolation level, all existing records will be locked during the first "REPEAT UNTIL" loop and other users will also be blocked from inserting new records within the specified range. The record that has Document No=2, Line No=1 will also be blocked. Therefore, the second loop will always read exactly the same result as the first loop. With REPEATABLE READ transaction isolation level, someone can theoretically insert a new record within the mentioned range which will then appear as an additional record in the second loop.To disable the 4194304 flag, run the following TSQL statement:
update [$ndo$dbproperty] set diagnostics = diagnostics ^ 4194304
Microsoft recommends thorough testing before making any changes to a live environment to ensure that there are no unexpected results.
Fast Forward-only (FFO) for Browse cursor with OPTION (FAST x)
Microsoft Dynamics NAV replaced the Cursor type from DYNAMIC to Fast Forward-only (FFO) for Browse when in a Browse transaction in hotfix releases. A Dynamic cursor is still used when inside a write transaction. The OPTION (FAST x) support was added after the change to FFO for Browse to improve performance. It would be recommended to be on the latest hotfix builds to include the OPTION (FAST x) support. No additional setup is needed once the hotfix has been implemented. Again, the following information is for a reference to know if you are on a build that has the OPTION (FAST x) support, it would be recommended if you are not that you obtain the most current hotfix release for the version you are on:
5.0 SP1 – (Build 29729 / KB 974798)
6.0 – (Build 29894 / KB 974798)
6.0 SP1 – (Build 29958 / KB 974798)
North America Senior Escalation Engineer
We currently have our NAV database using the SERIALIZABLE transaction isolation level, we have versions 5.0 SP1 and NAV 2009 R2 for the client, I have seen info. that chaning to the REPEATABLE READ transaction isolation level can help with blocking issues, however, I have only seen info. regarding NAV 2009 SP1 and it's corresponding hotfixes.
Do we need to apply a hotfix to the R2 client as well before we can change the isolation level?
Since SP1 platform builds rolled into R2 for NAV 2009, as long as you are on a build higher then 30609, you are on a build that supports the REPEATABLEREAD option, which are ALL builds for NAV 2009 R2. So since you are on NAV 2009 R2, you can go ahead and implement the above steps to have NAV start using the REPEATABLEREAD isolation level.
Thanks Nick, I assumed that much but wanted to confirm.