Whitepaper : Blocking and Locking Troubleshooting

Blocking and locking inside SQL Server is part of the system. It is inevitable, because to maintain integrity of data and show consistent data to users accessing the system, locking is important. SQL Server uses locks on data to prevent data corruption and stop multiple users from updating the same record at the same time. As a special case scenario, keep an eye on deadlocks, and the best way to mitigate them will be proper coding practices.

In this whitepaper, we look at various ways to identify potential problems of locking and blocking, how they are different, and what options SQL Server gives to identify and resolve them. This whitepaper talks about how to troubleshoot and identify blocking behaviour. These quick troubleshooting techniques will surely make you efficient in looking at locks and blocking situations. The whitepaper describes ACID properties of transactions, locking types, blocking behaviors, understanding blocking and waits, knowing your deadlocks, tools to identify blocking, SQL Server Profiler, Profiler’s deadlock graphs, and extended events.