【转】阻塞分析

  1. --阻塞     
  2. /***********************************************************************************************************************     
  3. 阻塞:其中一个事务阻塞,其它事务等待对方释放它们的锁,同时会导致死锁问题。     
  4.     
  5. 整理人:中国风(Roy)     
  6.     
  7. 日期:2008.07.20     
  8. ************************************************************************************************************************/     
  9.     
  10. --生成测试表Ta     
  11. if not object_id('Ta'is null    
  12.     drop table Ta     
  13. go     
  14. create table Ta(ID int Primary key,Col1 int,Col2 nvarchar(10))     
  15. insert Ta      
  16. select 1,101,'A' union all    
  17. select 2,102,'B' union all    
  18. select 3,103,'C'     
  19. go     
  20. 生成数据:     
  21. /*     
  22. 表Ta     
  23. ID          Col1        Col2     
  24. ----------- ----------- ----------     
  25. 1           101         A     
  26. 2           102         B     
  27. 3           103         C     
  28.     
  29. (3 行受影响)     
  30. */     
  31.     
  32. 将处理阻塞减到最少:     
  33. 1、事务要尽量短     
  34. 2、不要在事务中请求用户输入     
  35. 3、在读数据考虑便用行版本管理     
  36. 4、在事务中尽量访问最少量的数据     
  37. 5、尽可能地使用低的事务隔离级别     
  38.     
  39. go     
  40. 阻塞1(事务):     
  41. --测试单表     
  42.     
  43. -----------------------------连接窗口1(update/insert/delete)----------------------     
  44. begin tran     
  45. --update     
  46.     update ta set col2='BB' where ID=2     
  47. --或insert     
  48. begin tran     
  49.     insert Ta values(4,104,'D')     
  50. --或delete     
  51. begin tran     
  52.     delete ta where ID=1     
  53.     
  54. --rollback tran     
  55.     
  56. ------------------------------------------连接窗口2--------------------------------     
  57. begin tran     
  58.     select * from ta     
  59.     
  60. --rollback tran     
  61.     
  62. --------------分析-----------------------     
  63. select      
  64.     request_session_id as spid,     
  65.     resource_type,     
  66.     db_name(resource_database_id) as dbName,     
  67.     resource_description,     
  68.     resource_associated_entity_id,     
  69.     request_mode as mode,     
  70.     request_status as Status     
  71. from      
  72.     sys.dm_tran_locks     
  73. /*     
  74. spid        resource_type dbName resource_description resource_associated_entity_id mode  Status     
  75. ----------- ------------- ------ -------------------- ----------------------------- ----- ------     
  76. 55          DATABASE      Test   0                    S                             GRANT NULL    
  77. 54          DATABASE      Test   0                    S                             GRANT NULL    
  78. 53          DATABASE      Test   0                    S                             GRANT NULL    
  79. 55          PAGE          Test   1:201                72057594040483840             IS    GRANT    
  80. 54          PAGE          Test   1:201                72057594040483840             IX    GRANT    
  81. 55          OBJECT        Test   1774629365           IS                            GRANT NULL    
  82. 54          OBJECT        Test   1774629365           IX                            GRANT NULL    
  83. 54          KEY           Test   (020068e8b274)       72057594040483840             X     GRANT --(spID:54请求了排它锁)     
  84. 55          KEY           Test   (020068e8b274)       72057594040483840             S     WAIT  --(spID:55共享锁+等待状态)     
  85. (9 行受影响)     
  86. */     
  87.     
  88. --查连接住信息(spid:54、55)     
  89. select connect_time,last_read,last_write,most_recent_sql_handle       
  90. from sys.dm_exec_connections where session_id in(54,55)     
  91.     
  92. --查看会话信息     
  93. select login_time,host_name,program_name,login_name,last_request_start_time,last_request_end_time      
  94. from sys.dm_exec_sessions where session_id in(54,55)     
  95.     
  96. --查看阻塞正在执行的请求     
  97. select      
  98.     session_id,blocking_session_id,wait_type,wait_time,wait_resource     
  99. from      
  100.     sys.dm_exec_requests     
  101. where    
  102.     blocking_session_id>0--正在阻塞请求的会话的 ID。如果此列是 NULL,则不会阻塞请求     
  103.     
  104. --查看正在执行的SQL语句     
  105.     
  106. select      
  107.     a.session_id,sql.text,a.most_recent_sql_handle     
  108. from      
  109.     sys.dm_exec_connections a     
  110. cross apply     
  111.     sys.dm_exec_sql_text(a.most_recent_sql_handle) as SQL   --也可用函数fn_get_sql通过most_recent_sql_handle得到执行语句     
  112. where    
  113.     a.Session_id in(54,55)     
  114. /*     
  115. session_id  text     
  116. ----------- -----------------------------------------------     
  117. 54          begin tran   update ta set col2='BB' where ID=2     
  118. 55          begin tran   select * from ta     
  119. */     
  120.     
  121. 处理方法:     
  122. --连接窗口2     
  123. begin tran     
  124.     select * from ta with (nolock)--用nolock:业务数据不断变化中,如销售查看当月时可用。     
  125.     
  126.     
  127.     
  128.     
  129.     
  130. 阻塞2(索引):     
  131.     
  132. -----------------------连接窗口1     
  133. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE    --针对会话设置了 TRANSACTION ISOLATION LEVEL     
  134. begin tran     
  135.     update ta set col2='BB' where COl1=102     
  136.     
  137. --rollback tran     
  138.     
  139.     
  140.     
  141. ------------------------连接窗口2     
  142. insert into ta(ID,Col1,Col2) values(5,105,'E')     
  143.     
  144.     
  145.     
  146. 处理方法:     
  147.     
  148. create index IX_Ta_Col1 on Ta(Col1)--用COl1列上创索引,当更新时条件:COl1=102会用到索引IX_Ta_Col1上得到一个排它键的范围锁     
  149.     
  150.     
  151.     
  152. 阻塞3(会话设置):     
  153.     
  154. -------------------------------连接窗口1     
  155.     
  156. begin tran     
  157. --update     
  158.     update ta set col2='BB' where ID=2     
  159.     select col2 from ta where ID=2     
  160.     
  161. --rollback tran     
  162.     
  163. --------------------------------连接窗口2     
  164.     
  165. SET TRANSACTION ISOLATION LEVEL READ COMMITTED --设置会话已提交读:指定语句不能读取已由其他事务修改但尚未提交的数据     
  166. begin tran     
  167.     select * from ta      
  168.     
  169.     
  170.     
  171. 处理方法:     
  172. --------------------------------连接窗口2(善用会话设置:业务数据不断变化中,如销售查看当月时可用)     
  173.     
  174. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --设置会话未提交读:指定语句可以读取已由其他事务修改但尚未提交的行     
  175. begin tran     
  176.     select * from ta     
  177. <pre>  
posted @ 2011-10-24 12:37  哥本哈根  阅读(182)  评论(0编辑  收藏  举报