How to release or remove lock on a table SQL server

How to release or remove lock on a table SQL server

Lock is a mechanism by which SQL server maintains its data consistency. SQL Server locks objects when it starts a transaction. Once the transaction is over, it automatically releases the lock.

Let’s create a locking scenario manually.

Assume that you have a database called “Books” and within that database you have a table called “Titles” having some rows of data.

Now execute the following query without any rollback or commit of the transaction.

BEGIN TRANSACTION

DELETE TOP(1) FROM Titles

SQL server first locks the row and the page [depends on isolation level] before making the changes. Now if you are using SQL server Management studio to test this, you can open a new query window and run the below select query without nolock hint.

SELECT * FROM Titles

This simple query will keep running without any result. This is because, this select query is trying to access the row which is locked by above delete query.

HOW CAN YOU FIND LOCKS IN SQL SERVER?

SQL server keeps all records internally which is available using Dynamic Management Views (DMV) sys.dm_tran_locks.

SELECT
    OBJECT_NAME(P.object_id) AS TableName,
    Resource_type, request_status,  request_session_id	
FROM
    sys.dm_tran_locks dtl
    join sys.partitions P
ON dtl.resource_associated_entity_id = p.hobt_id
WHERE   OBJECT_NAME(P.object_id) = 'Titles' 

Resource Type => it is type of resource being locked, whether a Key, Row (RID), Page or database.

In the above scenario, I can see the below result.

Where we can see, Session 51 is locking the session 53 and that is why, the query in question with session 53 is waiting for 51 to be completed.

HOW TO KILL A SQL SERVER QUERY SESSION?

In sql clear locks, or if you are looking for how to release table lock in SQL Server, or How to Get Rid of SQL Locks, Or how to find locked tables in SQL Server, or for terminating SQL Server blocking processes, How do I kill a blocked session in SQL Server?

Kill the request session id which has lock on the table Titles. Here is it is 51. Execute the query:

Kill 51

HOW TO KNOW WHICH QUERY IS BLOCKED BY WHICH SESSION?

If you want to know more on which query is blocked by which query or how to find locked tables in SQL Server,, you can find that using sys.dm_exec_sql_text

SELECT blocking_session_id AS BlockingSessionID, session_id AS VictimSessionID,    
[text] AS VictimQuery, wait_time/1000 AS WaitDurationSecond
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE blocking_session_id > 0 

Sample query result is as below: