Transactions

A transaction is a sequence of SQL statements that are committed or rolled back as a unit.

In this Topic:

Introduction

What is a Transaction?

A transaction is a sequence of SQL statements that are processed as an atomic unit. All statements in the transaction are either applied (i.e. committed) or undone (i.e. rolled back) together.

A transaction can include both reads and writes.

Snowflake transactions, like most database transactions, guarantee ACID properties.

Snowflake supports READ COMMITTED transaction isolation.

Transactions are never “nested”. For example, you cannot create an “outer” transaction that would roll back an “inner” transaction that was committed, or create an “outer” transaction that would commit an “inner” transaction that had been rolled back.

A transaction is associated with a single session. Multiple sessions cannot share the same transaction. For information about handling transactions with overlapping threads in the same session, see Transactions and Multi-threading.

Terminology

In this topic:

  • The term “DDL” includes CTAS statements (CREATE TABLE AS SELECT ...) as well as other DDL statements.

  • The term “DML” refers to INSERT, UPDATE, DELETE, MERGE, and TRUNCATE.

  • The term “query statement” refers to SELECT and CALL.

Although a CALL statement (which calls a stored procedure) is a single statement, the stored procedure it calls can contain multiple statements. There are special rules for stored procedures and transactions.

Explicit Transactions

A transaction can be started explicitly by executing a BEGIN statement. Snowflake supports the synonyms BEGIN WORK and BEGIN TRANSACTION. Snowflake recommends using BEGIN TRANSACTION.

A transaction can be ended explicitly by executing COMMIT or ROLLBACK. Snowflake supports the synonym COMMIT WORK for COMMIT, and the synonym ROLLBACK WORK for ROLLBACK.

In general, if a transaction is already active, any BEGIN TRANSACTION statements are ignored. Users should avoid extra BEGIN TRANSACTION statements, however, because extra BEGIN TRANSACTION statements make it much more difficult for human readers to pair up COMMIT (or ROLLBACK) statements with the corresponding BEGIN TRANSACTION.

One exception to this rule involves a nested stored procedure call. For details, see Scoped Transactions.

Note

Explicit transactions should contain only DML statements and query statements. DDL statements implicitly commit active transactions (for details, see the DDL section).

Implicit Transactions

Transactions can be started and ended implicitly, without an explicit BEGIN TRANSACTION or COMMIT/ROLLBACK. Implicit transactions behave the same way as explicit transactions. However, the rules that determine when the implicit transaction starts are different from the rules that determine when an explicit transaction starts.

The rules for stopping and starting depend upon whether the statement is a DDL statement or a DML statement or a query statement. If the statement is a DML or query statement, the rules depend upon whether AUTOCOMMIT is enabled.

DDL

Each DDL statement executes as a separate transaction.

If a DDL statement is executed while a transaction is active, the DDL statement:

  1. Implicitly commits the active transaction.

  2. Executes the DDL statement as a separate transaction.

Because a DDL statement is its own transaction, you cannot roll back a DDL statement; the transaction containing the DDL completes before you can execute an explicit ROLLBACK.

If a DDL statement is followed immediately by a DML or query statement, then that DML or query statement implicitly starts a new transaction.

AUTOCOMMIT

Snowflake supports an AUTOCOMMIT parameter. The default setting for AUTOCOMMIT is on.

While AUTOCOMMIT is enabled:

  • Each statement outside an explicit transaction is treated as though it is inside its own implicit single-statement transaction. In other words, that statement is automatically committed if it succeeds, and automatically rolled back if it fails.

    Statements inside an explicit transaction are not affected by AUTOCOMMIT. For example, statements inside an explicit BEGIN TRANSACTION ... ROLLBACK are rolled back even if AUTOCOMMIT is TRUE.

While AUTOCOMMIT is disabled:

  • An implicit BEGIN TRANSACTION is executed at:

    • The first DML statement or query statement after a transaction ends. This is true regardless of what ended the preceding transaction (e.g. a DDL statement, or an explicit commit or rollback).

    • The first DML statement or query statement after disabling AUTOCOMMIT.

  • An implicit COMMIT is executed at the following (if a transaction is already active):

    • The execution of a DDL statement.

    • The execution of an ALTER SESSION SET AUTOCOMMIT statement, regardless of whether the new value is TRUE or FALSE, and regardless of whether or not the new value is different from the previous value. For example, even if you set AUTOCOMMIT to FALSE when it is already FALSE, an implicit COMMIT is executed.

  • An implicit ROLLBACK is executed at the following (if a transaction is already active):

    • The end of a session.

    • The end of a stored procedure.

      Regardless of whether the stored procedure’s active transaction was started explicitly or implicitly, Snowflake rolls back the active transaction and issues an error message.

