详解SQL Server Profiler剖析死锁几大步骤.docx

1、详解SQL Server Profiler剖析死锁几大步骤本文将为大众讲的是SQL Server Profiler剖析死锁几大步骤，这里也是为了大众更好的做好数据库的维护工作。在两个或多个SQL Server进程中，每一个进程锁定了其他进程试图锁定的资源，就会出现死锁，比方，进程process1对table1持有1个排它锁(X),同时process1对table2请求1个排它锁(X), 进程process2对table2持有1个排它锁(X),同时process2对table1请求1个排它锁(X) 类似这种情况，就会出现死锁,除非当某个外部进程断开死锁，否则死锁中的两个事务都将无限期等待下去。M

2、icrosoft SQL Server 数据库引擎死锁监视器定期检验陷入死锁的任务。假如监视器检测到循环依靠联系，将挑选其中一个任务作为牺牲品(通常是挑选占资源比较小的进程作为牺牲品)，然后终止其事务并提示不正确 1205。这里咱们议决 SQL Server Profiler来监视剖析死锁的发生流程，那样咱们就会深刻理会死锁的成因。1.建立测试表。在 Microsoft SQL Server Management Studio上，新建一个查询，写建立表DealLockTest_1 & DealLockTest_2两个表：脚本：1. useTest 2. -建立3. 剖析4. 死锁运用5. 到的

3、两个表DealLockTest_1&DealLockTest_2 6. go 7. SetNocountOn 8. Go 9. ifobject_id(DealLockTest_1)IsNotNull 10. DropTableDealLockTest_1 11. go 12. CreateTableDealLockTest_1 13. ( 14. IDintIdentity(1,1)PrimaryKey, 15. Namenvarchar(512) 16. ) 17. ifobject_id(DealLockTest_2)IsNotNull 18. DropTableDealLockTest

4、_2 19. go 20. CreateTableDealLockTest_2 21. ( 22. IDintIdentity(1,1)PrimaryKey, 23. Namenvarchar(512) 24. ) 25. GoCode highlighting produced by Actipro CodeHighlighter (freeware)http:/www.CodeH-插入一些测试数据 1. InsertIntoDealLockTest_1(Name) 2. SelectnameFromsys.all_objects 3. InsertIntoDealLockTest_2(Na

5、me) 4. SelectnameFromsys.all_objects 5. Go 建立好表和插入测试数据后，先执行脚本代码（因为咱们不须要跟踪该代码），紧接着，咱们就模拟两个会话，一个会话里面包含一个事务。这里咱们就新建两个查询，其中第一个会话，是更新DealLockTest_1表后，等待5秒钟，更新DealLocktest_2.1. UseTest2. Go3. -第一个会话4. BeginTran5. UpdateDealLockTest_16. SetName=Ntest17. WhereID08. /*这里的Waitfor等待，是为了简单 获取死锁的发生*/9. WaitforDe

6、lay00:00:0510. UpdateDealLockTest_211. SetName=Ntest212. WhereID013. CommitTran14. Go代码写好后，咱们先不要执行代码，接下来就写第二个会话代码； 第二个会话更新表的顺序，刚好与第一个会话相反，是更新DealLockTest_2表后，等待5秒钟，更新DealLocktest_1. 1. UseTest 2. Go 3. -第二个会话 4. BeginTran 5. UpdateDealLockTest_2 6. SetName=Ntest17. WhereID0 8. /*这里的Waitfor等待，是为了简单9.

7、 获取死锁的发生*/ 10. WaitforDelay00:00:05 11. UpdateDealLockTest_1 12. SetName=Ntest213. WhereID0 14. CommitTran 15. Go第二个会话代码，也先不要执行。2.启动SQL Server Profiler，建立 Trace(跟踪).启动SQL Server Profiler工具(在Microsoft SQL Server Management Studio的工具菜单上就发觉它)，建立一个Trace,Trace属性挑选首要是包含:Deadlock graph Lock: Deadlock Lock:

8、 Deadlock Chain RPC:Completed SP:StmtCompleted SQL:BatchCompleted SQL:BatchStarting点击查看大图点执行按钮，启动Trace。3.执行测试代码&监视死锁。转到 Microsoft SQL Server Management Studio界面，执行第一个会话&第二个会话的代码，稍稍等待5秒钟，咱们就会发觉其中一个会话收到报错消息咱们再切换到SQL Server Profiler界面，就能发觉 SQL Server Profiler收到执行脚本流程发生死锁的信息。点击查看大图OK,这里就先停止SQL Server Pr

