The Rollback orchestration component ends a transaction within the database and undoes any changes made since the latest Begin component. Changes are never visible to other database users. Transactions can help make multiple changes to a database as a single, logical unit of work.
All jobs start in 'Auto Commit' mode. All work is committed immediately as the job runs. This default behavior can be changed using the Begin, Commit, and Rollback components. After a Begin component, any database changes that are made are effectively invisible to other database sessions until you choose to commit them. You may also perform a rollback to undo all the changes made since Begin. After a commit or rollback, you are then placed back into 'Auto Commit' mode until another Begin.
For Snowflake users, read ROLLBACK for more information.
For Redshift users, read ROLLBACK for more information.
|Name||Text||A human-readable name for the component.|
Notes On Transactions
- It is an error to try to rollback a transaction if no transaction is in progress.
- If you forget to commit or rollback after starting a transaction, the transaction will be left open (and continue to hold any associated locks) but only for a few minutes—after which, the connection will be closed and an automatic rollback will be performed.
- During a transaction, a rollback must be performed to recover from any error. A rollback may also be performed to undo changes even if there has been no error—for example, if a data quality check fails.
- Redshift: All components in Matillion ETL that would ordinarily use TRUNCATE will instead use DELETE FROM to ensure transaction safety.
- Redshift: Using DELETE FROM will mean you should also VACUUM tables regularly to recover the space taken by deleted rows. The vacuum must be done after a commit.
- Snowflake: Rollback affects only DML statements but not DDL statements. This means, for example, a Rollback can undo changes to a table's data but not the creation of a table.
- If a rollback is connected to the 'Failure' connector of a component, the success of a rollback will make that otherwise-failed job appear to succeed.