Locking and Blocking

This article explores how enabling READ_COMMITTED_SNAPSHOT on your SQL Server database might ease excessive blocking.

Learn about how SQL Server uses locks when modifying data and how data is committed to a database after data changes.

In this article, we look at troubleshooting a SQL Server blocking issue and how the use of NOLOCK was able to resolve the problem.

In this article, we walk through the process used to help troubleshoot SQL Server deadlocks that were occurring from stored procedures.

This article provides SQL code to determine what file, table, row, or other object is locked and causing SQL Server blocking.

Learn how to control which SQL Server transaction will get rolled back when a deadlock occurs by using DEADLOCK_PRIORITY.

This article covers how to find NOLOCK usage in your code by looking through DML statements that have come in via ad hoc queries and application code.

In this final tip, we add additional logic to easily find problematic update and delete statements in SQL Server that use the NOLOCK hint.

Using the NOLOCK hint for doing updates or deletes in SQL Server can be problematic. This tip includes code to quickly find these problem statements.

Using NOLOCK hint for SQL Server updates or deletes can problematic in this tip we look at how to identify these problematic statements.

In this article we look at how to find SQL Server deadlocks using the system health Extended Events session.

In this article we look at why deadlocks occur in SQL Server and a way to avoid deadlock situations using the UPDLOCK hint.

In this tip we look at a way to minimize the length of blocking using SQL Server lock timeout.

In this tip we look at some additional issues you might not be aware of that can occur when using NOLOCK for SQL Server queries.

In this tip we will look at a situation where an index reorg and an update statistics process were blocking other processes and why this occurred.

In this tip we show how to use the SSMS GUI to build an extended events session for locking in SQL Server.

In this tip we look at useful scripts that can be used with sqlcmd to help troubleshoot blocking issues.

You are experiencing high query execution times. You as the SQL Server DBA decide that updating database statistics is one possible solution to the problem, but your boss says that it will cause blocking and advises not to update statistics.

This tip will show you how you can use SQL Profiler to find blocking queries in a SQL Server instance.

Compare the SQL Server NOLOCK and READPAST table hints with step by step examples to show which data is deleted, returned in results sets and locked.

Learn the differences between the SQL Server Repeatable Read and Serializable Transaction Isolation Levels with step by step code examples.

Learn about SQL Server lock escalation modes and how to modify how lock escalation occurs on user tables.