A Data Engineer ran a stored procedure containing various transactions. During the execution, the session abruptly disconnected, preventing one transaction from committing or rolling back. The…

Question

Options

• ACall the system function SYSTEM$ABORT_TRANSACTION.
• BCall the system function SYSTEM$CANCEL_TRANSACTION.
• CSet the LOCK_TIMEOUT to FALSE in the stored procedure.
• DSet the TRANSACTION_ABORT_ON_ERROR to TRUE in the stored procedure.

• A
  81% (26)
• B
  13% (4)
• C
  3% (1)
• D
  3% (1)

Explanation

SYSTEM$ABORT_TRANSACTION is a Snowflake system function specifically designed to forcibly terminate a detached (orphaned) transaction by its transaction ID, immediately releasing any locks it holds on resources and allowing a new transaction to proceed. Option B is wrong because SYSTEM$CANCEL_TRANSACTION does not exist in Snowflake - the correct function name is ABORT, not CANCEL. Option C is wrong because LOCK_TIMEOUT is a numeric session parameter (in seconds) that controls how long to wait for a lock - it cannot be set to FALSE and does nothing to resolve an already-detached transaction. Option D is wrong because TRANSACTION_ABORT_ON_ERROR is a preventive setting that auto-rolls back future transactions on error; it has no effect on a transaction that is already orphaned and locked.

Memory tip: Think "ABORT = emergency stop" - when a transaction is stuck and holding locks hostage, you need to abort it with SYSTEM$ABORT_TRANSACTION, just like force-quitting a frozen process on your computer.

No community discussion yet for this question.