SQL Server2008 事务和锁详解

今天主要讲下锁，但是说到锁肯定就是要说到事务。

说到事务就要说下它的三个特性： 自动性，一致性，独立性，持久性。

事务的分类：自动提交事务，显式事务，隐式事务，批处理级事务。

在这里不做过多的介绍和说明，如有疑问可以留言。。

 

[sql]  view plain copy print ?

1.  --事务个数的查询  
2.  select @@TRANCOUNT  
3.    
4.  /*  
5. -----------  
6. 0  
7.   
8. (1 行受影响)*/  
9.   
10. --save transaction  设置保存点   
11.   
12.   
13.   
14. use Erp_System   
15. go  
16. begin transaction InsertTransl  
17. save transaction A  
18. delete consume_Shopid_Range where  
19.  id='10010xxxxxxx1'   
20.  and Shopid=2   
21.  and GoodsId=6   
22.  and Amount =894   
23.  and ConsumeDate='2012-07-26 11:55:40.153'  
24.  and mark='2FA4E2F3-DD8B-4ED3-8D9C-D7AE33BA5D0D'  
25.  rollback transaction A  
26.  insert into consume_Shopid_Range   
27.  (id,Shopid,GoodsId,Amount,ConsumeDate,mark)values  
28.  ('10010xxxxxxx2',3,1,321,'2012-07-31 11:55:40.153','szstephenzhou')  
29.  commit transaction  InsertTransl   
30.  go  
31.     
32. /*(0 行受影响)  
33.   
34. (1 行受影响)*/  
35. --分析下上个结果：  
36. --在上面的一个事务中，所限开始了一个事务 InsertTransl  
37. --在这个事务中设置了保存的A  
38. --然后删除一条记录   
39. --回顾到A点 也就是没有删除  
40. --新增了一条记录  
41. --commit提交事务InsertTransl  
42.   
43.   
44.   
45.   
46. ---Set xact_abort 语句   
47. /*  
48.  *格式如下:  
49.  *SET XACT_ABORT {ON|OFF}  
50.  *当设置为 setxact_abort on 时，如果SQL语句出现运行错误，则数据库引擎将终止并回滚整个事务，  
51.     即使前面能够正确的执行的SQL。  
52.  *当设置成set xact_abort off 如果SQL错误的时候，数据库只回滚产生错误的sql语句。并且事务继续  
53.     默认的情况是ON  
54. */  
55. if OBJECT_ID('tb') is not null  
56. drop table tb  
57. go  
58. create table tb(id int identity primary key ,name varchar(10))  
59.    
60.    
61.  set xact_abort on   
62.  begin transaction insetran  
63.  insert into tb values('stephen');  
64.  insert into tb values('mrzhou');  
65.  insert into tb values('stephen----------');  
66.  insert into tb values('刘德华');  
67.  insert into tb values('张学友');  
68.  commit transaction insetran  
69.  go  
70.  /*  
71. 消息 208，级别 16，状态 1，第 3 行  
72. 对象名 'tb' 无效。  
73.   
74.  */  
75.  select * from tb  
76.  /*  
77.  id          name  
78. ----------- ----------  
79.   
80. (0 行受影响)  
81.   
82.  */  
83.  set xact_abort off   
84.  begin transaction insetran  
85.  insert into tb values('stephen');  
86.  insert into tb values('mrzhou');  
87.  insert into tb values('stephen----------');  
88.  insert into tb values('刘德华');  
89.  insert into tb values('张学友');  
90.  commit transaction insetran  
91.  go  
92.  /*  
93. (1 行受影响)  
94.   
95. (1 行受影响)  
96. 消息 8152，级别 16，状态 14，第 4 行  
97. 将截断字符串或二进制数据。  
98. 语句已终止。  
99.   
100. (1 行受影响)  
101.   
102. (1 行受影响)  
103. */  
104.   
105. select * from tb  
106. /*id          name  
107. ----------- ----------  
108. 1           stephen  
109. 2           mrzhou  
110. 4           刘德华  
111. 5           张学友  
112.   
113. (4 行受影响)  
114. */  
115.   
116. --如上所述 当set xact_abort on 如果出现错误就会全部回滚 而设置成off的时候就只跳过错误继续执行。。  
117.   
118. --但是如果设置成off的时候也是没办法执行跳过。这是为什么呢？？  
119.   
120.  set xact_abort off   
121.  begin transaction insetran  
122.  insert into tb values('stephen');  
123.  insert into tb values('mrzhou');  
124.  insert into tb values(1,'stephen');  
125.  insert into tb values('刘德华');  
126.  insert into tb values('张学友');  
127.  commit transaction insetran  
128.  go  
129.    
130.  /*  
131.  消息 8101，级别 16，状态 1，第 5 行  
132. 仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表'tb'中的标识列指定显式值。  
133.  */  
134.  --因为在这里是属于编译错误，在identity属性中不能插入数字。  
135.    
136.    
137.    
138.    
139.    
140.    
141.  --在事务中有些语句是不能用的 如下  
142.  /*  
143.     *CREATE DATABASE  
144.     *ALTER DATABASE  
145.     *DROP DATABASE  
146.     *RECONFIGURE  
147.     *BACKUP  
148.     *RESTORE  
149.     *UPDATE STATISTICS  
150.     
151.  */  

 

 

 

 

 

 

