70-465 Question #52: Real Exam Question with Answer & Explanation

Question

You need to recommend a solution that addresses the concurrency requirement. What should you recommend?

Options

• ACall the stored procedures in a Distributed Transaction Coordinator (DTC) transaction.
• BModify the stored procedures to update tables in the same order for all of the stored
• CMake calls to Sales.Proc1 and Sales.Proc2 synchronously.
• DBreak each stored procedure into two separate procedures, one that changes

Explanation

Deadlocks in SQL Server commonly occur when stored procedures access tables in inconsistent orders, causing circular wait conditions. Ensuring all procedures update tables in the same order eliminates this circular dependency.

Common mistakes.

• A. Using a DTC transaction adds distributed transaction overhead and still does not prevent deadlocks if the underlying table access order remains inconsistent across procedures.
• C. Making calls to Sales.Proc1 and Sales.Proc2 synchronously serializes application-level execution but does not address deadlocks that occur within the database engine due to inconsistent lock ordering across concurrent sessions.
• D. Breaking each stored procedure into two separate procedures does not inherently resolve deadlocks unless the resulting procedures also update tables in a consistent order, making this an incomplete and unnecessarily complex solution.

Concept tested. Deadlock prevention via consistent table update ordering

Reference. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-deadlocks-guide

No community discussion yet for this question.