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.
Author: Pinal Dave
Pinal Dave is a Developer Evangelist. He has authored 11 SQL Server database books, 14 Pluralsight courses and over 2900 articles on the database technology on his blog at http://blog.sqlauthority.com. Along with 10+ years of hands-on experience, he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ.