In this post I would like to highlight some aspects of using Snowflake Scripting and how it relates to Snowflake transactions. We will see some rather fundamental information here, but even an experienced developer can find interesting information, especially since Snowflake’s behavior sometimes differs from what we have in other DB systems (such as Oracle). We will start by comparing the BEGIN; statement with the BEGIN keyword in Snowflake Scripting. We will also see how the AUTOCOMMIT mechanism works and how to get around it when programming in Snowflake Scripting. At the end of the post, we will see the concept of scoped transactions and its effects in an example scenario.
As a reminder, a transaction is a series of SQL statements that can be committed or rolled back. ACID conditions are satisfied by such series of statements (http://en.wikipedia.org/wiki/ACID). This means that commands grouped into transactions meet the following requirements:
– atomicity – all commands in a transaction are written to the database at the same time. A transaction is not divided into smaller components.
– isolation – modifications made within a transaction are invisible to other transactions until they are approved
– durability – once a transaction is committed, the database guarantees that it is permanently saved (and not just cached)
– consistency – a transaction after commit should leave the database in a consistent state (keeping in mind that Snowflake does not enforce most of the defined constraints as described at https://docs.snowflake.com/en/sql-reference/constraints.html)
In Snowflake we can start a transaction using the BEGIN TRANSACTION command. According to the documentation (https://docs.snowflake.com/en/sql-reference/sql/begin.html) “BEGIN TRANSACTION” is recommended however we can use its shorthand “BEGIN”. Let’s check:
CREATE TABLE table1 (i int);
Ok. We have a sample table. Let’s play. Please run the following example line by line:
BEGIN;
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;
As you can see above – a transaction started with the BEGIN command is committed with the COMMIT command, and an invalid INSERT does not abort the transaction.
BEGIN;
INSERT INTO table1 (i) VALUES (3);
INSERT INTO table1 (i) VALUES (4);
ROLLBACK;
SELECT i FROM table1 ORDER BY i;
As we can see – transaction rolled back with ROLLBACK doesn’t change data at all.
Let’s see what happens when we use a similar command sequence in the Snowlfake Scripting block (https://docs.snowflake.com/en/developer-guide/snowflake-scripting/blocks.html):
BEGIN
INSERT INTO table1 (i) VALUES (5);
INSERT INTO table1 (i) VALUES (6);
ROLLBACK;
END;
SELECT i FROM table1 ORDER BY i;
That might be surprising. After executing the SELECT command, we see that row 5 and 6 are committed, even though we executed the ROLLBACK statement in the Snowflake block and – what’s more – Snowflake executed the command without any error, so we expect that the rows are indeed rolled back. As we can see – this is not the case here. It seems that ROLLBACK did not work. What happened and why?
The solution: the beginning of the Snowflake Scripting block written with the BEGIN keyword is quite different from the BEGIN statement in SQL. Despite the (unfortunate in this case) similarity, the BEGIN keyword, as the start of the Snowflake Scripting block, does not start a transaction. Since the INSERT statement inside the block is executed without an active transaction then we see the action of the AUTOCOMMIT option (https://docs.snowflake.com/en/sql-reference/transactions.html#label-txn-autocommit). The AUTOCOMMIT option – enabled by default – causes any DML command that is not in an explicitly started transaction (even inside a Snowflake Scripting block) to be automatically and immediately committed. Since this is the case, the ROLLBACK present at the end has nothing to do, since we have no active transaction at the time this command is executed. All inserts are already automatically committed. ROLLBACK used with an inactive transaction does not raise any error, as described in the documentation here https://docs.snowflake.com/en/sql-reference/transactions.html#mixing-implicit-and-explicit-starts-and-ends-of-a-transaction
Hmm. Ok. Fair enought (if not – please ask a question in the comments). The next question is: can we manage transactions inside the Snowflake Scripting block? Yes, we can. See the following example:
BEGIN
BEGIN TRANSACTION;
INSERT INTO table1 (i) VALUES (7);
INSERT INTO table1 (i) VALUES (8);
INSERT INTO table1 (i) VALUES (9);
ROLLBACK;
END;
SELECT i FROM table1 ORDER BY i;
Note that you cannot use a shortened version of the “BEGIN” command inside the Snowflake Scripting block. “BEGIN;” will result in an error report. Therefore, inside the block we have “BEGIN TRANSACTION;”. However, the rows inserted inside the block are correctly rolled back, indicating that the transaction was successfully started with “BEGIN TRANSACTION;”. Conclusion: we can manage transactions in the Snowflake Scripting block.
Let’s look at another example:
BEGIN;
BEGIN
INSERT INTO table1 (i) VALUES (10);
INSERT INTO table1 (i) VALUES (11);
END;
ROLLBACK;
SELECT i FROM table1 ORDER BY i;
The above example behaves very similarly to the previous one, but in the last example the transaction is started outside the script block and also outside the script block is rolled back. From the SELECT results, however, we see that the INSERT commands executed in the Snowflake Scripting block are not saved in table. So the conclusion is therefore the following: the commands executed in the Snowflake Scripting block fall into the transaction surrounding that block.
Hmm. Can we try something else, like executing a ROLLBACK inside a Snowflake Scripting block to undo a transaction started before the Snowflake Scripting block? Let’s see at the another example:
BEGIN;
BEGIN
INSERT INTO table1 (i) VALUES (12);
INSERT INTO table1 (i) VALUES (13);
ROLLBACK;
END;
Uncaught exception of type 'STATEMENT_ERROR' on line 5 at position 1 : Modifying a transaction that has started at a different scope is not allowed.
Why do we get such an error? Snowflake does not allow to commit or rollback of a transaction started in another scope (other than Snowflake Scripting block in our case). This is described in the documentation: https://docs.snowflake.com/en/sql-reference/transactions.html#stored-procedures-and-transactions
We have already seen that we can manage transactions in Snowflake Scripting. We have also seen that we can run a Snowflake Scripting block inside an already started transaction. Can we try to combine this?
BEGIN;
INSERT INTO table1 (i) VALUES (14);
BEGIN
BEGIN TRANSACTION;
INSERT INTO table1 (i) VALUES (15);
INSERT INTO table1 (i) VALUES (16);
ROLLBACK;
END;
INSERT INTO table1 (i) VALUES (17);
COMMIT;
SELECT i FROM table1 ORDER BY i;
As you can see, rows 14 and 17 were committed but rows 15 and 16 were rolled back. It seems that Snowflake treats a transaction inside Snowflake Scripting independently from a transaction started outside the block. This is called a scoped transaction and is described here https://docs.snowflake.com/en/sql-reference/transactions.html#scoped-transactions. The concept is similar to autonomous transaction in Oracle DB.
The scoped transaction is started when the BEGIN TRANSACTION statement is executed and Snowflake treats this transaction as completely separate (not nested!) from the outer transaction. The scoped transaction does not see changes made in the outer transaction that have not yet been committed, which can lead to interesting results:
BEGIN;
INSERT INTO table1 (i) VALUES (18);
DECLARE
v1 numeric;
v2 numeric;
v3 numeric;
BEGIN
select count(*) INTO v1 FROM table1 WHERE i = 18;
BEGIN TRANSACTION;
select count(*) INTO v2 FROM table1 WHERE i = 18;
ROLLBACK;
select count(*) INTO v3 FROM table1 WHERE i = 18;
RETURN 'Before BEGIN TRANSACTION: ' || v1 || ', in ' || v2 || ', after: ' || v3;
END;
As we can see, row 18 is visible inside the block, but is invisible from inside the scoped transaction. This means that the BEGIN TRANSACTION statement; has started a separate transaction from the outer one. This is important to watch out for, because it means that the scoped transaction may compete for resources blocked by the outer transaction, which could lead to a deadlock. We can also note that the ROLLBACK instruction is only applied to the scoped transaction and not to the external transaction. Execution of the following code will confirm that the external transaction has not yet completed:
ROLLBACK;
SELECT i FROM table1 ORDER BY i;
Finally, let’s check a simple example – what happens in the simple case when we start a transaction inside the Snowflake Scripting block, but do not perform a commit or rollback:
BEGIN
BEGIN TRANSACTION;
INSERT INTO table1 (i) VALUES (19);
END;
Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back.
So be careful to process transactions correctly, especially with conditional statements and exception handling.
To summarize:
– Snowflake Scripting cooperates with transactions
– The BEGIN keyword that starts a Snowflake block does not start a transaction
– The AUTOCOMMIT mechanism is the reason why all executed DML statements (without an active transaction) are immediately committed upon successful completion.
– You can use the BEGIN TRANSACTION statement to start an explicit transaction in Snowflake Scripting, but in that case you must also complete it (via COMMIT or ROLLBACK)
– A transaction started explicitly in Snowflake Scripting is an autonomous transaction.
– If you do not start a transaction explicitly in Snowflake Scripting, and there is an active external transaction – all DML commands in your block belong to this external transaction.
