一次死锁情况分析过程

一般认为，数据库死锁是发生在不同数据库对象争用时，但最近，遇到了一个情况：一个SP会执行很长时间（数据量太大)，在执行过程中，需要反复插入和更新一个缓冲数据表。在SP单独工作时正常，后来用户需要及时分析数据，需要随时用这个SP处理少量的数据。这时，就发生了SP同时有多个副本在运行。会操作同一个缓冲表，由于数据有标识，所以数据不会冲突，但却常会发生死锁。经分析，认为是由于行锁的争用造成了死锁的发生。

测试过程如下：

如始化数据：

 

DROP TABLE TestLock
Create Table TestLock(ID CHAR(1000),
                      Data CHAR(1000))

INSERT INTO TestLock (ID,DATA) VALUES('1','BASHAN1')
INSERT INTO TestLock (ID,DATA) VALUES('2','BASHAN2')
INSERT INTO TestLock (ID,DATA) VALUES('3','BASHAN3')
INSERT INTO TestLock (ID,DATA) VALUES('4','BASHAN4')
INSERT INTO TestLock (ID,DATA) VALUES('5','BASHAN5')
INSERT INTO TestLock (ID,DATA) VALUES('6','BASHAN6')
INSERT INTO TestLock (ID,DATA) VALUES('7','BASHAN7')

INSERT INTO TestLock (ID,DATA) VALUES('8','BASHAN8')
INSERT INTO TestLock (ID,DATA) VALUES('9','BASHAN9')
INSERT INTO TestLock (ID,DATA) VALUES('10','BASHAN10')

INSERT INTO TestLock (ID,DATA) VALUES('11','BASHAN11')
INSERT INTO TestLock (ID,DATA) VALUES('12','BASHAN12')
INSERT INTO TestLock (ID,DATA) VALUES('13','BASHAN13')
INSERT INTO TestLock (ID,DATA) VALUES('14','BASHAN14')
INSERT INTO TestLock (ID,DATA) VALUES('15','BASHAN15')
INSERT INTO TestLock (ID,DATA) VALUES('16','BASHAN16')
INSERT INTO TestLock (ID,DATA) VALUES('17','BASHAN17')

INSERT INTO TestLock (ID,DATA) VALUES('18','BASHAN18')
INSERT INTO TestLock (ID,DATA) VALUES('19','BASHAN19')
INSERT INTO TestLock (ID,DATA) VALUES('20','BASHAN20')

go

SP_SPACEUSED TestLock
SELECT * FROM TestLock

 

启动一个事务：

BEGIN TRAN T2

UPDATE TestLock SET DATA= RTRIM(DATA)+'X' WHERE ID = '20'

这时,事务T2会获取一个页意向排它锁和行排它锁,

spid	dbid	ObjId	IndId	Type	Resource	Mode	Status
53	6	1701581100	0	TAB		IX	GRANT
53	6	1701581100	0	RID	1:9197:1	X	GRANT
53	6	1701581100	0	PAG	1:9197	IX	GRANT

这时,在前一个事务T2还没提交事务时,执行下面的事务

启动另一个事务：（需要要另一个会话中执行)
BEGIN TRAN T1

UPDATE TestLock WITH(TABLOCKX) SET DATA= RTRIM(DATA)+'X' WHERE ID = '2'

COMMIT

则事务 T1会占用锁的情况如下：

spid	dbid	ObjId	IndId	Type	Resource	Mode	Status
52	6	1701581100	0	RID	1:9186:1	X	GRANT
52	6	1701581100	0	RID	1:9197:1	U	WAIT
52	6	1701581100	0	PAG	1:9197	IU	GRANT
52	6	1701581100	0	PAG	1:9186	IX	GRANT

 

这时，T1占用了1:9186:1  行上的排它锁，并等待1:9187:1  上的更新锁，如果这时，我们再在事务T2中申请行1:9186:1上的锁，就会发现死锁。如： 

现在再执行T2剩下的事务：（原会话中）

    UPDATE TestLock WITH(TABLOCKX) SET DATA= RTRIM(DATA)+'X' WHERE ID = '1'

COMMIT

 则事务T2又会试图申请行1:9186:1上的锁，而T1已持用这个行上的排它锁，这时，死锁就发生了。

分析：

 

通过上面的测试过程，我们可以发现，在更新记录时，系统会在数据所在页、表上加意向锁，由于意向锁是相兼容的，所以不会发生等待，可以及时获取。

但对于更新数据行，系统会加上行排它锁，同时，系统在检索需要更新的数据时，需要对搜索的所有数据加上更新锁，由于排它锁和更新锁不兼容，所以T1的更新操作必须等待T2事务释放占有的排它锁。 这时如果T2事务顺利结束了，那一切都好了，但如果T2事务再去申请T1事务独占的资料时，死锁就发生了。

解决办法：

基于上分析，要解决这种情况的死锁，可行的方向有两个:隔离资源，使锁不冲突，这个具体的办法是添加索引，如在我测试的示例中，只需要对ID列添加一个聚集索引，就不会发生死锁了。

另一个方向是：实现资源的串行访问，避免事务部分占用资源，争取一次性分配完事务所需要的所有资料。对这种方式，可以在更新语句上加了表锁提示,如：

UPDATE TestLock WITH(TABLOCKX) SET DATA= RTRIM(DATA)+'X' WHERE ID = '20'

我的SP里没有启用事务，SP主要功能是分析计算而不是事务处理。SP里有大量的UPDATE语句和少量的Insert语句，几乎都是操作同一个数据表，UDATE语句相对较复杂，即需要更新，又需要统计计算（由于主要功能是分析计算，所以在统计查找数据时，使用了NOLOCK)，如：

UPDATE dbo.g_CalculateChargeListBuffer
SET CombineBalance1 = b.CombineBalance1,
FROM  dbo.g_CalculateChargeListBuffer a,
(
    SELECT ISNULL(RefChargeCode,ChargeCode) AS ChargeCode, 
           CombineBalance1 = SUM(Balance1)
    FROM dbo.g_CalculateChargeListBuffer WITH(NOLOCK)
    WHERE VPackageID = @VPackageID 
    GROUP BY ISNULL(RefChargeCode,ChargeCode)
) b
WHERE a.VPackageID = @VPackageID AND a.ChargeCode = b.ChargeCode

由于SP用于分析计算，整个SP中各批处理之间没有采用事务。所以SP中的事务只会发生在同一条SQL语句执行期间。故可以认为这些事务相对较小。

同时，基于SP的作用，对系统的并发性要求不高，主要是处理性能。

重要的是，Where条件筛选列的选择性不高，创建索引效果估计不太理想。

 

所以我现在采用的是直接加表锁的形式解决了此问题。

 

不知那位网友有更好的办法，欢迎指教。