锁机制

什么是锁，锁到底是个什么东西？有很多人都感觉是懂非懂的样子，了解了，好像是个什么东西。其实很简单 什么到底是什么呢？锁是一种规则。是用来控制同步

数据访问控制的一种机制。

打个比方吧，数据库中的锁就像交通信号灯。而车子就是每个事务。如果没有好好的控制红绿灯的话就会怎么样，当然是堵车了。如果没有控制好锁 同样会堵塞。

 

 

锁的定制

大家都知道，数据库的资源只由一个用户使用只要程序没问题，数据就不会出现不一致的情况。如果两个或者多个用户同时修改就有可能出现并发冲突导致如下错误：

 

更新丢失

更新丢失是指两个用户同时更新一个数据库对象，其中一个用户更新覆盖了之前那个用户的更新从而导致错误

 

不可重复读

一个用户在一个事务中读取的数据前后不一致，其中可能是有别的用户做了修改

 

幻读

一个用户读取一个结果集后，其他用户对该结果集进行了插入或者删除，当第一个用户再读这个结果集的时候发现数据多了或者少了。

 

为了解决这些问题，SQLserver 数据库引入了锁。

 

 

 

1》从数据库系统的角度分为 共享锁S 排它锁X（独占锁）  更新锁 U  意向锁 架构锁和大容量更新锁等

共享锁S：

并发执行对一个数据资源读取操作时，任何其他事务不能修改该资源的数据；读取操作完成后 S锁释放。

排它锁X：

在执行INSERT ,UPDATE,DELETE时，确保不会同时对同一资源进行多重更新操作。修改数据之前，需要执行读取操作获取数据 此时需要申请共享锁S，然后再申请排它锁X

更新锁U：

为了避免死锁的情况而使用的锁模式

两个事务对一个数据资源先读取再更新的操作，使用了S锁和X锁进行操作。X锁一次只有一个X锁在对象上的，也就是说一次只有一个事务可以获取资源的更新锁。如果需要对数据进行修改操作，则需要把更新锁转换为U锁，否则将锁转换成S锁

意向锁I：

需要在层次结构中的某些底层资源上获取S锁或者X锁或者U锁。意向锁可以提高性能，因为数据库引擎不需要检查表中的每行或每页上的S锁就确定是否可以获取到该表上的X锁。

架构锁：

为了防止修改表结构时对表进行的并发访问锁。

大容量更新锁

允许多个线程将数据并发地大容量加载到同一个表中，同时禁止其他与大容量插入数据无关的进程访问该表

 

2》从并发的手段分为乐观并发和悲观并发 

乐观并发：

允许事务在执行过程中不锁定任何资源，只有当事务视图修改数据时，才会对资源检查。如果确定有冲突，应用程序重新读取数据然后修改操作。这是假设冲突不存在，节约了锁的机制。如果遇到并发冲突，再重新执行事务。

 

悲观并发：

