Using Transaction Steps

Overview

Transaction steps are used when developers want to perform multiple database operations. These steps ensure that the database operations occur either in their entirety or don't occur at all.

Example

In this example, create a Flow that uses Transaction steps to alter a Customer Info table. There is already a Report of all customer details and an action designed to edit the customer information. Transaction steps are used to maintain data that is read or written to the database during an application process.

1. Within a Designer Folder, select CREATE FLOW. 
2. Name the Flow and select CREATE.
3. Add an Edit Customer Information Form to the workspace. This Form displays all customer details that are available to edit. 
4. 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.
5. Place the Start Transaction step after the Edit Customer Information Form, followed by the Commit Transaction step. 
6. The steps placed between these two will be the action taken and committed to the database.
   
7. 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 is taking place.
   Chaos	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 at the same time. 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.

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