Caution

Do not change AUTOCOMMIT settings inside a stored procedure. You will get an error message.

Mixing Implicit and Explicit Starts and Ends of a Transaction

To avoid writing confusing code, you should avoid mixing implicit and explicit starts and ends in the same transaction. The following are legal, but discouraged:

  • An implicitly-started transaction can be ended by an explicit COMMIT or ROLLBACK.

  • An explicitly-started transaction can be ended by an implicit COMMIT or ROLLBACK.

Failed Statements Within a Transaction

Although a transaction is committed or rolled back as a unit, that is not quite the same as saying that it succeeds or fails as a unit. If a statement fails within a transaction, you can still commit, rather than roll back, the transaction.

When a DML statement or CALL statement in a transaction fails, the changes made by that failed statement are rolled back. However, the transaction stays active until the entire transaction is committed or rolled back. If the transaction is committed, the changes by the successful statements are applied.

For example, consider the following code, which inserts two valid values and one invalid value:

CREATE TABLE table1 (i int);
BEGIN TRANSACTION;
INSERT INTO table1 (i) VALUES (1);
INSERT INTO table1 (i) VALUES ('This is not a valid integer.');    -- FAILS!
INSERT INTO table1 (i) VALUES (2);
COMMIT;
SELECT i FROM table1 ORDER BY i;

The output of the final SELECT statement includes the rows with integer values 1 and 2, even though one of the other statements in the transaction failed.

Transactions and Multi-threading

Although multiple sessions cannot share the same transaction, multiple threads using a single connection share the same session, and thus share the same transaction. This can lead to unexpected results, such as one thread rolling back work done in another thread.

This situation can occur when a client application using a Snowflake driver (such as the Snowflake JDBC Driver) or connector (such as the Snowflake Connector for Python) is multi-threaded. If two or more threads share the same connection, then those threads also share the current transaction in that connection. A BEGIN TRANSACTION, COMMIT, or ROLLBACK by one thread affects all threads using that shared connection. If the threads are running asynchronously, the results can be unpredictable.

Similarly, changing the AUTOCOMMIT setting in one thread affects the AUTOCOMMIT setting in all other threads that use the same connection.

Snowflake recommends that multi-threaded client programs do at least one of the following:

  • Use a separate connection for each thread.

    Note that even with separate connections, your code can still hit race conditions that generate unpredictable output; for example, one thread might delete data before another thread tries to update it.

  • Execute the threads synchronously rather than asynchronously, to control the order in which steps are performed.

Stored Procedures and Transactions

In general, the rules described in the previous sections also apply to stored procedures. This section provides additional information specific to stored procedures.

A transaction can be inside a stored procedure, or a stored procedure can be inside a transaction; however, a transaction cannot be partly inside and partly outside a stored procedure, or started in one stored procedure and finished in a different stored procedure.

For example:

  • You cannot start a transaction before calling the stored procedure, then complete the transaction inside the stored procedure. If you try to do this, Snowflake reports an error similar to Modifying a transaction that has started at a different scope is not allowed.

  • You cannot start a transaction inside the stored procedure, then complete the transaction after returning from the procedure. If a transaction is started inside a stored procedure and is still active when the stored procedure finishes, then an error occurs and the transaction is rolled back.

These rules also apply to nested stored procedures. If procedure A calls procedure B, then B cannot complete a transaction started in A or vice versa. Each BEGIN TRANSACTION in A must have a corresponding COMMIT (or ROLLBACK) in A, and each BEGIN TRANSACTION in B must have a corresponding COMMIT (or ROLLBACK) in B.

If a stored procedure contains a transaction, that transaction can contain either part or all of the body of the stored procedure. For example, in the following stored procedure, only some of the statements are in a transaction.

create procedure ...
    as
    $$
        ...
        statement1;

        BEGIN TRANSACTION;
        statement2;
        COMMIT;

        statement3;
        ...

    $$;

Non-Overlapping Transactions

