Developing SQL Databases — Question 145

You have multiple stored procedures inside a transaction.
You need to ensure that all the data modified by the transaction is rolled back if a stored procedure causes a deadlock or times out.
What should you do?

Answer options

• A. Use the NOLOCK option.
• B. Execute the DBCC UPDATEUSAGE statement.
• C. Use the max worker threads option.
• D. Use a table-valued parameter.
• E. Set SET ALLOW_SNAPSHOT_ISOLATION to ON.
• F. Set SET XACT_ABORT to ON.
• G. Execute the ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO); statement.
• H. Use the OUTPUT parameters.

Correct answer: F

Explanation

Setting SET XACT_ABORT to ON ensures that if a stored procedure encounters an error, the entire transaction is aborted and rolled back, preventing partial updates. The other options do not provide the same level of control over transaction handling during deadlocks or timeouts.