Transactions

A transaction is a set of SQL statements, both reads and writes, that are processed as a unit. All the statements in the transaction are either applied (i.e. committed) or undone (i.e. rolled back). Similar to other databases, Snowflake transactions guarantee ACID properties.

In this Topic:

Scope of a Snowflake Transaction

A transaction is associated with a single session. Multiple sessions cannot share the same transaction.

A transaction can be started explicitly by executing a BEGIN statement. After a transaction has been started, it must be closed by executing either a COMMIT or ROLLBACK statement. If a session with an open transaction is closed, then the open transaction is rolled back.

Every Snowflake transaction is assigned a unique start time (includes milliseconds), which serves as the ID for the transaction. For some examples of transaction start times, see the Examples for BEGIN.

Autocommit

A DML statement executed without explicitly starting a transaction is automatically committed on success or rolled back on failure at the end of the statement. This behavior is called autocommit.

DDL statements are always autocommitted. Executing a DDL statement within an open transaction will first COMMIT the open transaction and then execute the DDL statement.

Statement Rollback

If a DML statement executed in an explicitly-started transaction fails, the changes made by the DML will be rolled back. However, the transaction will be kept open, until the transaction is committed or rolled back.

Isolation Level

Snowflake transactions support read committed isolation. Read committed is the only isolation level currently supported.

Read Committed Isolation

With read committed isolation, a statement only sees data that was committed before the statement began. It never sees uncommitted data or changes committed from concurrent transactions during its execution.

However, when executed within a transaction, a statement does see the changes made by previous statements executed within the same transaction, even though those changes are not yet committed.

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.

Resource Locking

Transactional operations acquire locks on resources while they are being modified. This blocks other statements from modifying the resource concurrently until the lock holder completes the changes.

There are two types of locks:

Partition
DML operations acquire partition locks within a table only for micro-partitions they might modify. This schema allows INSERT statements to add micro-partitions to the same table without locking any existing partitions, i.e. inserts can execute without being blocked by other DMLs.
Table
A transaction only acquires a table lock during the commit phase, a length of time proportional to the amount of data inserted or modified in the transaction but overall very short. During the commit phase, INSERT and DML operations can continue. A table lock only blocks a separate transaction from performing a concurrent commit.

The TYPE column in the SHOW LOCKS output indicates whether an active lock was acquired on a table or on the partitions in a table.

Locks held by a statement are released on COMMIT or ROLLBACK of the transaction. In the event of a ROLLBACK, any locks acquired by that statement will be released.

Lock Wait Timeout

A blocked statement will either acquire a lock on the resource it was waiting for or time 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 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 backed, but the transaction itself is left open and must be committed or rolled back.

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 will be left in a detached state, including any locks that the transaction is holding on resources. If this happens, you will likely want/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.