The sections below describe:

  • Using a stored procedure inside a transaction.

  • Using a transaction inside a stored procedure.

Using a Stored Procedure Inside a Transaction

In the simplest case, a stored procedure is considered to be inside of a transaction if the following conditions are met:

  • A BEGIN TRANSACTION is executed before the stored procedure is called.

  • The corresponding COMMIT (or ROLLBACK) is executed after the stored procedure completes.

  • The body of the stored procedure does not contain an explicit or implicit BEGIN TRANSACTION or COMMIT (or ROLLBACK).

The stored procedure inside the transaction follows the rules of the enclosing transaction:

  • If the transaction is committed, then all the statements inside the procedure are committed.

  • If the transaction is rolled back, then all statements inside the procedure are rolled back.

The following pseudo-code shows a stored procedure called entirely inside a transaction:

CREATE PROCEDURE my_procedure()
...
AS
$$
    statement X;
    statement Y;
$$;

BEGIN TRANSACTION;
statement W;
CALL my_procedure();
statement Z;
COMMIT;

This is equivalent to executing the following sequence of statements:

begin;
statement W;
statement X;
statement Y;
statement Z;
commit;

Using a Transaction in a Stored Procedure

You can execute zero, one, or more transactions inside a stored procedure. The following pseudo-code shows an example of two transactions in one stored procedure:

CREATE PROCEDURE p1()
...
$$
    begin transaction;
    statement C;
    statement D;
    commit;

    begin transaction;
    statement E;
    statement F;
    commit;
$$;

The stored procedure could be called as shown below:

BEGIN TRANSACTION;
statement A;
statement B;
COMMIT;

CALL p1();

BEGIN TRANSACTION;
statement G;
statement H;
COMMIT;

This is equivalent to executing the following sequence:

BEGIN TRANSACTION;
statement A;
statement B;
COMMIT;

BEGIN TRANSACTION;
statement C;
statement D;
COMMIT;

BEGIN TRANSACTION;
statement E;
statement F;
COMMIT;

BEGIN TRANSACTION;
statement G;
statement H;
COMMIT;

In this code, four separate transactions are executed. Each transaction either starts and completes outside the procedure, or starts and completes inside the procedure. No transaction is split across a procedure boundary - partly inside and partly outside the stored procedure. No transaction is nested in another transaction.

Scoped Transactions

A stored procedure that contains a transaction can be called from within another transaction. For example, a transaction inside a stored procedure can include a call to another stored procedure that contains a transaction.

Snowflake does not treat the inner transaction as nested; instead, the inner transaction is a separate transaction. Snowflake calls these “autonomous scoped transactions” (or simply “scoped transactions”).

The starting point and ending point of each scoped transaction determine which statements are included in the transaction. The start and end can be explicit or implicit. Each SQL statement is part of only one transaction. An enclosing ROLLBACK or COMMIT does not undo an enclosed COMMIT or ROLLBACK.

Note

The terms “inner” and “outer” are commonly used when describing nested operations, such as nested stored procedure calls. However, transactions in Snowflake are not truly “nested”; therefore, to reduce confusion when referring to transactions, this document frequently uses the terms “enclosed” and “enclosing”, rather than “inner” and “outer”.

The diagram below shows two stored procedures and two scoped transactions. In this example, each stored procedure contains its own independent transaction. The first stored procedure calls the second stored procedure, so the procedures overlap in time – however, they do not overlap in content. All the statements inside the shaded inner box are in one transaction; all the other statements are in another transaction.

Illustration of two stored procedures, each with its own scoped transaction.

In the next example, the transaction boundaries are different from the stored procedure boundaries; the transaction that starts in the outer stored procedure includes some but not all of the statements in the inner stored procedure.

Illustration of two stored procedures and two scoped transactions, in which one transaction includes some statements from the inner stored procedure as well as all statements from the outer stored procedure.

In the code above, the second stored procedure contains some statements (SP2_T1_S2 and SP2_T1_S3) that are in the scope of the first transaction. Only statement SP2_T2_S1, inside the shaded inner box, is in the scope of the second transaction.

