SqlServer并发事务：死锁跟踪（三）3种跟踪死锁的方法总结

方法一：打开profiler跟踪事件 locks: deadlock graph

方法二：打开1222或者1204标志记录死锁,在sqlserver日志查看

1 2	`DBCC TRACEON(1222,-1)` `DBCC TRACEON(1204,-1)`

 
        如果日志太多就不好找了，这时可以用系统扩展存储过程筛选！ 
       
        --查看是否死锁，确定死锁的的时间 
       
        exec 
        xp_readerrorlog 0,1, 
        'DeadLock' 
        , 
        NULL 
        , 
        '2015-01-01' 
        , 
        '2015-01-10' 
        , 
        'DESC' 
       
        --按时间查看sqlserver日志，即为死锁信息 
       
        exec 
        xp_readerrorlog 0,1, 
        NULL 
        , 
        NULL 
        , 
        '2015-01-07 22:13:10.300' 
        , 
        '2015-01-07 22:13:10.330' 
        , 
        'DESC'

方法三：系统扩展事件会话system_health自动记录

 
        --扩展事件会话的信息 
       
        select 
        *  
        from 
        sys.dm_xe_sessions  
        where 
        name 
        =  
        'system_health' 
       
        SELECT 
       
        xed.value( 
        '@timestamp' 
        , 
        'datetime' 
        ) 
        as 
        Creation_Date,   
       
        xed.query( 
        '.' 
        ) 
        AS 
        Extend_Event   
       
        FROM 
       
        (   
       
        SELECT 
        CAST 
        ([target_data]  
        AS 
        XML) 
        AS 
        Target_Data   
       
        FROM 
        sys.dm_xe_session_targets  
        AS 
        xt   
       
        INNER 
        JOIN 
        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(xed)   
       
        ORDER 
        BY 
        Creation_Date  
        DESC