Snowflake DML Locking, Transactions and Concurrency

1.Where a transaction starts and ends

Every concurrency question in Snowflake comes back to one thing: how long a transaction stays open. A transaction is a sequence of statements that is committed or rolled back as one unit. It belongs to exactly one session, and transactions are never nested. In this topic, DML means INSERT, UPDATE, DELETE, MERGE and TRUNCATE. DDL includes CTAS as well as the other statements that define objects.

You start a transaction explicitly with BEGIN TRANSACTION, which is the recommended synonym for BEGIN and BEGIN WORK. You end it with COMMIT or ROLLBACK. If a transaction is already active, Snowflake usually ignores a second BEGIN TRANSACTION. Avoid extra ones anyway, because they make it hard for a reader to match each COMMIT to the BEGIN it closes.

Transactions can also start and end on their own. AUTOCOMMIT is TRUE by default. While it is on, each statement outside an explicit transaction runs as its own single-statement transaction: it commits if it succeeds and rolls back if it fails. Statements inside an explicit BEGIN TRANSACTION block are not affected by AUTOCOMMIT. When AUTOCOMMIT is off, the first DML statement after a transaction ends starts a new one implicitly. That transaction stays open until something commits or rolls it back.

DDL works differently. Each DDL statement runs as its own transaction. If you run DDL while a transaction is active, Snowflake first commits the active transaction implicitly, then runs the DDL separately. That is why Snowflake advises that explicit transactions contain only DML and query statements. It is also why a DDL statement cannot be rolled back. An open transaction is rolled back implicitly when the session ends or when a stored procedure finishes with it still active. A transaction cannot start inside a stored procedure and finish outside it, or the reverse.

Isolation is fixed: READ COMMITTED is the only isolation level currently supported for tables. A statement sees only data that was committed before it began, and never sees uncommitted data. Two successive statements in the same transaction can therefore see different data if another transaction commits in between. A statement does see the earlier, uncommitted changes made in its own transaction.

Read consistency across sessions is a separate setting. By default, changes committed in one session may not be visible at once to a query in another session that started earlier. If you need queries in near-concurrent sessions to see each other's changes, an ACCOUNTADMIN can set READ_CONSISTENCY_MODE to 'GLOBAL' at the account level, at the cost of a small delay (usually milliseconds). Running dependent queries in a single session is the most recommended alternative.

CREATE TABLE table1 (i int);
BEGIN TRANSACTION;
INSERT INTO table1 (i) VALUES (1);
INSERT INTO table1 (i) VALUES ('This is not a valid integer.');    -- FAILS!
INSERT INTO table1 (i) VALUES (2);
COMMIT;
SELECT i FROM table1 ORDER BY i;

Whether the later statements run depends on the client. Snowsight stops at the first error. SnowSQL with the -f option keeps going. In a Snowflake Scripting procedure, an unhandled exception means the COMMIT never runs, so the transaction is rolled back. If you want any statement error to abort the whole transaction, set TRANSACTION_ABORT_ON_ERROR at the session or account level.

Sources1

2.How DML locks a standard table, and how long others wait

Once you know where a transaction ends, you can tell how long its locks last. When a statement modifies a table, it takes a lock on that table. The lock stops other statements from modifying the table until it is released. Release happens only when the whole transaction commits or rolls back, not when the statement that took the lock finishes. COMMIT itself also locks resources, but usually only briefly. Some DDL briefly locks the underlying table too: CREATE TABLE, CREATE DYNAMIC TABLE, CREATE STREAM and ALTER TABLE all do this when they set CHANGE_TRACKING = TRUE. Snowflake's docs say only UPDATE and DELETE operations are blocked by that kind of lock, and INSERT is not.

Which DML statements can run side by side depends on their type. UPDATE, DELETE and MERGE 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, so they often run in parallel with other INSERT and COPY operations, and sometimes in parallel with an UPDATE, DELETE or MERGE. On hybrid tables the locks are per row, so statements that touch different rows can progress together.

This is why long jobs on a shared table end up waiting for each other. Say one job opens an explicit transaction, updates a table, then spends an hour on other statements before it commits. It holds that lock for the whole hour. With AUTOCOMMIT, each DML statement commits as soon as it succeeds, so its lock lasts only as long as that statement. A lock lasts until COMMIT or ROLLBACK, so shorter transactions mean shorter waits.

A statement blocked by a lock waits until it either gets the lock or times out. LOCK_TIMEOUT sets that wait in seconds, and it can be set for a session. A value of 0 turns waiting off: the statement must get the lock immediately or abort. Hybrid tables use their own parameter, HYBRID_TABLE_LOCK_TIMEOUT, and take row-level locks instead of the PARTITIONS locks that SHOW LOCKS reports for standard tables.

STATEMENT_TIMEOUT_IN_SECONDS is a different control. It limits the overall time a statement takes, including queue time, locked time, execution time and compilation time, so it also caps time spent blocked, but it is not the setting that defines the lock wait.

ALTER SESSION SET  ?  = 3600;

Sources1234

3.When deadlocks can happen

A deadlock happens when concurrent transactions each wait for a resource the other one has locked. Whether one is possible depends on the kind of transaction. SELECT statements are always read-only, so concurrent autocommit queries cannot deadlock, on standard or hybrid tables. Autocommit DML on standard tables cannot deadlock either. Autocommit DML on hybrid tables can.

