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, regardless of the AUTOCOMMIT setting. Executing a DDL statement within an open transaction does the following: - Commits the open transaction. - Executes the DDL statement and commits it.

CTAS statements (CREATE TABLE AS SELECT ...) are treated as DDL; as a result, they are also autocommitted.

Because a DDL statement is its own transaction, you cannot roll back a DDL statement; the transaction containing the DDL has completed before you can execute the ROLLBACK. Undoing most DDL statements requires you to execute another DDL statement (e.g. DROP a table that you created, ALTER a table back to its original state if you ALTERed it, or UNDROP or re-create an object that you dropped).

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 a resource, such as a table, while that resource is being modified. Locks block other statements from modifying the resource until the lock is released.

The following guidelines apply in most situations:

  • COMMIT operations (including both autocommit and explicit COMMIT) lock resources, but usually only briefly.

  • UPDATE, DELETE, and MERGE statements hold locks that generally prevent them from running in parallel with other UPDATE, DELETE, and MERGE statements.

  • Most INSERT and COPY statements write only new partitions. Those statements often can run in parallel with other INSERT and COPY operations, and sometimes can run in parallel with an UPDATE, DELETE, or MERGE statement.

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.