9、ofiler上的“暂停跟踪” Or 停止跟踪按钮，下面咱们详细剖析死锁发生流程。4.剖析死锁如下图，咱们能够看到第一个会话在SPID 54,第二个会话在SPID 55,一旦SQL Server发觉死锁，它就会确定一个优胜者，可成功执行，和另一个作为牺牲品，要回滚。能够到看到EventClass列中，两条SQL:BatchCompleted事件紧跟在Lock:DealLock后面，其中一条，它就是作为牺牲品，它会被回滚.而另一条SQL:BatchCompleted将会是优胜者，成功执行。那么，谁是优胜者，谁是牺牲品呢? 不用着急，议决 DealLock graph事件，所返回来的信息，咱们能够知

10、晓结果。 点击查看大图咱们虽然无法明白DealLock graph图示的意思，但议决图中描述的联系，咱们知晓一些有用的信息。图中左右两旁椭圆形相当一个处理节点(Process Node),当鼠标移动到上面的时刻，能够看到内部执行的代码，如Insert,UPdate,Delete.有打叉的左边椭圆形就是牺牲者，没有打叉的右边椭圆形是优胜者。中间两个长方形就是一个资源节点(Resource Node)，描述数据库中的对象,如一个表、一行或一个索引。在咱们当前的实例中，资源节点描述的是，在聚集索引请求取得排它锁(X)。椭圆形与长方形之间，带箭头的连线示意，处理节点与资源节点的联系，包含描述锁的模式.

11、接下来咱们更细致的看图里面的数据表明。先看右边作为优胜者的这椭圆形，咱们能够看到内容包含有：服务器进程 ID： 服务器进程标识符 (SPID)，即服务器给拥有锁的进程分配的标识符。服务器批 ID： 服务器批标识符 (SBID)。执行上下文 ID： 执行上下文标识符 (ECID)。与指定 SPID 有关联的给定线程的执行上下文 ID。ECID = 0,1,2,3, .n，其中 0 始终示意主线程或父线程，并且 1,2,3, .n 示意子线程。死锁优先级： 进程的死锁优先级有关 可能值的细致信息，请参阅 SET DEADLOCK_PRIORITY (Transact-SQL)。已用日志： 进程所运

12、用的日志空间量。所有者 ID： 正在运用事务并且当前正在等待锁的进程的事务 ID。事务描述符： 指向描述事务状态的事务描述符的指针。这些数据描述，对于咱们理会死锁，只须要知晓其中的一些就够，除非咱们在专门SQL Server机构工作，才可能要深入理会它们。点击查看大图下面咱们来看左边作为牺牲品的这椭圆形处理节点，它告诉咱们以下信息：1.它是一个失败的事务。（蓝色的交叉示意）2.它是作为牺牲品的T-SQL代码。3.它对右下方的资源节点有一个排它锁(X).4.它对右上方的资源节点请求 一个排它锁（X）.点击查看大图咱们再来看中间两个长方形的资源节点,两个处理节点对它们各自都运用权，来执行它们各自的

13、代码，同时又有对对方运用资源请求的动作，从而发生了资源的竞争。这也就让咱们明白死锁发生的原由。这里表明下资源节点的一些信息：HoBT：堆或 B 树。 用于保卫没有聚集索引的表中的 B 树（索引）或堆数据页的锁associated objid: 关联的对象ID,这里只是索引关联的对象ID. Index name:索引名点击查看大图让咱们再对SQL Server Profiler监视到的数据，作一次整理：回顾图：点击查看大图1.在第3行SQL:BatchStarting, SPID 54 （第一个会话启动）,在索引PK_DealLock_3214EC274222D4EF取得一个排它锁，再处理等待状

14、态，(因为在这个实例中我配置了Waitfor Delay 00:00:05)2.在第6行SQL:BatchStarting, SPID 55 （第二个会话启动）,在索引PK_DealLock_3214EC2745F365D3取得一个排它锁，再处理等待状态，(因为在这个实例中我配置了Waitfor Delay 00:00:05)3.两个进程都各自取得一个排它锁(X),几秒过去，它们就开始请求排它锁。SPID 54 （第一个会话）,先对PK_DealLock_3214EC2745F365D3请求一个排它锁(X),但PK_DealLock_3214EC2745F365D3当前已经给SPID 55 （

15、第二个会话）取得。SPID 54要于等待。同时，SPID 55 （第二个会话）,开始对PK_DealLock_3214EC274222D4EF请求一个排它锁(X),但PK_DealLock_3214EC274222D4EF当前已经给SPID 54 （第一个会话）取得。SPID 55要等待。这里就出现了进程阻塞，从而发生死锁。4.SQL Server 检验到这两个进程(第一个&第二个会话)发生死锁，并对占用资源比较少的进程，列入牺牲品名单，将它终止（Kill）。议决左右椭圆形进程节点显示，能够发觉已用日志最少的是左边的进程节点。5. SPID 54 （第一个会话）被回滚(Rollback)，SPID 55 （第二个会话）执行成功。到这里咱们已算完成了，对死锁的监视和剖析。（注：是于其他死锁的解释，死锁模式，死锁防止 &预防,等等，不是本文重点，我没有提出,网上太多这方面的文章）