Basic Troubleshooting for Microsoft SQL Table Lock

Introduction

While working with Microsoft SQL server database, there are many scenarios where database tables are being blocked/locked. An expected small result set from a SELECT statement, takes ages. An UPDATE statement for a single row runs forever. Most of the time, developers were not able to know the reason. I have seen people restarting the database service to fix these types of issues, but that is not correct. We need to find the real reason of blocking and how to unblock it.

Background

Before knowing how to fix, we need to know why blocking occurs in database. When we connect to a SQL database or application connects to the database, each connection appears as a session for the database. Hence, each session is provided with a unique id which is called as Session ID (SPID). So when one connection holds a lock to the table and if at the same time another session requires a conflicting lock, blocking occurs. For example, we are running a script under transaction and forgot to rollback or commit the transaction. Sometimes, when we cancel an execution running under transaction creates these types of problems.

To unblock a table, we need to know the session id that holds the transaction on the table. We can follow the below steps for the same.

In SQL Server Management Studio, go to Object Explorer and open the SQL instance. Select the database where we have the table being blocked. Right-click on the database > Reports > Standard Reports > click on All Blocking Transactions.

It would show us all blocking transactions on the database. From that report, we can get the SPID, script causing the blockage and the scripts being blocked.

To know who is the culprit, we can take the [SPID] and execute SP_WHO2 [SPID]. As per the example in the screenshot, it should be SP_WHO2 52. It will provide the Login, Hostname (system name from where the command is executed) and the Program Name (application from where the command is executed). This information is enough for us to find who is causing the blockage. We can kill the transaction by using KILL [SPID]. As per the example, it should be KILL 52. This would eventually rollback the blocking transaction.

Else, we can check the management studio of the user and find the exact SQL query window by checking the 3^rd section of the status bar where it shows the SPID with the user log in. So either we need to rollback or commit the transaction to fix the issue.

I have one copy of the same tips on my own Wordpress.