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
|
26 comments:
Thank you very much.
Hi, The above sql just keeps on executing on my table.
Hi, your tutorial has helped me a lot. Thank you very much.
thanks,
Ramesh
Hi,
Nice solution.. Expressed in a simple way..
Thanks..
Thanks for ur solution
Thank You very much
Anjum Rizwi
Thanks bro.
Nick
Solution prevented a massive restore! Thanks a ton!
Thank you so much buddy.. i was stuck with this problem for hours.. :) solved in i seconds :)
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.
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 ...
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
Thank you very much. I will include your suggestion!!
Thank-you, this saved my day and the locked table of data!
realy a very nice solution
thanks you dear
Excellent,you save my day
Great
Thanks a Lot.. It's Working for me..
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
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
Thanks for your post, much appreciated!
Thank you, it helped me release my table which is not responding.
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.
Awsome, Thanks a lot :)
I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article. Seattle Locksmith
Post a Comment