Locking & Deadlocks

Locking is the mechanism SQL Server uses to ensure data integrity. Blocking is normal—it means one user is waiting for another to finish. Deadlocking is an error—it means User A is waiting for User B, and User B is waiting for User A. They are stuck forever until SQL Server kills one of them.

1. Shared (S) vs Exclusive (X) Locks

• Shared Lock: Acquired for Read operations. Multiple users can hold Shared locks on the same row.
• Exclusive Lock: Acquired for Write operations. Only one user can hold an Exclusive lock. It blocks everyone else.

2. The Deadlock Graph

When a deadlock occurs, SQL Server chooses a Deadlock Victim (usually the transaction that is easiest to roll back) and throws an error. As an architect, you should use the profiler or extended events to capture the Deadlock Graph, which visually shows you which tables and indexes were involved in the collision.

4. Interview Mastery

Q: "How can I prevent deadlocks in my application code?"

Architect Answer: "The most effective way is to **Access Tables in the same order** everywhere in your app. If Procedure A updates 'Users' then 'Orders', and Procedure B updates 'Orders' then 'Users', they will eventually deadlock. If both always do 'Users' then 'Orders', they will simply block and wait for each other, which is much safer. Also, keeping transactions as short as possible reduces the 'Window of Collision'."