Snowflake Transactions in Stored Procedures

By: Sergey Gigoyan | Updated: 2023-07-28 | Comments | Related: More > Snowflake

Problem

A previous article discussed how Snowflake handles stored procedures inside transactions. We demonstrated how to guarantee the consistency and atomicity of data changes made by stored procedures with transactions. Also, we discussed some limitations, like how DDL statements are executed as separate transactions in Snowflake and cannot be included in transactions.

In this article, we will continue to discuss transactions in Snowflake. More specifically, we will focus on the opposite case—including transactions inside stored procedures.

Solution

When working with stored procedures in Snowflake, it is important to understand how transactions are handled within stored procedures. Just as stored procedures can be invoked within transactions, transactions can also be called within stored procedures. However, there are some limitations and rules that are essential to consider.

Below, we will demonstrate transaction calls inside stored procedures and their limitations.

Let's create a test environment. (This is the same environment created in the previous article):

CREATE DATABASE TestDB;
 
CREATE SCHEMA TestSchema;
 
USE DATABASE TestDB;
 
CREATE OR REPLACE TABLE TestDB.TestSchema.TestTable (
   ID INT NOT NULL,
   Val INT
);

Transaction Call Inside a Stored Procedure

As mentioned above, transactions can be included in a stored procedure. Moreover, a stored procedure can contain more than one transaction. It is also worth noting that a transaction can contain the whole body of a stored procedure and some part of it. Let's illustrate several possible options for using transactions within a stored procedure.

Transaction Within a Stored Procedure Containing the Whole Body of the Procedure

In the example below, the transaction includes the entire body of the stored procedure:

CREATE OR REPLACE PROCEDURE TestSchema.InsertData()
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
    BEGIN TRANSACTION;
    
    INSERT INTO TestTable (ID, Val) VALUES (1, 100);
    INSERT INTO TestTable (ID, Val) VALUES (2, 200);
 
    COMMIT;
END;

Now, let's execute the stored procedure:

    
CALL TestDB.TestSchema.InsertData();
 
SELECT * FROM TestDB.TestSchema.TestTable;

As we can see, the data is successfully inserted:

transaction includes the entire body of the stored procedure

Transaction Within a Stored Procedure Containing the Part of the Body of the Procedure

In the next example, the transaction includes only a part of the stored procedure:

CREATE OR REPLACE PROCEDURE TestSchema.InsertData()
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
        
    INSERT INTO TestTable (ID, Val) VALUES (3, 300);
 
    
    BEGIN TRANSACTION;
    
    INSERT INTO TestTable (ID, Val) VALUES (4, 400);
    INSERT INTO TestTable (ID, Val) VALUES (5, 500);
 
    COMMIT;
 
    INSERT INTO TestTable (ID, Val) VALUES (6, 600);
        
END;
 
    
CALL TestDB.TestSchema.InsertData();
 
SELECT * FROM TestDB.TestSchema.TestTable;

More Than One Transaction Within a Stored Procedure

In the next example, there are two transactions included in a stored procedure:

CREATE OR REPLACE PROCEDURE TestSchema.InsertData()
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
        
    INSERT INTO TestTable (ID, Val) VALUES (7, 700);
 
    BEGIN TRANSACTION;
    
    INSERT INTO TestTable (ID, Val) VALUES (8, 800);
    INSERT INTO TestTable (ID, Val) VALUES (9, 900);
 
    COMMIT;
 
    INSERT INTO TestTable (ID, Val) VALUES (10, 1000);
 
    BEGIN TRANSACTION;
    
    INSERT INTO TestTable (ID, Val) VALUES (11, 1100);
    INSERT INTO TestTable (ID, Val) VALUES (12, 1200);
 
    COMMIT;
        
END;
 
    
CALL TestDB.TestSchema.InsertData();
 
SELECT * FROM TestDB.TestSchema.TestTable;

Rules and Limitations

In Snowflake, it is not possible to have transactions partly included in stored procedures. In other words, it is impossible to start a transaction outside a stored procedure and complete it within the stored procedure. And vice versa, it is impossible to start a transaction within a stored procedure and complete it outside the procedure. Additionally, it is not possible to start a transaction within a stored procedure and complete it in another stored procedure. This rule applies to transactions in nested procedures as well. If a stored procedure contains a nested stored procedure, a transaction started in the outer procedure cannot be completed in the inner procedure and vice versa. Each transaction started in the outer procedure must be completed there (committed or rolled back), and each transaction started in the inner procedure must be completed within that procedure.

In the example below, we are starting a transaction before the call of the stored procedure and trying to commit it within the stored procedure:

CREATE OR REPLACE PROCEDURE TestSchema.InsertData()
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
        
    
    INSERT INTO TestTable (ID, Val) VALUES (14, 1400);
    INSERT INTO TestTable (ID, Val) VALUES (15, 1500);
 
    COMMIT;
        
END;
 
BEGIN TRANSACTION;
    
INSERT INTO TestTable (ID, Val) VALUES (13, 1300);
    
CALL TestDB.TestSchema.InsertData();
 
 
 
SELECT * FROM TestDB.TestSchema.TestTable;

As a result, we are receiving an error message:

“Uncaught exception of type 'STATEMENT_ERROR' on line 7 at position 4 : Modifying a transaction that has started at a different scope is not allowed.”

starting a transaction before the call of the stored procedure and trying to commit it within the stored procedure

Conclusion

Understanding transaction management within stored procedures is essential for Snowflake developers. We discussed and illustrated the main rules and limitations of using transactions within stored procedures in Snowflake.

The key takeaways of this article are:

Transactions can be included in a Snowflake stored procedure.
A stored procedure can contain zero, one, or more transactions.
Transactions can include the entire body of the stored procedure or some part of it.
Transactions cannot be partly included in a stored procedure.
Transactions cannot start in one stored procedure and complete in another.
In the case of nested procedures, transactions started in the outer procedure cannot be completed in the inner procedure and vice versa. Each transaction started in a procedure must be completed in the same stored procedure.

Next Steps

For additional information, check out the following links:

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-07-28

Snowflake for SQL Server Developers - Transactions in Stored Procedures