Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2012

Basic Troubleshooting for Microsoft SQL Table Lock

4.88/5 (4 votes)
1 Oct 2014CPOL2 min read 16.1K  
How to check the query locking the table and to unlock it

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.

Image 1

All blocking Transaction Report

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.

Image 2

Transactions 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.

Image 3

Status bar showing the SPID

Else, we can check the management studio of the user and find the exact SQL query window by checking the 3rd 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)