Blocking In SQL Server

Let say one session has placed a lock which will not allow other sessions to access the same resource (table or row or page). The other session query waits for unlimited time till the previous session releases the lock. This scenario is called blocking.

When a transaction is trying to place any type of lock (shared, exclusive, update) on a resource on which already exclusive lock is placed, then it results into blocking.

Certain amount of blocking is normal and unavoidable but regular blocking or blocking which causes the applications or users to wait for unlimited times should be consider. We have to minimize blocking in order to reduce the performance bottlenecks. Blocking causes timeout problems for applications.

Example
 

In the above figure check that session1 has placed the exclusive lock on the row with pid=1 and second session is trying to access the same row. The second session is currently executing and waiting for the first session to release the lock. Hence session2 is blocked by session1.

SELECT command causes any blocking?

                When select command is executed it places Shared Locks and restricts other queries to perform manipulations till the lock is released. But this lock does not cause too much blocking. But locks placed by insert or update or delete as we have seen in the previous example causes excessive blocking.

Scenarios where sessions experience with excessive blocking

1.       Long running queries which places Shared or Exclusive locks.

2.       Queries which are cancelled and not rollback.

3.       In some cases, lock escalation also one reason for blocking because SQL has escalated locks, and so locked the entire table.

4.       When the process of index rebuilding is running.

5.       Snapshot agent is generating fresh snapshot in case of Snapshot of transactional replication.

FAQ: - While tracing server activities using profiler, any blocking occurs?

                While using tuning template in profiler causes blocking.

Monitoring blocking

                SQL Server supports stored procedures and DMVs (Dynamic Management Views) to monitor and troubleshoot blocking issues. Using DMVs as well as event notifications, and the blocked_

process_report and lock_escalation events, we can automate notification when a

blocking event occurs.

            To find blocking we can use sp_who or sp_who2.

 

In the above picture, we can check that session 53 is blocking session 55.  

 

Alternatively we can use sysprocesses of master database.

  

Steps to reduce blocking

1.       Keep the workload small as possible

a.       Avoid INSERTing, UPDATEing, or DELETEing large numbers of records in a single transaction.

2.       Ensure that your tables have appropriate indexes

a.       For the above indexes specially clustered we can set Online Index Processing to true.

3.       Use NOLOCK hint or read uncommitted isolation level

a.       We can make use of NOLOCK option to read the rows and provide more concurrency.

Using SNAPSHOT ISOLATION LEVEL