The next example demonstrates the problems that occur if a transaction does not begin and end within the same stored procedure. The example contains the same number of COMMIT statements as BEGIN statements. However, the BEGIN and COMMIT statements are not paired properly, so this example contains two errors:

  • The outer stored procedure starts a scoped transaction, but doesn’t explicitly complete it. Therefore that scoped transaction causes an error at the end of that stored procedure, and the active transaction is implicitly rolled back.

  • The second stored procedure contains a COMMIT, but there is no corresponding BEGIN in that stored procedure. This COMMIT does not commit the open transaction started in the first stored procedure. Instead, the improperly paired COMMIT causes an error.

Illustration of two stored procedures that create improperly-scoped transactions.

The next example shows three scoped transactions that overlap in time. In this example, stored procedure p1() calls another stored procedure p2() from inside a transaction, and p2() contains its own transaction, so the transaction started in p2() also runs independently.

CREATE PROCEDURE p2()
...
$$
    BEGIN TRANSACTION;
    statement C;
    COMMIT;
$$;

CREATE PROCEDURE p1()
...
$$
    BEGIN TRANSACTION;
    statement B;
    CALL p2();
    statement D;
    COMMIT;
$$;

BEGIN TRANSACTION;
statement A;
CALL p1();
statement E;
COMMIT;

In these three scoped transactions:

  • The transaction that outside any stored procedure contains statements A and E.

  • The transaction in stored procedure p1() contains statements B and D

  • The transaction in p2() contains statement C.

The rules for scoped transactions also apply to recursive stored procedure calls. A recursive call is just a specific type of nested call, and follows the same transaction rules as a nested call.

Caution

Overlapping scoped transactions can cause a deadlock if they manipulate the same database object (e.g. table). Scoped transactions should be used only when necessary.

When AUTOCOMMIT is off, be especially careful combining implicit transactions and stored procedures. If you accidentally leave a transaction active at the end of a stored procedure, the transaction is rolled back.

For example, the following code causes an implicit ROLLBACK at the end of the stored procedure:

CREATE PROCEDURE p1() ...
$$
    INSERT INTO parent_table (column1) VALUES ('value 1');
    INSERT INTO child_table (column1) VALUES ('value 2');
$$;


ALTER SESSION SET AUTOCOMMIT = FALSE;
CALL p1;
COMMIT WORK;

In this example, the command to set AUTOCOMMIT commits any active transaction. A new transaction is not started immediately. The stored procedure contains a DML statement, which implicitly begins a new transaction. That implicit BEGIN TRANSACTION does not have a matching COMMIT or ROLLBACK in the stored procedure. Because there is an active transaction at the end of the stored procedure, that active transaction is implicitly rolled back.

If you want to run the entire stored procedure in a single transaction, start the transaction before you call the stored procedure, and commit the transaction after the call:

CREATE PROCEDURE p1() ...
$$
    INSERT INTO parent_table (column1) VALUES ('value 1');
    INSERT INTO child_table (column1) VALUES ('value 2');
$$;


ALTER SESSION SET AUTOCOMMIT = FALSE;
BEGIN TRANSACTION;
CALL p1;
COMMIT WORK;

In this case, the BEGIN and COMMIT are properly paired, and the code executes without error.

As an alternative, put both the BEGIN TRANSACTION and the COMMIT inside the stored procedure. For example:

CREATE PROCEDURE p1() ...
$$
    BEGIN TRANSACTION;
    INSERT INTO parent_table (column1) VALUES ('value 1');
    INSERT INTO child_table (column1) VALUES ('value 2');
    COMMIT WORK;
$$;


ALTER SESSION SET AUTOCOMMIT = FALSE;
CALL p1;

If you do not pair your BEGIN/COMMIT blocks properly in a scoped transaction, Snowflake reports an error. That error can have further impacts, such as preventing a stored procedure from being completed or preventing an enclosing transaction from being committed. For example, in the following pseudo-code example, some statements in the enclosing stored procedure, as well as the enclosed stored procedure, are rolled back:

CREATE or replace PROCEDURE outer_sp1()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    insert 'osp1_alpha';
    BEGIN WORK;
    insert 'osp1_beta';
    CALL inner_sp2();
    INSERT 'osp1_delta';
    COMMIT WORK;
    INSERT 'osp1_omega';
$$;

CREATE or replace PROCEDURE inner_sp2()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    BEGIN WORK;
    insert 'isp2';
    -- Missing COMMIT, so implicitly rolls back!
$$;

CALL outer_sp1();

SELECT * FROM st;

