Monitoring Snowflake Transactions, Locks and Blocked Queries

1.Seeing what is running and what is locked right now

When a writer is stuck, start by looking at what is running now. Snowflake gives you two SHOW commands for this, and neither needs a running warehouse.

SHOW TRANSACTIONS lists running transactions with their id, user, session, name, started_on, state and scope. The scope column is 0 for an ordinary transaction. For a scoped transaction it holds an ID, which tells you whether two transactions are in the same scope; this happens when a stored procedure with a transaction is called from inside another transaction. SHOW LOCKS lists running transactions that hold locks. By default, both commands show only the current user's sessions. Add IN ACCOUNT to see every user. For SHOW TRANSACTIONS that option requires ACCOUNTADMIN; for SHOW LOCKS, other roles still see only their own locks. Both commands return at most ten thousand records. You can post-process their output with RESULT_SCAN or the ->> pipe operator, putting the lowercase column names in double quotes.

Every transaction has a unique ID, a signed 64-bit integer. Inside a session, CURRENT_TRANSACTION returns the ID of the transaction that is running there. If you already have an ID, DESCRIBE TRANSACTION shows its details while it is still running or after it has committed or aborted. The output includes a state column and an ended_on column. Other context functions that help here are CURRENT_STATEMENT, LAST_QUERY_ID and LAST_TRANSACTION.

DESCRIBE TRANSACTION 1721161383427000000;

Sources12

2.Finding the blocker after the fact with LOCK_WAIT_HISTORY

SHOW LOCKS only shows the present. To investigate last night's waiting, use two Account Usage views together. In QUERY_HISTORY, the TRANSACTION_BLOCKED_TIME column shows how long each query waited for a lock. LOCK_WAIT_HISTORY records the lock waits themselves. Each row is one transaction waiting on a lock, with its QUERY_ID, TRANSACTION_ID, the LOCK_TYPE (PARTITION, STREAM, TABLE or ROW), REQUESTED_AT, ACQUIRED_AT, and a BLOCKER_QUERIES JSON array of up to 20 blockers. A blocker with is_snowflake set to TRUE is a Snowflake background process, such as automatic maintenance of materialized views. Other transactions may be queued ahead of the one in a given row for the same lock.

SELECT query_id, query_text, start_time, session_id, execution_status, total_elapsed_time, compilation_time, execution_time, transaction_blocked_time FROM snowflake.account_usage.query_history WHERE start_time >= dateadd('hours', -24, current_timestamp()) AND transaction_blocked_time > 0 ORDER BY transaction_blocked_time DESC;

Next, look up those query IDs in LOCK_WAIT_HISTORY and note the transaction_id of each entry in blocker_queries. Then query QUERY_HISTORY by each of those transaction IDs to see every statement in the blocker transaction. Look at all of them. The first blocker query is the statement that was running when the wait began, but an earlier DML statement in that transaction may already have taken the lock. A later run of the same jobs may block on a different statement in the same transaction.

Which statements can block each other? 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, so they often can run in parallel with other INSERT and COPY operations, and sometimes can run in parallel with an UPDATE, DELETE or MERGE statement. This is why concurrent loads usually do not appear as blockers of each other.

SELECT query_id, object_name, transaction_id, blocker_queries
  FROM SNOWFLAKE.ACCOUNT_USAGE.LOCK_WAIT_HISTORY
  WHERE  ?  >= DATEADD('hours', -24, CURRENT_TIMESTAMP());

Sources34

3.Aborting transactions, automatic aborts and timeouts

Once you have the blocker's ID from SHOW TRANSACTIONS or SHOW LOCKS, you can end it with SYSTEM$ABORT_TRANSACTION. The user who started the transaction can do this, and so can an account administrator. The function works only on explicit, multi-statement transactions. For an autocommit transaction, abort the associated job instead. Calling it on a transaction that has already committed or rolled back changes nothing. If a DDL statement has already committed the open transaction implicitly, that transaction can no longer be aborted.

The typical case is a session that disconnects abruptly. Its transaction is left detached and keeps its locks. If nobody aborts it, Snowflake does so automatically under the rules below.

SELECT SYSTEM$ABORT_TRANSACTION(1442254688149);

Cancelling a query is a different action. Snowflake recommends cancelling through the client's interface or the JDBC/ODBC cancellation API. In SQL, SYSTEM$CANCEL_QUERY cancels a single query, and SYSTEM$CANCEL_ALL_QUERIES cancels every active query in one session. To cancel by warehouse or by user, use ALTER WAREHOUSE … ABORT ALL QUERIES or ALTER USER … ABORT ALL QUERIES.

Don't confuse the two kinds of timeout. LOCK_TIMEOUT limits how long a statement waits for a lock. STATEMENT_TIMEOUT_IN_SECONDS limits how long a running statement may run before the system cancels it. You can set it on the account, user or session, and also on a warehouse. When it is set in both the session hierarchy and the warehouse, the lower non-zero value applies.

Sources54678

Exam traps

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

1. 1.The first entry in BLOCKER_QUERIES is the statement that took the lock, so it is the only one to investigate.Why is that wrong?

   The first entry is just the statement that was running when the wait began. Earlier or later DML in the same blocker transaction may hold the lock.

   Covered in Finding the blocker after the fact with LOCK_WAIT_HISTORY

2. 2.SYSTEM$ABORT_TRANSACTION can stop any long-running autocommit DML statement.Why is that wrong?

   The function works only on explicit, multi-statement transactions. To stop an autocommit transaction, abort its job.

   Covered in Aborting transactions, automatic aborts and timeouts

Practise it for real

Create a lock in one session, watch it from another, then release it with SYSTEM$ABORT_TRANSACTION.

1. 1.In session A, run BEGIN TRANSACTION, then an UPDATE on a scratch table, then SELECT CURRENT_TRANSACTION(); and do not commit.

   Why: An open explicit transaction keeps its lock until COMMIT or ROLLBACK.

   You should see: A 64-bit transaction ID is returned.

2. 2.In session B, logged in as the same user as session A, run SHOW TRANSACTIONS; and then SHOW LOCKS;

   Why: These list the running transactions and the locks they hold for the current user across all of that user's sessions. Use IN ACCOUNT as ACCOUNTADMIN to see other users.

   You should see: Session A's transaction appears, and SHOW LOCKS shows the table with type PARTITIONS and status HOLDING.

3. 3.In session B, run an UPDATE on the same table. From a third session of the same user, run SHOW LOCKS; again.

   Why: Locks block other statements from modifying the resource until they are released, so the second UPDATE has to wait for session A's lock.

   You should see: Session B's UPDATE does not complete, and session A's transaction still shows HOLDING.

4. 4.Run SELECT SYSTEM$ABORT_TRANSACTION(<id from step 1>); and then DESCRIBE TRANSACTION <id>;

   Why: Aborting releases the lock. DESCRIBE TRANSACTION still works after a transaction ends.

   You should see: An 'Aborted transaction id' message. Session B's UPDATE then proceeds, and DESCRIBE shows a state that is no longer running.