在事务中需要使用不同的锁。如果一个用户的某个操作应用了锁，则只有等释放后其他用与该锁相冲突的操作。

 

 

下表显示了最常见的锁模式的兼容性。

	现有的授权模式
请求的模式	IS	S	U	IX	SIX	X
意向共享 (IS)	是	是	是	是	是	否
共享 (S)	是	是	是	否	否	否
更新 (U)	是	是	否	否	否	否
意向排他 (IX)	是	否	否	是	否	否
意向排他共享 (SIX)	是	否	否	否	否	否
排他 (X)	否	否	否	否	否	否

意向排他锁（IX 锁）与 IX 锁模式兼容，因为 IX 表示打算只更新部分行而不是所有行。还允许其他事务尝试读取或更新部分行，只要这些行不是其他事务当前更新的行即可。此外，如果两个事务尝试更新同一行，则将在表级和页级上授予这两个事务 IX 锁。但是，将在行级授予一个事务 X 锁。另一个事务必须在行级锁被删除前等待。

 

 

锁的粒度

锁粒度指SQL Server锁定数据资源的类型。锁定的粒度越小，数据库的并发就越高。因为锁定越小的数据库资源，不会影响其他用户使用其他资源。

SQL SERVER 支持的粒度包括：

行RID 键 key     页page  区间 extent 堆hobt    表table  文件file      应用程序application  元数据metadata     分配单元 application_unit 数据库database

 

当然在实际使用过程中不需要考虑粒度。sqlserver数据库引擎采用动态锁策略。

如果一个事务在一个表中很多行放了意向锁，则数据库引擎可以为其分配一个表级锁，然后释放所有低级别的行级锁，从而减少管理锁的开销。

 

跟踪锁的活动情况

      在用户看来锁是透明的，因为SQL Server数据库引擎会根据具体情况决定是否用锁，以及使用任何类型的锁。虽然感觉不到锁的存在，但是锁的使用情况直接关系到数据库引擎工作发生的。

1>使用SQL Server事件探测器跟踪数据库的工作情况，监视锁的活动情况。

 新建跟踪， 在跟踪属性对话框 单击  事件选择 展开 Locks事件类 如图：

 

 

解释下上面的一些锁事件：

Deadlock Graph ：用于提供死锁的xml说明

Lock：Acquired 表示已经获取资源如表的一行的锁

Lock ：Cancel 跟踪在获取锁之前取消的锁请求

Lock Deadlock Chain 监视死锁条件的发生事件和涉及的对象

Lock ：Deadlock 跟踪死锁的时间，通常犹豫某事件申请被其他事务锁定的资源时发生死锁。

Lock：Escalation 用于指示粒度较小转换成粒度较大的锁。

Lock：Released 跟踪释放事件

Lock：Timeout 跟踪锁超时的情况。由于一个事务持有申请资源的阻塞锁，导致另一个事务规定时间内无法完成锁的请求，从而超时。

 

2>利用系统监视器监视SQL Server锁的活动情况。

前面已经介绍了相关操作 这里不做过多的介绍 只说下主要关于锁的介绍

管理工具 性能  添加打开如下：

 

 Average Wait Time ：获取锁而等待的平均时间

Lock Requests :锁管理每秒请求的新锁和锁转换数量

Lock Timeout(TIMEOUT>0) 每秒超时的锁请求数量不包括timeout=0

。。。。。

 

设置事务隔离级别选项

 

 READ UNCOMMITTED 指定语句可以读取已由其他事务修改但是没有提交的行。这种读取叫脏读。

READ COMMITTED 不能读取其他事务正在修改但是没有提交的事务。默认

REPEATABLE READ 不能读取其他事务正在修改但没有提交的行 而且其他事务也不能修改当前事务也不能修改在提交前所读取的数据。

SNAPSHOT 指定事务在开始时，就获取了已经提交的快照。因此当前事务只能看到事务开始之前对数据所做的修改。

SERIALIZABLE *别事务隔离。一个查询智能看到事务开始之前提交的数据 ，无法看到脏数据或事务执行中其他并夯实无锁修改的数据。在这个级别下事务好像一个个被串起来执行。也就没有并发这一说了。