In this example, the only value that is inserted is ‘osp1_alpha’. None of the other values are inserted because a COMMIT is not correctly paired with a BEGIN. The error is handled as follows:

  1. When procedure inner_sp2() finishes, Snowflake detects that the BEGIN in inner_sp2() has no corresponding COMMIT (or ROLLBACK).

    1. Snowflake implicitly rolls back the scoped transaction that started in inner_sp2().

    2. Snowflake also emits an error because the CALL to inner_sp2() failed.

  2. Because the CALL of inner_sp2() failed, and because that CALL statement was in outer_sp1(), the stored procedure outer_sp1() itself also fails and returns an error, rather than continuing.

  3. Because outer_sp1() does not finish executing:

    • The INSERT statements for values ‘osp1_delta’ and ‘osp1_omega’ never execute.

    • The open transaction in outer_sp1() is implicitly rolled back rather than committed, so the insert of value ‘osp1_beta’ is never committed.

Isolation Level

READ COMMITTED is the only isolation level currently supported for tables.

READ COMMITTED Isolation

With READ COMMITTED isolation, a statement sees only data that was committed before the statement began. It never sees uncommitted data.

When a statement is executed inside a multi-statement transaction:

  • A statement sees only data that was committed before the statement began. Two successive statements in the same transaction can see different data if another transaction is committed between the execution of the first and the second statements.

  • A statement does see the changes made by previous statements executed within the same transaction, even though those changes are not yet committed.

Resource Locking

Transactional operations acquire locks on a resource, such as a table, while that resource is being modified. Locks block other statements from modifying the resource until the lock is released.

The following guidelines apply in most situations:

  • COMMIT operations (including both AUTOCOMMIT and explicit COMMIT) lock resources, but usually only briefly.

  • UPDATE, DELETE, and MERGE statements hold locks that generally prevent them from running in parallel with other UPDATE, DELETE, and MERGE statements.

  • Most INSERT and COPY statements write only new partitions. Those statements often can run in parallel with other INSERT and COPY operations, and sometimes can run in parallel with an UPDATE, DELETE, or MERGE statement.

Locks held by a statement are released on COMMIT or ROLLBACK of the transaction.

Lock Wait Timeout

A blocked statement either acquires a lock on the resource it was waiting for or times out waiting for the resource to become available. The amount of time (in seconds) that a statement should block can be configured by setting the LOCK_TIMEOUT parameter.

For example, to change the lock timeout to 2 hours (7200 seconds) for the current session:

ALTER SESSION SET LOCK_TIMEOUT=7200;

SHOW PARAMETERS LIKE 'lock%';

+--------------+-------+---------+---------+-------------------------------------------------------------------------------+
| key          | value | default | level   | description                                                                   |
|--------------+-------+---------+---------+-------------------------------------------------------------------------------|
| LOCK_TIMEOUT | 7200  | 43200   | SESSION | Number of seconds to wait while trying to lock a resource, before timing out  |
|              |       |         |         | and aborting the statement. A value of 0 turns off lock waiting i.e. the      |
|              |       |         |         | statement must acquire the lock immediately or abort. If multiple resources   |
|              |       |         |         | need to be locked by the statement, the timeout applies separately to each    |
|              |       |         |         | lock attempt.                                                                 |
+--------------+-------+---------+---------+-------------------------------------------------------------------------------+

Deadlocks

Deadlocks occur when concurrent transactions are waiting on resources that are locked by each other.

In Snowflake, a deadlock cannot occur while executing autocommit DML or query statements concurrently. However, deadlocks can occur with explicitly-started transactions and multiple statements in each transaction. Snowflake detects deadlocks and chooses the most recent statement which is part of the deadlock as the victim. The statement is rolled back, but the transaction itself remains active and must be committed or rolled back.

Deadlock detection can take time.

Allowing Statement Errors to Abort Transactions

To allow a statement error within a transaction to abort the transaction, set the TRANSACTION_ABORT_ON_ERROR parameter at the session or account level.

Transaction Commands and Functions

Snowflake provides the following SQL commands to support transactions:

In addition, Snowflake provides the following context functions for obtaining information about transactions in a session:

Aborting Transactions

If a transaction is running in a session and the session disconnects abruptly, preventing the transaction from committing or rolling back, the transaction is left in a detached state, including any locks that the transaction is holding on resources. If this happens, you might need to abort the transaction.

To abort a running transaction, the user who started the transaction or an account administrator can call the system function, SYSTEM$ABORT_TRANSACTION.

