Sunday, December 11, 2016

Understanding COMMIT and Transaction Scope within Microsoft Dynamics NAV

Have you tried and thought about what is the transaction scope. In this blog post I am going to discuss little bit about transaction scopes and how the transaction scope works within Microsoft Dynamics NAV. 

In order to check the simple scope I am planning to use below code. Pretty simple, its just inserting a record to Customer Table (T18) and trying to run a report using RUNMODAL


Even before we execute the code most of you knew what will happen if we try to run the above code. You will get most probably the largest or second largest error message in Dynamics NAV. 


What will happen if we add a COMMIT


It runs without any issue and open up the Report Request page as define in the code. What this means is that once the COMMIT is called it will end a write transaction scope.

Following is the definition for COMMIT in MSDN :
When a C/AL codeunit begins, it automatically enables write transactions to be performed. When a C/AL code module completes, it automatically ends the write transaction by committing the updates made by the C/AL code. This means that if you want the C/AL codeunit to perform a single write transaction, it is automatically handled for you. However, if you want the C/AL codeunit to perform multiple write transactions, you must use the COMMIT function to end one write transaction before you can start the next. The COMMIT function separates write transactions in a C/AL code module. (Link)
In simple COMMIT statement ends active Write Transaction. Refer below image to understand how the transaction scope is defined. 


Lets do a small change, 

When we run the code system throw out the Error message. Even though the error happen after the insert statement system rollback the transaction and move to the last system state. This is a one reason you never have to handle the transaction scope manually by you, NAV does it for you.  (Beautiful!)

Lets do another change to our code, I removed the error code and added new code segment. 


What will happen if we run this code? Wll get the same error what we received when we run the first segment of the code. However there is one small interesting thing in this code, to check that will open the RTC. 

Dynamics NAV has inserted the first customer record to the table and never rolled back even though the error has occurred. Reason behind that is can be understand easily by referring to below image :

Once we do a COMMIT manually, Dynamics NAV register those transactions in Database and never go back and undo the changes even though error occurs afterward. 

Therefore during the C/AL coding it is highly important to use COMMIT statement very wisely. If you use the COMMIT code without identify possible issues and identify them and handle them properly, your Database will end up with uncompleted transactions and then you might have to go and manually correct those records. 

Within Dynamics NAV Default Code: 
In some of the Dynamics NAV Default codeunits, COMMIT is been used inappropriate ways (my view), easiest example is in the Sales-Post codeunit. There are sevaral COMMIT statments within the codeunit which complex the process. 

As an example, system COMMIT after the Posting No. is assigned to a document and IF the document gets an error afterwards No. Series never get rolledback. If a user tried to delete the document then it will have a number gap in the posting documents. 

Reason behind COMMIT in the sales-post is to release the tables that was grabbed so that other posting routines can use those tables. There are ways around it and will discuss about those on futuer blog posts. 

Will the Temporary record works same with COMMIT? Await for next blog post.. 

Please provide your feedback with a comment. 
Thank you and Regards,
Tharanga Chandrasekara

8 comments:

  1. Hello guys! Thank you for providing this information! I like your blog and think it's very useful for me:) visit our website Here you can also find a lot interesting information about MS Dynamics

    ReplyDelete
  2. Howdy! I could have sworn I've been to this web site before but
    after looking at many of the posts I realized it's new to me.
    Nonetheless, I'm definitely pleased I came across it and
    I'll be book-marking it and checking back regularly!

    ReplyDelete
  3. Pretty! This has been an extremely wonderful post. Thank you for supplying this information.

    ReplyDelete