ADA-C01 Question 354
Single answerMonitor and manage transaction activityA Snowflake administrator receives reports that several ETL tasks are failing because target tables remain locked for long periods. Investigation shows an analyst opened an explicit transaction in a worksheet, ran multiple UPDATE statements against a large table, and then left the session idle without issuing COMMIT or ROLLBACK. The administrator needs to quickly identify the blocking transaction and end it with the least operational impact. Which action should the administrator take?
- A
Query ACCOUNT_USAGE.QUERY_HISTORY to find the last UPDATE statement, then use SYSTEM$CANCEL_QUERY on that statement ID
- B
Use SHOW LOCKS or LOCK_WAIT_HISTORY to identify the blocking lock and session, then cancel the session so the open transaction is rolled back
- C
Increase the warehouse size for the analyst's session so the transaction completes faster and releases the lock
- D
Run ALTER SESSION ABORT_TRANSACTION for the analyst's user from the administrator session
- E
Suspend the warehouse used by the analyst so Snowflake automatically commits the open transaction
Show answer and explanation
Correct answer: B
Explanation
This scenario tests practical transaction monitoring and remediation in Snowflake. When a user starts an explicit transaction and leaves the session idle without COMMIT or ROLLBACK, any locks held by that transaction can continue to block DML from other sessions. The administrator's goal is to identify the blocker and remove it with minimal impact. In Snowflake, the right operational approach is to inspect lock-related metadata, identify the blocking session, and terminate that session so the open transaction is rolled back and locks are released. Canceling a query is not sufficient when the query has already completed and the session is merely idle in an open transaction. Likewise, changing warehouse size or suspending the warehouse does not manage transaction state. Snowflake documentation and best practices around transaction semantics, locks, and monitoring views/commands support using lock visibility plus session termination to resolve idle blocking transactions.
- A. Incorrect.
Incorrect. QUERY_HISTORY can help identify statements that ran, but canceling a query only affects a currently running query. In this scenario, the user already left the session idle with an open explicit transaction. The lock persists because the transaction remains uncommitted, not because an UPDATE is still actively executing. Looking only at the last UPDATE statement ID does not reliably resolve the blocking condition.
- B. Correct.
Correct. The administrator should first identify the blocking lock and associated session using Snowflake lock and transaction monitoring views/commands, such as SHOW LOCKS or relevant account usage/history views like LOCK_WAIT_HISTORY where applicable. Once the blocking session is identified, canceling or terminating that session is the appropriate way to end the idle session; Snowflake rolls back the open transaction, which releases the locks. This is the most direct and least disruptive way to resolve the issue for other workloads.
- C. Incorrect.
Incorrect. Warehouse size affects query compute performance, but it does not resolve an idle open transaction. If the session is no longer actively running statements, adding compute will not commit or roll back the transaction, and the locks will remain in place.
- D. Incorrect.
Incorrect. There is no general pattern where one session can issue ALTER SESSION ABORT_TRANSACTION against another user's session to terminate that transaction. This option reflects a common misconception that administrators can directly manipulate another session's transaction state using ALTER SESSION syntax. In practice, the administrator would identify and terminate the blocking session.
- E. Incorrect.
Incorrect. Suspending a warehouse does not commit open transactions. Transactions are tied to session state and SQL transaction control, not to warehouse suspension behavior. In fact, suspending a warehouse could create additional disruption without clearing the lock.