By: Sergey Gigoyan | Updated: 2023-08-09 | Comments | Related: More > Snowflake
Problem
In the previous two articles of this series, Using Stored Procedures inside Snowflake Transactions and Snowflake for SQL Server Developers - Transactions in Stored Procedures, we discussed how to use Snowflake stored procedures inside transactions and correctly include transactions into stored procedures. This article will discuss the Snowflake concept of autonomous scoped transactions, also known as scoped transactions.
Solution
As discussed in the previous articles, Snowflake stored procedures can include transactions. In turn, stored procedures can be called within transactions. Thus, a stored procedure containing transactions can be included in another transaction.
Snowflake does not consider these inner transactions as nested transactions but autonomous. This means that each inner transaction is treated as a separate transaction and is called an autonomous scoped transaction or, in short, a scoped transaction.
The beginning and end of each transaction determine which statements are included in that transaction. Each SQL statement belongs to a single transaction. The ROLLBACK or COMMIT statements of the enclosed (inner) transactions are not affected by the ROLLBACK or COMMIT statements of the enclosing (outer) transactions.
Now, let's illustrate the behavior of scoped transactions with examples. First, we will create a test environment:
CREATE DATABASE TestDB; CREATE SCHEMA TestSchema; USE DATABASE TestDB; CREATE OR REPLACE TABLE TestDB.TestSchema.TestTable ( ID INT NOT NULL, Val INT );
In the example below, we create two stored procedures. Both include transactions, and the second procedure calls the first procedure within a transaction:
CREATE OR REPLACE PROCEDURE TestSchema.InsertData_1()
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
BEGIN TRANSACTION;
INSERT INTO TestTable (ID, Val) VALUES (2, 200);
INSERT INTO TestTable (ID, Val) VALUES (3, 300);
COMMIT;
END;
CREATE OR REPLACE PROCEDURE TestSchema.InsertData_2()
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
BEGIN TRANSACTION;
INSERT INTO TestTable (ID, Val) VALUES (1, 100);
CALL TestDB.TestSchema.InsertData_1();
INSERT INTO TestTable (ID, Val) VALUES (4, 400);
COMMIT;
END;
Now, we will call the second procedure (which, in turn, will call the first one within its body):
CALL TestDB.TestSchema.InsertData_2();
SELECT * FROM TestDB.TestSchema.TestTable;
As a result, all transactions are successfully completed:
Next, let's do another test. Let's modify the second stored procedure to do a ROLLBACK instead of COMMIT:
TRUNCATE TABLE TestDB.TestSchema.TestTable;
CREATE OR REPLACE PROCEDURE TestSchema.InsertData_2()
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
BEGIN TRANSACTION;
INSERT INTO TestTable (ID, Val) VALUES (1, 100);
CALL TestDB.TestSchema.InsertData_1();
INSERT INTO TestTable (ID, Val) VALUES (4, 400);
ROLLBACK;
END;
CALL TestDB.TestSchema.InsertData_2();
SELECT * FROM TestDB.TestSchema.TestTable;
As a result, only SQL statements in the enclosing transaction are rolled back, and the statements of the enclosed transaction are committed:
This means that the statements in the inner transaction are not affected by the ROLLBACK statement of the outer transaction and are committed independently.
In the following example, we will modify the stored procedures to do another test:
TRUNCATE TABLE TestDB.TestSchema.TestTable;
CREATE OR REPLACE PROCEDURE TestSchema.InsertData_1()
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
INSERT INTO TestTable (ID, Val) VALUES (2, 200);
BEGIN TRANSACTION;
INSERT INTO TestTable (ID, Val) VALUES (3, 300);
COMMIT;
INSERT INTO TestTable (ID, Val) VALUES (4, 400);
END;
CREATE OR REPLACE PROCEDURE TestSchema.InsertData_2()
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
BEGIN TRANSACTION;
INSERT INTO TestTable (ID, Val) VALUES (1, 100);
CALL TestDB.TestSchema.InsertData_1();
INSERT INTO TestTable (ID, Val) VALUES (5, 500);
ROLLBACK;
END;
CALL TestDB.TestSchema.InsertData_2();
SELECT * FROM TestDB.TestSchema.TestTable;
In the code, we can see that the first and the last INSERT statements of the first stored procedure are within the scope of the transaction started in the second procedure, and only the middle INSERT statement is within the scope of the transaction started inside the first procedure. Therefore, the ROLLBACK statement of the second procedure rolls back all statements besides the "INSERT INTO TestTable (ID, Val) VALUES (3, 300);" statement:
Now, it's time to discuss some problems associated with the incorrect usage of BEGIN and COMMIT statements inside stored procedures. In the example below, we removed the COMMIT (or ROLLBACK) statement in the second procedure:
TRUNCATE TABLE TestDB.TestSchema.TestTable;
CREATE OR REPLACE PROCEDURE TestSchema.InsertData_1()
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
BEGIN TRANSACTION;
INSERT INTO TestTable (ID, Val) VALUES (2, 200);
INSERT INTO TestTable (ID, Val) VALUES (3, 300);
COMMIT;
END;
CREATE OR REPLACE PROCEDURE TestSchema.InsertData_2()
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
BEGIN TRANSACTION;
INSERT INTO TestTable (ID, Val) VALUES (1, 100);
CALL TestDB.TestSchema.InsertData_1();
INSERT INTO TestTable (ID, Val) VALUES (4, 400);
END;
CALL TestDB.TestSchema.InsertData_2();
SELECT * FROM TestDB.TestSchema.TestTable;
Since there is no COMMIT or ROLLBACK statement in the second procedure to complete the transaction started inside that procedure, the transaction will be automatically rolled back:
Only the enclosed stored procedure changes will be inserted:
In the next example, we will try to start a transaction in one stored procedure and complete it in another:
TRUNCATE TABLE TestDB.TestSchema.TestTable;
CREATE OR REPLACE PROCEDURE TestSchema.InsertData_1()
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
BEGIN TRANSACTION;
INSERT INTO TestTable (ID, Val) VALUES (2, 200);
INSERT INTO TestTable (ID, Val) VALUES (3, 300);
END;
CREATE OR REPLACE PROCEDURE TestSchema.InsertData_2()
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
INSERT INTO TestTable (ID, Val) VALUES (1, 100);
CALL TestDB.TestSchema.InsertData_1();
INSERT INTO TestTable (ID, Val) VALUES (4, 400);
COMMIT;
END;
CALL TestDB.TestSchema.InsertData_2();
SELECT * FROM TestDB.TestSchema.TestTable;
We will receive an error because it is not allowed in Snowflake:
Only the first INSERT statement is completed, as it is out of the scope of the transaction:
Conclusion
In summary, enclosed transactions in Snowflake are not considered nested transactions and are handled autonomously. These transactions are called scoped transactions and are controlled only by transaction control commands defined within the scope of that transaction. They remain unaffected by the outer COMMIT or ROLLBACK commands.
Next Steps
For more information, please follow the links below:
- Transactions in Snowflake Stored Procedures with Code Examples (mssqltips.com)
- Snowflake Transactions vs SQL Server Transactions (mssqltips.com)
- DDL commands in Transactions in SQL Server versus Oracle (mssqltips.com)
- Transactions — Snowflake Documentation
About the author
Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-08-09