If the transaction is not aborted by the user:

  • If it blocks another transaction from acquiring a lock on the same table and is idle for 5 minutes, it is automatically aborted and rolled back.

  • If it does not block other transactions from modifying the same table and is older than 4 hours, it is automatically aborted and rolled back.

Best Practices

  • A transaction should contain statements that are related and should succeed or fail together, for example, withdrawing money from one account and depositing that same money to another account. If a rollback occurs, either the payer or the recipient ends up with the money; the money never “disappears” (withdrawn from one account but never deposited to the other account).

    In general, one transaction should contain only related statements. Making a statement less granular means that when a transaction is rolled back, it might roll back useful work that didn’t actually need to be rolled back.

  • Larger transactions can improve performance in some cases.

    Although the preceding bullet point emphasized the importance of grouping only statements that truly need to be committed or rolled back as a group, larger transactions can sometimes be useful. In Snowflake, as in most databases, managing transactions consumes resources. For example, inserting 10 rows in one transaction is generally faster and cheaper than inserting one row each in 10 separate transactions. Combining multiple statements into a single transaction can improve performance.

  • Overly large transactions can reduce parallelism or increase deadlocks. If you do decide to group unrelated statements to improve performance (as described in the previous bullet point), keep in mind that a transaction can acquire locks on resources, which can delay other queries or lead to deadlocks.

  • Snowflake recommends keeping AUTOCOMMIT enabled and using explicit transactions as much as possible. Using explicit transactions makes it easier for human readers to see where transactions begin and end. This, combined with AUTOCOMMIT, makes your code less likely to experience unintended rollbacks, for example at the end of a stored procedure.

  • Avoid changing AUTOCOMMIT merely to start a new transaction implicitly. Instead, use BEGIN TRANSACTION to make it more obvious where a new transaction starts.

  • Avoid executing more than one BEGIN TRANSACTION statement in a row. Extra BEGIN TRANSACTION statements make it harder to see where a transaction actually begins, and make it harder to pair COMMIT/ROLLBACK commands with the corresponding BEGIN TRANSACTION.

Transaction ID

Every Snowflake transaction is assigned a unique start time (with a precision of milliseconds), which serves as the ID for the transaction. For some examples of transaction start times, see the Examples for BEGIN TRANSACTION.

Examples

Simple Example of Scoped Transaction and Stored Procedure

This is a simple example of scoped transactions. The stored procedure contains a transaction that inserts a row with the value 12 and then rolls back. The outer transaction commits. The output shows that all rows in the scope of the outer transaction are kept, while the row in the scope of the inner transaction is not kept.

Note that because only part of the stored procedure is inside its own transaction, INSERTs that are in the stored procedure, but outside the stored procedure’s transaction, are kept.

Create two tables:

create table tracker_1 (id integer, name varchar);
create table tracker_2 (id integer, name varchar);

Create the stored procedure:

create procedure sp1()
returns varchar
language javascript
AS
$$
    // This is part of the outer transaction that started before this
    // stored procedure was called. This is committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (11, 'p1_alpha')"}
        );

    // This is an independent transaction. Anything inserted as part of this
    // transaction is committed or rolled back based on this transaction.
    snowflake.execute (
        {sqlText: "begin transaction"}
        );
    snowflake.execute (
        {sqlText: "insert into tracker_2 values (12, 'p1_bravo')"}
        );
    snowflake.execute (
        {sqlText: "rollback"}
        );

    // This is part of the outer transaction started before this
    // stored procedure was called. This is committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (13, 'p1_charlie')"}
        );

    // Dummy value.
    return "";
$$;

Call the stored procedure:

begin transaction;
insert into tracker_1 values (00, 'outer_alpha');
call sp1();
insert into tracker_1 values (09, 'outer_zulu');
commit;

The results should include 00, 11, 13, and 09. The row with ID = 12 should not be included. This row was in the scope of the enclosed transaction, which was rolled back. All other rows were in the scope of the outer transaction, and were committed. Note in particular that the rows with IDs 11 and 13 were inside the stored procedure, but outside the innermost transaction; they are in the scope of the enclosing transaction, and were committed with that.

select id, name FROM tracker_1
union all
select id, name FROM tracker_2
order by id;
+----+-------------+
| ID | NAME        |
|----+-------------|
|  0 | outer_alpha |
|  9 | outer_zulu  |
| 11 | p1_alpha    |
| 13 | p1_charlie  |
+----+-------------+

