CODEWITHSUNDEEP
sqlsql-server
JD_Sudz
JD_Sudz

Reputation: 194

how can I check if a lock is table or row?

I want to check if a session is holding a Table lock or row lock.

The sys.dm_tran_locks doesn't show this level of detail, that I can see.

Is there a way to identify this?

Upvotes: 0

Views: 1850

Answers (2)

Jeffrey
Jeffrey

Reputation: 412

You can also use this query, which will give you a lot of information, like username, sql, database, resource type etc:

SELECT DTL.[request_session_id] AS [session_id]
    ,DB_NAME(DTL.[resource_database_id]) AS [Database]
    ,DTL.resource_type
    ,CASE 
        WHEN DTL.resource_type IN (
                'DATABASE'
                ,'FILE'
                ,'METADATA'
                )
            THEN DTL.resource_type
        WHEN DTL.resource_type = 'OBJECT'
            THEN OBJECT_NAME(DTL.resource_associated_entity_id, DTL.[resource_database_id])
        WHEN DTL.resource_type IN (
                'KEY'
                ,'PAGE'
                ,'RID'
                )
            THEN (
                    SELECT OBJECT_NAME([object_id])
                    FROM sys.partitions
                    WHERE sys.partitions.hobt_id = DTL.resource_associated_entity_id
                    )
        ELSE 'Unidentified'
        END AS [Parent Object]
    ,DTL.request_mode AS [Lock Type]
    ,DTL.request_status AS [Request Status]
    ,DER.[blocking_session_id]
    ,DES.[login_name]
    ,CASE DTL.request_lifetime
        WHEN 0
            THEN DEST_R.TEXT
        ELSE DEST_C.TEXT
        END AS [Statement]
FROM sys.dm_tran_locks DTL
LEFT JOIN sys.[dm_exec_requests] DER ON DTL.[request_session_id] = DER.[session_id]
INNER JOIN sys.dm_exec_sessions DES ON DTL.request_session_id = DES.[session_id]
INNER JOIN sys.dm_exec_connections DEC ON DTL.[request_session_id] = DEC.[most_recent_session_id]
OUTER APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST_C
OUTER APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_R
WHERE DTL.[resource_database_id] = DB_ID()
    AND DTL.[resource_type] NOT IN (
        'DATABASE'
        ,'METADATA'
        )
ORDER BY DTL.[request_session_id];

Upvotes: 1

Midhun Siva
Midhun Siva

Reputation: 35

Try sp_lock. You can see the level of lock under column named TYPE.

If it is TAB then table level lock. If it is RID then row level lock.

likewise,

KEY = Lock within an index that protects a range of keys in serializable transactions.

PAG = Lock on a data or index page.

EXT = Lock on an extent.

TAB = Lock on an entire table, including all data and indexes.

DB = Lock on a database.

FIL = Lock on a database file.

APP = Lock on an application-specified resource.

MD = Locks on metadata, or catalog information.

HBT = Lock on a heap or B-Tree index. This information is incomplete in SQL Server.

AU = Lock on an allocation unit. This information is incomplete in SQL Server.

Refer the link for more details--> https://msdn.microsoft.com/en-IN/library/ms187749.aspx

Upvotes: 0

Related Questions