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


24 comments:

  1. Anonymous4/09/2013

    Thank you very much.

    ReplyDelete
  2. Anonymous4/17/2013

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

    ReplyDelete
  3. Anonymous6/19/2013

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

    thanks,
    Ramesh

    ReplyDelete
  4. Anonymous6/26/2013

    Hi,

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

    ReplyDelete
  5. Anonymous7/09/2013

    Thanks for ur solution

    ReplyDelete
  6. Anonymous7/09/2013

    Thank You very much

    Anjum Rizwi

    ReplyDelete
  7. Anonymous8/28/2013

    Thanks bro.

    Nick

    ReplyDelete
  8. Anonymous9/25/2013

    Solution prevented a massive restore! Thanks a ton!

    ReplyDelete
  9. Anonymous1/28/2014

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

    ReplyDelete
  10. 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.

    ReplyDelete
    Replies
    1. 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 ...

      Delete
    2. Anonymous8/01/2016

      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.

      Delete
  11. Anonymous3/14/2014

    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

    ReplyDelete
    Replies
    1. Thank you very much. I will include your suggestion!!

      Delete
  12. Anonymous4/24/2014

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

    ReplyDelete
  13. Anonymous7/30/2014

    realy a very nice solution
    thanks you dear

    ReplyDelete
  14. Anonymous2/07/2015

    Excellent,you save my day

    ReplyDelete
  15. Anonymous10/30/2015

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

    ReplyDelete
  16. This comment has been removed by the author.

    ReplyDelete
  17. Anonymous3/24/2016

    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

    ReplyDelete
  18. Anonymous3/24/2016

    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

    ReplyDelete
  19. Thanks for your post, much appreciated!

    ReplyDelete
  20. Anonymous7/18/2016

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

    ReplyDelete