SnowPro Advanced: Data Engineer — Question 19

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 transaction was left in a detached state and created a lock on resources.
What step must the Engineer take to immediately run a new transaction?

Answer options

• A. Call the system function SYSTEM$ABORT_TRANSACTION.
• B. Call the system function SYSTEM$CANCEL_TRANSACTION.
• C. Set the LOCK_TIMEOUT to FALSE in the stored procedure.
• D. Set the TRANSACTION_ABORT_ON_ERROR to TRUE in the stored procedure.

Correct answer: A

Explanation

The correct answer is A, as calling SYSTEM$ABORT_TRANSACTION will effectively terminate the detached transaction and release the lock on resources, allowing a new transaction to be initiated. The other options do not address the need to clear the existing lock; B simply cancels a transaction but does not necessarily resolve the locked state, while C and D modify settings that do not directly resolve the issue at hand.