Pages

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:

BEGINTRANSACTION

DELETETOP(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

joinsys.partitions P

ON L.resource_associated_entity_id = p.hobt_id

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

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 nullif (input_parameter=in) then retrieve rows having return_date is not nullif (input_parameter=both) then retrieve all rows from table

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 nullif (input_parameter=in) then retrieve rows having return_date is not nullif (input_parameter=both) then retrieve all rows from table

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.