死锁分析，死锁详细介绍

1.?? --死鎖 ??

2.?? /********************************************************************************************************************** ??

3.?? 死鎖指兩個以上事務相互阻塞相互等待對方釋放它們的鎖，SQL?Server會通過回滾其中一個事務並返回一個錯誤來自已解決阻塞問題， ??

4.?? 讓其他事務完成它們的工作。 ??

5.?? ??

6.?? 整理人：中国风(Roy) ??

7.?? ??

8.?? 日期:2008.07.20 ??

9.?? ***********************************************************************************************************************/ ??

10.? ??

11.? set?nocount?on?; ??

12.? if?object_id('T1')?is?not?null??

13.? ????drop?table?T1 ??

14.? go ??

15.? create?table?T1(ID?int?primary?key,Col1?int,Col2?nvarchar(20)) ??

16.? insert?T1?select?1,101,'A'??

17.? insert?T1?select?2,102,'B'??

18.? insert?T1?select?3,103,'C'??

19.? go ??

20.? ??

21.? if?object_id('T2')?is?not?null??

22.? ????drop?table?T2 ??

23.? go ??

24.? create?table?T2(ID?int?primary?key,Col2?nvarchar(20)) ??

25.? insert?T2?select?1,201,'X'??

26.? insert?T2?select?2,202,'Y'??

27.? insert?T2?select?3,203,'Z'??

28.? ??

29.? ??

30.? go ??

31.? 生成表數據: ??

32.? /* ??

33.? T1: ??

34.? ID??????????Col1????????Col2 ??

35.? -----------?-----------?-------------------- ??

36.? 1???????????101?????????A ??

37.? 2???????????101?????????B ??

38.? 3???????????101?????????C ??

39.? ??

40.? T2: ??

41.? ID??????????Col1????????Col2 ??

42.? -----------?-----------?-------------------- ??

43.? 1???????????201?????????X ??

44.? 2???????????201?????????Y ??

45.? 3???????????201?????????Z ??

46.? */ ??

47.? ??

48.? 防止死鎖： ??

49.? 1、??最少化阻塞。阻塞越少，發生死鎖機會越少 ??

50.? 2、??在事務中按順序訪問表（以上例子：死鎖2） ??

51.? 3、??在錯誤處理程式中檢查錯誤1205並在錯誤發生時重新提交事務 ??

52.? 4、??在錯誤處理程式中加一個過程將錯誤的詳細寫入日誌 ??

53.? 5、??索引的合理使用(以上例子：死鎖1、死鎖3) ??

54.? 當發生死鎖時，事務自動提交，可通過日誌來監視死鎖 ??

55.? ??

56.? ??

57.? 死鎖1(索引): ??

58.? --連接窗口1 ??

59.? --1步: ??

60.? begin?tran ??

61.? ????update?t1?set?col2=col2+'A'?where?col1=101 ??

62.? ??

63.? --3步: ??

64.? ????select?*?from?t2?where?col1=201 ??

65.? commit?tran ??

66.? ??

67.? ??

68.? --連接窗口2 ??

69.? ??

70.? --2步: ??

71.? begin?tran ??

72.? ????update?t2?set?col2=col2+'B'?where?col1=203 ??

73.? ??

74.? --4步: ??

75.? ????select?*?from?t1?where?col1=103 ??

76.? commit?tran ??

77.? ??

78.? ??

79.? ??

80.? --連接窗口1:收到死鎖錯誤,連接窗口2得到結果: ??

81.? ??

82.? /* ??

83.? 訊息?1205，層級?13，狀態?51，行?3 ??

84.? 交易?(處理序識別碼?53)?在?鎖定?資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。 ??

85.? */ ??

86.? ??

87.? --連接窗口2:得到結果 ??

88.? ??

89.? /* ??

90.? -----------?-----------?-------------------- ??

91.? 3???????????103?????????C ??

92.? */ ??

93.? ??

94.? 處理方法: ??

95.? --在t1、t2表的col1條件列建索引 ??

96.? create?index?IX_t1_col1?on?t1(col1) ??

97.? create?index?IX_t2_col1?on?t2(col1) ??

98.? go ??

99.? ??

100.????????????? --連接窗口1 ??

101.????????????? --1步: ??

102.????????????? begin?tran ??

103.????????????? ????update?t1?set?col2=col2+'A'?where?col1=101 ??

104.????????????? ??

105.????????????? --3步: ??

106.????????????? select?*?from?t2?with(index=IX_t2_col1)where?col1=201???--因表數據少，只能指定索引提示才能確保SQL?Server使用索引 ??

107.????????????? commit?tran ??

108.????????????? ??

109.????????????? ??

110.????????????? ??

111.????????????? --連接窗口2 ??

112.????????????? ??

113.????????????? --2步: ??

114.????????????? begin?tran ??

115.????????????? ????update?t2?set?col2=col2+'B'?where?col1=203 ??

116.????????????? ??

117.????????????? ??

118.????????????? --4步: ??

119.????????????? select?*?from?t1?with(index=IX_t1_col1)?where?col1=103??--因表數據少，只能指定索引提示才能確保SQL?Server使用索引 ??

120.????????????? commit?tran ??

121.????????????? ??

122.????????????? ??

123.????????????? ??

124.????????????? --連接窗口1: ??

