SQL Server Blocking

Summary: in this tutorial, you’ll learn about the SQL Server Blocking concept and fully understand it via an example.

Introduction to the SQL Server blocking

A block ( or blocking block) occurs when two sessions attempt to update the same data concurrently.

The first session locks the data and the second session needs to wait for the first one to complete and release the lock.

As the result, the second session is blocked from updating the data. Once the first session completes, the second session resumes operation.

In general, blocking occurs when one session holds a lock on a resource and a second session attempts to acquire a conflicting lock type on the same resource.

The following picture illustrates SQL Server blocking:

Typically, the time in which the first session locks the data is very short. When it releases the lock, the second session can acquire its own lock on the resource and continue processing.

Blocking is an unavoidable and by-design feature of SQL Server with lock-based concurrency. It is normal behavior and doesn’t impact the server performance.

SQL Server blocking example

We’ll create a new HR database with a table called People for the demonstration:

DROP DATABASE IF EXISTS HR;
GO 

CREATE DATABASE HR;
GO

USE HR;

CREATE TABLE People (
  Id int IDENTITY PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL
);


INSERT INTO People (FirstName, LastName)
VALUES  ('Jane', 'Doe');


SELECT * FROM People;Code language: SQL (Structured Query Language) (sql)

The People table has one row with Id 1.

First, begin a transaction and update the LastName of the row with Id 1 to 'Smith':

BEGIN TRAN;

UPDATE People
SET LastName = 'Smith'
WHERE Id = 1;Code language: SQL (Structured Query Language) (sql)

Note that we did not commit or rollback the transaction in the first session.

Second, create a new session. In the second session, update the LastName of the row with id 1 to ‘Brown’:

BEGIN TRAN;

UPDATE People
SET LastName = 'Brown'
WHERE Id = 1;

COMMIT;Code language: SQL (Structured Query Language) (sql)

Since the transaction in the first session is ongoing, the second session will wait for the first session to complete.

If you use SQL Server Management Studio, you’ll see the following message:

Executing query....Code language: SQL (Structured Query Language) (sql)

To list all the processes that are currently connected to the SQL Server, you use the sp_who2 stored procedure.

sp_who2;Code language: SQL (Structured Query Language) (sql)

The output shows that the system process ID (SPID) 55 is blocked by SPID 69. In this example, the first session is 69 while the second session is 55. Note that your SPID may be different from the ones in this example.

Third, go back to the first transaction and commit it:

COMMIT;Code language: SQL (Structured Query Language) (sql)

As soon as the first session completes its transaction, the second session also completes.

Summary

• Blocking occurs when two sessions attempt to update a resource at the same time. The second session is blocked because it has to wait for the second session to complete.
• Use the sp_who2 stored procedure to check which SPID is blocked by another SPID.