Database · SQL Server
How would you handle a practical scenario involving Deadlocks in SQL Server?
For a practical scenario involving Deadlocks, I would first clarify the business goal, scale, constraints, and the failure or quality attribute being tested. A deadlock occurs when sessions hold resources that other sessions need, creating a cycle SQL Server must break. The scenario centers on two API requests occasionally deadlock while updating account and audit rows; describe how you would diagnose and redesign the access pattern. I would then compare the main alternatives and tradeoffs, identify likely failure modes, and explain how I would validate the solution through testing, observability, security controls, and recovery or rollback planning.