The risky pattern is an explicit transaction that runs several statements, because it can hold one lock while it asks for another. Snowflake detects the deadlock and picks the most recent statement involved as the victim. That statement is rolled back, but its transaction stays active and still has to be committed or rolled back. Detection can take time, so a deadlock first looks like ordinary blocking.

Sources1

4.Best practices for concurrent DML

The rules above lead to a short list of practices.

Keep explicit transactions short and limited to DML and query statements. Locks last until COMMIT or ROLLBACK, so every extra statement before the commit makes other writers wait longer. Keeping DDL out also stops it from silently committing your work.

Don't run INSERT or COPY at the same time as DDL on the same object in a different session. Doing so can result in inconsistencies. If the INSERT or COPY runs in an explicit transaction, avoid DDL on that object from other sessions for the whole transaction. For example, don't insert into a table in one session while another session changes the data type of one of its columns.

Don't mix implicit and explicit boundaries. Ending an implicitly started transaction with an explicit COMMIT is allowed, but Snowflake discourages it because the code becomes confusing. Don't change AUTOCOMMIT inside a stored procedure; doing so raises an error.

Set LOCK_TIMEOUT deliberately. The default lets a blocked statement wait for 43200 seconds (12 hours). A lower value makes a blocked job fail early instead of holding up a pipeline.

Be careful with multi-threaded clients. Threads that share one connection share one session, and so one transaction. If one thread runs BEGIN TRANSACTION, COMMIT or ROLLBACK, every thread on that connection is affected, and an AUTOCOMMIT change in one thread changes it for all of them. One thread can roll back another thread's work. Snowflake recommends doing at least one of these: give each thread its own connection, or run the threads synchronously. Separate connections still allow race conditions, such as one thread deleting data that another is about to update.

Sources1

5.Monitoring and managing transaction activity

Snowflake gives you SQL commands to see what transactions are running, who holds locks, and who is blocked. SHOW TRANSACTIONS lists running transactions for the current user, or for the whole account with IN ACCOUNT, which needs the ACCOUNTADMIN role. Each row has the transaction id, user, session, start time and state. SHOW LOCKS lists running transactions that hold or wait for locks. Its type column shows PARTITIONS for standard tables and ROW for hybrid tables, and its status column shows HOLDING or WAITING. DESCRIBE TRANSACTION takes a transaction id and reports its state, such as running, committed or aborted, even after it has ended. CURRENT_TRANSACTION returns the id of the transaction running in your own session. Neither SHOW command needs a running warehouse.

For transactions on hybrid tables, the output is filtered. Transactions are listed only if they are blocking others or are blocked, and only once a blocked transaction has been blocked for more than 5 seconds. After it is no longer blocked, it may linger in the output for up to 15 seconds. Hybrid-table locks show as type ROW, and the resource column shows the ID of the blocking transaction.

For history, use the Account Usage views. QUERY_HISTORY has a TRANSACTION_BLOCKED_TIME column, so filtering for values above 0 finds statements that waited on locks. The LOCK_WAIT_HISTORY view then shows, for each waiting transaction, when it requested the lock and the BLOCKER_QUERIES that held it. The first blocker query is the one running when the wait began, but earlier queries in the blocker transaction may also have taken the lock, so investigate the whole blocker transaction by joining on its transaction_id in QUERY_HISTORY.

To manage a stuck transaction, call SYSTEM$ABORT_TRANSACTION with an id from SHOW TRANSACTIONS or SHOW LOCKS. Only the user who started the transaction or an account administrator can abort it. The function works only for explicit, multi-statement transactions; an autocommit statement is stopped by aborting its job. If a session disconnects abruptly, its transaction can be left detached and keep its locks. Snowflake aborts and rolls back such transactions on its own: after 5 minutes idle if they block another transaction on the same table, or when older than 4 hours if they block nobody. Transactions on hybrid tables are aborted after 5 minutes idle whether or not they block anyone.

SELECT SYSTEM$ABORT_TRANSACTION(1442254688149);

Sources15

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.You can undo a CREATE TABLE ... AS SELECT inside an explicit transaction by issuing ROLLBACK.Why is that wrong?

   Every DDL statement, CTAS included, runs as its own transaction and commits implicitly, so it is finished before any ROLLBACK runs.

   Covered in Where a transaction starts and ends

2. 2.You can raise the isolation level to SERIALIZABLE for stricter protection between concurrent transactions.Why is that wrong?

   READ COMMITTED is the only isolation level currently supported for tables.

   Covered in Where a transaction starts and ends

3. 3.A DML statement releases its lock as soon as that statement finishes.Why is that wrong?

   Locks are held until the whole transaction commits or rolls back. Statements that come later in a long explicit transaction keep the lock for the full duration.

   Covered in How DML locks a standard table, and how long others wait

4. 4.Setting LOCK_TIMEOUT to 0 lets a statement wait for a lock indefinitely.Why is that wrong?

   0 turns lock waiting off. The statement must get the lock immediately or it aborts.

   Covered in How DML locks a standard table, and how long others wait

5. 5.When Snowflake resolves a deadlock, the victim's transaction is closed for you.Why is that wrong?

   Only the victim statement is rolled back. Its transaction stays open and must be ended explicitly.

   Covered in When deadlocks can happen

6. 6.SYSTEM$ABORT_TRANSACTION can abort any running statement, including autocommit ones.Why is that wrong?

   The function supports explicit multi-statement transactions only. An autocommit transaction is stopped by aborting its associated job.

   Covered in Monitoring and managing transaction activity