笔记46-徐 一个常见的select ,update ,insert ,delete动作要申请的锁

笔记46-徐 一个常见的select ,update ,insert ,delete动作要申请的锁

  1 --一个常见的select ,update ,insert ,delete动作要申请的锁
  2 
  3 --两个表都加了索引的字段
  4 --employeeID
  5 --managerid
  6 --modifieddate
  7 
  8 --------------------一个常见的select动作要申请的锁-----------------------------------------------
  9 --在可重复读的级别下,共享锁要保留到事务提交的时候才释放,所以如果
 10 --这个隔离级别下开启一个事务,再运行一个查询语句,就能看到这个查询所申请的主要
 11 --共享锁。因此可以使用这种简单方法分析一下一个查询语句会申请哪些锁,并且不需要
 12 --SQL Trace
 13 --(1)在连接A中,将事务隔离级别设置为可重复读(repeatable read)
 14 --(2)在运行查询语句之前先开启一个事务
 15 --(3)运行查询语句,但是不提交这个事务
 16 --(4)在第二个连接里,查询sys.dm_tran_locks动态管理视图来分析查询结束后连接
 17 --A还持有的锁
 18 
 19 
 20 --我们先在有聚集索引的那张表上运行一句最简单的查询 在SSMS里新建一个查询---------
 21 --查询一:
 22 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
 23 GO
 24 SET STATISTICS PROFILE ON
 25 GO
 26 --以下查询使用了聚集索引查找 ctrl+l
 27 BEGIN TRAN
 28 SELECT [EmployeeID],[LoginID],[Title] FROM [dbo].[Employee_Demo_BTree] WHERE [EmployeeID] IN(3,30,200)
 29 -------------------------------------------------------------------------------------------------------
 30 
 31 --查看DMV看一下有多少个锁被这个连接持有-----------------------------------------------------------
 32 USE [AdventureWorks] --要查询申请锁的数据库
 33 GO
 34 SELECT
 35 [request_session_id],
 36 c.[program_name],
 37 DB_NAME(c.[dbid]) AS dbname,
 38 [resource_type],
 39 [request_status],
 40 [request_mode],
 41 [resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
 42 p.[index_id]
 43 FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
 44 ON a.[resource_associated_entity_id]=p.[hobt_id]
 45 LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
 46 WHERE c.[dbid]=DB_ID('AdventureWorks')  ----要查询申请锁的数据库
 47 ORDER BY [request_session_id],[resource_type]
 48 
 49 
 50 --查询一所持有的锁
 51 --(1)因为连接正在访问数据库[AdventureWorks],所以在数据库一级加了一个共享锁,以防止
 52 --别人将数据库删除
 53 
 54 
 55 --(2)因为正在访问表格[Employee_Demo_BTree],所以在表格上加了一个意向共享锁,以防止
 56 --别人修改表的定义  resource_type:object
 57 
 58 
 59 --(3)查询有3条记录返回,所以在这3条记录所在的聚集索引键上,分别持有一个共享锁。
 60 --在这3个键所在的页面上,持有一个意向共享锁  resource_type:page,key 锁住整页
 61 
 62 --可以说,这个查询申请锁的数目是很少的。其他用户访问同一张表,只要不访问这3条记录
 63 --,就不会影响到。这是因为查询使用了“聚集索引查找”的关系
 64 
 65 ---------------------------------------------------------------------------------------------
 66 
 67 --运行查询二:在SSMS里新建一个查询,运行之前记得将前面查询一的事务提交或者回滚 多次运行最后那句COMMIT TRAN
 68 --BEGIN TRAN
 69 --SELECT [EmployeeID],[LoginID],[Title] FROM [dbo].[Employee_Demo_BTree] WHERE [EmployeeID] IN(3,30,200)
 70 --COMMIT TRAN
 71 
 72 --查询二
 73 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
 74 GO
 75 SET STATISTICS PROFILE ON
 76 GO
 77 BEGIN TRAN
 78 SELECT
 79 [EmployeeID],
 80 [LoginID],
 81 [Title]
 82 FROM [dbo].[Employee_Demo_Heap]
 83 WHERE [EmployeeID]=3
 84 
 85 --COMMIT TRAN
 86 --运行之后最后记得加上COMMIT TRAN
 87 
 88 ---------------分析-------------------------------------------
 89 --因为[Employee_Demo_Heap]的[EmployeeID]上是一个非聚集索引,所以SQL在用非聚集索引
 90 --找到这条记录之后,必须再到数据页面上把其他的行上面的数据找出来(所谓的“书签查找” bookmark lookup)
 91 --从申请的锁上也能看出来,虽然只返回一条记录,可是他在[PK_Employee_EmployeeID_Demo_Heap]
 92 --(index_id是2 表明是非聚集索引)上申请了一个key锁,在RID(datapage数据页上的行row)
 93 --申请了一个row锁。在这两个资源所在的页面上各申请了一个page意向锁
 94 
 95 
 96 --与上面的例子比较,虽然查询二返回的结果和查询一是一样的,但是由于他使用的是非聚集索引+书签查找
 97 --bookmark lookup,所以申请的锁的数目要比查询一多。一个查询要使用的索引键(或者RID)数目越多
 98 --,他申请的锁也就会越多。没有用到的索引上不会申请共享锁
 99 
100 
101 
102 
103 --那么是不是所有的查询都只在返回的记录上加锁呢?现在再来做下面这个试验,运行他之前
104 --请记得将前面那个事务提交或回滚
105 
106 --------------------------------------------------------------------------------------
107 --修改一 :update语句:在SSMS里新建一个查询
108 --首先开启一个事务,修改一条查询不会返回记录
109 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
110 GO
111 SET STATISTICS PROFILE ON
112 GO
113 BEGIN TRAN
114 UPDATE [dbo].[Employee_Demo_Heap]
115 SET [Title]='aaa'
116 WHERE [EmployeeID]=70
117 
118 --再在另一个连接里运行查询三
119 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
120 GO
121 SET STATISTICS PROFILE ON
122 GO
123 BEGIN TRAN
124 SELECT
125 [EmployeeID],
126 [LoginID],
127 [Title]
128 FROM [dbo].[Employee_Demo_Heap]
129 WHERE [EmployeeID] IN(3,30,200)
130 
131 
132 ---------------------分析--------------------------------------------------
133 --由于要返回3条分布在不同数据页上的记录,SQL认为做非聚集索引+书签查找并不比
134 --做一个表扫描快,所以他直接选择了一个表扫描。
135 
136 --DMV显示查询三已经得到了RID 1:3181:2和1:3181:29上的锁,他们应该是EmployeeID为3和30
137 --的,他在往下找EmployeeID为200的时候,读到了RID 1:3208:22。他是EmployeeID为70,
138 --被上面的update语句修改了,update那个事务还没有提交,所以查询三被阻塞了
139 
140 --现在把修改一回滚,阻塞解除,查询三能够执行完毕,可以看到他的执行计划与他持有的锁
141 
142 --SQL一直往下找
143 --书签
144 --70   ->还没有提交
145 --80
146 --90
147 --100
148 --.
149 --.
150 --.
151 --.
152 --.
153 --.
154 --200
155 
156 --所以不能继续往下找,如果跳过的话,他不知道跳过的是不是就是他要找的那条记录,所以阻塞了
157 --一个RID对应一个EmployeeID
158 
159 --与查询一不同的是,查询三不但在这三条记录所在页面(1:3211,1:3181)申请了IS锁,
160 --还在表格的所有页面上都申请了IS锁。所以这就是全表扫描,扫描了所有的数据页面带来
161 --的后果。更严重的是,查询三在扫描每一张页面的时候,会对读到的每一个数据记录加上
162 --一个共享锁(读完了这条记录就会释放,不用等到整个语句结束)只要有任何一个记录
163 --上的锁没有申请到,查询就会被阻塞
164 
165 
166 --------------------------------------------------------------------------------------------
167 --运行修改二,再运行查询一
168 --修改二:update语句:在SSMS里新建一个查询
169 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
170 GO
171 SET STATISTICS PROFILE ON
172 GO
173 BEGIN TRAN
174 UPDATE [dbo].[Employee_Demo_BTree]
175 SET [Title]='aaa'
176 WHERE [EmployeeID]=70
177 
178 
179 
180 --再在另一个连接里运行查询一
181 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
182 GO
183 SET STATISTICS PROFILE ON
184 GO
185 --以下查询使用了聚集索引查找 ctrl+l
186 BEGIN TRAN
187 SELECT [EmployeeID],[LoginID],[Title] FROM [dbo].[Employee_Demo_BTree] WHERE [EmployeeID] IN(3,30,200)
188 
189 
190 ---------------------------分析-------------------------------------------------------------
191 --这是因为查询一使用的是索引查找index seek,不需要每条记录都读一遍,所以就不用
192 --去读EmployeeID70,也就不会被阻塞住  因为select是select书签里面的内容根本不用到表里去读取
193 --数据
194 
195 
196 
197 --------------------------总结select动作--------------------------------------------------------
198 --规律:在非“未提交读”的隔离级别上
199 --已提交读
200 --可重复读
201 --可序列化
202 --(1)查询在运行过程中,会对每一条读到的记录或键值加共享锁。如果记录不用返回。
203 --那锁就会被释放。如果记录需要被返回,则视隔离级别而定,如果是“已提交读”,则也释放
204 --否则,不释放
205 
206 --(2)对每一个使用到的索引,SQL也会对上面的键值加共享锁
207 
208 --(3)对每个读过的页面,SQL会加一个意向锁
209 
210 --(4)查询需要扫描页面和记录越多,锁的数目也会越多。查询用到的索引越多,锁的数目
211 --也会越多
212 
213 
214 --避免阻塞采取的方法
215 --(1)尽量返回少的记录集,返回的结果越多,需要的锁也就越多
216 
217 --(2)如果返回结果集只是表格所有记录的一小部分,要尽量使用index seek,避免全表扫描这种
218 --执行计划
219 
220 --(3)可能的话,设计好合适的索引,避免SQL通过多个索引才找到数据
221 
222 --当然,这些对于“已提交读”以上隔离级别而言。如果使用“未提交读”,SQL就不会申请这些共享锁
223 --阻塞也不会发生
224 
225 
226 
227 
228 --------------------一个常见的update动作要申请的锁-----------------------------------------------
229 --对于update语句,可以简单理解为SQL先做查询,把需要修改的记录给找到,然后在这个记录
230 --上做修改。找记录的动作要加S锁,找到修改的记录后加U锁,再将U锁升级为X锁。
231 
232 --这里用上面两张表做例子,选用repeatable read的隔离级别,运行一个update语句
233 USE [AdventureWorks]
234 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
235 GO
236 BEGIN TRAN
237 UPDATE [dbo].[Employee_Demo_Heap]
238 SET [Title]='changeheap'
239 WHERE [EmployeeID] IN(3,30,200)
240 
241 
242 --这个update语句在非聚集索引上申请了3个U锁,在RID上申请了3个X锁。这是因为语句借助非聚集索引
243 --PK_Employee_EmployeeID_Demo_Heap(index_id是2)找到了这3条记录。非聚集索引PK_Employee_EmployeeID_Demo_Heap
244 --本身没有用到Title这一列,所以他自己不需要做修改。但是数据RID上有了修改,所以RID上加的是X锁,其他
245 --索引上没有加锁
246 
247 --从这个例子可以看出,如果update借助了哪个索引,这个索引的键值上就会有U锁,没有用到的
248 --索引上没有锁。真正修改发生的地方会有X锁。对于查询涉及的页面,SQL加了IU锁意向更新锁,修改
249 --发生的页面,SQL加了IX锁 意向排他锁 (先查询再修改)锁key 锁索引键值 因为修改的列没有被索引
250 
251 --如果修改的列被一个索引使用到了,会是什么情况呢?为了完成这个测试,先在Employee_Demo_BTree
252 --上建一个会被修改的索引
253 
254 CREATE NONCLUSTERED INDEX [Employee_Demo_BTree_Title] ON [AdventureWorks].[dbo].[Employee_Demo_BTree]
255 ([Title] ASC)
256 
257 
258 --再运行下面语句
259 USE [AdventureWorks]
260 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
261 GO
262 BEGIN TRAN
263 UPDATE [dbo].[Employee_Demo_Heap]
264 SET [Title]='changeheap'
265 WHERE [EmployeeID] IN(3,30,200)
266 
267 
268 
269 --语句利用聚集索引找到要修改的3条记录.但是我们看到有9个键上有X锁。
270 --很有意思:PK_Employee_EmployeeID_Demo_BTree(index_id=1)聚集索引,也是数据存放的地方。
271 --刚才做的update语句没有改到他的索引列,他只需把Title这个列的值改掉。所以在index1上,
272 --他只申请3个X锁,每条记录一个
273 
274 --但是表格在Title上面有一个非聚集索引IX_Employee_ManagerID_Demo_BTree(index_id=5),
275 --并且Title是第一列。他被修改后,原来的索引键值就要被删除掉,并且插入新的键值。
276 --所以在index_id=5 上要申请6个X锁,老的键值3个,新的键值3个
277 
278 --因为其他索引没有使用到Title这一列,所以他们上面都没有申请锁
279 --这就是9个key锁的来源
280 
281 
282 --------------------update语句的规律---------------------------------------------------------
283 --(1)对每一个使用到的索引,SQL会对上面的键值加U锁
284 
285 --(2)SQL只对要做修改的记录或键值加X锁
286 
287 --(3)使用到要修改的列的索引越多,锁的数目也会越多
288 
289 --(4)扫描过的页面越多,意向锁也会越多。在扫描的过程中,对所有扫描到的记录也会加锁,哪怕
290 --上面没有修改
291 
292 -----------------------------结论-------------------------------------------------------
293 --想降低一个update语句被别人阻塞住的几率,除了注意他的查询部分之外,数据库设计者
294 --还要做的事情有:
295 
296 --(1)尽量修改少的记录集。修改的记录越多,需要的锁也就越多
297 
298 --(2)尽量减少无谓的索引。索引的数目越多,需要的锁也可能越多
299 
300 --(3)但是也要严格避免表扫描的发生。如果只是修改表格记录的一小部分,要尽量使用index seek索引查找
301 --避免全表扫描这种执行计划
302 
303 
304 
305 
306 
307 
308 --------------------一个常见的delete动作要申请的锁-----------------------------------------------
309 --这次使用read committed这个默认隔离级别
310 
311 USE [AdventureWorks]
312 BEGIN TRAN
313 DELETE [dbo].[Employee_Demo_BTree]
314 WHERE [LoginID]='adventure-works\kim1'
315 
316 
317 --可以看到delete语句在聚集索引(index_id=1)和两个非聚集索引(index_id=2和3)上各申请了一个X锁
318 --在她们所在的页面上申请了一个IX锁
319 
320 --如果使用repeatable read这个级别运行上面的delete命令,就能看出好像做select的时候一样,做
321 --delete的时候SQL也需要先找到要删除的记录。在找的过程中也会加锁
322 
323 --现在运行一个新的delete语句,会使用全表扫描
324 
325 USE [AdventureWorks]
326 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
327 GO
328 BEGIN TRAN
329 DELETE [dbo].[Employee_Demo_Heap]
330 WHERE [LoginID]='adventure-works\tete0'
331 
332 --可以看到delete语句在3个非聚集索引(index_id=2、3、4)上各申请了一个X锁。在她们
333 --所在的页面上申请了一个IX锁。在修改发生的heap数据页面上,申请了一个IX锁,相应的
334 --RID上(真正的数据记录)申请了一个X锁。其他扫描过的页面申请了IU锁
335 
336 ----------------------------规律-----------------------------------------------------------------
337 --(1)delete的过程是先找到符号条件的记录,然后做删除。可以理解为先是一个select,然后
338 --是delete.所以,如果有合适的索引,第一步申请的锁就会比较少 不用表扫描
339 
340 --(2)delete不但是把数据行本身删除,还要删除所有相关的索引键.所以一张表上索引数目越多
341 --锁的数目就会越多,也就越容易发生阻塞
342 
343 --为了防止阻塞,我们既不能绝对地不建索引,也不能随随便便地建立很多索引,
344 --而是要建立对查找有利的索引.对于没有使用到的索引,还是去掉比较好
345 
346 
347 
348 
349 
350 --------------------一个常见的insert动作要申请的锁-----------------------------------------------
351 --相对于select,update,delete,单条记录的insert操作对锁的申请比较简单。SQL会为新插入
352 --的数据本身申请一个X锁,在发生变化的页面上申请一个IX锁。由于这条记录是新插入的,被
353 --其他连接引用到的概率会相对小一些,所以出现阻塞的几率也要小
354 
355 --还是用刚才的那两张表做例子。首先要插入的是heap结构的表
356 USE [AdventureWorks]
357 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
358 GO
359 BEGIN TRAN
360 INSERT INTO [dbo].[Employee_Demo_Heap]
361         ( [EmployeeID] ,
362           [NationalIDNumber] ,
363           [ContactID] ,
364           [LoginID] ,
365           [ManagerID] ,
366           [Title] ,
367           [BirthDate] ,
368           [MaritalStatus] ,
369           [Gender] ,
370           [HireDate] ,
371           [ModifiedDate]
372         )
373 SELECT
374 501,
375 480168528,
376 1009,
377 'adventure-works\thierry0',
378 263,
379 'Tool Desinger',
380 '1949-08-29 00:00:00.000',
381 'M',
382 'M',
383 '1998-01-11 00:00:00.000',
384 '2004-07-31 00:00:00.000'
385 
386 
387 
388 
389 --如果插入的是有B树结构的表格
390 
391 USE [AdventureWorks]
392 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
393 GO
394 BEGIN TRAN
395 INSERT INTO [dbo].[Employee_Demo_BTree]
396         ( [EmployeeID] ,
397           [NationalIDNumber] ,
398           [ContactID] ,
399           [LoginID] ,
400           [ManagerID] ,
401           [Title] ,
402           [BirthDate] ,
403           [MaritalStatus] ,
404           [Gender] ,
405           [HireDate] ,
406           [ModifiedDate]
407         )
408 SELECT
409 501,
410 480168528,
411 1009,
412 'adventure-works\thierry0',
413 263,
414 'Tool Desinger',
415 '1949-08-29 00:00:00.000',
416 'M',
417 'M',
418 '1998-01-11 00:00:00.000',
419 '2004-07-31 00:00:00.000'
420 
421 
422 --两者都申请了以下锁资源:
423 --(1)数据库上的S锁(resource_type=DATABASE)
424 
425 --(2)表上的IX锁(resource_type=OBJECT)
426 
427 --(3)每个索引上都要插入一条新数据,所以有一个key上的X锁
428 
429 --(4)在每个索引上发生变化的那个页面,申请了一个IX锁(resource_type=PAGE)
430 
431 --唯一不同的是,是在heap结构上还得申请一个RID锁。因为真正的数据不是放在索引上,而是放在heap数据页面上
432 
433 
434 
435 
436 
437 
438 ----------------------------------------结论-------------------------------------------
439 --如果SQLDBA要控制SQL锁的申请和释放行为,以缓解阻塞和死锁问题,需要考虑的因素有:
440 --1、事务隔离级别的选定
441 --事务隔离级别越高,隔离度就越高,并发度也就越差。如果选择了比较高的隔离级别,SQL
442 --不可避免地要申请更多的锁,持有的时间也会增加。所以在设计应用的时候,一定要
443 --和用户谈好,尽量选择默认的隔离级别(read committed)
444 
445 
446 --2、事务的长短和事务的复杂度
447 --事务的长度和复杂度决定论这个事务在SQL内部会持续多长时间,也能决定SQL会同时在
448 --多少张表和索引上申请和持有锁。事务越简单,就越不容易发生阻塞和死锁。所以这
449 --也必须和用户商量好,尽量避免在一个事务里做很多事情
450 
451 
452 --3、从应用整体并发度考虑,单个事务一次处理的数据量不能过多
453 --应用的性能,不单要衡量单个连接的处理速度,也要衡量在并发处理的情况下,整体
454 --的平均速度怎么样。从连接个体来讲,可能在一个事务里把数据一次都处理掉比较快
455 --但是如果处理的数据量很大,就会影响到其他连接同时访问同一对象。所以,如果
456 --一个应用的并发要求比较高,就一定要严格控制单个事务处理的数据量。如果有什么
457 --事务操作需要访问或修改表格内的大量数据,最好调整到并发用户比较少的时候运行
458 
459 
460 --4、针对语句在表格上设计合适的索引
461 --合适的索引能使SQL在读取尽可能少的数据量的前提下,把需要处理的数据找到。如果
462 --没有合适的索引,SQL在做select,update,delete的时候,会申请比要处理的目标数据量
463 --多得多的锁,从而导致阻塞或死锁。这种情形可以通过加索引的方式提高并发度
464 --同时,SQL在做update,insert,delete的时候,会对有关联的所有索引都做修改,在她们
465 --上面申请锁。从这个角度讲,索引越多,产生的锁的数目也就越多,阻塞和死锁的几率
466 --也就会越高
467 
468 --所以数据库设计员需要做的,是要确保有足够的索引,防止语句做全表扫描,但是也要
469 --去掉那些对语句运行贡献不大的索引。不能随便往表格上加索引

 

posted @ 2013-07-27 16:21 桦仔 阅读(...) 评论(...)  编辑 收藏