Locks in MS SQL Server are one way to ensure the integrity of data when changes are made by multiple users at the same time. MSSQL locks objects on a table when a transaction starts and releases the lock when the transaction ends. In this article, we will learn how to find locks in the MS SQL Server database and remove them.
It is possible to simulate a lock on one of the tables using an unfinished transaction (one that has not been completed by rollback or commit). For example, use the following SQL query:
USE tesdb1
BEGIN TRANSACTION
DELETE TOP(1) FROM tblStudents
Before any changes are made, SQL Server will lock the table. Now try opening SQL Server Management Studio and executing a simple SQL select query:
SELECT * FROM tblStudents
The query will freeze in the “Executing query” state until it timed out. This is because the SELECT query is trying to access data in a table that is locked by SQL Server.
To get a list of all blocked queries in MSSQL Server, run the command
select cmd,* from sys.sysprocesses
where blocked > 0
SELECT * FROM master.dbo.sysprocesses
WHERE
dbid = DB_ID('testdb12') and blocked <> 0
order by blockedThe Blocked column shows the process ID of the process that blocked the resources. It also shows the wait time for this query (waittime in milliseconds). If required, this can be used to search for the earliest or latest locks.
select * FROM
master.dbo.sysprocesses
where 1=1
--and blocked <> 0
and spid = 59
The process SPID gives you the T-SQL code of the last SQL query executed by that process or transaction:
DBCC INPUTBUFFER(59)
To force kill the process and release the lock, run the command:
KILL number_of_session
GO
In my case, it will be:
KILL 59
You can create a separate stored procedure if locks are constantly occurring and you want to identify the most resource-intensive queries:
CREATE PROCEDURE GetCurrentQueryCode
@SPID int
AS
DECLARE @sql_handle binary(20), @stmt_start int, @stmt_end int
SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE spid = @SPID AND ecid = 0
DECLARE @line nvarchar(4000)
SET @line = (SELECT SUBSTRING([text], COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end WHEN -1 THEN DATALENGTH([text]) ELSE (@stmt_end - @stmt_start) END) FROM ::fn_get_sql(@sql_handle))
print @line
Now, if you want to see the SQL query that locked the table, all you need to do is specify its SPID:
Exec GetCurrentQueryCode 51
You can also get the query code from the sql_handle of the locking process. For example
select * from sys.dm_exec_sql_text (0x0100050069139B0650B35EA64702000000000000)
You can use the Microsoft SQL Server Management Studio to search for locks in MS SQL Server. You can use one of the following methods:
- Right-click on the server, open the Activity Monitor and expand Processes. You will see a list of requests waiting for resources to be released with a SUSPENDED status.
- Select a database and navigate to Reports -> All Blocking Transactions. It also shows a list of locked queries and the SPID of the locking source.
