I have the following query which returns any locks on a specific table. However, I need it to give more information. I need to know which row is currently being locked.
SELECT
DB_NAME(resource_database_id)
, s.original_login_name
, s.status
, s.program_name
, s.host_name
,*
from
sys.dm_tran_locks dbl
JOIN sys.dm_exec_sessions s ON dbl.request_session_id = s.session_id
where
resource_associated_entity_id = object_id('dbname.scheme.table')AND DB_NAME(resource_database_id) = 'dbname'
This query works for showing when there is a lock. I just need a bit more information. As an example, the table I'm looking at contains a lot of orders. If someone is sat in one of those orders within the application. The row will be locked, I need the query to show the order-No of the row that is locked.
EDIT: Attempts Below -
select *
from db.scheme.table
where %%lockres%% in
(
select l.resource_description
from sys.dm_tran_locks as l
where l.resource_type in ('RID')
);
The above returns the row I'm expecting it to return but it will also return a lot of older rows within the table that I wouldn't expect it to. It however, seems close to what I need. With the suggestions from the answer below I couldn't get them to return any rows. I feel like a where statement on the above is what I'm missing.
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