Logging Information Independently of a Transaction’s Success

This is a simple, practical example of how to use a scoped transaction. In this example, a transaction logs certain information; that logged information is preserved whether the transaction itself succeeds or fails. This technique can be used to track all attempted actions, whether or not each succeeded.

Create two tables:

create table data_table (id integer);
create table log_table (message varchar);

Create the stored procedure:

create procedure log_message(MESSAGE VARCHAR)
returns varchar
language javascript
AS
$$
    // This is an independent transaction. Anything inserted as part of this
    // transaction is committed or rolled back based on this transaction.
    snowflake.execute (
        {sqlText: "begin transaction"}
        );
    snowflake.execute (
        {sqlText: "insert into log_table values ('" + MESSAGE + "')"}
        );
    snowflake.execute (
        {sqlText: "commit"}
        );

    // Dummy value.
    return "";
$$;

create procedure update_data()
returns varchar
language javascript
AS
$$
    snowflake.execute (
        {sqlText: "begin transaction"}
        );
    snowflake.execute (
        {sqlText: "insert into data_table (id) values (17)"}
        );
    snowflake.execute (
        {sqlText: "call log_message('You should see this saved.')"}
        );
    snowflake.execute (
        {sqlText: "rollback"}
        );

    // Dummy value.
    return "";
$$;

Call the stored procedure:

begin transaction;
call update_data();
rollback;

The data table is empty because the transaction was rolled back:

select * from data_table;
+----+
| ID |
|----|
+----+

However, the logging table is not empty; the insert into the logging table was done in a separate transaction from the insert into data_table.

select * from log_table;
+----------------------------+
| MESSAGE                    |
|----------------------------|
| You should see this saved. |
+----------------------------+

Examples of Scoped Transactions and Stored Procedures

The next few examples use the tables and stored procedures shown below. By passing appropriate parameters, the caller can control where BEGIN TRANSACTION, COMMIT, and ROLLBACK statements are executed inside the stored procedures.

Create the tables:

create table tracker_1 (id integer, name varchar);
create table tracker_2 (id integer, name varchar);
create table tracker_3 (id integer, name varchar);

This procedure is the enclosing stored procedure, and depending upon the parameters passed to it, can create an enclosing transaction.

create procedure sp1_outer(
    USE_BEGIN varchar,
    USE_INNER_BEGIN varchar,
    USE_INNER_COMMIT_OR_ROLLBACK varchar,
    USE_COMMIT_OR_ROLLBACK varchar
    )
returns varchar
language javascript
AS
$$
    // This should be part of the outer transaction started before this
    // stored procedure was called. This should be committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (11, 'p1_alpha')"}
        );

    // This is an independent transaction. Anything inserted as part of this
    // transaction is committed or rolled back based on this transaction.
    if (USE_BEGIN != '')  {
        snowflake.execute (
            {sqlText: USE_BEGIN}
            );
        }
    snowflake.execute (
        {sqlText: "insert into tracker_2 values (12, 'p1_bravo')"}
        );
    // Call (and optionally begin/commit-or-rollback) an inner stored proc...
    var command = "call sp2_inner('";
    command = command.concat(USE_INNER_BEGIN);
    command = command.concat("', '");
    command = command.concat(USE_INNER_COMMIT_OR_ROLLBACK);
    command = command.concat( "')" );
    snowflake.execute (
        {sqlText: command}
        );
    if (USE_COMMIT_OR_ROLLBACK != '') {
        snowflake.execute (
            {sqlText: USE_COMMIT_OR_ROLLBACK}
            );
        }

    // This is part of the outer transaction started before this
    // stored procedure was called. This is committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (13, 'p1_charlie')"}
        );

    // Dummy value.
    return "";
$$;

This procedure is the inner stored procedure, and depending upon the parameters passed to it, can create an enclosed transaction.

create procedure sp2_inner(
    USE_BEGIN varchar,
    USE_COMMIT_OR_ROLLBACK varchar)
