-- 查看锁和被锁的 SQL SELECT t1.resource_type , DB_NAME( resource_database_id) AS dat_name , t1.resource_associated_entity_id, t1.request_mode, t1.request_session_id, t2.wait_duration_ms, ( SELECT TEXT FROM sys.dm_exec_requests r CROSS apply sys.dm_exec_sql_text ( r.sql_handle ) WHERE r.session_id = t1.request_session_id ) AS wait_sql, t2.blocking_session_id, ( SELECT TEXT FROM sys.sysprocesses p CROSS apply sys.dm_exec_sql_text ( p.sql_handle ) WHERE p.spid = t2.blocking_session_id ) AS blocking_sql FROM sys.dm_tran_locks t1, sys.dm_os_waiting_tasks t2 WHERE t1.lock_owner_address = t2.resource_address;
-- List all Locks of the Current Database SELECT TL.resource_type AS ResType ,TL.resource_description AS ResDescr ,TL.request_mode AS ReqMode ,TL.request_type AS ReqType ,TL.request_status AS ReqStatus ,TL.request_owner_type AS ReqOwnerType ,TAT.[name] AS TransName ,TAT.transaction_begin_time AS TransBegin ,DATEDIFF(ss, TAT.transaction_begin_time, GETDATE()) AS TransDura ,ES.session_id AS S_Id ,ES.login_name AS LoginName ,COALESCE(OBJ.name, PAROBJ.name) AS ObjectName ,PARIDX.name AS IndexName ,ES.host_name AS HostName ,ES.program_name AS ProgramName FROM sys.dm_tran_locks AS TL INNERJOIN sys.dm_exec_sessions AS ES ON TL.request_session_id = ES.session_id LEFTJOIN sys.dm_tran_active_transactions AS TAT ON TL.request_owner_id = TAT.transaction_id AND TL.request_owner_type ='TRANSACTION' LEFTJOIN sys.objects AS OBJ ON TL.resource_associated_entity_id = OBJ.object_id AND TL.resource_type ='OBJECT' LEFTJOIN sys.partitions AS PAR ON TL.resource_associated_entity_id = PAR.hobt_id AND TL.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT') LEFTJOIN sys.objects AS PAROBJ ON PAR.object_id = PAROBJ.object_id LEFTJOIN sys.indexes AS PARIDX ON PAR.object_id = PARIDX.object_id AND PAR.index_id = PARIDX.index_id WHERE TL.resource_database_id = DB_ID() AND ES.session_id <> @@Spid-- Exclude "my" session -- optional filter AND TL.request_mode <>'S'-- Exclude simple shared locks ORDERBY TL.resource_type ,TL.request_mode ,TL.request_type ,TL.request_status ,ObjectName ,ES.login_name;
SELECT xdr.value('@timestamp', 'datetime') AS [Date], xdr.query('.') AS [Event_Data] FROM (SELECTCAST([target_data] AS XML) AS Target_Data FROM sys.dm_xe_session_targets AS xt INNERJOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address WHERE xs.name = N'system_health' AND xt.target_name = N'ring_buffer' ) AS XML_Data CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr) ORDERBY [Date] DESC;
WITH -- get trace folder path and append session name with wildcard (assumes base file name is same as session name) all_trace_files AS (SELECT path +'\system_health*.xel'AS FileNamePattern FROM sys.dm_os_server_diagnostics_log_configurations) -- get xml_deadlock_report events from all system_health trace files , deadlock_reports AS (SELECTCAST(event_data AS xml) AS deadlock_report_xml FROM all_trace_files CROSS APPLY sys.fn_xe_file_target_read_file(FileNamePattern, NULL, NULL, NULL) AS trace_records WHERE trace_records.object_name LIKE'xml_deadlock_report') SELECT deadlock_report_xml.value('(/event/@timestamp)[1]', 'datetime2') AS UtcTimestamp , deadlock_report_xml AS DeadlockReportXml FROM deadlock_reports;
This wait type is when a thread is waiting to acquire an Exclusive lock on a resource and there is at least one other lock in an incompatible mode granted on the resource to a different thread.