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 warning和Sort warning则反映可能没有好的索引可供使用。

 

建议解决方法

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

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

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

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

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

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

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

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

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

 

 

（二）不正确的事务或事务隔离级别设置

      当死锁是由于不正确的事务或事务隔离级别设置所导致时，SQL SERVER 2005/2008动态管理视图sys.dm_exec_requests会提供相关信息，该SESSION_ID的status字段值为“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 在发生任何错误时，都要回滚事务)