SQLserver阻塞.docx
- 文档编号:8654060
- 上传时间:2023-02-01
- 格式:DOCX
- 页数:44
- 大小:32.96KB
SQLserver阻塞.docx
《SQLserver阻塞.docx》由会员分享,可在线阅读,更多相关《SQLserver阻塞.docx(44页珍藏版)》请在冰豆网上搜索。
SQLserver阻塞
SQLserver阻塞(来自微软技术支持人员)
作者:
lyf1840
阻塞定义
===============
当来自应用程序的第一个连接控制锁而第二个连接需要相冲突的锁类型时,将发生阻塞。
其结果是强制第二个连接等待,而在第一个连接上阻塞。
不管是来自同一应用程序还是另外一台客户机上单独的应用程序,一个连接都可以阻塞另一个连接。
说明 一些需要锁保护的操作可能不明显,例如系统目录表和索引上的锁。
大多数阻塞问题的发生是因为一个进程控制锁的时间过长,导致阻塞的进程链都在其它进程上等待锁。
常见的阻塞情形包括
===============
1.提交执行时间长的查询。
长时间运行的查询会阻塞其它查询。
例如,影响很多行的DELETE或UPDATE
操作能获取很多锁,这些锁不论是否升级到表锁都阻塞其它查询。
因此,一般不要将长时间运行的决策支持查询和联机事务处理(OLTP)
查询混在一起。
解决方案是想办法优化查询,如更改索引、将大的复杂查询分成简单的查询或在空闲时间或单独的计算机上运行查询。
2.查询不适当地使用游标。
游标可能是在结果集中浏览的便利方法,但使用游标可能比使用面向集合的查询慢。
3.取消没有提交或回滚的查询。
如果应用程序取消查询(如使用开放式数据库连接(ODBC)sqlcancel函数)但没有同时发出所需数目的ROLLBACK和COMMIT
语句,则会发生这种情况。
取消查询并不自动回滚或提交事务。
取消查询后,所有在事务获取的锁都将保留。
应用程序必须提交或回滚已取消的事务,从而正确地管理事务嵌套级。
4.应用程序没处理完所有结果。
将查询发送到服务器后,所有应用程序必须立即完成提取所有结果行。
如果应用程序没有提取所有结果行,锁可能会留在表上而阻塞其他用户。
如果使用的应用程序将
Transact-SQL语句透明地提交给服务器,则该应用程序必须提取所有结果行。
如果应用程序没这样做(如果无法配置它执行此操作),则可能无法解决阻塞问题。
为避免此问题,可以将这些应用程序限制在报表或决策支持数据库上。
5.分布式客户端/服务器死锁。
与常规死锁不同,分布式死锁无法由MicrosoftSQLServer?
2000自动检测到。
如果应用程序打开多个与SQLServer
的连接并异步提交查询,则可能会发生分布式客户端/服务器死锁。
例如,一个客户端应用程序线程有两个开放式连接。
该线程异步启动事务并在第一个连接上发出查询。
应用程序随后启动其它事务,在另一个连接上发出查询并等待结果。
当SQLServer返回其中一个连接的结果时,应用程序开始处理这些结果。
应用程序就这样处理结果,直到生成结果的查询被另一个连接上执行的查询阻塞而导致再没有可用的结果为止。
此时第一个连接阻塞,无限期等待处理更多的结果。
第二个连接没有在锁上阻塞,但仍试图将结果返回给应用程序。
然而,由于应用程序阻塞而在第一个连接上等待结果,第二个连接的结果将得不到处理。
避免阻塞方法
===============
1.对每个查询使用查询超时。
2.对每个查询使用锁定超时。
有关更多信息,请参见自定义锁超时。
3.使用绑定连接。
有关更多信息,请参见使用绑定连接。
4.SQLServer本质上是受客户端应用程序操纵的傀儡。
客户端应用程序对服务器上获取的锁几乎有完全的控制(并对锁负责)。
虽然SQLServer
锁管理器自动使用锁保护事务,但这受客户端应用程序发出的查询类型和对结果的处理方式的直接鼓动。
因此,大多数阻塞问题的解决方案都涉及检查客户端应用程序。
5.阻塞问题常要求检查应用程序提交的SQL语句本身,以及检查与连接管理、所有结果行的处理等有关的应用程序行为本身。
如果开发工具不允许显式控制连接管理、查询超时、结果处理等,阻塞问题可能得不到解决。
设计应用程序以避免阻塞的准则包括
===============
1.不要使用或设计使用户得以填写编辑框的应用程序,编辑框会生成长时间运行的查询。
例如,不要使用或设计提示用户输入的应用程序,允许某些字段保留空白或允许输入通配符。
这可能导致应用程序提交运行时间过长的查询,从而导致阻塞问题。
2.不要使用或设计使用户得以在事务输入容的应用程序。
3.允许取消查询。
4.使用查询或锁定超时,防止失控查询和避免分布式死锁。
5.立即完成提取所有结果行。
6.使事务尽可能简短。
7.显式控制连接管理。
8.在所预计的并发用户全负荷下对应用程序进行应力测试。
以下是一些相关的技术文档。
UnderstandingandResolvingSQLServer7.0or2000BlockingProblems
scid=kb;en-us;224453> HOWTO: TroubleshootApplicationPerformancewithSQLServer scid=kb;en-us;224587> 了解和解决SQLServer7.0或2000阻塞问题-- 本文的发布号曾为CHS224453 本页容 概要 更多信息 概要 本文是对如下MicrosoftKnowledgeBase文章(针对SQLServer6.x)中的SQLServer7.0的更新: 162361INF: 了解和解决SQLServer阻塞问题 以上文章所包含的很多信息已经更新,并包括在SQLServer7.0BooksOnline的“UnderstandingandAvoidingBlocking”主题中。 继续阅读本文之前,请仔细检查这些信息,本文将不再重复这些容。 本文重点讨论如何监视SQLServer捕获相关的系统信息,以及如何分析信息以便成功地解决阻塞问题。 本文使用的标准术语与上面信息中定义的相同。 在本文的讨论中,术语“连接”是指数据库的单个已登录会话。 每个连接均以一个系统进程ID(SPID)出现。 每个SPID通常作为一个进程来引用,尽管在一般意义上它不是一个独立的进程环境。 更准确地说,每个SPID由响应指定客户端请求的单个连接时所需要的服务器资源和数据结构组成。 单个客户端应用程序可能有一个或多个连接。 从SQLServer的角度看,来自单个客户端计算机的单个客户端应用程序的多个连接,与来自多个客户端应用程序或多个客户端计算机的多个连接之间,没有什么区别。 一个连接可以阻塞另一个连接,这与它们是否出自同一个应用程序或出自两个不同的客户端计算机上的独立应用程序无关。 更多信息 阻塞是具有基于锁定的并发特性的任何关系数据库管理系统(RDBMS)所不可避免的特征。 在SQLServer上,当一个SPID锁定了一个特定资源,而第二个SPID试图获得对同一资源的冲突锁定类型时,就会发生阻塞。 通常,第一个SPID锁定资源的时间围非常小。 当它释放锁定时,第二个连接即可顺利地获得对资源的自有锁定,并继续下一步操作。 这是正常情况,在一天中可能发生许多次,对系统性能没有任何明显的影响。 查询的持续时间和事务环境决定了锁定被占据的时间长短,因而也决定了对其它查询的影响。 如果查询不是在事务执行(并且没有使用任何锁定提示),对于SELECT语句引起的锁定,则只有当某个资源被实际读取时才会对该资源锁定,而不会在整个查询持续期间锁定该资源。 至于INSERT、UPDATE和DELETE语句,则在查询期间锁定它们,这既是为了数据的一致性,也是为了允许在必要时进行回滚查询。 对于事务执行的查询,决定锁定持续时间长短的因素包括: 查询类型、事务隔离级别以及查询中是否使用了锁定提示。 有关锁定、锁定提示和事务隔离级别的说明,请参见SQLServer7.0BooksOnline中的如下主题: • “UnderstandingLockinginSQLServer” • “LockingArchitecture” • “LockCompatibility” • “LockingHints” • “ChangingDefaultLockingBehaviorinOracleandSQLServer” 当锁定和阻塞增加到对系统性能产生不利影响的时候,通常是由于如下原因所至: • 一个SPID锁定了一组资源,并且在释放锁之前持续了很长一段时间。 这种类型的阻塞在一段时间后自行消失,但会导致性能降低。 • 一个SPID锁定了一组资源,并且不再释放它们。 这种类型的阻塞不会自行消失,并且会无限期地阻止对受影响资源的访问。 在上面第一种情况中,一段时间后SPID释放锁定时阻塞问题将自行消失。 但是,情况可能是变化多端的,因为一段时间后不同的SPID会阻塞不同的资源,总是产生变化的目标。 由于这个原因,这些情况很难使用SQLServerEnterpriseManager或单独的SQL查询来解决问题。 而第二种情况则产生更便于诊断的持续状态。 收集阻塞信息 要减少解决阻塞问题的难度,数据库管理员可以使用SQL脚本连续地监视SQLServer上的锁定和阻塞状态。 这些脚本可以提供一段时间后指定实例的快照,从而帮助用户全面了解存在的问题。 关于如何用SQL脚本监视阻塞的说明,请参见下面的MicrosoftKnowledgeBase文章: 251004INF: 如何监视SQLServer7.0阻塞 271509INF: 如何监视SQLServer2000阻塞 本文中的脚本将执行下面的任务。 在可能的情况下,我们还提供通过EnterpriseManager或特定SQL查询来获得该信息的方法。 1. 找出阻塞链源头的SPID。 除了使用上面提到的脚本外,还可以使用SQLEnterpriseManager找出阻塞链源头的SPID,方法如下: a. 展开服务器组,然后展开服务器。 b. 展开Management,然后展开CurrentActivity。 c. 展开Locks/ProcessID。 SPID及其阻塞信息将显示在详细信息窗格中。 正在造成阻塞的SPID将显示为“(Blocking)”。 但请注意,有时需要使用脚本查询而不使用EnterpriseManager,因为某些类型的tempdb阻塞问题可能会阻止您运行使用临时表操作的查询。 使用直接查询可以给您提供必要的控制能力,以便避免出现该问题。 2. 查找引起阻塞的SPID正在运行的查询。 脚本方法使用如下查询来确定特定SPID发出的命令: DBCCINPUTBUFFER( 另外一种方法,可以按如下步骤使用SQLEnterpriseManager: a. 展开服务器组,然后展开服务器。 b. 展开Management,然后展开CurrentActivity。 c. 单击ProcessInfo。 SPID将显示在详细信息窗格中。 d. 双击引起阻塞的SPID即可看见该SPID执行的最后一个Transact-SQL命令组。 3. 查找引起阻塞的SPID当前占用的锁定类型。 执行sp_lock系统存储过程,即可找到该信息。 另外一种方法,可以按如下步骤使用EnterpriseManager获得该信息: a. 展开服务器组,然后展开服务器。 b. 展开Management,然后展开CurrentActivity。 c. 展开Locks/ProcessID。 SPID以及正在使用的锁定的相关信息将显示在详细信息窗格中。 4. 查找引起阻塞的SPID的事务嵌套级别和进程状态。 SPID的事务嵌套级别可以在TRANCOUNT全局变量中找到。 但是,通过按如下方式查询sysprocesses表,可以从SPID以外找到它。 SELECTopen_tranFROMSYSPROCESSESWHERESPID= go 返回的值为该SPID的TRANCOUNT值。 该值显示阻塞SPID的事务嵌套级别,反过来可以解释为什么SPID正在使用锁定。 例如,如果该值大于零,则SPID位于事务中间(这种情况下,根据事务隔离级别,可以预计它将保持已经获得的某些锁定)。 要查看数据库中是否存在任何长时间打开的事务,还可以使用DBCCOPENTRANdatabase_name。 收集SQLServer事件探查器跟踪信息 除以上信息外,通常还需要捕获服务器上各种活动的“事件探查器”跟踪文件,以便彻底调查SQLServer上的阻塞问题。 如果SPID在事务执行了多个语句,那么只有最后一个语句出现在DBCCINPUTBUFFER输出中。 但是,有可能是更前面的某个命令导致锁定仍然被占据。 “事件探查器”跟踪文件将使您能看见由SPID在当前事务执行的所有命令。 下面的步骤将帮助您设置SQLServer事件探查器以便捕获踪迹。 1. 打开SQLServer事件探查器。 2. 在Tools菜单上,单击Options。 3. 确保选中AllEventClasses和AllDataColumns选项。 4. 单击OK。 5. 在File菜单中,指向New,然后单击Trace。 6. 在General选项卡上,指定要捕获数据的跟踪名称和文件。 7. 在Events选项卡上,将如下事件类型添加到跟踪中: ErrorandWarning Exception 该事件表示发生了异常错误。 严重度低于25的异常错误表示错误已从SQLServer返回客户端。 严重度为25的异常错误是部的SQLServer异常错误,如下所述应当被筛选掉。 Misc. Attention 该事件表示已经发出一个注意信号。 发出注意信号的一般原因是发生客户端取消操作或查询超时。 Sessions Connect 该事件表示已建立一个新连接。 Sessions Disconnect 该事件表示一个客户端已经断开连接。 Sessions ExistingConnection 该事件表示当“SQL事件探查器”跟踪启动时存在一个连接。 TSQL RPC: Starting 该事件表示已经开始执行远程过程调用(RPC)。 TSQL SQL: BatchStarting 该事件表示已经开始执行Transact-SQL命令组。 StoredProcedures SP: StmtStarting 该事件表示存储过程中的语句正在开始执行。 存储过程名位于事件的Text部分的开头。 另外,您可以包括如下事件以便获得更进一步的信息。 如果当前的运行环境是高数据量运营环境,那么可以决定只使用以上事件,因为有了它们足以能解决阻塞问题了。 如果还包括以下的附加事件,可以使您能更方便迅速地找出问题的根源,但这也会增加系统负载并增加跟踪输出的大小。 Misc. ExecutionPlan 该事件显示被执行的Transact-SQL语句的计划树。 Transactions DTCTransaction 该事件跟踪两个或多个数据库或服务器之间的Microsoft分布式事务处理协调器(MSDTC)事务。 Transactions SQLTransaction 该事件跟踪SQLBEGIN、SAVE、COMMIT和ROLLBACKTRANSACTION语句。 TSQL RPC: Completed 该事件表示远程过程调用(RPC)已经执行完毕。 TSQL SQL: BatchCompleted 该事件表示Transact-SQL命令组已经执行完毕。 StoredProcedures SP: StmtCompleted 该事件表示存储过程中的语句已经执行完毕。 8. 在DataColumns选项卡上,确保包括了如下列: StartTime、EndTime、ConnectionID、SPID,EventClass、Text、IntegerData、BinaryData、ApplicationName、NTUserName和SQLUserName。 如果包括上面第二个表中的附加事件,则还要包括以下数据列: Duration、CPU、Reads和Writes。 9. 在Filters选项卡上,排除SQLServer部异常错误。 在TraceEventCriteria框中,选择Severity并在Maximum框中键入24。 然后单击OK。 有关监视从SQLServer发送到客户端的错误消息的详细信息,请参见下面的MicrosoftKnowledgeBase文章: 199037INF: 跟踪从SQLServer发送到客户端的错误消息 有关使用“事件探查器”的信息,请参见SQLServerBooksOnline。 识别和解决常见的阻塞情形 通过查看以上信息,可以找出大多数阻塞问题的原因。 本文下面的容将讨论如何使用这些信息来识别并解决常见的阻塞情形。 该讨论假设您已经使用文章Q251004(前面已提到)中的阻塞脚本来捕获有关阻塞SPID的信息,并已经使用上面提到的事件制作了“事件探查器”跟踪。 查看阻塞脚本输出 • 检查sysprocesses输出,以找到阻塞链的开头。 如果没有为阻塞脚本指定快速模式,则在脚本输出中会有一个标题为“SPIDsattheheadofblockingchains”的段落列出引起阻塞的SPID: 在阻塞链的开头的SPID spid ------ 9 10 如果指定了快速选项,仍然可以通过查看sysprocesses输出来找出阻塞头。 以下容摘自sysprocesses输出: spidstatusblocked 9sleeping0 10sleeping0 11sleeping13 12sleeping10 13sleeping9 14sleeping12 其中,可以在blocked列中看见SPID9和SPID10均为0,这表示它们当前没有被阻塞,但它们出现在其它SPID的blocked列中。 这表示SPID9和SPID10分别位于独立阻塞链的开头。 • 检查sysprocesses输出中有关位于阻塞链开头的SPID的信息。 一定要检查如下sysprocesses字段: • Status 该列快速展示特定SPID的状态。 通常,状态sleeping表示SPID已经执行完毕,并且正在等候应用程序提交另一个查询或命令组。 状态runnable表示SPID当前正在处理查询。 下表简单解释各种状态值。 Background SPID正在执行后台任务。 Sleeping SPID当前没有执行任务。 通常,这表示SPID正在等待应用程序的命令。 Runnable SPID当前正在执行任务。 Dormant 与Sleeping相同,但Dormant还表示在完成RPC事件后SPID已被重置。 重置将清除RPC事件期间所使用的资源。 这是个正常状态,并且SPID可用,正在等待执行后续命令。 Rollback SPID在事务的回滚过程中。 Defwakeup 表示SPID正在等待处于释放过程中的资源。 waitresource字段应当表示提到的资源。 Spinloop 在试图获得用于SMP系统并发控制的旋转锁定(spinlock)时进程正处于等待中。 • Open_tran 该字段告诉您SPID的事务嵌套级别。 如果该值大于0,则SPID位于一个打开的事务中,并且可能正在占用由事务中的任何语句获得的锁定。 • Lastwaittype、waittype和waittime lastwaittype字段告诉您SPID的最后一个waittype或当前waittype。 该字段是SQLServer7.0中的新字段,是waittype字段的字符串表现形式(waittype是被保留的部二进制列)。 如果waittype是0x0000,那么SPID当前没有等待任何任务,并且lastwaittype的值表示SPID拥有的最后一个waittype。 如果waittype为非零,则lastwaittype的值表示SPID的当前waittype。 有关不同lastwaittype和waittype值的简要说明,请参见下面的MicrosoftKnowledgeBase文章: 244455INF: SysprocessesWaittype和Lastwaittype字段的定义 waittime值可以用来确定SPID是否正在执行任务。 如果在查询sysprocesses表时返回waittime列中的值,而该值小于从上一个sysprocesses查询所获得的waittime值,那么,这表示前面的锁定已被获得并被释放,现在正在等候新的锁定(假设是非零的waittime)。 通过比较各个sysprocesses输出中的waitresource,可以对此进行确认。 • Waitresource 该字段表示SPID正在等候的资源。 下表列出了waitresource常见格式及其意义: Table DatabaseID: ObjectID TAB: 5: 261575970 这里,数据库ID5是pubs示例数据库,对象ID261575970是titles表。 Page DatabaseID: FileID: PageID PAG: 5: 1: 104 这里,数据库ID5是pubs,文件ID1是主数据文件,而页104是属于titl
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLserver 阻塞