如何排查SQL死锁的错误?

最新推荐文章于 2025-01-12 14:29:41 发布

pclzr

最新推荐文章于 2025-01-12 14:29:41 发布

阅读量668

点赞数

分类专栏： SQL Server 文章标签： sql microsoft table sql server properties profiler

SQL Server 专栏收录该内容

8 篇文章

订阅专栏

本文详细介绍了死锁的概念，分析了可能引发死锁的因素，并提供了几种排查死锁的方法，包括开启死锁日志输出、使用SQL Profiler以及通过查询语句检查被锁住的进程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

什么是死锁?

==============

A deadlock occurs when two system server process IDs (SPIDs) are waiting for a resource and neither process can advance because the other process is preventing it from getting the resource. For example, Andrew holds a lock on table A and requests a lock on table B; Lindsay holds a lock on table B and requests a lock on table A.

什么东西可以引发死锁?

==============

Locks
Worker threads
Memory
Parallel query execution-related resources
Multiple Active Result Sets (MARS) resources.

如何排查死锁?

==============

1. 开启死锁日志输出(deadlock trace)

    DBCC TRACEON(1204,1222)

Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock.

Trace flag 1222 formats deadlock information, first by processes and then by resources.

开启了上面的选项之后, SQL会输出死锁的细节信息到SQL Error Log中(默认位置Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n)

2. 开启SQL Profiler.

Start SQL profiler
On the Trace Properties dialog box, on the General tab, check Save to file and specify a path to save the trace
Click the Events tab, only add Locks\Lock:deadlock and Locks\Lock:deadlock chain
Click the Data columns tab, add DatabaseID, IndexID, ObjectID

可以通过下面的语句把DatabaseID和ObjectID换成DatabaseName和ObjectName

           SELECT database_name(DatabaseID)
           SELECT object_name(ObjectID)

3. 使用下面的查询语句来检查那个进程被锁住了.

            SELECT * FROM sys.sysprocesses WHERE blocked <> 0          
从Blocked列中得到SPID        
            DBCC inputbuffer (SPID)
            sp_who2            
            sp_lock2

参考资料:

SQL: How to debug SQL deadlocks

http://www.dalun.com/blogs/10.13.2006.htm

SQL Server technical bulletin - How to resolve a deadlock

http://support.microsoft.com/?kbid=832524

Detecting and Ending Deadlocks

http://msdn.microsoft.com/en-us/library/ms178104.aspx