Snowflake Transaction Management

The default automatic commit behavior of the Snowflake database causes each SQL command that runs separately to commit individually. By default, a DML statement executed without explicitly starting a transaction is automatically committed on query success or rolled back on failure at the end of the statement. The AUTOCOMMIT parameter will control the auto commit behavior in the Snowflake cloud data warehouse.

BEGIN Command:

The BEGIN command begins a transaction block. Both BEGIN and START TRANSACTION are same in Snowflake. We don’t need any special privileges to use the BEGIN command in Snowflake.

Syntax:

BEGIN [ { WORK | TRANSACTION } ] [NAME <name> ];

Start session syntax:

Start TRANSACTION [NAME <name>]

WORK and TRANSACTION are optional keywords that you can provide while creating transaction block. And NAME is an optional string that assigns a name to the transaction.

BEGIN ;

Select 1;

BEGIN Work;

Select 1;

BEGIN Transaction;

Select 1;

BEGIN NAME t1;

COMMIT Command:

A COMMIT command in Snowflake commits an open transaction in the current session. If there are any statements such as update or insert commands, this COMMIT makes those commands from the transaction permanent.

Syntax:

COMMIT [ {Work} ];

Following examples commits the current transaction to the database.

Commit;

Commit work;

ROLLBACK Command:

The Snowflake ROLLBACK command aborts the current transaction and discards all updates made by that transaction.

Syntax:

ROLLBACK [ {Work} ];

WORK keyword is optional.

Redshift BEGIN, COMMIT and ROLLBACK transaction control management commands.

Create table Sample (id int);

Begin;

Insert into Sample values (1);

Insert into sample values (2);

Commit;

Select * from Sample;

O/P: ID

        ------

          1

          2

Insert into Sample values (3);

ROLLBACK;

Select * from Sample;

O/P: ID

        ------

          1

          2

          3