returns varchar
language javascript
AS
$$
    snowflake.execute (
        {sqlText: "insert into tracker_2 values (21, 'p2_alpha')"}
        );

    if (USE_BEGIN != '')  {
        snowflake.execute (
            {sqlText: USE_BEGIN}
            );
        }
    snowflake.execute (
        {sqlText: "insert into tracker_3 values (22, 'p2_bravo')"}
        );
    if (USE_COMMIT_OR_ROLLBACK != '')  {
        snowflake.execute (
            {sqlText: USE_COMMIT_OR_ROLLBACK}
            );
        }

    snowflake.execute (
        {sqlText: "insert into tracker_2 values (23, 'p2_charlie')"}
        );

    // Dummy value.
    return "";
$$;

Commit the Middle Level of Three Levels

This example contains 3 transactions. This example commits the “middle” level (the transaction enclosed by the outer-most transaction and enclosing the inner-most transaction). This rolls back the outer-most and inner-most transactions.

begin transaction;
insert into tracker_1 values (00, 'outer_alpha');
call sp1_outer('begin transaction', 'begin transaction', 'rollback', 'commit');
insert into tracker_1 values (09, 'outer_charlie');
rollback;

The result is that only the rows in the middle transaction (12, 21, and 23) are committed. The rows in the outer transaction and the inner transaction are not committed.

-- Should return only 12, 21, 23.
select id, name from tracker_1
union all
select id, name from tracker_2
union all
select id, name from tracker_3
order by id;
+----+------------+
| ID | NAME       |
|----+------------|
| 12 | p1_bravo   |
| 21 | p2_alpha   |
| 23 | p2_charlie |
+----+------------+

Roll Back the Middle Level of Three Levels

This example contains 3 transactions. This example rolls back the “middle” level (the transaction enclosed by the outer-most transaction and enclosing the inner-most transaction). This commits the outer-most and inner-most transactions.

begin transaction;
insert into tracker_1 values (00, 'outer_alpha');
call sp1_outer('begin transaction', 'begin transaction', 'commit', 'rollback');
insert into tracker_1 values (09, 'outer_charlie');
commit;

The result is that all rows except the rows in the middle transaction (12, 21, and 23) are committed.

select id, name from tracker_1
union all
select id, name from tracker_2
union all
select id, name from tracker_3
order by id;
+----+---------------+
| ID | NAME          |
|----+---------------|
|  0 | outer_alpha   |
|  9 | outer_charlie |
| 11 | p1_alpha      |
| 13 | p1_charlie    |
| 22 | p2_bravo      |
+----+---------------+

Using Error Handling with Transactions in Stored Procedures

The following code shows simple error handling for a transaction in a stored procedure. If the parameter value ‘fail’ is passed, the stored procedure tries to delete from two tables that exist and one table that doesn’t exist, and the stored procedure catches the error and returns an error message. If the parameter value ‘fail’ is not passed, the procedure tries to delete from two tables that do exist, and succeeds.

Create the tables and stored procedure:

begin transaction;

create table parent(id integer);
create table child (child_id integer, parent_ID integer);

-- ----------------------------------------------------- --
-- Wrap multiple related statements in a transaction,
-- and use try/catch to commit or roll back.
-- ----------------------------------------------------- --
-- Create the procedure
create or replace procedure cleanup(FORCE_FAILURE varchar)
  returns varchar not null
  language javascript
  as
  $$
  var result = "";
  snowflake.execute( {sqlText: "begin transaction;"} );
  try {
      snowflake.execute( {sqlText: "delete from child where parent_id = 1;"} );
      snowflake.execute( {sqlText: "delete from parent where id = 1;"} );
      if (FORCE_FAILURE === "fail")  {
          // To see what happens if there is a failure/rollback,
          snowflake.execute( {sqlText: "delete from no_such_table;"} );
          }
      snowflake.execute( {sqlText: "commit;"} );
      result = "Succeeded";
      }
  catch (err)  {
      snowflake.execute( {sqlText: "rollback;"} );
      return "Failed: " + err;   // Return a success/error indicator.
      }
  return result;
  $$
  ;

commit;

Call the stored procedure and force an error:

call cleanup('fail');
+----------------------------------------------------------+
| CLEANUP                                                  |
|----------------------------------------------------------|
| Failed: SQL compilation error:                           |
| Object 'NO_SUCH_TABLE' does not exist or not authorized. |
+----------------------------------------------------------+

Call the stored procedure without forcing an error:

call cleanup('do not fail');
+-----------+
| CLEANUP   |
|-----------|
| Succeeded |
+-----------+