SQL SERVER 查询性能优化——分析事务与锁(四)

上接SQL SERVER 查询性能优化——分析事务与锁(三)

 

二、死锁的原因及相关处理

       死锁的原因很多,尤其是前端应用程序没有合理的使用事务,或者对错误处理不当而导致事务长期持有而没有关闭。接下来讲讲最常见的几种死锁情况,并提供可能的解决方法。

1.费时的查询事务

2.不正确的事务或事务隔离等级设置

3.事务未正确处理

4.未检测到的分布式死锁

5.锁定数据粒度太高或太低

6.Compile Blocking

 

  (一)费时的查询事务

       当查询或事务所花的时间较长时,可由SQL SERVER 2005/2008 动态管理视图sys.dm_exec_requests提供相关信息(也可观察sysprocesses系统视图),如status字段为“running”,wait_type为非“NULL”值。“running”代表该进程依然在执行,而wait_type则表示该进程是否在等待资源。如下图。

 

       这也可以从SQL SERVER 2005的Microsoft SQL Server Management Studio管理工具中的活动监视器--》进程信息--》查看相关信息。如下图,所圈出的相关字段可以观察进程之间的相关信息。

 

 

 

      如上图所示,进程“55”被“54”锁住,也可以从“等待类型”列中看出相关信息。

     通过SQL PROFILER工具中观察“T SQL”事件下的“SQL StmtCompleted/SQL BatchComplete”,或是“存储过程”事件类别下的“SP StmtCompleted/SP BatchComplete/RPC Complete ”等事件,可观察SQL语句执行情况,并通过TextData(呈现T-SQL语句执行内容)Duration(语句执行所需的时间)字段判断哪一句语句是否执行时间过长而导致锁定行为。如下图1、图2。

 

图1

      如图1中圈出来的地方,没有结束时间,所以此SQL语句还在继续等待。

 

图2

     如图2中圈出来的地方,虽然有执行结束时间,但是duration的执行时间过长,也就是说此SQL语句等待了这么长的时间,等待其他事务释放资源。

 

     如果查询语句使用大量系统资源导致查询耗时过长,可能伴随的现象有:处理器,硬盘I/O,内存等的使用率很高。SQL PROFILER工具中的“错误和警告”事件类别中的Missing Column Statistics,产生过多“存储过程”事件类型的SP:Recompile事件也值得注意,前者表示无法产生有效的执行计划,后者表示存储过程的编写方式,无法提供高速缓存执行计划。“错误和警告”事件类别中的Hash warningSort warning则反映可能没有好的索引可供使用。

 

建议解决方法

      如果事务执行时间过长,一直锁住资源不放可能导致其他想要执行的事务被锁。例如:设置事务隔离级别为“可重复读取”,当查询语句(SELECT)执行时间过长时,则更新语句(UPDATE)则无法对数据进行更新,最终导致系统瘫痪。出现此类情况,可以试着使用以下方式进行解决

1.新增或设置适当的索引以增加查询速度

2.更新统计信息以避免执行计划使用旧的统计信息

3.重新设计数据表、存储过程等对象

4.检查是否过度使用触发器和游标。

如果无法通过以上方式提高 工作效率,则可能要考虑修改系统的工作流程

1. 分割工作,不要同时执行所有的需求

2. 切割工作时间,将工作排至系统不繁忙的时段执行

3. 切割工作属性,将工作交给另一个数据库去执行,把查询与更新分成两个数据库来执行。

 

 

(二)不正确的事务或事务隔离级别设置

      当死锁是由于不正确的事务或事务隔离级别设置所导致时,SQL SERVER 2005/2008动态管理视图sys.dm_exec_requests会提供相关信息,该SESSION_IDstatus字段值为“running”,wait_type非“NULL”值,通过sys.dm_exec_session动态视图的transaction_isolation_level字段可以看出进程所设置的事务隔离级别。且从Microsoft SQL Server Management Studio管理工具中的“活动监视器--》进程信息”视图,该进程的“打开的事务”字段显示为非“0”值,表示为该进程仍握有事务资源。

       通过SQL PROFILER工具查找“TextData”,观察前端传递命令中是否含有不当的事务设置,例如,设置隐含式事务(SET IMPLICIT_TRANSACTION ON)、事务隔离等级或是设置锁定提示等。

 

建议解决方式:

       事务设置大多与实际业务逻辑有关,不容易界定是否有必要,如果你通过跟踪文件找到不正确的事务或事略等级隔离设置时,也需要与开发者讨论设置的必要性。尤其是当事务中包含大量数据的运算的情况,可能需要研究如何切割成较小的事务,但仍需要符合原来的数据完整性和业务逻辑要求。

 

 

(三)事务未正确处理

 

      开启了事务,但是没有回滚或没有提交,形成了未提交事务。它的特征与观察方式与上面所述相同。从下图中可以看出进程“54”仍持有事务,但此进程停滞不做事,也无等待任何资源,但仍持有事务,从SQL SERVER 2005的Microsoft SQL Server Management Studio管理工具中的“活动监视器--》进程信息”视图,进一步观察“上一批”字段,检查进程是否已经持有资源一段时间。

 

 

在SQL 2005(2008)执行代码示例一,得到如下图。

 

select spid 进程,STATUS 状态, 登录帐号=SUBSTRING(SUSER_SNAME(sid),1,30)

,用户机器名称=SUBSTRING(hostname,1,12)

,是否被锁住=convert(char(3),blocked)

,数据库名称=SUBSTRING(db_name(dbid),1,20),cmd 命令,waittype as 等待类型

,last_batch 最后批处理时间,open_tran 未提交事务的数量

from master.sys.sysprocesses

--列出锁住别人(在别的进程中blocked字段中出现的值)但自己未被锁住(blocked=0)

Where spid in (select blocked from master.sys.sysprocesses) and blocked=0

 

 

 

 

建议解决方式

      利用SQL PROFILER 工具中的事务事件类别,录制SQL SERVER所触发的事务事件,也可以通过dbcc opentran (‘<数据库>’)命令观察针对某个数据库执行最久的事务事件,由哪个程序拥有,如果没有指定数据库名称或ID,则返回当前连接所在的数据库执行最久的事务事件,一般未提交事务可能是由于未做好错误处理所造成的。

 执行dbcc opentran命令的之后,如下图。其中UID是无意义的。

 

 

     例如,执行命令逾时,放弃批处理但未回滚事务。其中的错误处理,应该如下例一般。

If @@trancount>0

   Rollback tran

 

---或是设置:

 

Set XACT_ABORT on

 

(上述设置是指当SQL SERVER 在发生任何错误时,都要回滚事务)

posted @ 2013-06-01 00:00  DotNet菜园  阅读(5333)  评论(2编辑  收藏  举报