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. The transaction can include both reads and writes. 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. A transaction can be started implicitly in the following situations:

  • After AUTOCOMMIT has been turned off, the next DML statement (if any) implicitly starts a transaction.

  • While AUTOCOMMIT is off, the first DML statement (if any) after a COMMIT or ROLLBACK implicitly starts a transaction.

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.

In addition, if the AUTOCOMMIT state changed (from false to true or vice versa), the currently executing transaction will COMMIT.

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.

Transactions and Multi-threading

Although multiple sessions cannot share the same transaction, multiple threads using a single connection share the same session, and thus share the same transaction. This can lead to unexpected results, such as one thread rolling back work done in another thread.

This situation can occur when a client application using a Snowflake driver (such as the Snowflake JDBC Driver) or connector (such as the Snowflake Connector for Python) is multi-threaded. If two or more threads share the same connection, then those threads also share the current transaction in that connection. A BEGIN, COMMIT, or ROLLBACK by one thread affects all threads using that shared connection. If the threads are running asynchronously, the results can be unpredictable.

Similarly, changing the AUTOCOMMIT setting in one thread affects the AUTOCOMMIT setting in all other threads that use the same connection.

Note that changing the state of the AUTOCOMMIT setting commits and existing transaction.

Snowflake recommends that multi-threaded client programs do at least one of the following:

  • Use a separate connection for each thread.

    Note that even with separate connections, your code can still hit race conditions that generate unpredicable output; for example, one thread might delete data before another thread tries to update it.

  • Execute the threads synchronously rather than asynchronously, to control the order in which steps are performed.

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 might 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 typically rolls back the transaction after it has been idle for four hours.