现在的位置: 首页 > 综合 > 正文

阻塞分析

2013年02月01日 ⁄ 综合 ⁄ 共 4852字 ⁄ 字号 小 中 大 ⁄ 评论关闭
 

--阻塞   

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

作者:

抱歉!评论已关闭.

返回首页

Copyright © 2013-2018 学步园  保留所有权利.
软文销售 QQ客服：2265327166 点击这里给我发消息（其他合作也可洽谈）
必威体育
必威电竞