Search code examples
sqlsql-serverlocking

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


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.


Solution

  • 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