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.

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. Special rules for stored procedures and transactions are described in Stored Procedures and Transactions.

Scope of a Snowflake Transaction

A transaction has a “scope”, which determines the statements that are included in the transaction. The starting point and ending point of the transaction determine which statements are included in the transaction. The start and end can be explicit or implicit.

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.

Explicit Transactions

A transaction can be started explicitly by executing a BEGIN statement.

A transaction can be ended explicitly by executing COMMIT or ROLLBACK.

If a BEGIN TRANSACTION is executed while a transaction is open (e.g. if a second BEGIN TRANSACTION is executed after a prior BEGIN TRANSACTION and before that prior BEGIN TRANSACTION’s corresponding COMMIT or ROLLBACK is executed), then the second BEGIN TRANSACTION is ignored. Users should avoid this, 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.

Note

Explicit transactions should contain only DML statements and query statements. DDL statements implicitly COMMIT open 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 and stops are different from the rules for explicit transactions.

The rules for stopping and starting depend upon whether the statement is a DDL statement or a DML or 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 outside a transaction, then the DDL statement executes as though it were immediately preceded by a BEGIN TRANSACTION and immediately followed by a COMMIT:

BEGIN TRANSACTION;
ddl_statement;
COMMIT;      -- Or rollback if the statement fails.

(If the DDL statement fails, then it is rolled back instead of committed.)

If a DDL statement is inside an open transaction, the statement executes as though it were immediately preceded by a COMMIT and BEGIN TRANSACTION, and followed by a COMMIT. In the example below, the lower-case code shows the explicit statements, while the upper-case code shows the implicit BEGIN TRANSACTION and COMMIT statements caused by the DDL statement:

begin;
...
COMMIT;
BEGIN TRANSACTION;
ddl_statement;
COMMIT;

(If the DDL statement fails, then it is rolled back instead of committed.)

In other words, the DDL statement commits any open transaction, then executes in its own 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. Undoing most DDL statements requires you to execute another DDL statement (e.g. DROP a table that you created, ALTER a table back to its original state if you ALTERed it, or UNDROP or re-create an object that you dropped).

A DDL statement does not start a new transaction after it commits itself. However, if the 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 AUTOCOMMIT. 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, each statement (outside an explicit transaction) is automatically committed if it succeeds, and automatically rolled back if it fails.

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. implicit rollback, DDL statement, or 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 open):

    • The execution of a DDL statement.

    • The execution of a statement that enables AUTOCOMMIT.

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

    • The end of a session.

    • The end of a stored procedure that contained a BEGIN TRANSACTION (regardless of whether the BEGIN TRANSACTION was implicit or explicit).

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 is kept open 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, stored procedures follow the same rules for transactions as described above in Scope of a Snowflake Transaction. 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.

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 open when the stored procedure finishes, then that transaction is silently rolled back at the end of the stored procedure.

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. In other words, a BEGIN TRANSACTION must have a corresponding COMMIT (or ROLLBACK) at the same “level”. Failure to follow this rule can result in implicit ROLLBACK.

If a stored procedure contains a transaction, that transaction can contain only part of the body of the stored procedure. For example, the following is valid:

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

        BEGIN TRANSACTION;
        statement2;
        COMMIT;

        statement3;
        ...

    $$;

Non-Overlapping Transactions

A stored procedure can contain more than one transaction, as long as each transaction starts and completes within the stored procedure, and as long as the transactions do not overlap. For example, the following is valid inside a stored procedure:

...
statement1;

begin;
statement2;
commit;

statement3;

begin;
statement4;
commit;

statement5;

...

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 (and no corresponding COMMIT or ROLLBACK was executed before the stored procedure was called).

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

  • The body of the stored procedure does not contain any 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 as part of that COMMIT.

  • 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.

Overlapping Transactions

This section describes overlapping transactions.

A stored procedure that contains a transaction can be called from within another transaction. The outer transaction can be in an outer stored procedure or can be outside any stored procedure.

