How to Check If a Table Is Locked in SQL Server

How to check if a table is locked in sql server

Better yet, consider sp_getapplock which is designed for this. Or use SET LOCK_TIMEOUT

Otherwise, you'd have to do something with sys.dm_tran_locks which I'd use only for DBA stuff: not for user defined concurrency.

How to check which locks are held on a table

To add to the other responses, sp_lock can also be used to dump full lock information on all running processes. The output can be overwhelming, but if you want to know exactly what is locked, it's a valuable one to run. I usually use it along with sp_who2 to quickly zero in on locking problems.

There are multiple different versions of "friendlier" sp_lock procedures available online, depending on the version of SQL Server in question.

In your case, for SQL Server 2005, sp_lock is still available, but deprecated, so it's now recommended to use the sys.dm_tran_locks view for this kind of thing. You can find an example of how to "roll your own" sp_lock function here.

How to find out what is locking my tables?

Take a look at the following system stored procedures, which you can run in SQLServer Management Studio (SSMS):

• sp_who
• sp_lock

Also, in SSMS, you can view locks and processes in different ways:

Different versions of SSMS put the activity monitor in different places. For example, SSMS 2008 and 2012 have it in the context menu when you right-click on a server node.

Finding out which Row in a Table is locked - SQL server

adhoc %%lockres%% row-level locks (not for constant monitoring etc.)

set transaction isolation level read uncommitted;

select *
from dbX.schemaY.tableZ 
where %%lockres%% in 
(
    select l.resource_description
    from sys.dm_tran_locks as l
    join sys.partitions as p on l.resource_associated_entity_id = p.partition_id
    where l.resource_type in ('KEY', 'RID')
    and p.object_id = object_id('dbX.schemaY.tableZ')
);

--demo
use tempdb
go

create table dbo.testtableX
(
    id int constraint pkclusteredid primary key clustered,
    thename nvarchar(128)
);
go

insert into dbo.testtableX(id, thename)
select object_id, name
from sys.objects
go

--select *
--from  dbo.testtableX;

--lock some rows
begin transaction
update dbo.testtableX with(rowlock)
set thename = thename+'update'
where id in (44, 45, 46)

--..or in another ssms windows
select 'locked rows', *
from dbo.testtableX with(nolock)
where %%lockres%% in
(
select l.resource_description
from sys.dm_tran_locks as l
where l.resource_type in ('KEY', 'RID') --index lock, KEY
);

select l.resource_description, *
from sys.dm_tran_locks as l
where l.resource_type in ('KEY', 'RID') --index lock, KEY

rollback transaction
go

--to heap
alter table dbo.testtableX drop constraint pkclusteredid;

--...repeat
begin transaction
update dbo.testtableX with(rowlock)
set thename = thename+'update'
where id in (54, 55, 56)

--..or in another ssms windows
select 'locked rows', *
from dbo.testtableX with(nolock)
where %%lockres%% in
(
select l.resource_description
from sys.dm_tran_locks as l
where l.resource_type in ('KEY', 'RID') --row identifier lock, RID
);

select l.resource_description, *
from sys.dm_tran_locks as l
where l.resource_type in ('KEY', 'RID') --RID

rollback transaction
go

drop table dbo.testtableX;
go

Find out number of active locks on a table

Here is a start. Remember that locks can go parallel so you may see the same object being locked on multiple resource_lock_partition values.

USE yourdatabase;
GO

SELECT * FROM sys.dm_tran_locks
  WHERE resource_database_id = DB_ID()
  AND resource_associated_entity_id = OBJECT_ID(N'dbo.yourtablename');

Please look at the documentation for sys.dm_tran_locks

Table Lock SQL Server 2 sessions

I think you want the READPAST hint:

select  *
from    test with(readpast)

It will skip rows that are currently locked.

---

Related Topics