Transaction management

When working with databases, Etlworks provides 3 options for managing transactions:

Auto-commit

When creating a database connection, users can specify the auto-commit option. The auto-commit is disabled by default, therefore the system controls when to commit (or rollback) the transaction.

If auto-commit is disabled

• The system will coordinate the commit across all opened database connections with auto-commit disabled if the flow was executed successfully. If the flow is a part of the nested flow the coordinated commit will be executed when the main nested flow is finished.
• The system will coordinate the rollback across all opened database connections with auto-commit disabled if the flow was executed with an error.
• If the flow was executed with an error but was configured to ignore all or specific exceptions, the system will commit the transaction. Read how to override this behavior. 

If auto-commit is enabled

Enable automatic commit on each DML statement by enabling the auto-commit option when creating a database connection.

Manually Commit

For SQL flows, you can configure them to commit the transaction regardless of the auto-commit settings for the connection.

If SQL flow executes multiple ";" separated SQL statements, the commit will be issued after the last statement.

Rollback On Exception

The system always rollbacks connections on exception, unless the flow or transformation was configured to ignore all or specific exceptions. You can override this behavior by unselecting the option "Rollback on exception" under the On Exception tab.