125.????????????? /* ??

126.????????????? ID??????????Col1????????Col2 ??

127.????????????? -----------?-----------?-------------------- ??

128.????????????? 1???????????201?????????X ??

129.????????????? ??

130.????????????? (1?個資料列受到影響) ??

131.????????????? ??

132.????????????? */ ??

133.????????????? --連接窗口2 ??

134.????????????? /* ??

135.????????????? ID??????????Col1????????Col2 ??

136.????????????? -----------?-----------?-------------------- ??

137.????????????? 3???????????103?????????C ??

138.????????????? ??

139.????????????? (1?個資料列受到影響) ??

140.????????????? */ ??

141.????????????? ??

142.????????????? ??

143.????????????? 死鎖2(訪問表順序): ??

144.????????????? ??

145.????????????? --連接窗口1: ??

146.????????????? --1步: ??

147.????????????? begin?tran ??

148.????????????? ????update?t1?set?col1=col1+1?where?ID=1 ??

149.????????????? ??

150.????????????? --3步: ??

151.????????????? select?col1?from?t2?where?ID=1 ??

152.????????????? commit?tran ??

153.????????????? ??

154.????????????? ??

155.????????????? ??

156.????????????? --連接窗口2: ??

157.????????????? --2步: ??

158.????????????? begin?tran ??

159.????????????? ????update?t2?set?col1=col1+1?where?ID=1 ??

160.????????????? ??

161.????????????? --4步 ??

162.????????????? select?col1?from?t1?where?ID=1 ??

163.????????????? commit?tran ??

164.????????????? ??

165.????????????? ??

166.????????????? --連接窗口1: ??

167.????????????? ??

168.????????????? /* ??

169.????????????? col1 ??

170.????????????? ----------- ??

171.????????????? 201 ??

172.????????????? ??

173.????????????? (1?個資料列受到影響) ??

174.????????????? */ ??

175.????????????? ??

176.????????????? --連接窗口2: ??

177.????????????? ??

178.????????????? /* ??

179.????????????? col1 ??

180.????????????? ----------- ??

181.????????????? 訊息?1205，層級?13，狀態?51，行?1 ??

182.????????????? 交易?(處理序識別碼?54)?在?鎖定?資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。 ??

183.????????????? */ ??

184.????????????? ??

185.????????????? 處理方法: ??

186.????????????? ??

187.????????????? --改變訪問表的順序 ??

188.????????????? ??

189.????????????? --連接窗口1: ??

190.????????????? --1步: ??

191.????????????? begin?tran ??

192.????????????? ????update?t1?set?col1=col1+1?where?ID=1 ??

193.????????????? ??

194.????????????? --3步: ??

195.????????????? ????select?col1?from?t2?where?ID=1 ??

196.????????????? commit?tran ??

197.????????????? ??

198.????????????? --連接窗口2: ??

199.????????????? --2步: ??

200.????????????? begin?tran ??

201.????????????? ????select?col1?from?t1?where?ID=1--會等待連接窗口1提交 ??

202.????????????? --4步 ??

203.????????????? ????update?t2?set?col1=col1+1?where?ID=1 ??

204.????????????? commit?tran ??

205.????????????? ??

206.????????????? 死鎖3(單表): ??

207.????????????? ??

208.????????????? --連接窗口1: ??

209.????????????? ??

210.????????????? while?1=1 ??

211.????????????? ????update?T1?set?col1=203-col1?where?ID=2 ??

212.????????????? ??

213.????????????? --連接窗口2: ??

214.????????????? declare?@i??nvarchar(20) ??

215.????????????? while?1=1 ??

216.????????????? ????set?@i=(select?col2?from?T1?with(index=IX_t1_col1)where?Col1=102);--因表數據少，只能指定索引提示才能確保SQL?Server使用索引 ??

217.????????????? ??

218.????????????? --連接窗口1 ??

219.????????????? /* ??

220.????????????? 訊息?1205，層級?13，狀態?51，行?4 ??

221.????????????? 交易?(處理序識別碼?53)?在?鎖定?資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。 ??

222.????????????? */ ??

223.????????????? ??

224.????????????? ??

225.????????????? 處理方法: ??

226.????????????? 1、刪除col1上的非聚集索引,這樣影響SELECT速度，不可取. ??

227.????????????? ????drop?index?IX_t1_col1?on?t1 ??

228.????????????? 2、建一個覆蓋索引 ??

229.????????????? ????A、drop?index?IX_t1_col1?on?t1 ??

230.????????????? ????B、create?index?IX_t1_col1_col2?on?t1(col1,col2) ??

231.????????????? ??

232.????????????? ??

233.????????????? 通過SQL?Server?Profiler查死鎖信息: ??

234.????????????? ??

235.????????????? 啟動SQL?Server?Profiler——連接實例——事件選取範圍——顯示所有事件 ??

236.????????????? 選擇項: ??

237.????????????? TSQL——SQL:StmtStarting ??

238.????????????? Locks——Deadlock?graph(這是SQL2005新增事件，生成包含死鎖信息的xml值) ??

239.????????????? ?????——Lock:DeadlockChain?死鎖鏈中的進程產生該事件，可標識死鎖進程的ID並跟蹤操作 ??

240.????????????? ?????——Lock:Deadlock?該事件發生了死鎖 ??

241.????????????? <PRE></PRE>??

（编辑：李大同）

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