Using Transaction Steps

Using Transaction Steps

In a database, a Transaction is a sequence of one or more SQL statements executed as a single unit of work. A Transaction is used to ensure the consistency and integrity of data within a database. It follows the principles of the ACID properties, which stands for Atomicity, Consistency, Isolation, and Durability.

Available Steps

Transaction steps can be located in the Toolbox under Database > Transaction. The following table describes all the available steps in the Toolbox.

Using Transaction Steps within a Flow

The above Flow chart helps to demonstrate how to use the Transaction Steps to perform a Transaction to the database. Users must build their own logic to perform the CRUD actions to the database, and this logic must always be built in between the Start Transaction and the Commit Transaction Step. To roll back the Transaction, enable the Add Outcome for Exception on the Commit Transaction step and map it to the Rollback Transaction Step.

In this example, we have configured a Flow that uses Transaction steps to alter a Customer Info table.

1. Within a Flow, we have added a Form that allows editing the Customer Information Details.
2. After this Form, place three Transaction Steps onto the Flow by navigating to Integration > Database and then selecting the Start Transaction, Commit Transaction, and Rollback Transaction steps found under the database holding the table to edit.
3. Place the Start Transaction step after the Edit Customer Information Form, followed by the Commit Transaction step. 
4. The steps placed between these two will be the action taken and committed to the database.
   
   
5. The Start Transaction step allows the isolation level to be specified under the step's Input properties. 
   For assistance on setting transaction isolation levels in SQL, please read the Set Transaction Isolation Levels Microsoft Document.

   Isolation Level	Description
   Read Uncommitted	Lowest isolation level. A transaction may read uncommitted changes made by another transaction because they are not isolated from each other.
   Read Committed	Locks are placed on the data being read, but it can be changed before the end of the Transaction, resulting in non-repeatable reads or phantom data.
   Repeatable Read	This holds read locks on all rows it references and writes locks on all rows it inserts, updates, or deletes. This prevents non-repeatable reads.
   Serializable	A lock is placed on the data to prevent other users from editing it while the Transaction takes place.
   Chaos	It behaves the same way as Read Uncommitted but checks the isolation level of other pending transactions during a write to respect more restrictive isolation levels.
   Snapshot	This choice uses a version of the data at the start of the Transaction, so you cannot see changes by other transactions happening simultaneously. This choice offers the benefit of reduced blocking. The Snapshot value uses Snapshot Isolation if the ALLOW_SNAPSHOT_ISOLATION ON database option has been set by a DBA in the SQL Server database.
   Unspecified	A different, undetermined isolation level is being used.

6. To edit the Customer Information table, place the Edit CustomerInfo step between the Start Transaction and Commit Transaction steps. 
7. If the Transaction fails, roll back the changes to the table before starting the Transaction.
8. To do this, select the Commit Transaction step and navigate to the properties tab on the right side.
9. Under outcomes, choose the option to Add Outcome for Exception.
10. Add the Rollback Transaction step to the outcome path generated from this selection. 
11. Connect both the Rollback Transaction step and the Commit Transaction step to the End Step to complete the Flow. 
12. Test the Flow by selecting a customer from the Report and successfully updating the address.

Feature Changes