`
hyw520110
  • 浏览: 220912 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

SQL Server 技术公告-How to 解决死锁

    博客分类:
  • sql
阅读更多

简介

<script type="text/javascript"></script>

这篇文章检查死锁状态并提供了解决死锁的步骤。 每个死锁可能会不同,可能引起几个不同的环境变量。 这篇文章中提供该信息可以帮助您识别和解决死锁。

回到顶端

案例研究

<script type="text/javascript"></script>

in case study,we examine 911 system that has six operators。 during peak activity,are using Microsoft Visual Basic front-end application experiences broken 的 connections。 because of broken connections,operators must re-input data。 for 911 system that operates day 24 hours,week,seven days this behavior is unacceptable。

回到顶端

什么是死锁?

<script type="text/javascript"></script>

当两个时,发生死锁系统服务器进程 ID (SPID) 正在等待资源和既不过程可以提前,因为其他进程使其无法获取该资源。

锁管理器的线程检查死锁。 当一个锁管理器的死锁检测算法检测到死锁时,锁管理器选择其中一个 SPID 为牺牲品。 锁管理器启动的发送给客户端,1205 错误邮件并锁管理器删除 SPID。 取消 SPID 释放资源并允许继续其他 SPID。 取消,它是死锁牺牲品 SPID 是何而起 Visual Basic 前端应用程序遇到断开的连接。

在设计良好应用程序,前端应用程序应 1205 错误的补漏白、 重新连接到 SQL Server,然后 re-submit 该事务。

尽管可以最小化死锁,它们无法完全避免。 这就是原因前端应用程序应被设计为处理死锁。

回到顶端

如何识别死锁

<script type="text/javascript"></script>

第 1 步

若要标识死锁,您必须首先获取日志信息。 如果您怀疑死锁,您必须收集有关 (SPID) 和死锁中涉及该资源的信息。 为此,添加-T1204 和到 SQL Server-T3605 启动参数。 要添加这两个启动参数,请按照下列步骤操作:
启动 SQL Server 企业管理器。
选择,右键单击该服务器。
单击 属性
单击 启动参数
启动参数 对话框,键入 -T1204 参数 文本中框,然后再单击 添加
参数 文本框中,键入 -T3605 ,然后单击 添加
单击 确定

startup parameters will take effect when SQL Server is stopped and then re-started。

-T1204 启动参数收集关于该进程和死锁检测算法遇到死锁时资源的信息。 -T3605 启动参数将此信息写入 SQL Server 错误日志。

-T1205 启动参数收集信息每次死锁算法检查为死锁,不当遇到死锁时。 您不必使用-T1205 启动参数。

if do use-T1205 startup parameter,following is sample of that will be in SQL Server error log output:

2003-05-14 11:46:26.76 spid4     Starting deadlock search 1
2003-05-14 11:46:26.76 spid4     Target Resource Owner:
2003-05-14 11:46:26.76 spid4      ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340
2003-05-14 11:46:26.76 spid4      Node:1       ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340
2003-05-14 11:46:26.76 spid4     
2003-05-14 11:46:26.76 spid4     End deadlock search 1 ... a deadlock was not found.
2003-05-14 11:46:26.76 spid4     ----------------------------------
2003-05-14 11:46:31.76 spid4     ----------------------------------
2003-05-14 11:46:31.76 spid4     Starting deadlock search 2


有时,您可能无法停止并重新启动 SQL Server。 在这种情况下,您可以使用查询分析器运行以下命令以便死锁跟踪标志。

注意 这种方式可以收集有关死锁信息立即。 "-1"表示所有 SPID。

dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go

步骤 2

接下来,您必须收集 SQL 事件探查器跟踪。 如果您打开死锁跟踪标志,您将获得大多数必需的信息,但不是总。 例如,一个案例研究中跟踪标志输出标识在 sp_cursoropen 系统存储过程和"UPDATE tblQueuedEvents 设置 notifyid = 3,ResynchDate"语句参与死锁。 unfortunately,do not know of sp_cursoropen system stored procedure definition。 also do not have complete UPDATE statement because was truncated。

SQL Profiler can obtain in addition to of statements execution plans full statements。 SQL Profiler trace also has for"deadlock"and"deadlock chain"for lock event。 "deadlock"corresponds to-T1204 flag,and"deadlock chain"corresponds to-T1205 flag。 打开死锁跟踪标志和死锁的匹配项时运行 SQL 事件探查器跟踪应提供您 must have to 疑难解答死锁的数据。 在这种情况下,并在其他,运行 SQL 事件探查器更改,以防止死锁执行足够的时间。 因此,您将通常捕获使用跟踪标志,死锁信息,然后在运行 SQL 事件探查器。

回到顶端

疑难解答死锁

<script type="text/javascript"></script>

死锁之后,您可以通过使用 sqldiag 实用程序以及使用 SQL 事件探查器收集有关死锁的信息。 在 SQLDiag.txt 文件的输出,查找一个"等待--图形"项。 A"等待-为图形"项表示已遇到死锁。

以下是当您使用时,可能看见在 SQL Server 错误日志中输出的一个示例-T1205 启动参数。

2003-05-05 15:11:50.80 spid4    Wait-for graph
2003-05-05 15:11:50.80 spid4    Node:1
2003-05-05 15:11:50.80 spid4    ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193
2003-05-05 15:11:50.80 spid4    Victim Resource Owner:
2003-05-05 15:11:50.80 spid4    ResType:LockOwner Stype:'OR' Mode: X SPID:60 ECID:0 Ec:(0x1F1BB5B0) Value:0x193
2003-05-05 15:11:50.80 spid4    Requested By: 
2003-05-05 15:11:50.80 spid4    Input Buf: RPC Event: sp_cursoropen;1
2003-05-05 15:11:50.80 spid4    SPID: 55 ECID: 0 Statement Type: EXECUTE Line #: 1
2003-05-05 15:11:50.80 spid4    Owner:0x1937f2a0 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:55 ECID:0
2003-05-05 15:11:50.80 spid4    Grant List 0::
2003-05-05 15:11:50.80 spid4    KEY: 8:1653632984:2 (da00ce043a9e) CleanCnt:1 Mode: U Fl ags: 0x0
 
2003-05-05 15:11:50.80 spid4    Node:2
2003-05-05 15:11:50.80 spid4    ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193
2003-05-05 15:11:50.80 spid4    Requested By: 
2003-05-05 15:11:50.80 spid4    Input Buf: Language Event: Update tblQueuedEvents Set NotifyID = 2, ResynchDate
2003-05-05 15:11:50.80 spid4    SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 1
2003-05-05 15:11:50.80 spid4    Owner:0x1936e420 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:60 ECID:0
2003-05-05 15:11:50.80 spid4    Grant List 0::
2003-05-05 15:11:50.80 spid4    KEY: 8:1653632984:1 (2d018af70d80) CleanCnt:1 Mode: X Flags: 0x0


在"等待--图形"项,必须节点 1 和节点 2。 在每个节点,您可以在授予部分和请求部分。 授予部分是"允许列表",请求部分是"请求按"。
每个节点中, 可以识别以下:
SPID。
SPID 正在执行该命令。
该资源。
该资源上的锁模式。

例如,在节点 1,授予列表,SPID 55 已被授予的更新锁,模式: U,资源 KEY 上的: 8:1653632984:2。 8 = DBID,1653632984 = ObjectID 和 2 = Indid。 若要获取数据库标识号,运行 sp _ helpdb 存储过程。 要获取表,运行下面的代码:
select * from sysobjects where id = 1653632984


要获得索引,运行下面的代码:
select * from sysindexes where indid = 2 and id = 1653632984

IndexId is equal to 2,if know index is nonclustered index。 that SPID 55 was executing command was sp_cursoropen stored procedure。

在节点 2,授予列表,SPID 60 已被授予的排他锁,模式: X,资源 KEY 上的: 8:1653632984:1。 8 = DBID,1653632984 = ObjectID,1 = Indid。 这是在同一表上但 index 1 是聚集的索引。 SPID 60 正在执行的命令是:
Update tblQueuedEvents Set NotifyID = 2, ResynchDate

等于 1 的一个 IndexId 是聚集的索引。

等于 2 的 IndexId 是一个非聚集索引。

注意 死锁是很时间敏感的。

接下来,在节点 1,请求,SPID 55 请求共享的锁,模式: S,IndexId 上 = 1。 在节点 2,请求、 SPID 60 请求的排他锁,模式: X,IndexId 上的 = 2。 因为这些锁请求发生在同一时间,发生死锁。 每个 SPID 授予锁防止继续请求的锁定。

following table shows lock compatibility chart。 for more information about lock compatibility,see in SQL Server 2000 Books Online"Lock Compatibility"topic。

锁兼容性图表
请求模式 IS   S   U   IX   SIX   X
意向共享 (IS)          
共享 (S)          
更新 (U)          
意向排他 (IX)          
与意向排他 (六) 共享          
排他 (X)          


next,by looking at output,identify as tblQueuedEvents table,ObjectId 1653632984 and obtain for table output sp_help stored procedure。 there were two indexes on table。 two indexes were ix_tblQueuedEvents and PK_tblQueuedEvent ix_tblQueuedEvents is on ResynchDate,clustered 的 index and PK_tblQueuedEvent is primary key,on EventSID unique nonclustered index。

SQL 事件探查器跟踪不能捕获死锁匹配项。 请记住,死锁是很时间相关。 SQL 事件探查器的系统开销可能添加一段时间之一进程的执行并的阻止在死锁情况下获取 SQL 事件探查器。 但是,它提供了可用于解决此问题信息。 您找到完全更新 tblQueuedEvents 语句为与以下内容类似:

Update tblQueuedEvents Set NotifyID = 2, ResynchDate = '5/7/2003 10:44:16' where eventSID = 73023
您还找到执行计划。 您还没有完全 sp_cursoropen 存储过程语句,但您没有足够的信息,可以建议一个解决方案,将解决死锁。

下面是执行计划。

注意 is read this particular execution plan right to left and bottom to top.

StmtText                                                      
				                                                                           
				   
				                                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
				
Update tblQueuedEvents Set NotifyID = 2, ResynchDate = '5/7/2003 10:44:16'
				where eventSID = 73023                                                     
				   
				                    
|--Clustered Index
				Update(OBJECT:([SOTS].[dbo].[tblQueuedEvents].[ix_tblQueuedEvents ]),
				SET:([tblQueuedEvents].[NotifyID]=[@1],
				[tblQueuedEvents].[ResynchDate]=[Expr1004]))  
     |--Top(1)                                                                 
				                                                                           
				   
				               
           |--Compute Scalar(DEFINE:([Expr1004]=Convert([@2])))                
				                                                                           
				   
				                
                 |--Index
				Seek(OBJECT:([SOTS].[dbo].[tblQueuedEvents].[PK_tblQueuedEvents]),
				SEEK:([tblQueuedEvents].[EventSID]=[@3]) 

回到顶端

建议一个解决方案解决死锁

<script type="text/javascript"></script>

请注意在 UPDATE 语句聚集索引上执行一个"聚集的索引更新"。 因此,该非聚集索引和聚集的索引必须都进行更新。 非聚集的索引聚集的索引是 ix_tblQueuedEvents PK_tblQueuedEvents 。 要执行更新,UPDATE 语句必须获取这两个索引的排他锁。 这些两个索引是的索引所涉及到的死锁。 从审阅 SQL 事件探查器跟踪,您看不到在 WHERE 子句中使用 ResynchDate 的任何查询。 所有语句都是非常特定,以及它们在 WHERE 子句中使用 EventSID。 of clustered index better choice would be EventSID。 使用此信息和与客户讨论,我们发现 ResynchDate 索引是旧,而且它不必要的。 我们建议的客户放 ResynchDate, ix_tblQueuedEvents 索引并且它们使得 PK_tblQueuedEvent 一个聚集的索引。 这解决死锁情况。

this is only one of that involves locks deadlock case example。 deadlocks can also involve parallelism and involve threads。 can involve one,two,three,or more SPIDs,and resources。 with any deadlock case,must obtain –T1204 startup parameter output,and to identify,to troubleshoot,and to resolve deadlock SQL Profiler trace。 your deadlock situation will involve different processes and resources。 therefore,solutions will vary from case to case。 可用于解决死锁典型的方法包括:
adding and dropping indexes。
正在添加索引提示。
修改应用程序访问一种类似的模式中的资源。
从触发器像在事务中移除活动。 默认情况下触发器是事务性。
保持事务尽可能短。

回到顶端

其他阅读

<script type="text/javascript"></script>

有关死锁信息,请访问下面的 Microsoft Web 站点:

http://msdn2.microsoft.com/en-us/library/Aa213040 (http://msdn2.microsoft.com/en-us/library/Aa213040)

http://msdn2.microsoft.com/en-us/library/aa213042(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa213042(SQL.80).aspx)

http://msdn2.microsoft.com/en-us/library/aa213028(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa213028(SQL.80).aspx)

http://msdn2.microsoft.com/en-us/library/aa937573(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa937573(SQL.80).aspx)

http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx)
分享到:
评论

相关推荐

    sql server 2012 T-SQl基础教程 源码和示例数据库

    《SQL Server 2012 T-SQL基础教程——源码与示例数据库》 本教程专注于Microsoft SQL Server 2012中的Transact-SQL(T-SQL)语言,这是SQL Server的主要查询语言,用于数据操作、查询、存储过程和数据库对象的编程...

    [电子书] SQL Server 2012 T-SQL 实用技巧 (英文版)

    《SQL Server 2012 T-SQL 实用技巧》是一本专为SQL Server 2012数据库管理员和开发人员编写的指南,由Apress出版社出版。这本书以英文呈现,旨在提供解决日常T-SQL编程挑战的具体解决方案,涵盖了T-SQL语言的广泛...

    SQL Server 2005 T-SQL增强

    ### SQL Server 2005 T-SQL增强 #### 富有表现力的数据类型 (Richer Data Types) 在SQL Server 2005中,T-SQL得到了显著增强,其中一个重要方面是引入了新的数据类型。这些数据类型更加丰富且灵活,能够更好地支持...

    SQL Server死锁产生的原因及解决办法

    此外,SQL Server自带的死锁检测机制也会自动发现并解决死锁,但这种方法并不能完全预防死锁。 6. **合理设计索引**:优化索引可以提高查询效率,减少锁定时间,从而降低死锁发生的可能性。 7. **使用更细粒度的...

    SQL Server死锁的解除方法

    "SQL Server死锁的解除方法" SQL Server死锁是指在多个事务之间的资源竞争时,出现的循环...解决SQL Server死锁需要具备一定的技术知识和经验。但是,只要按照正确的步骤进行操作,就可以快速解决SQL Server死锁问题。

    sqlserver如何快速查询死锁语句

    本文将详细介绍如何在SQL Server中快速查询并解决死锁问题。 首先,我们要检查是否有未提交的事务。未提交的事务可能会导致阻塞其他事务,进一步引发死锁。以下是一段SQL查询语句,用于找出所有未提交事务的详细...

    通过SQL Server Profiler来监视分析死锁

    本文将深入探讨如何使用SQL Server Profiler工具来监视和分析死锁,以便更好地理解和解决这类问题。 首先,理解死锁的基本概念至关重要。死锁发生时,两个事务A和B各自持有对方需要的资源,导致双方都无法继续执行...

    Microsoft SQL Server 2008 T-SQL Fundamentals

    《Microsoft SQL Server 2008 T-SQL Fundamentals》是一本专为SQL Server初学者设计的教程,它深入浅出地介绍了Transact-SQL(T-SQL)的基础知识。T-SQL是SQL Server中用于数据查询、更新、操作和编程的主要语言。...

    《Microsoft SQL Server 2005技术内幕:T-SQL查询》示例代码

    通过《Microsoft SQL Server 2005技术内幕:T-SQL查询》中的示例代码,读者可以深入理解T-SQL的各个层面,从而更好地在实际项目中应用这些技术,解决复杂的数据问题。同时,书中还可能包含一些高级主题,如XML处理、...

    SQLServer排查死锁

    ### SQL Server 死锁排查与解决方法 #### 一、SQL Server Profiler 监控数据库死锁 在处理SQL Server数据库中的死锁问题时,一个非常实用的工具就是SQL Server Profiler。它可以帮助我们捕捉到数据库运行过程中的...

    Microsoft SQL Server 性能调校 -- 源代码

    在IT领域,数据库管理系统是企业数据存储和处理的核心,而Microsoft SQL Server作为一款广泛应用的关系型数据库,其性能优化至关重要。本资源"Microsoft SQL Server 性能调校 -- 源代码"聚焦于如何通过源代码层面...

    SQL SERVER 死锁的解决之道

    本文将深入探讨SQL Server死锁的概念、原因、检测方法以及如何有效地解决和预防死锁。 1. **死锁的概念** 死锁是系统资源分配的一种状态,其中两个或更多的事务相互等待对方释放资源,形成一个循环等待链,导致...

    数据库系统原理与应用(第三版)(SQL Server 2012)-电子教案.rar

    同时,还会探讨并发控制和死锁问题,以及SQL Server 2012提供的解决方案。 安全性是数据库系统不可忽视的一环。教程将介绍用户权限管理、角色和权限的分配,以及如何设置访问控制以保护数据安全。此外,备份与恢复...

    SQL Server死锁总结

    ### SQL Server死锁总结 #### 一、死锁原理 死锁是计算机系统中常见的问题之一,特别是在数据库管理系统中,由于并发控制不当可能导致系统性能下降甚至完全停止响应。在SQL Server中,死锁指的是两个或多个事务...

    Sqlserver死锁处理(比较好用)

    /**//*--调用示例 exec p_lockinfo 0,1 --*/ create proc p_lockinfo @kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉...@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示

    SQL Server 2000 70-229中文版部分答案解析(整理版)下

    70-229考试主要测试考生对SQL Server数据库管理和性能调优的理解,特别是关于死锁和事务隔离级别的概念。 死锁是两个或多个事务在等待对方释放资源而形成的僵局。在描述中提到的例子中,线程T1持有Supplier表的排他...

    sql server 2005 技术内幕t-sql查询源码

    以及SQL Server中的事务管理,包括读未提交(READ UNCOMMITTED)、读已提交(READ COMMITTED)、可重复读(REPEATABLE READ)和串行化(SERIALIZABLE)隔离级别,同时可能涉及死锁问题及解决方案。 通过学习这本书...

    基于java的开发源码-多线程程序死锁检查 JCarder.zip

    基于java的开发源码-多线程程序死锁检查 JCarder.zip 基于java的开发源码-多线程程序死锁检查 JCarder.zip 基于java的开发源码-多线程程序死锁检查 JCarder.zip 基于java的开发源码-多线程程序死锁检查 JCarder.zip ...

    在SQL Server 2005中解决死锁问题

    SQL Server 2005引入了新的处理机制,使得开发者可以通过T-SQL捕获并处理这些异常,从而更有效地解决死锁问题。 在SQL Server 2000中,当事务遇到异常,如死锁,T-SQL脚本会立即停止执行,使得对死锁的处理变得困难...

Global site tag (gtag.js) - Google Analytics