Apr 1, 2013

How to release or remove lock on a table SQL server

We can free or release or remove or delete or check all locks on the sql server 2008,2012 and 2014 objects like table etc by killing the process id which is keeping the locks on the objects like tables and views. 

For example, Let us assume there is table named tblStudent.  Any user or application has executed following sql statements:

BEGIN TRANSACTION
DELETE TOP(1) FROM tblStudent

While he missed to commit or to rollback the above transaction.

If we will try select the recodes without NOLOCK,

SELECT * FROM tblStudent

It will not able to get the object tblStudent since it has locked.

Solution:

Step 1: Get the request session id by executing following sql statement:

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

We will get the output something like this:

TableName
Resource_type
request_session_id
tblStudent
RID
54
tblStudent
RID
54
tblStudent
RID
55
tblStudent
PAGE
55
tblStudent
PAGE
54
tblStudent
RID
54
tblStudent
RID
54
tblStudent
RID
54
tblStudent
RID
54
tblStudent
RID
54
tblStudent
RID
54


Step 2: Kill the request session id which has kept lock on the table tblStudent. Assume its request_session_id is 54. Execute following query:

KILL 54


We can check or get closer look or see if or find all blocking or locking on databse tables by following script:

SELECT
     blocking_session_id AS BlockingSessionID,
     session_id AS VictimSessionID,
    
     (SELECT [text] FROM sys.sysprocesses
      CROSS APPLY sys.dm_exec_sql_text([sql_handle])
      WHERE spid = blocking_session_id) AS BlockingQuery,
    
     [text] AS VictimQuery,
     wait_time/1000 AS WaitDurationSecond,
     wait_type AS WaitType,
     percent_complete AS BlockingQueryCompletePercent
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE blocking_session_id > 0

Sample output:

BlockingSessionID
VictimSessionID
BlockingQuery
VictimQuery
WaitSecond
WaitType
CompletePercent
54
60
BEGIN TRANSACTION  DELETE TOP(1) FROM tblStudent   
SELECT * FROM tblStudent
655
LCK_M_S
0


25 comments:

Anonymous said...

Thank you very much.

Anonymous said...

Hi, The above sql just keeps on executing on my table.

Anonymous said...

Hi, your tutorial has helped me a lot. Thank you very much.

thanks,
Ramesh

Anonymous said...

Hi,

Nice solution.. Expressed in a simple way..
Thanks..

Anonymous said...

Thanks for ur solution

Anonymous said...

Thank You very much

Anjum Rizwi

Anonymous said...

Thanks bro.

Nick

Anonymous said...

Solution prevented a massive restore! Thanks a ton!

Anonymous said...

Thank you so much buddy.. i was stuck with this problem for hours.. :) solved in i seconds :)

Swapnil's Tips said...

Hey ,

m Facing issue related to "release lock in SQL server database"..What will be the proper SQL statement to avoid the deadlock or no locks on any table in database??

Please help on this.

Priyanka kumari said...

If you are using transaction then in all select statements write WITH(NOLOCK READUNCOMMITTED). For example:

SELECT * FROM tblA WITH(NOLOCK READUNCOMMITTED) INNER JOIN tblB (NOLOCK READUNCOMMITTED)
ON ...

Anonymous said...

This is where your "tutorial" falls apart:

Assume its request_session_id is 54

Why would we assume it is the blocker. Why not use sp_whoisactive, or something like this:
select blocking_session_id, *
from sys.dm_exec_requests
where blocking_session_id > 0

Priyanka kumari said...

Thank you very much. I will include your suggestion!!

Anonymous said...

Thank-you, this saved my day and the locked table of data!

Anonymous said...

realy a very nice solution
thanks you dear

Anonymous said...

Excellent,you save my day

Unknown said...

Great

Anonymous said...

Thanks a Lot.. It's Working for me..

Unknown said...
This comment has been removed by the author.
Anonymous said...

Hi ,

Need help with this sql query

I want rows to be retrieved based on value in the input_parameter, table has a column return_date
if (input_parameter=out) then retrieve rows having return_date is null
if (input_parameter=in) then retrieve rows having return_date is not null
if (input_parameter=both) then retrieve all rows from table

Anonymous said...

Hi ,

Need help with this sql query

I want rows to be retrieved based on value in the input_parameter, table has a column return_date
if (input_parameter=out) then retrieve rows having return_date is null
if (input_parameter=in) then retrieve rows having return_date is not null
if (input_parameter=both) then retrieve all rows from table

Sheldon Damons said...

Thanks for your post, much appreciated!

Anonymous said...

Thank you, it helped me release my table which is not responding.

Anonymous said...

be careful on using nolock on you queries as it might return an outdated output. Select queries with this will show uncommitted data and might make your output incomplete or more than it should have. Your data might get inaccurate.

deweshpushkar said...

Awsome, Thanks a lot :)