死锁

1. --死鎖 ??
2. /****************************************************************************************************************************************************** ??
3. 死鎖指兩個以上事務相互阻塞相互等待對方釋放它們的鎖，SQL?Server會通過回滾其中一個事務並返回一個錯誤來自已解決阻塞問題，讓其他事務完成它們的工作。 ??
4. ??
5. 整理人：中国风(Roy) ??
6. ??
7. 日期:2008.07.20 ??
8. ******************************************************************************************************************************************************/ ??
9. ??
10. set?nocount?on?; ??
11. if?object_id('T1')?is?not?null??
12. ????drop?table?T1 ??
13. go ??
14. create?table?T1(ID?int?primary?key,Col1?int,Col2?nvarchar(20)) ??
15. insert?T1?select?1,101,'A' ??
16. insert?T1?select?2,102,'B' ??
17. insert?T1?select?3,103,'C' ??
18. go ??
19. ??
20. if?object_id('T2')?is?not?null??
21. ????drop?table?T2 ??
22. go ??
23. create?table?T2(ID?int?primary?key,Col2?nvarchar(20)) ??
24. insert?T2?select?1,201,'X' ??
25. insert?T2?select?2,202,'Y' ??
26. insert?T2?select?3,203,'Z' ??
27. ??
28. ??
29. go ??
30. 生成表數據: ??
31. /* ??
32. T1: ??
33. ID??????????Col1????????Col2 ??
34. -----------?-----------?-------------------- ??
35. 1???????????101?????????A ??
36. 2???????????101?????????B ??
37. 3???????????101?????????C ??
38. ??
39. T2: ??
40. ID??????????Col1????????Col2 ??
41. -----------?-----------?-------------------- ??
42. 1???????????201?????????X ??
43. 2???????????201?????????Y ??
44. 3???????????201?????????Z ??
45. */ ??
46. ??
47. 防止死鎖： ??
48. 1、??最少化阻塞。阻塞越少，發生死鎖機會越少 ??
49. 2、??在事務中按順序訪問表（以上例子：死鎖2） ??
50. 3、??在錯誤處理程式中檢查錯誤1205並在錯誤發生時重新提交事務 ??
51. 4、??在錯誤處理程式中加一個過程將錯誤的詳細寫入日誌 ??
52. 5、??索引的合理使用(以上例子：死鎖1、死鎖3) ??
53. 當發生死鎖時，事務自動提交，可通過日誌來監視死鎖 ??
54. ??
55. ??
56. 死鎖1(索引): ??
57. --連接窗口1 ??
58. --1步: ??
59. begin?tran ??
60. ????update?t1?set?col2=col2+'A'?where?col1=101 ??
61. ??
62. --3步: ??
63. ????select?*?from?t2?where?col1=201 ??
64. commit?tran ??
65. ??
66. ??
67. --連接窗口2 ??
68. ??
69. --2步: ??
70. begin?tran ??
71. ????update?t2?set?col2=col2+'B'?where?col1=203 ??
72. ??
73. --4步: ??
74. ????select?*?from?t1?where?col1=103 ??
75. commit?tran ??
76. ??
77. ??
78. ??
79. --連接窗口1:收到死鎖錯誤,連接窗口2得到結果: ??
80. ??
81. /* ??
82. 訊息?1205，層級?13，狀態?51，行?3 ??
83. 交易?(處理序識別碼?53)?在?鎖定?資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。 ??
84. */ ??
85. ??
86. --連接窗口2:得到結果 ??
87. ??
88. /* ??
89. -----------?-----------?-------------------- ??
90. 3???????????103?????????C ??
91. */ ??
92. ??
93. 處理方法: ??
94. --在t1、t2表的col1條件列建索引 ??
95. create?index?IX_t1_col1?on?t1(col1) ??
96. create?index?IX_t2_col1?on?t2(col1) ??
97. go ??
98. ??
99. --連接窗口1 ??
100. --1步: ??
101. begin?tran ??
102. ????update?t1?set?col2=col2+'A'?where?col1=101 ??
103. ??
104. --3步: ??
105. select?*?from?t2?with(index=IX_t2_col1)where?col1=201???--因表數據少，只能指定索引提示才能確保SQL?Server使用索引 ??
106. commit?tran ??
107. ??
108. ??
109. ??
110. --連接窗口2 ??
111. ??
112. --2步: ??
113. begin?tran ??
114. ????update?t2?set?col2=col2+'B'?where?col1=203 ??
115. ??
116. ??
117. --4步: ??
118. select?*?from?t1?with(index=IX_t1_col1)?where?col1=103??--因表數據少，只能指定索引提示才能確保SQL?Server使用索引 ??
119. commit?tran ??
120. ??
121. ??
122. ??
123. --連接窗口1: ??
124. /* ??
125. ID??????????Col1????????Col2 ??
126. -----------?-----------?-------------------- ??
127. 1???????????201?????????X ??
128. ??
129. (1?個資料列受到影響) ??
130. ??
131. */ ??
132. --連接窗口2 ??
133. /* ??
134. ID??????????Col1????????Col2 ??
135. -----------?-----------?-------------------- ??
136. 3???????????103?????????C ??
137. ??
138. (1?個資料列受到影響) ??
139. */ ??
140. ??
141. ??
142. 死鎖2(訪問表順序): ??
143. ??
144. --連接窗口1: ??
145. --1步: ??
146. begin?tran ??
147. ????update?t1?set?col1=col1+1?where?ID=1 ??
148. ??
149. --3步: ??
150. select?col1?from?t2?where?ID=1 ??
151. commit?tran ??
152. ??
153. ??
154. ??
155. --連接窗口2: ??
156. --2步: ??
157. begin?tran ??
158. ????update?t2?set?col1=col1+1?where?ID=1 ??
159. ??
160. --4步 ??
161. select?col1?from?t1?where?ID=1 ??
162. commit?tran ??
163. ??
164. ??
165. --連接窗口1: ??
166. ??
167. /* ??
168. col1 ??
169. ----------- ??
170. 201 ??
171. ??
172. (1?個資料列受到影響) ??
173. */ ??
174. ??
175. --連接窗口2: ??
176. ??
177. /* ??
178. col1 ??
179. ----------- ??
180. 訊息?1205，層級?13，狀態?51，行?1 ??
181. 交易?(處理序識別碼?54)?在?鎖定?資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。 ??
182. */ ??
183. ??
184. 處理方法: ??
185. ??
186. --改變訪問表的順序 ??
187. ??
188. --連接窗口1: ??
189. --1步: ??
190. begin?tran ??
191. ????update?t1?set?col1=col1+1?where?ID=1 ??
192. ??
193. --3步: ??
194. ????select?col1?from?t2?where?ID=1 ??
195. commit?tran ??
196. ??
197. --連接窗口2: ??
198. --2步: ??
199. begin?tran ??
200. ????select?col1?from?t1?where?ID=1--會等待連接窗口1提交 ??
201. --4步 ??
202. ????update?t2?set?col1=col1+1?where?ID=1 ??
203. commit?tran ??
204. ??
205. 死鎖3(單表): ??
206. ??
207. --連接窗口1: ??
208. ??
209. while?1=1 ??
210. ????update?T1?set?col1=203-col1?where?ID=2 ??
211. ??
212. --連接窗口2: ??
213. declare?@i??nvarchar(20) ??
214. while?1=1 ??
215. ????set?@i=(select?col2?from?T1?with(index=IX_t1_col1)where?Col1=102);--因表數據少，只能指定索引提示才能確保SQL?Server使用索引 ??
216. ??
217. --連接窗口1 ??
218. /* ??
219. 訊息?1205，層級?13，狀態?51，行?4 ??
220. 交易?(處理序識別碼?53)?在?鎖定?資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。 ??
221. */ ??
222. ??
223. ??
224. 處理方法: ??
225. 1、刪除col1上的非聚集索引,這樣影響SELECT速度，不可取. ??
226. ????drop?index?IX_t1_col1?on?t1 ??
227. 2、建一個覆蓋索引 ??
228. ????A、drop?index?IX_t1_col1?on?t1 ??
229. ????B、create?index?IX_t1_col1_col2?on?t1(col1,col2) ??
230. ??
231. ??
232. 通過SQL?Server?Profiler查死鎖信息: ??
233. ??
234. 啟動SQL?Server?Profiler——連接實例——事件選取範圍——顯示所有事件 ??
235. 選擇項: ??
236. TSQL——SQL:StmtStarting ??
237. Locks——Deadlock?graph(這是SQL2005新增事件，生成包含死鎖信息的xml值) ??
238. ?????——Lock:DeadlockChain?死鎖鏈中的進程產生該事件，可標識死鎖進程的ID並跟蹤操作 ??
239. ?????——Lock:Deadlock?該事件發生了死鎖??

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!