格式：

SET TRANSACTION ISOLATION LEVEL

{READ UNCOMMITTED

|READ COMMITTED

|REPEATABLE READ

|SNAPSHOT

|SERIALIZABLE

}

接下来上实例：

 

[sql]  view plain copy print ?

1.    
2.  select * from consume_Shopid_Range   
3. where  
4.  id='10010xxxxxxx2' and Shopid=3and  GoodsId=1 and Amount=321 and ConsumeDate='2012-07-31 11:55:40.153' and mark='szstephenzhou'   
5.    
6.  /*  
7.  id                                                 Shopid      GoodsId     Amount                 ConsumeDate             mark  
8. -------------------------------------------------- ----------- ----------- ---------------------- ----------------------- ----------------------------------------------------------------------------------------------------  
9. 10010xxxxxxx2                                      3           1           321                    2012-07-31 11:55:40.153 szstephenzhou  
10.   
11. (1 行受影响)  
12.   
13.  */  
14.    
15.  use Erp_System  
16. go  
17. begin tran   
18. update consume_Shopid_Range set mark='http://blog.csdn.net/szstephenzhou'  
19. where  
20.  id='10010xxxxxxx2' and Shopid=3and  GoodsId=1 and Amount=321 and ConsumeDate='2012-07-31 11:55:40.153' and mark='szstephenzhou'  
21. waitfor delay '00:00:10'  
22. rollback tran   
23.   
24.   
25. --然后在另外一个新建查询 执行  
26. use Erp_System  
27. go  
28. set transaction ISOLATION level READ UNCOMMITTED   
29.     
30. select * from consume_Shopid_Range   
31. where  
32.  id='10010xxxxxxx2' and Shopid=3and  GoodsId=1 and Amount=321 and ConsumeDate='2012-07-31 11:55:40.153' and mark='szstephenzhou'   
33.  WAITFOR DELAY '00:00:10'  
34.    
35.  select * from consume_Shopid_Range   
36. where  
37.  id='10010xxxxxxx2' and Shopid=3and  GoodsId=1 and Amount=321 and ConsumeDate='2012-07-31 11:55:40.153' and mark='szstephenzhou'   
38.    
39.  /*  
40.    
41.  id                                                 Shopid      GoodsId     Amount                 ConsumeDate             mark  
42. -------------------------------------------------- ----------- ----------- ---------------------- ----------------------- ----------------------------------------------------------------------------------------------------  
43.   
44. (0 行受影响)  
45.   
46. id                                                 Shopid      GoodsId     Amount                 ConsumeDate             mark  
47. -------------------------------------------------- ----------- ----------- ---------------------- ----------------------- ----------------------------------------------------------------------------------------------------  
48. 10010xxxxxxx2                                      3           1           321                    2012-07-31 11:55:40.153 szstephenzhou  
49.   
50. (1 行受影响)  
51.  */  
52.    
53.    

解释：

 

在第二个查询查看 分别相隔10秒查询的结果不一样。原因就是在查询这条记录的时候 ，设置了事务隔离级别为uncommitted，这时候读出来的是脏数据。因为第一个事务在执行修改了，但是没有提交 而事务二读出来所以是空的。  等第一个事务回滚  第二个事务再次读取的时候还是原来的事务了

 

 

说的有点含糊换个例子来说明 可能看的更仔细了 看下面的

 

 

[sql]  view plain copy print ?

