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) as a single unit. 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

By default, 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. This behavior is controlled with the AUTOCOMMIT parameter.

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. DDL statements commit and end a multi-statement transaction. DDL statements are always autocommitted, regardless of the AUTOCOMMIT setting. Note that CREATE TABLE AS SELECT (CTAS) statements are treated as DDL; as a result, they are also autocommitted.

Statement Rollback

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

Isolation Level

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

Read Committed Isolation

With read committed isolation, a statement sees only data that was committed before the statement began. It never sees uncommitted data.

When a statement is executed inside a multi-statement transaction:

  • A statement sees only data that was committed before the statement began. 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.

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

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

INSERT operations acquire partition locks within a table only for micro-partitions they add or might modify. This allows INSERT statements to add micro-partitions to the same table without necessarily locking any existing partitions, i.e. INSERTs can generally execute without being blocked by other DML statements.

Note

Although Snowflake allows multiple clients to INSERT rows in parallel even when other DML statements are executing, users might see contention problems if too many INSERT statements execute in parallel.

DELETE, UPDATE, and MERGE operations apply partition locks on all partitions of the table.

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.

Lock Wait Timeout

A blocked statement either acquires a lock on the resource it was waiting for or times 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 back, 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 is 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.

If the transaction is left open, Snowflake will typically roll back the transaction after it has been idle for four hours.