How to check which locks are held on a table

How can we check which database locks are applied on which rows against a query batch?

Any tool that highlights table row level locking in real time?

  • Is a Primary Key necessary in SQL Server?
  • Function in SQL Server 2008 similar to GREATEST in mysql?
  • How to manage a database connection in an R Package
  • SQL Server stored procedure no statements run when from RPC
  • Migration from SQL Server 2000 to 2012 without Outage
  • select first in and last out time from door access table
  • DB: SQL Server 2005

  • Stored procedure setting a value if row is empty
  • Counting rows for all tables for a particular column and column value
  • DB Designer in Visual Studio 2010
  • Restore Failed using C# Code
  • connecting to secure database on private network from website host
  • Missing value after join two query
  • 5 Solutions collect form web for “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.

    This is not exactly showing you which rows are locked, but this may helpful to you.

    You can check which statements are blocked by running this:

    select cmd,* from sys.sysprocesses
    where blocked > 0
    

    It will also tell you what each block is waiting on. So you can trace that all the way up to see which statement caused the first block that caused the other blocks.

    Edit to add comment from @MikeBlandford:

    The blocked column indicates the spid of the blocking process. You can run kill {spid} to fix it.

    I use a Dynamic Management View (DMV) to capture locks as well as the object_id or partition_id of the item that is locked.

    (MUST switch to the Database you want to observe to get object_id)

    SELECT 
         TL.resource_type,
         TL.resource_database_id,
         TL.resource_associated_entity_id,
         TL.request_mode,
         TL.request_session_id,
         WT.blocking_session_id,
         O.name AS [object name],
         O.type_desc AS [object descr],
         P.partition_id AS [partition id],
         P.rows AS [partition/page rows],
         AU.type_desc AS [index descr],
         AU.container_id AS [index/page container_id]
    FROM sys.dm_tran_locks AS TL
    INNER JOIN sys.dm_os_waiting_tasks AS WT 
     ON TL.lock_owner_address = WT.resource_address
    LEFT OUTER JOIN sys.objects AS O 
     ON O.object_id = TL.resource_associated_entity_id
    LEFT OUTER JOIN sys.partitions AS P 
     ON P.hobt_id = TL.resource_associated_entity_id
    LEFT OUTER JOIN sys.allocation_units AS AU 
     ON AU.allocation_unit_id = TL.resource_associated_entity_id;
    

    You can also use the built-in sp_who2 stored procedure to get current blocked and blocking processes on a SQL Server instance. Typically you’d run this alongside a SQL Profiler instance to find a blocking process and look at the most recent command that spid issued in profiler.

    You can find current locks on your table by following query.

    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');
    

    See sys.dm_tran_locks

    If multiple instances of the same request_owner_type exist, the request_owner_id column is used to distinguish each instance. For distributed transactions, the request_owner_type and the request_owner_guid columns will show the different entity information.

    For example, Session S1 owns a shared lock on Table1; and transaction T1, which is running under session S1, also owns a shared lock on Table1. In this case, the resource_description column that is returned by sys.dm_tran_locks will show two instances of the same resource. The request_owner_type column will show one instance as a session and the other as a transaction. Also, the resource_owner_id column will have different values.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.