1. use Erp_System  
2. go  
3. select * from tb  
4. /*  
5. id          name  
6. ----------- --------------------  
7. 1           刘德华  
8. 2           mrzhou  
9. 3           mrjun  
10. 4           俊俊  
11. 5           慧慧  
12.   
13. (5 行受影响)  
14.   
15. */  
16.   
17.   
18. -----------------------------------------------------------------------------------------------  
19. -------------------------1.read uncommitted 脏读数据-------------------------------------------  
20. -----------------------------------------------------------------------------------------------  
21.   
22.   
23. --打开一个窗口输入这个事务  
24. use Erp_System  
25. go  
26. begin  tran  
27. update tb set name='张学友' where id=1  
28. waitfor delay '00:00:10'  
29. rollback tran   
30. go  
31.   
32. --再打开一个窗口输入查询事务 设置事件隔离级别为uncommitted 产生了脏读。在第一个事务更新没有提交的数据第二个事务也读到了  
33. use Erp_System  
34. go  
35. set transaction isolation level read  uncommitted   
36. select * from tb  
37. waitfor delay '00:00:10'  
38. select * from tb  
39.   
40. --输出结果为  
41. /*  
42. id          name  
43. ----------- --------------------  
44. 1           张学友  
45. 2           mrzhou  
46. 3           mrjun  
47. 4           俊俊  
48. 5           慧慧  
49.   
50. (5 行受影响)  
51.   
52. id          name  
53. ----------- --------------------  
54. 1           刘德华  
55. 2           mrzhou  
56. 3           mrjun  
57. 4           俊俊  
58. 5           慧慧  
59.   
60. (5 行受影响)  
61. */  
62.   
63.   
64. -----------------------------------------------------------------------------------------------  
65. -------------------------1.read committed -------------------------------------------  
66. -----------------------------------------------------------------------------------------------  
67.   
68.   
69. -- 打开一个窗口输入查询事务 设置事件隔离级别为read committed   
70. use Erp_System  
71. go  
72. set transaction isolation level read   committed   
73. begin tran  
74. select * from tb  
75. waitfor delay '00:00:10'  
76. select * from tb  
77. rollback tran  
78.   
79. --再打开一个窗口输入  
80.  use Erp_System  
81.  go  
82.  update tb set name='我是大傻瓜' where id=1  
83.    
84.  ---再查看第一个窗口的结果如下：  
85.  /*  
86.  id          name  
87. ----------- --------------------  
88. 1           刘德华  
89. 2           mrzhou  
90. 3           mrjun  
91. 4           俊俊  
92. 5           慧慧  
93.   
94. (5 行受影响)  
95.   
96. id          name  
97. ----------- --------------------  
98. 1           我是大傻瓜  
99. 2           mrzhou  
100. 3           mrjun  
101. 4           俊俊  
102. 5           慧慧  
103.   
104. (5 行受影响)  
105.   
106.  */  
107.    
108.    
109.  --分析  
110.  --在第一个查询事务前后相隔十秒查询的结果不一致，事务隔离级别为read committed  原因是在查询的时候有事务修改了这个  
111.  --数据。必须使用repeatable read级别才能隔离  
112.    
113.    
114.    
115.  -----------------------------------------------------------------------------------------------  
116. -------------------2.repeatable read 事务级别在未回滚提交别的事务是不可以更改一直处于等待-------------  
117. -----------------------------------------------------------------------------------------------  
118.  ---打开一个窗口输入查询事务   
119.  use Erp_System  
120. go  
121. set transaction isolation level repeatable read  
122. begin tran  
123. select * from tb  
124. waitfor delay '00:00:10'  
125. select * from tb  
126. rollback tran  
127.   
128.   
129. --再打开一个窗口输入  
130.  use Erp_System  
131.  go  
132.  update tb set name='我是刘德华' where id=1  
133.    
134.  --查看结果  
135.  /*  
136.  id          name  
137. ----------- --------------------  
138. 1           我是大傻瓜  
139. 2           mrzhou  
140. 3           mrjun  
141. 4           俊俊  
142. 5           慧慧  
143.   
144. (5 行受影响)  
145.   
146. id          name  
147. ----------- --------------------  
148. 1           我是大傻瓜  
149. 2           mrzhou  
150. 3           mrjun  
151. 4           俊俊  
152. 5           慧慧  
153.   
154. (5 行受影响)  
155.   
156.  */  
157.    
158.    
159.   -----------------------------------------------------------------------------------------------  
160. -------------------2.SNAPSHOT事务隔离级别，-----------------------------------------------------  
161. --------------SNAPSHOT级别无视数据的更新，数据在这个事务的读取都是读取之前快照上的数据-----------  
162.   
163. --如果把以上的update改成insert 在事务一查询出来两个结果还是不同。如下实例  
164.   
165. --在窗口中执行  
166.   
167. use Erp_System  
168. go  
169. set transaction isolation level repeatable read  
170. begin tran  
171. select * from tb  
172. waitfor delay '00:00:10'  
173. select * from tb  
174. rollback tran  
175.   
176. --再打开窗口执行  
177.  use Erp_System  
178.  go  
179.  insert into tb values('SNAPSHOT')  
180.    
181.  --结果如下：  
182. /*  
183. id          name  
184. ----------- --------------------  
185. 1           我是刘德华  
186. 2           mrzhou  
187. 3           mrjun  
188. 4           俊俊  
189. 5           慧慧  
190.   
191. (5 行受影响)  
192.   
193. id          name  
194. ----------- --------------------  
195. 1           我是刘德华  
196. 2           mrzhou  
197. 3           mrjun  
198. 4           俊俊  
199. 5           慧慧  
200. 6           SNAPSHOT  
201.   
202. (6 行受影响)  
203.   
204. */  
205. --需要把事务级别提高到SNAPSHOT  
206. set transaction isolation level SNAPSHOT  
207. /*  
208. 提示错误如下：  
209. id          name  
210. ----------- --------------------  
211. 消息 3952，级别 16，状态 1，第 3 行  
212. 快照隔离事务访问数据库 'Erp_System' 失败，因为在此数据库中不允许快照隔离。请使用 ALTER DATABASE 以允许快照隔离。  
213. */  
214.   
215. --需要更改  
216. select  snapshot_isolation_state_desc from sys.databases where name='Erp_System'  
217.   
218. /*  
219. snapshot_isolation_state_desc  
220. ------------------------------------------------------------  
221. OFF  
222.   
223. (1 行受影响)  
224.   
225. */  
226.   
227. alter database Erp_System set allow_snapshot_isolation on   
228.   
229. --命令已成功完成。  
230. select  snapshot_isolation_state_desc from sys.databases where name='Erp_System'  
231.   
232. /*  
233. snapshot_isolation_state_desc  
234. ------------------------------------------------------------  
235. ON  
236.   
237. (1 行受影响)  
238. */  
239.   
240. use Erp_System  
241. go  
242. set transaction isolation level snapshot   
243. begin tran  
244. select * from tb  
245. waitfor delay '00:00:10'  
246. select * from tb  
247. rollback tran  
248.   
249. --再打开窗口执行  
250.  use Erp_System  
251.  go  
252.  insert into tb values('SNAPSHOT')  
253.  /*  
254.  执行结果；  
255.  id          name  
256. ----------- --------------------  
257. 1           我是刘德华  
258. 2           mrzhou  
259. 3           mrjun  
260. 4           俊俊  
261. 5           慧慧  
262.    
263.   
264. (5 行受影响)  
265.   
266. id          name  
267. ----------- --------------------  
268. 1           我是刘德华  
269. 2           mrzhou  
270. 3           mrjun  
271. 4           俊俊  
272. 5           慧慧  
273.    
274.   
275. (5 行受影响)  
276.   
277. */  
278.   
279.   
280.   -----------------------------------------------------------------------------------------------  
281. -------------------5.SERIALIZABLE事务隔离级别，-----------------------------------------------------  
282. --------------SEIALIZABLE 不做过多的说明，*别把所有事务作为一个串行执行 没有并发的效果----------   

 

 

 

 

 全面解释死锁

