Here is a quick script which will help users to identify locked tables in the SQL Server.
SELECT
OBJECT_NAME(p.OBJECT_ID) AS TableName,
resource_type, resource_description
FROM
sys.dm_tran_locks l
JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
When you run above script, it will display table name and lock on it.
I have written code to lock the person table in the database. After locking the database, when I ran above script – it gave us following resultset.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
Pinal – What does resource_description mean?
How do you lock a single table in database?
create table Table1(a int)
begin tran
insert Table1 values(1)
Until the transaction will be committed or rolled back, the table Table1 will be locked.
Manioko – I see, its locking table only for SELECT and UPDATE but for INSERT. it does not stop any one to INSERT data in table.
I want to completely lock the table and not allow anyone do any operation on that table? One way is write a trigger on table and ROLLBACK everything.
Looking for TABLOCKX hint, it will help you
Thanks Dave, Hopefully I’ll never have to use this, but it’ll be a handy tool in the ol’ toolbox.
Hi Pinal,
I think your code only finds locks in a particular database and not across all databases on the server? If this assumption is correct, I would like to know how to find all locked tables and views across all databases on a given server?
Thanks,
Vlad.
How can I know which of user has locked the Table and from which host computer?
what is resource_description here?
Exactly what I needed!
I find your articles informative and, more important, correct.
Cheers!