关于SQL Server数据库死锁的探讨

 

现在大型软件项目基本上都是采用数据驱动方式进行开发，数据库的设计，性能，用户的并发数都将影响到产品能否正常运行。如果数据库的设计存在不合理的地方，往往会导致产品性能低下，甚至发生死锁等异常现象。

    接下去我将结合文书项目中出现的死锁现象跟大家一起探讨一下数据库设计的时候，需要注意的一些事项。

 

死锁的原因

    死锁的最深层的原因就是资源竞争。具体表现如下：

表 现 一

一个用户A 访问表A(锁住了表A),然后又访问表B

另一个用户B 访问表B(锁住了表B),然后企图访问表A

这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B,才能继续，好了他老人家就只好老老实实在这等了

同样用户B要等用户A释放表A才能继续，这就死锁了

解决方法：

这种死锁是程序本身的BUG产生的，只能调整程序逻辑

1：尽量避免同时锁定两个资源

2：必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定

资源

表现二：

用户A读一条记录，然后修改该条记录

这时用户B修改该条记录

这里用户A的事务里锁的性质由共享锁(S)企图上升到独占锁(X)，而用户B里的独占锁(X)由于A 有共享锁存在，所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升为独占锁，于是出现了死锁。这种死锁比较隐蔽，在一般的大点的项目中经常发生。

解决方法：

让用户A的事务在Select时就用Update Lock，在资源上加上更新锁(U)，防止其他事务企图对该资源进行资源竞争。

语法如下：

select * from table1 with(updlock) where ....

 

 

案例

前提

文书项目中，对DB进行操作时，采用下面的几个策略

    事务级别为READUNCOMMITTED(NOLOCK)，也就是说允许脏读

    采用.Net的 SqlDataAdapter ( 数据适配器 ) 将 DataSet 中有变更的数据反映到 DB 中。从 Schema 文件中读取表的结构，自动构造 Insert 、 Update 、 Delete 语句。 ( 如果数据库定义发生了变更，只需更新 Schema 文件，可以避免程序的修正 ) 。

 

概述

文书项目从2004年开始，正式在日本多个自治体运行，死锁的现象一直都没有出现。今年，在一个比较大的自治体上线的时候，由于用户量比较大，频繁出现死锁的现象。客户的运用陷入瘫痪。

 

死锁过程

1、用户A的操作对象是文书4

2、用户B的操作对象是文书1

3、用户A更新数据表A的文书4纪录时，在该记录上加了排他锁(X)

4、用户B也同时更新数据表A的文书1纪录，并在该记录上加了排他锁(X)

5、用户A更新数据表B的文书4的纪录时，在该记录上加了排他锁(X)

6、用户B更新数据表B的文书4的纪录时，由于某种原因发生了LockTimeOut的异常

7、这时由于业务要求，用户A还得再次跟新数据表A的文书4纪录，也由于某种原因发生了LockTimeOut。而且这时候发生了死锁现象，用户B的事务被SQL SERVER管理员踢出执行队列。

数据表 A

文书OID番号(PK) numeric （ 18 ， 0 ）	……
1	文书1
4	文书4

数据表 B

起案情报番号(PK) numeric （ 18 ， 0 ）	……
1	文书4
4	文书1

 

 

原因分析

由于文书的事务级别允许脏读，并且用户A和用户B所操作的资源上不存在冲突，按道理来说不会发生死锁现象。通过SQL Server的性能分析器跟踪用户A和用户B对数据库的操作过程，发现他们对数据表A和数据表B更新时的执行计划里不是采用Index Seek定位纪录，而是采用Index Scan来定位纪录。

原来，在Schema文件中，数字类型不能定义最大的长度，共通基盘生成SQL语句的时候，将数字类型的长度自动设为28位(默认值)。数据库优化器进行优化处理时，发现SQL语句的长度比实际数据库中的长度大，不能进行优化处理，采用Index Scan对要更新的纪录进行定位。

 

对应策

1、新建一个XML文件，存放各个表的主健信息。

2、在共通基盘生成SQL语句的时候，通过读取XML文件，将正确的主健信息传递

给SQL服务器。

 

 

对应结果

Index Scan的执行操作全部转换为Index Seek，死锁现象消失。