先上例子 然后再来分析下这个代码

 

 

[sql]  view plain copy print ?

1. --两个表的如下  
2. select * from tb  
3. /*  
4. id          name  
5. ----------- --------------------------------------------------  
6. 1           我是刘德华  
7. 2           mrzhou  
8. 3           mrjun  
9. 4           俊俊  
10. 5           慧慧  
11. 8           SNAPSHOT  
12.   
13. (6 行受影响)  
14. */  
15. select * from ta  
16. /*  
17. id          name  
18. ----------- --------------------------------------------------  
19. 1           mrzhou  
20. 2           stephenzhou  
21. 3           Hello  
22.   
23. (3 行受影响)  
24.   
25.   
26. */  
27.   
28. --新建查询输入执行  
29. use Erp_System  
30. go  
31. set transaction isolation level SNAPSHOT  
32. begin tran  
33. update ta set name='http://blog.csdn.net/szstephenzhou' where id=2  
34. waitfor delay '00:00:10'  
35. update tb set name='http://blog.csdn.net/szstephenzhou' where id=8  
36. commit tran  
37.    
38.  --然后再新建查询窗口 执行 注意相隔不要超过十秒  
39.  use Erp_System  
40. go  
41. set transaction isolation level SNAPSHOT  
42. begin tran  
43. update ta set name='http://blog.csdn.net/szstephenzhou' where id=2  
44. waitfor delay '00:00:10'  
45. update tb set name='http://blog.csdn.net/szstephenzhou' where id=8  
46. commit tran  
47.    
48.  --提示错误如下：  
49.  /*  
50.    
51.  (1 行受影响)  
52. 消息 1205，级别 13，状态 45，第 5 行  
53. 事务(进程 ID 56)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。  
54.  */  
55.   select * from tb  
56.  select * from ta  
57.    
58.  /*  
59.  id          name  
60. ----------- --------------------------------------------------  
61. 1           我是刘德华  
62. 2           mrzhou  
63. 3           mrjun  
64. 4           俊俊  
65. 5           慧慧  
66. 8           http://blog.csdn.net/szstephenzhou  
67.   
68. (6 行受影响)  
69.   
70. id          name  
71. ----------- --------------------------------------------------  
72. 1           mrzhou  
73. 2           http://blog.csdn.net/szstephenzhou  
74. 3           Hello  
75.   
76. (3 行受影响)  
77.   
78.  */  
79.    
80.  --很明显第二个事务报错牺牲了  
81.    

 
 可以这样理解这个死锁。每个事务可以看成一个用户，他们独有线程spid。
 第一个事务首先更新ta表等10秒再更新tb表 而
 第二个事务先更新tb表再过十秒更新ta表
 
 第一个事务先请求ta表X锁
 第二个事务先请求tb表X锁
 两个事务都可以得到他们的X锁。
 
 当第一个用完成了ta表的更新需要获取tb表的X锁的时候第一用户还没有完成tb表的更新，还持有tb表的X锁，应为X锁是互斥的
 需要等待第二个用户的任务完成释放tb表的X锁。然而当第二个用户完成了tb表的更新执行ta表更新 请求ta表X锁时候，这个时候
 第一个用户持有ta表的X锁，第一个一个用户一直处于等待。
 所以 第一个用户和第一个用户分别个持有tb ta表上的X锁 ，互相等待所以行程死锁。
 
 
 数据库引擎会定期检查死锁情况，一旦发现问题，就会选择其中的一个事务作牺牲。
 从而释放资源

 

 

