Whenever you are changing data it is important to make changes in a consistent and safe manner. D365 ttsBegin and ttsCommit statements in x++ are the key to doing this in Microsoft Dynamics 365 for Finance and Operations. They mark the beginning and end of a transaction block of code, containing changes to data that all need to either be written to the database. Or, reverted back to their original state. When the system runs into an error, these statements allow the system to revert the database back to an earlier state without specifically adding code to undo the changes. The best way to explain the need for this is with an example.
Imagine the system is creating a sales order based on information sent from a website. A complete sales order requires a SalesTable record, one or more SalesLine records, and likely many other records. If, for example, the system only created the SalesTable record, then had an error before it could create the remaining records, this would leave an incomplete sales order. It would be better to have no records, than leave an incomplete error and have it accidentally be processed.
Overview
D365 ttsBegin and ttsCommit statements in x++ help developers ensure that the system does not create incomplete data. In fact, this can occur for just one record, several records in the same table, or many records across many tables.
The D365 database uses a ‘normalized’ compared to a ‘de-normalized’ database structure. A normalized database means that the data is divided into multiple tables that relate to each other. This has the benefit of reducing data redundancy and inconsistently.
However, with this structure it is important that the system does not accidentally leave required records uncreated. The system needs to either successfully complete ALL of the records. Or, if an error occurs, it needs to ensure that no records that represent that thing are created.
More Functional Examples
To explain the importance of this, I wanted to provide several more functional examples. Then we will get into specific technical examples in code.
Creating a Customer
Imagine the system has received customer information, and it needs to create a customer within D365. The main table that stores customer information is the CustTable. However, there are quite a few other tables that are needed to support all the D365 functionality.
Specifically, a customer can have a first name, last name, and can be a person or organization. This information is stored in the DirPartyTable, DirPerson, DirPersonName, and DirOrganization tables.
If these related records were not created because an error occurred part way in creating them, the result would be corrupted and incorrect information.
Additionally, a customer can have optionally have a billing address, a shipping address, a phone number, and an email address. Creating an address requires records in the following tables: DirPartyLocation, LogitiscsLocation, LogitisticsPostalAddress or LogisticsElectronicAddress.
Ultimately, missing just one record will make it so the system’s functionality will not work.
Invoicing A Sales Order
It is important to realize that D365 ttsBegin and ttsCommit statements are needed in more processes than just creating new master data. They are needed in processes like posting journals and invoicing sales orders.
To explain, these processes create and update multiple records as part of the process. As part of invoicing a sales order, there are several journals and general ledger accounts that all get created to record the transaction. If only some of the changes were to take place you would again have incorrect and corrupt data.
The Basics
Most of the time, developers will use a D365 ttsBegin and ttsCommit when updating or deleting a single record.
- ttsBegin – marks the beginning of a transaction block in x++ code.
- ttsCommit – marks the end of the transaction block in x++ code.
If an error occurs after a ttsBegin, but before a ttsCommit, the system will revert all data changes back to the state they were when the ttsBegin was called.
To start, look at this simple example
CustTable custTable;
select firstonly forupdate custTable
where custTable.AccountNum == "1001";
if (custTable)
{
ttsbegin;
custTable.CustGroup = "80";
custTable.update();
ttscommit;
}Please read my article on How To Update Data In D365 for a more detailed explanation of this code.
First, notice, the location of the D365 ttsBegin and ttsCommit statements.
Next, understand that if the system were to throw an error within the call of the ‘update’ method, the system would automatically roll back the data to the state when the ttsbegin statement was called.
Specifically, the custTable record’s CustGroup field would not longer be set to “80”. It would revert back to whatever it was previously.
This is awesome! To clarify, it means that we do not have to write code to keep track of the previous values in the record. And, we don’t have to write code to revert the record back, when an error is thrown.
ForUpdate
While not the main topic of this article, please notice the ‘forUpdate‘ keyword in the select statement. The ‘forUpdate‘ keyword is required for both updating a record or deleting a record. Without this keyword, the change will not occur.
CustTable custTable;
select firstonly forupdate custTable
where custTable.AccountNum == "1001";Technically, instead of using ‘forUpdate’, you can write this code:
CustTable custTable;
select firstonly custTable
where custTable.AccountNum == "1001";
if (custTable)
{
ttsbegin;
custTable.selectForUpdate(true);
custTable.CustGroup = "80";
custTable.update();
ttscommit;
}
And there are sometimes you need to use this code. However, when you know you are updating a record, it is easier to read code that uses the ‘forUpdate‘ keyword as part of the ‘select‘ or ‘while select’ statement.
Where To Place The TTSBegin and TTSCommit
It is important to realize that the placement of the D365 ttsBegin and ttsCommit statements will affect what and how many records are reverted. Therefore, it is vital developers understand and think this through.
ttsBegin and ttsCommit Inside The Loop
Consider, the following example x++ code.
CustTable custTable;
//If an error is thrown,
//only that last custTable will be reverted.
while select forupdate custTable
where custTable.CustGroup == "30"
{
ttsbegin;
custTable.CustGroup = "80";
custTable.update();
ttscommit;
}To explain, the code loops through all customer records where the customer group is “30”.
Next, each record’s CustGroup field value is changed to “80”.
Finally, the record is updated.
Notice the ttsBegin and ttsCommit statements are INSIDE the “while select” loop. This is important because it means that each time through the loop, the record will get committed to the database.
To explain, imagine one thousand records are updated successfully. Then, when the system calls the update() method on the next record, an error is thrown.
What will happen? The system will revert the data to the state it was when the last ttsbegin statement was run.
This means that only that very last customer record will be reverted back. All of the other thousand customer records that were already updated will stay updated.
Note, this behavior is often preferred when there is no dependence between the changed records. If you have a batch job that has been running for hours, you do not necessarily want to revert all the work that it has done. Only the record that experienced an error.
ttsBegin and ttsCommit Outside The Loop
Now, let us look at what happens if we place the D365 ttsBegin and ttsCommit OUTSIDE the loop.
CustTable custTable;
//If an error is thrown,
//all customer records that were changed
//will be reverted.
ttsbegin;
while select forupdate custTable
where custTable.CustGroup == "30"
{
custTable.CustGroup = "80";
custTable.update();
}
ttscommit;Consider the same example. Where one thousand records have been updated successfully, but then the next one experiences an error when the update() method is called.
What will happen? In this case, the system will again revert back to the state of the database when the ttsbegin statement was run. But because this statement is before any of the calls to the update() method, this means that all one thousand and one records will be reverted back.
In this code example, this is probably NOT desirable.
However, in other examples, like the ones explained earlier, you many be creating or updating several related records. And, if an error occurs any place before it completes successfully, you would want the data to revert back to its original state.
This would allow you to fix the cause of the error, and try again from a known state. Rather than having to handle complex scenarios of partially updated or inserted data.
When To Use
Before moving on, I wanted to clarify when to use the D365 ttsBegin and ttsCommit statements.
Technically, when making changes to a single record, you only are required to use ttsBegin and ttsCommit statements when you are calling update(), doUpdate(), delete(), or doDelete().
If you do not have a ttsBegin and ttsCommit statement before and after these method calls, then the data will NOT be changed in the database. And you will be very confused. 🙂 So remember to use them!
There are several other methods and keywords used in x++ to change data. But for these, you do not strictly have to use ttsBegin and ttsCommit. Specifically, you do not have to use it when calling insert(), doinsert(), update_recordset, insert_recordset, delete_from, insertDatabase(). The record will still be changed.
However, you may still choose to use ttsBegin and ttsCommit around a block of code, regardless of what data method you are using to utilize the revert functionality of these statements when an error is thrown. Therefore, I definitely recommend you think carefully and use them when they can be helpful.
Nested Transaction Blocks
Now, what happens if we have nested D365 ttsbegin and ttsCommit statements?
Consider this more complex example of multiple ttsBegin and ttsCommit statements.
CustTable custTable;
select firstonly forupdate custTable
where custTable.AccountNum == "1001";
ttsbegin;
if (custTable)
{
ttsbegin;
custTable.CustGroup = "80";
custTable.update();
ttscommit;
if (custTable.CreditMax == 0)
{
ttsbegin;
custTable.CreditMax = 1000;
custTable.update();
ttscommit;
}
}
else
{
ttsbegin;
custTable.AccountNum = "1001";
custTable.CustGroup = "80";
custTable.update();
ttscommit;
}
//Nothing is committed to the database until this final closing ttsCommit;
ttscommit; In short, nothing will be committed to the database until the final closing ttsCommit is run of that block. Said another way, once there have been the same number of ttsCommit statements as there have been ttsBegin statements, the data is committed.
While it absolutely makes sense that you may use other ttsBegin and ttsCommit statements inside different ‘if conditions’, or method calls, the system will not write to the physical database until the closing commit statement.
To explain further, if you were to run SQL Sever Management Studio and query the table while you were debugging the code, you would not see the change recorded until after the last closing ttsCommit is run.
That said, importantly, when you write x++ select statements in the middle of these blocks, the x++ will see the changed data since the changed data is in its memory.
Unbalanced ttsBegin/ttsCommit Pair
Lastly, there are some important rules when using D365 ttsBegin and ttsCommit statements.
I like to think of ttsBegin and ttsCommit statements the same way I think of curly braces when writing ‘if’ statements. You must have one ttsCommit for each ttsBegin. Making a pair. You cannot have more of one than the other run in any given process.
To demonstrate, consider this code:
CustTable custTable;
select firstonly forupdate custTable
where custTable.AccountNum == "sdfkljlsdfsd";
ttsbegin;
if (custTable)
{
custTable.CustGroup = "80";
custTable.update();
ttscommit;
}In the scenario where a custTable is not found, the system will NOT go into the ‘if’ statement, and the corresponding ttsCommit statement will never get called.
Importantly, Visual Studio will NOT report a compile error. However, you will receive a runtime error.
If the system finishes a process, and it has not run the same number of ttsCommit statements as ttsBegin statements, then an error will be thrown.
The error may read “an unbalanced x++ ttsbegin/ttscommit pair has been detected. Causes include too many/few ttsbegin or ttscommit, return calls within ttsbegin/ttscommit pars and user interaction within ttsbegin/ttscommit pars”
Another Example
Additionally, consider this code which causes an unbalanced ttsBegin and ttsCommit.
CustTable custTable;
ttsbegin;
if (custTable)
{
custTable.CustGroup = "80";
custTable.update();
//This return statement means the corresponding ttscommit is never run.
return;
}
ttscommit;To explain, the ‘return’ statement will cause the system to leave the method, without ever calling the corresponding ttsCommit.
In some scenarios, having code after a call to ‘return’ will cause a compile error. But because the ‘return’ is inside an ‘if’ statement, the compile finds it acceptable.
And unfortunately, the compiler is not intelligent enough to detect the possible unbalanced ttsBegin and ttsCommit error that could occur at runtime.
Preventing unbalanced ttsBegin and ttsCommit
Specifically, the way to keep this rule is to ensure your corresponding ttsCommit statement is in the same scope, and code block as the starting ttsBegin statement. This ensures the first rule is met, and that the same number of ttsCommit statements as ttsBegin statements are run.
Again, I think of them like curly braces. And ensure my ttsCommit statement is in the same level of a condition statement or method as the ttsbegin statement.
TTSAbort
Before finishes, there is one more type of transaction block.
- ttsAbbort – This allows a developer to explicitly discard changes in the current transaction.
I will save a more detailed explanation for another article. In short, this statement will cause the system to revert back to the first ttsBegin statement, similar to when an error is thrown.
Microsoft Documentation
For more information see this article on Transaction Integrity. As well as this one.
Conclusion
Using D365 ttsbegin and ttscommit allows developers to control what data is committed to the database and when in the process this occurs. Additionally, these statements allow data to be reverted back without the need for specific rollback code. Developers must understand how they work differently in different places in code. This way, they can determine how much data should be reverted when an error occurs.
