SQLServer|SqlServer 并发事务（死锁跟踪（一）简单测试） SQLServer|事务死锁

-- 测试代码 -- DROP TABLE TestCREATE TABLE Test ( id INT, name VARCHAR(20), info VARCHAR(20), )CREATE CLUSTERED INDEX IX_Test ON DBO.Test(id) CREATE NONCLUSTERED INDEX IX_Test_name ON DBO.Test(name)INSERT INTO Test VALUES(1,'kk',null),(2,'mm',null)SELECT * FROM Test

【测试一】

------------------------------------------------------------ --打开：窗口【1】 BEGIN TRAN update dbo.Test set info='A' where id =1 waitfor delay '00:00:10' update dbo.Test set info='B' where id =2 COMMIT TRAN------------------------------------------------------------ ------------------------------------------------------------ --打开：窗口【2】 BEGIN TRAN update dbo.Test set info='C' where id =2 update dbo.Test set info='D' where id =1 COMMIT TRAN ------------------------------------------------------------

使用 SQL Server Profiler 分析死锁

保存死锁图形（SQL Server 事件探查器）

打开Profile监控事件locks：deadlock

文章图片

文章图片

先执行窗口【1】，再执行窗口【2】。结果窗口【2】死锁：

文章图片

文章图片

表更改结果：

文章图片

文章图片

Profile 捕获到的信息：

文章图片

文章图片

方形的图中，键锁 HoBt ID可以确定是哪个索引：

SELECT * FROM SYS.partitions WHERE hobt_id=72057594040549376 SELECT * FROM SYS.indexes WHERE OBJECT_ID= 629577281 AND index_id=1

文章图片

文章图片

将Profile捕获到的信息拷贝出来：
主要分为两部分，一部分为进程的执行信息，另一部分为堵塞资源的请求信息。

文章图片

文章图片

观察上面这几点，都可以找到死锁的客户端信息和产生死锁的对象，可以大致了解产生这个死锁的过程。
找到的批处理脚本，即可通过其他方法优化解决。

【测试二】

UPDATE Test SET info = NULL SELECT * FROM Test--开启死锁跟踪标志 DBCC TRACEON(1204,-1) DBCC TRACEON(1222,-1)

------------------------------------------------------------ --打开：窗口【3】 BEGIN TRAN update dbo.Test set info='A' where name ='kk' waitfor delay '00:00:05' update dbo.Test set info='B' where name ='mm' COMMIT TRAN------------------------------------------------------------ ------------------------------------------------------------ --打开：窗口【4】 BEGIN TRAN delete from dbo.Test where name ='mm' update dbo.Test set info='C' where name ='kk' COMMIT TRAN ------------------------------------------------------------

先运行窗口【3】，再运行窗口【4】，结果如下：

文章图片

文章图片

窗口【3】作为死锁牺牲品，死锁图如下：

文章图片

文章图片

这是聚集索引和非聚集索引引起的键死锁。

跟踪标志 1204和跟踪标志 1222：
发生死锁时，跟踪标志 1204和跟踪标志 1222会返回在SQLServer 2005 错误日志中捕获的信息。跟踪标志 1204会报告由死锁所涉及的每个节点设置格式的死锁信息。跟踪标志 1222会设置死锁信息的格式，顺序为先按进程，然后按资源。可以同时启用这两个跟踪标志，以获取同一个死锁事件的两种表示形式。

跟踪标志 (Transact-SQL)

检测和结束死锁

DBCC TRACEON(1204,-1)(如果打开1204标志，日志记录如下)

文章图片

文章图片

DBCC TRACEON(1222,-1)(如果打开1222标志，日志记录如下)

文章图片

--【若只有以下索引，死锁的模式和请求的锁如下】-- 不死锁,但都是聚集索引扫描 -- DROP INDEX IX_Test_name ON DBO.Test CREATE CLUSTERED INDEX IX_Test ON DBO.Test(id) --mode(U,U)和mode(X,U) 的键锁和RID锁 -- DROP INDEX IX_Test ON DBO.Test CREATE NONCLUSTERED INDEX IX_Test_name ON DBO.Test(name,id) WITH(DROP_EXISTING = ON)--mode(X,U)和mode(X,U) 的键锁和键锁 CREATE CLUSTERED INDEX IX_Test ON DBO.Test(id) CREATE NONCLUSTERED INDEX IX_Test_name ON DBO.Test(name) WITH(DROP_EXISTING = ON)

上面只是资源死锁确定的一般常用方法，都可以确定死锁问题。最不好的是死锁时输出的语句不是完整的，没有上下文环境。

减少死锁的一些方法：
◆ 按同一顺序访问对象。
◆ 避免事务中的用户交互。
◆ 保持事务简短并处于一个批处理中。
◆ 使用较低的隔离级别。
◆ 使用基于行版本控制的隔离级别。
将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON，使得已提交读事务使用行版本控制。
使用快照隔离。
◆ 使用绑定连接。

【SQLServer|SqlServer 并发事务（死锁跟踪（一）简单测试）】