The inner transaction is not treated as nested; instead, the inner transaction is a separate transaction. Snowflake calls these “autonomous scoped transactions” (or simply “scoped transactions”), because each transaction executes in a conceptually independent scope.

Note

Terminology note:

The terms “inner” and “outer” are commonly used when describing nested operations, such as nested stored procedure calls. Although Snowflake supports nested procedure calls, Snowflake does not support nested transactions; therefore, to reduce confusion, this document frequently uses the terms “enclosed” and “enclosing”, rather than “inner” and “outer”, when referring to autonomous scoped transactions, but continues to use the terms “inner” and “outer” when referring to nested procedure calls.

An enclosed transaction and enclosing transaction overlap in time, but not in content. Each SQL statement is part of only one transaction and is committed or rolled back by only one COMMIT or ROLLBACK. An enclosing ROLLBACK or COMMIT does not “undo” an enclosed COMMIT or ROLLBACK.

The following pseudo-code shows a stored procedure that both contains a transaction and is contained within a transaction:

Create a stored procedure that contains a transaction:

CREATE PROCEDURE p_enclosed ()
...
$$
    statement B;

    begin;
    statement C;
    statement D;
    commit;

    statement E;
$$;

Call the stored procedure from inside an enclosing transaction:

begin;
statement A;
call p_enclosed(); -- contains its own begin/commit
statement F;
commit;

The preceding statements are approximately (but not exactly) equivalent to the following sequence:

BEGIN TRANSACTION;
statement A;
statement B;
statement E;
statement F;
COMMIT;

BEGIN TRANSACTION;
statement C;
statement D;
COMMIT;

The sequence shown above is only approximately equivalent to the actual execution of the overlapping transactions because the transactions actually overlap in time, and Snowflake uses READ COMMITTED isolation level. In READ COMMITTED isolation, each statement sees only data and modifications that have been committed by other transactions, or that have been changed inside the current transaction. A statement does not see changes made inside other transactions that have not yet been committed.

The actual execution of the preceding statements looks more like the following:

Transaction 1

Transaction 2

Notes

BEGIN TRANSACTION;

statement A;

statement B;

Sees change made by statement A only.

BEGIN TRANSACTION;

statement C;

statement D;

Sees change made by statement C only.

COMMIT;

statement E;

Sees changes made by statements A, B, C, and D.

statement F;

Sees changes made by statements A, B, C, D, and E.

COMMIT;

Because the two transactions shown above are independent, statement C, for example, does not see the changes that the other transaction made in statements A and B because even though those changes were made before statement C executed, the changes were not committed before statement C.

However, statements E and F see changes made in statements C and D because changes C and D were committed before statements E and F ran.

This means that even though statements B and E are in the same transaction, B and E can see different values for rows that statements C and D modified.

The transactions are committed or rolled back independently. For example, if the COMMIT inside the stored procedure were replaced with a ROLLBACK, then statements A, B, E, and F would still be committed. If the COMMIT after the procedure were replaced with a ROLLBACK (and the COMMIT inside the procedure remained a COMMIT), then statements C and D would be committed, while statements A, B, E, and F would be rolled back.

The rules for autonomous scoped transactions continue to apply when stored procedures are nested. For example, if a stored procedure P1 called another stored procedure P2 from inside a BEGIN TRANSACTION/COMMIT block, and P2 contained its own transaction, then the transaction started in P2 would also run independently. For example, consider the following pseudo-code:

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 this example, there are 3 autonomous scoped transactions. One transaction contains statements A and E; one transaction contains statements B and D; and one transaction contains statement C.

Stored procedures can call themselves recursively. A recursive call is just a specific type of nested call, and follows the same transaction rules as a nested call.

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

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 is left open 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 left open, Snowflake typically rolls back the transaction after it has been idle for four hours.

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 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.

Examples

Simple Example of Scoped Transaction and Stored Procedure

This is a simple example of autonomous 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 an autonomous 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 Autonomous 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 |
+-----------+