通过设置当前会话的优先级来确定是否牺牲该会话。

 

[sql]  view plain copy print ?

1. SET DEADLOCK_PRIORITY  
2. {  
3. LOW|  
4. NORMAL|  
5. HIGH|  
6. 数字策略  
7. }  

 

设置锁超时时间

当会话发生阻塞，等待某个被锁定的资源，如果等待的事件超过指定的时间则被阻塞的语句自动被取消。释放锁定的数据资源把错误提示1222返回给程序

[sql]  view plain copy print ?

1. select @@LOCK_TIMEOUT  
2. /*  
3. -----------  
4. -1  
5.   
6. (1 行受影响)  

 

@@LOCK_TIMEOUT为-1说明没有设置。

如果设置为0 则不做任何操作直接返回1222。

设置等待时间来超时，将直接返回1222 而不会回滚取消当前事务。只有程序开发人员对1222消息在程序中处理。

如果程序没有对消息1222做处理，则落上错误。

 

 

如何解除死锁

可能有人会问为什么要解除死锁。数据库引擎不是会定期检测死锁情况吗？

是的，系统是会的。如果系统很繁忙的情况，系统在检测和监视性能追踪上看到可疑的也可以手动操作

如下查看：

[sql]  view plain copy print ?

1. EXECUTE sp_lock  
2. /*  
3. spid   dbid   ObjId       IndId  Type Resource                         Mode     Status  
4. ------ ------ ----------- ------ ---- -------------------------------- -------- ------  
5. 52     13     0           0      DB                                    S        GRANT  
6. 53     13     0           0      DB                                    S        GRANT  
7. 56     13     0           0      DB                                    S        GRANT  
8. 58     13     0           0      DB                                    S        GRANT  
9. 59     2      0           0      DB   [ENCRYPTION_SCAN]                S        GRANT  
10. 59     1      1131151075  0      TAB                                   IS       GRANT  
11. 59     13     0           0      DB                                    S        GRANT  
12.   
13. */  

 

