深入理解MSSQL之死锁查看心得（mssql 查看 死锁）

Deadlock Detection

A deadlock is a situation where one or more threads are no longer able to proceed with their tasks due to other threads having an exclusive lock on the resources they need. In a database system, transactions may be blocked as a result of deadlock, either waited for a certain period of time or aborted after a certain amount of waiting. Therefore, detecting deadlocks has become an important task for database administrators.

In a relational database system like Microsoft SQL Server, deadlock detection usually involves the use of a series of system tables and system procedures. The SQL Server Transaction Manager detects deadlocks based on various system and user-defined parameters which it uses as input for its deadlock detection algorithm. The main system tables used for deadlock detection are the sys.dm_os_waiting_tasks, sys.dm_exec_sessions,sys.dm_tran_locks, sys.dm_exec_requests, and sys.dm_os_threads.

We can use the system stored procedure ‘sp_lock’ to view information about the locks that have been obtained by user processes. This stored procedure will return a row for each lock taken out by a transaction. The columns of this table include the lock type, the name of the object that is locked, and the transaction process id (SPID) of the user process holding the lock. By traversing the lock hierarchy, you can determine quickly which application process is blocking other processes.

Additionally, SQL Server also provides a number of trace flags and event classes related to deadlock detection, which can be used to log deadlock information for debugging and troubleshooting purposes. The most useful of these trace flags is trace flag 1222 which creates an XML output of Deadlock Graphs that show us the details of the resources being blocked.

The understanding of deadlock detection and the use of trace flags and system tables within SQL Server are critical skills for any DBA. Deadlocks are one of the most common performance issues and should be taken seriously to ensure that the system remains stable and reliable.

In conclusion, deadlock detection is an essential element of the tuning process of any SQL Server. With the use of the system tables, trace flags and event classes available, it is possible to detect and resolve deadlocks quickly and efficiently.