Useful Commands for Snowflake Transactions

By:   |   Updated: 2022-12-28   |   Comments   |   Related: 1 | 2 | 3 | More > Other Database Platforms


Problem

In the previous articles of this series, we discussed Snowflake transactions in contrast with SQL Server transactions. In this article, we will explore some essential commands for working with transactions in Snowflake. These commands are specific to Snowflake and can be helpful for SQL Server developers in starting with Snowflake development.

Solution

To begin with, let's create a test environment.

We will open a new worksheet in the Snowflake Web UI, copy and paste the code below:

CREATE OR REPLACE TABLE TESTDB.TESTSCHEMA.TableA ( 
    ID INT, 
    Val CHAR(1) 
);
 
CREATE OR REPLACE TABLE TESTDB.TESTSCHEMA.TableB( 
        ID INT IDENTITY, 
        Val CHAR(1) 
);

This code creates two tables:

snowflake transactions

Next, we will open a new worksheet and will use the following code:

--Session 1 
BEGIN TRANSACTION; 
 
INSERT INTO TESTDB.TESTSCHEMA.TableA (Val) 
VALUES ('A'), ('B'); 
 
SELECT CURRENT_TRANSACTION(); 
 
COMMIT; 
 
SELECT CURRENT_TRANSACTION(); 
 
SELECT LAST_TRANSACTION(); 

CURRENT_TRANSACTION()

In this step, we will run only the highlighted part of the code:

snowflake transactions

This code uses the first command in our list – CURRENT_TRANSACTION(), which returns the transaction ID of an open transaction in the current session. In the picture above, we can see that we got the current transaction ID, which we can easily copy from the Web UI.

SHOW TRANSACTIONS

The next command we are going to discuss is SHOW TRANSACTIONS. Let's run this command in a different worksheet:

SHOW TRANSACTIONS; 

This command returns all running transactions for the current user:

snowflake transactions

We received the same transaction ID as in the previous query, and this transaction is in the running state. Please note that if we use the SHOW TRANSACTIONS IN ACCOUNT command, we will get all running transactions for all users in the account. Now, let's copy the code below into a new worksheet:

--Session 2 
BEGIN TRANSACTION; 
 
INSERT INTO TESTDB.TESTSCHEMA.TableB (Val) 
VALUES ('C'), ('D'); 
 
SELECT CURRENT_TRANSACTION(); 
 
ROLLBACK; 
 
SELECT CURRENT_TRANSACTION(); 
 
SELECT LAST_TRANSACTION(); 

After executing the selected part of the code, we will get the transaction ID for the second transaction:

snowflake transactions

If we run the command again, we can see that we now have two active transactions for the current user:

SHOW TRANSACTIONS; 
snowflake transactions

DESCRIBE TRANSACTION

Our third command is DESCRIBE TRANSACTION (shortened - DESC TRANSACTION version can also be used) which receives the transaction ID and provides information about that transaction (start time, state, etc.). Let's copy the transaction IDs of both our transactions and use them in the DESCRIBE TRANSACTION command:

DESCRIBE TRANSACTION 1668991042603000000;
 
DESCRIBE TRANSACTION 1668991514777000000;

As expected, we can see the transaction-related information as the result of this command:

snowflake transactions
snowflake transactions

LAST_TRANSACTION()

In this step, we will roll back the second transaction:

snowflake transactions

We can see that the CURRENT_TRANSACTION() returns an empty result as there is no transaction in the session. However, if we run the LAST_TRANSACTION() command, we can get the ID of the rolled-back transaction:

snowflake transactions

The LAST_TRANSACTION() command returns the last transaction ID in the session (which was either committed or rolled back). If we run the SHOW TRANSACTION command again, we can see only one – the first transaction:

snowflake transactions

Nevertheless, we can receive information about the second-rolled-back transaction by issuing the DESCRIBE TRANSACTION command. We can see that now the "sate" or the transaction is "rolled back":

snowflake transactions

When we commit the first transaction, in this case as well, we will receive an empty result after running the CURRENT_TRANSACTION() command:

snowflake transactions

The LAST_TRANSACTION() command will return the committed transaction ID:

snowflake transactions

An empty result will be received after running the SHOW TRANSACTION command:

snowflake transactions

As in the previous example, we will be able to receive the transaction's description by using its ID. In this case, the "state" is "committed":

snowflake transactions

Conclusion

To sum up, Snowflake provides a set of useful commands for working with transactions. Understanding and mastering these commands can be beneficial for developers working with Snowflake transactions.

Next Steps

For additional information, please follow the links below:






get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips


Article Last Updated: 2022-12-28

Comments For This Article

















get free sql tips
agree to terms