从上面的结果我们可以看到spid、dbid、objid、indid、type、resource、mode和status字段。spid是进程标识号码，用于识别到SQL 服务器的连接。要发现哪些用户和该spid相连，你就要执行存储过程sp_who，并将spid作为一个参数传输给该程序。dbid是锁定发生的数据库，你可以在主数据库中的sysdatabases表格中找到它。字段objid用来显示在数据库中锁定发生所在的对象。要查看这个对象，你可以在主数据库中的sysobjects表格中查询指定的objid。

在以上的屏幕截图中产生的单一记录并不一定能显示正在你的工作环境中发生的真实情况。在运行这个程序时，你想要找到500到1000个甚至更多结果。每一次你执行sp_lock，都将有可能得到不同的结果，因为又发生了新的锁定，而部分旧的锁定已经被解除了。如果你发现sp_lock返回的结果中，大量的结果都有着相同的spid，很有可能该进程正在进行大型的处理，同时这些锁定可能开始阻止新事务的发生。

当你发现一个spid 获得了大量的数据库锁定时，这将有助于确定什么存储过程或语句正在运行。为了达到这个目的，运行以下 DBCC 命令：

检查死锁信息

DBCC INPUTBUFFER(spid)

这个DBCC命令将返回正在EventInfo字段中运行的语句的相关信息,可以显示正在执行的sql命令。

[sql]  view plain copy print ?

1. DBCC INPUTBUFFER(58)   
2. /*  
3. EventType      Parameters EventInfo  
4. -------------- ---------- ------------------  
5. Language Event 0          select @@TRANCOUNT  
6.   
7. (1 行受影响)  
8.   
9. DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。  
10. */  

 

解除死锁

Kill spid

一个可靠的起点

系统运行缓慢可能说明你的表格上有大量的锁定。造成这些锁定的原因较多，如某个用户正在你的系统中运行一个相当长的查询，一个进程占用大量资源或者两个关键进程争夺同一资源，经常造成死锁。

一旦发现你认为正在减缓你系统速度的进程，应该怎么办？在大多数情况下，不能采取任何措施，只能监控系统。结束这个进程并不是明智之举，因为它包括了很多系统锁定，除非你完全肯定不会有其他的负面影响。不然的话，你就应该想办法自动分析锁定状况。还有一个解决办法就是想出一种方法，使得在一天的特定时间内，当系统锁数量达到极限时，发出通知。

[sql]  view plain copy print ?

1. kill 58  
2. --命令已成功完成。  

 

 

 

 

如果避免死锁的发生

1.尽量不要在一个事务中实现过于复杂的查询或者更新操作。

2.尽量不要在事务中请求用户响应。

3.死锁是由于并发访问数据资源造成的。减少死锁要减少用户的访问量。如后台有大量线程同时访问数据库，则会大大提高死锁的概率。河里的设置后台服务线程，把后台服务所要进行的操作河里的分解，分步骤 分阶段执行。

4.尽可能的分区表和分区视图来拆分包含大量的数据表。把其中放在物理上不通的磁盘文件组中。

5.尽量避免使用占用事件很长的复杂查询。可以考虑分页查询

6.尽可能使用较低的事务级别。因为隔离级别越低，事务互斥等待的情况就会减少。

7.统一访问表的顺序。

8.如果事务只进行读取操作，可以使用snapshot隔离级别。  快照隔离级别中，数据库引擎不会阻塞其他事务对当前事务占用资源的修改操作。

 

 转载地址：http://blog.csdn.net/szstephenzhou