Commit Component

Commit Component


This article is specific to the following platforms - Redshift - Snowflake.

Commit Component

Warning: Rollback in Snowflake 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.

The "Commit" orchestration component ends a transaction within Redshift and makes all changes made since the latest Begin component visible to other database users. Transactions can help make multiple changes to a database as a single, logical unit of work. For more information, see the documentation on COMMIT.

The "Commit" orchestration component ends a transaction within Snowflake and makes all changes made since the latest Begin component visible to other database users. Transactions can help make multiple changes to a database as a single, logical unit of work. For more information, see the documentation on COMMIT.

All jobs start in 'Auto Commit' mode. All work is committed immediately as the job runs. This default behaviour can be changed using the "Begin", "Commit" and "Rollback" components. After a Begin component, any database changes made are effectively invisible to other database sessions until you choose to "Commit" them. You may also perform a "Rollback" to undo all of the changes made since "Begin". After Commit or Rollback, you are then placed back into 'Auto Commit' mode until another Begin.


Properties

Property Setting Description
Name Text The descriptive 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.
  • All components in Matillion ETL that would ordinarily use TRUNCATE will instead use DELETE FROM to ensure transaction safety.
  • 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.
  • 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.

Example

This example removes all existing rows from a table and then reloads it using an S3 load. If all goes well, the transaction is committed and other database sessions will then see the new data. However, if removing the existing rows fails, or loading the new data fails, or the commit itself fails, then the transaction is rolled back and other database sessions will continue to see the old data. At no point can other database sessions see the intermediate empty table.

If anything does fail, then after the rollback the failure is reported via SNS before ensuring the job ends as a failure.

The Begin, Commit and Rollback components do not require any configuration other than a name.

The Truncate Tables Component will perform a DELETE FROM during a transaction and TRUNCATE cannot be rolled back.

A vacuum is added to the end of the job to recover the disk space taken up by the deleted rows.