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:
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: