sql server 与lock 有关的T-SQL

转载 不知道对不对 

The following query will show blocking information.

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;  

The following example returns information that associates a session ID with a Windows thread ID. The performance of the thread can be monitored in the Windows Performance Monitor. This query does not return session IDs that are currently sleeping.

SELECT STasks.session_id, SThreads.os_thread_id  
    FROM sys.dm_os_tasks AS STasks  
    INNER JOIN sys.dm_os_threads AS SThreads  
        ON STasks.worker_address = SThreads.worker_address  
    WHERE STasks.session_id IS NOT NULL  
    ORDER BY STasks.session_id;  
GO  

Lock:Escalation Event Class

https://msdn.microsoft.com/en-us/library/ms190723.aspx

Lock:Deadlock Chain Event Class

https://msdn.microsoft.com/en-us/library/ms189554.aspx

Lock:Deadlock Event Class

https://msdn.microsoft.com/en-us/library/ms181059.aspx

sys.dm_tran_locks (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms190345.aspx

sp_trace_setevent (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms186265.aspx

sp_trace_setstatus (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms176034.aspx