How to find the locks which are issued by the user session in SQL Server
Every session will try hold a kind of lock on the resource which it is processing.It might be shared/Update/exclusive/Intent/Schema/Bulk Update lock.
There are other locks as well (Intent shared/ Intent exclusive/ Shared with Intent exclusive).
http://msdn.microsoft.com/en-us/library/aa213039(v=sql.80).aspx
Query used for finding the locks issued by user sessions :
SELECT
resource_type,
resource_associated_entity_id,
request_status, request_mode,request_session_id,
resource_description
FROM sys.dm_tran_locks
SELECT
t1.resource_type, t1.resource_database_id,
t1.resource_associated_entity_id, t1.request_mode,
t1.request_session_id, t2.blocking_session_id
FROM sys.dm_tran_locks
AS t1 INNER JOIN
sys.dm_os_waiting_tasks AS t2 ON
t1.lock_owner_address = t2.resource_address;
More information about the session and its details which are causing the locks:
SELECT L.request_session_id
AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS
LockedObjectName,
P.object_id AS
LockedObjectId,
L.resource_type
AS LockedResource,
L.request_mode
AS LockType,
ST.text AS
SqlStatementText,
ES.login_name
AS LoginName,
ES.host_name AS
HostName,
TST.is_user_transaction
as IsUserTransaction,
AT.name as
TransactionName,
CN.auth_scheme
as AuthenticationMethod
FROM sys.dm_tran_locks
L
JOIN sys.partitions P ON
P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON
ES.session_id =
L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id
= AT.transaction_id
JOIN sys.dm_exec_connections CN ON
CN.session_id =
ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle)
AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id
Hope the above helps
Thanks
