Understanding COMMIT and Transaction Scope within Microsoft Dynamics NAV
Article is about transaction scopes and how the transaction scope works within Microsoft Dynamics NAV.
Understanding Transaction Scope
In order to explain the transaction scope within Microsoft Dynamics NAV, below code sample is used with various minor modifications to explain the concept. Code sample is very much simple, it will be inserting a record to Customer Table (T18) and will try to run a report using RUNMODAL.
https://3.bp.blogspot.com/-QEGaCH4StZ8/WE0eLyex0zI/AAAAAAAADoY/F5DrahkrSt8iVQsfXrfwrHQbvWONv6K0wCLcB/s640/code_insert.PNG
Once run the below code Dynamics NAV will return the below error message:
https://4.bp.blogspot.com/-RH09yqJchoU/WE0eM9qcHnI/AAAAAAAADoc/25x8mrCrU10rkfblJV-IA5zH2T98WCaaQCLcB/s400/Unable%2Bto%2BInsert.png
Understanding "Commit"
What will be the result if COMMIT is added to the code after record is been inserted to Table (Refer below image to understand the code modification)
https://2.bp.blogspot.com/-UFoWRQ7qYds/WE0gH_-o9lI/AAAAAAAADos/vrrzOUGWoDUyLyicag0xcf8peO9XC0wVQCLcB/s640/With%2BCommit.png
Code will run 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.
Transaction Scope
Refer below image to understand the transaction scope. As marked in the image, when executing the sample code system will start the transaction scope and then close the active transaction scope at the end of the COMMIT statement.
Even If the COMMIT statement is not written in the code, Dynamics NAV understand the end line of the code and close the Transaction Scope. There is no need of manually closing the Transaction Scope within Dynamics NAV, unless your design of the code required it. As example, in this sample code if the RUNMODAL is not been used, then Dynamics NAV would able to run the code without COMMIT statement.
https://3.bp.blogspot.com/-0Ry6RyzcM28/WE0kgy45CBI/AAAAAAAADo4/ZCiFwWUWJ-kPIOexpOJAeMhE0IJ5fFwogCLcB/s640/Scope.png
Transaction Scope with Errors
What would be the effect, if an error occurs within the transaction scope. Sample code is modified as below to throw an error at the run time of the code.
https://3.bp.blogspot.com/-xVjDsV-JR3M/WE0nYHIB39I/AAAAAAAADpU/9xOsz662S1kh1BUEraERVPI717QsNRO9wCLcB/s640/Error.png
When executing the above sample code, system throw out an error message as expected. Even though the error happen after the insert statement of the code, Dynamics NAV 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.
Two Transaction Scopes Together
How would Microsoft Dynamics NAV will handle two (02) transaction scopes together. To understand the execution plan, lets do another change to sample code by removing the error line and adding a new code segment as below.
https://4.bp.blogspot.com/-z2DJZanSA0M/WE0lJSX9hrI/AAAAAAAADo8/QYuiCkqKGgwSmo4hdfscschivh8olbgVQCLcB/s640/Code%2BScope%2B02.png
Once the code is get executed, system will throw the same error message which occurred while executing the first segment of the code. However there is one small interesting thing in this code. To check that it is required to open "Role Tailored Client" (RTC).
Once the RTC is opened, navigate to Customer Card (Customer Master) and check for a new customer record. You will notice a new customer call "Test Customer".
https://1.bp.blogspot.com/-Ro9x9x4s5D0/WE0mBeZZDSI/AAAAAAAADpM/E3Y9OrZfHNAjYIvCHDp0U5WIUIc9hgmxACLcB/s400/FirstCustomer.png
This means Dynamics NAV has insert the first customer record to the table and never rolled back even though the error has occurred. Reason behind it is can be understand easily by referring to below image:
https://3.bp.blogspot.com/-QK3KXCBQgo8/WE0o__FazdI/AAAAAAAADpg/at5pB5I9KVU_U9CEXfXbFXQqRZnLHrEtgCLcB/s640/two%2Bscopes.png
Once the system execute COMMIT statement in the code, 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 COMMIT statement is used without identify possible issues and handle them properly, your Database will filled with uncompleted transactions and then you might have to go and manually correct those records.