笔记47-徐 数据库引擎中基于行版本控制的隔离级别

笔记47-徐 数据库引擎中基于行版本控制的隔离级别

MSSQL隔离级别
READ UNCOMMITTED(未提交读),相当于(NOLOCK)
READ COMMITTED(已提交读,默认)
REPEATABLE READ(可重复读),相当于(HOLDLOCK)
SERIALIZABLE(序列化)
MVCC
SNAPSHOT(快照):可以防止幻读,可重复读
READ COMMITTED SNAPSHOT(已提交读快照):不可防止幻读,可重复读

 

参考:https://www.cnblogs.com/lyhabc/articles/3996187.html  (MICROSOFT SQL SERVER 2008技术内幕:T-SQL语言基础 笔记)

  1 --数据库引擎中基于行版本控制的隔离级别
  2 
  3 --在SQL标准的已提交读(read committed)隔离级别下,一个读操作会和一个写操作
  4 --相互阻塞。未提交读(read uncommitted)虽然不会有这种阻塞,但是读操作可能
  5 --会读到脏数据,这是大部分用户不能接受的。有些关系型数据库(例如ORACLE)
  6 --使用的是另一种处理方式。在任何一个修改之前,先对修改前的版本做一个复制,
  7 --后续的一切读操作都会去读这个复制的版本,修改将创建一个新的版本。在这种
  8 --处理方式下,读,写操作不会互相阻塞。使用这种行版本控制机制的好处,是
  9 --程序的并发性比较高,但是缺点是用户读到的虽然不是一个脏数据,但是可能
 10 --是个正在被修改马上就要过期的数据值。如果根据这个过期的值做数据修改,
 11 --会产生逻辑错误。
 12 
 13 --有些用户可能为了更高的并发性而不在乎这种缺点,所以更喜欢ORACLE的那种
 14 --处理方法。为了满足这部分用户的需求,SQL2005也引入了这种机制,来实现
 15 --类似的功能。所以选取行版本控制隔离级别也可以成为消除阻塞和死锁的一种
 16 --手段
 17 
 18 --SQL有两种行版本控制:
 19 --(1)行版本控制的已提交读隔离(read_committed_snapshot)
 20 --(2)直接使用snapshot事务隔离级别
 21 
 22 --(1)(read_committed_snapshot):read_committed_snapshot数据库选项为ON时,read_committed事务通过使用行
 23 --版本控制提供语句级读取一致性
 24 
 25 --(2)(snapshot事务隔离级别)allow_snapshot_isolation数据库选项为ON时,snapshot事务通过使用行版本
 26 --控制提供事务级读取一致性
 27 
 28 
 29 --下列示例可以说明使用普通已提交读事务,行版本控制的快照隔离事务和行版本
 30 --控制的已提交读事务的行为差异
 31 
 32 --示例:
 33 
 34 --A 普通已提交事务
 35 --在此示例中,一个普通read committed事务将读取数据,然后由另一事务修改此数据。执行
 36 --完的读操作不阻塞由其他事务执行的更新操作。但是,在其他事务已经做了更新操作后,读
 37 --操作会被阻塞住,直到更新操作事务提交为止
 38 
 39 --在会话1上:
 40 USE [AdventureWorks]
 41 GO
 42 BEGIN TRAN
 43 --查询1
 44 --这个查询将返回员工有48小时休假时间
 45 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
 46 
 47 -----------------------------------------------------------------------------------------------
 48 
 49 --在会话2上:
 50 USE [AdventureWorks]
 51 GO
 52 BEGIN TRAN
 53 --修改1
 54 --休假时间减去8
 55 --修改不会被阻塞,因为会话1不会持有S锁不放
 56 UPDATE [HumanResources].[Employee]
 57 SET [VacationHours]=[VacationHours]-8
 58 WHERE [EmployeeID]=4
 59 
 60 --查询1
 61 --现在休假时间只有40小时
 62 SELECT [VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
 63 
 64 --------------------------------------------------------------------------------------------------
 65 
 66 
 67 --在会话1上:
 68 --重新运行查询语句,会被会话2阻塞
 69 --查询2
 70 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
 71 
 72 -----------------------------------------------------------------------------------------------
 73 
 74 --在会话2上:
 75 --提交事务
 76 COMMIT TRAN
 77 GO
 78 
 79 -----------------------------------------------------------------------------------------------
 80 
 81 --在会话1上:
 82 --此时先前被阻塞的查询结束,返回会话2修改好的新数据:40
 83 --查询3
 84 --这里返回40,因为会话2已经提交了事务
 85 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
 86 
 87 --修改2
 88 --这里会成功
 89 UPDATE [HumanResources].[Employee]
 90 SET [SickLeaveHours]=[SickLeaveHours]-8
 91 WHERE [EmployeeID]=4
 92 
 93 SELECT [SickLeaveHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
 94 
 95 --可以回滚会话1的修改
 96 --会话2的修改不会受影响
 97 ROLLBACK TRAN

 98 GO
 99 
100 SELECT [SickLeaveHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
101 -------------------------------------------------------------------------------------------
102 
103 
104 --B 使用快照隔离
105 --此示例中,在快照隔离下运行的事务将读取数据,然后由另一个事务修改此数据。快照事务
106 --不阻塞由其他事务执行的更新操作,他忽略数据的修改继续从版本化的行读取数据。也就是
107 --说,读取到的是数据修改前的版本。但是,当快照事务尝试修改已由其他事务修改的数据时,
108 --他将生成错误并终止
109 
110 --在会话1上:
111 USE [AdventureWorks]
112 GO
113 
114 --启用快照隔离
115 ALTER DATABASE [AdventureWorks] SET ALLOW_SNAPSHOT_ISOLATION ON
116 GO
117 
118 --设置使用快照隔离级别
119 SET TRANSACTION ISOLATION LEVEL SNAPSHOT
120 GO
121 
122 BEGIN TRAN
123 --查询1
124 --查询返回员工有48小时假期
125 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
126 ---------------------------------------------------------------------------------------------
127 
128 --在会话2上:
129 USE [AdventureWorks]
130 GO
131 
132 BEGIN TRAN
133 --修改1
134 --假期时间减8
135 --修改不会被会话1阻塞
136 UPDATE [HumanResources].[Employee]
137 SET [VacationHours]=[VacationHours]-8
138 WHERE [EmployeeID]=4
139 
140 --查询1
141 --确认值已经被改成40
142 SELECT [VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
143 
144 -----------------------------------------------------------------------------------------------
145 
146 --在会话1上:
147 --查询2
148 --再次运行查询语句
149 --还是返回48(修改前的值),因为会话1是从版本化的行读取数据
150 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
151 
152 ------------------------------------------------------------------------------------------------
153 
154 --在会话2上:
155 --提交事务
156 COMMIT TRAN
157 GO
158 
159 -------------------------------------------------------------------------------------------------
160 
161 --在会话1上:
162 --查询3
163 --再次运行查询语句
164 --还是返回48(修改前的值),因为会话1还是从版本化的行读取数据
165 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
166 
167 --修改2
168 --因为数据已经被会话2修改过,会话1想做任何修改时
169 --会遇到3960错误
170 --事务会自动回滚
171 UPDATE [HumanResources].[Employee]
172 SET [SickLeaveHours]=[SickLeaveHours]-8
173 WHERE  [EmployeeID]=4
174 
175 --会话1的修改会回滚
176 --会话2的修改不会回滚
177 ROLLBACK TRAN
178 GO
179 
180 ----------------------------------------------------------------------------------------------
181 
182 
183 
184 --C 使用行版本控制的已提交读
185 --在此示例中,使用行版本控制的已提交读事务与其他事务并发运行。已提交读事务的行为与快照事务的行为
186 --有所不同。与快照事务相同的是,即使其他事务修改了数据,已提交读事务也将读取版本化的行。
187 --与快照事务不同的是,已提交读将执行下列操作:
188 
189 --(1)在其他事务提交数据更改之后,读取修改的数据
190 --(2)能够更新由其他事务修改的数据,而快照事务不能
191 
192 --在会话1上:
193 USE [AdventureWorks]
194 GO
195 --启用行版本控制的已提交读
196 --注意运行这句话的时候,不可以有其他连接同时使用[AdventureWorks]
197 ALTER DATABASE [AdventureWorks] SET READ_COMMITTED_SNAPSHOT ON
198 GO
199 
200 --设置使用已提交读隔离级别
201 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
202 GO
203 
204 BEGIN TRAN
205 --查询1
206 --这里将返回初始值48
207 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
208 
209 ----------------------------------------------------------------------------------------------------
210 
211 --在会话2上:
212 USE [AdventureWorks]
213 GO
214 
215 BEGIN TRAN
216 --修改1
217 --假期时间减8
218 --修改不会被会话1阻塞
219 UPDATE [HumanResources].[Employee]
220 SET [VacationHours]=[VacationHours]-8
221 WHERE [EmployeeID]=4
222 
223 --查询1
224 --确认值已经被修改为40
225 SELECT [VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
226 
227 ------------------------------------------------------------------------------------------------
228 
229 --在会话1上:
230 --查询2
231 --再次运行查询语句
232 --还是返回48(修改前的值),因为会话2还没有提交
233 --会话1是从版本化的行读取数据
234 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
235 
236 -------------------------------------------------------------------------------------------------
237 
238 --在会话2上:
239 --提交事务
240 COMMIT TRAN
241 GO
242 --------------------------------------------------------------------------------------------------
243 
244 --在会话1上:
245 --查询3
246 --这里和范例B不同,会话1始终返回已提交的值
247 --这里返回40,因为会话2已经提交了事务
248 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
249 
250 --修改2
251 --这里会成功
252 UPDATE [HumanResources].[Employee]
253 SET [SickLeaveHours]=[SickLeaveHours]-8
254 WHERE [EmployeeID]=4
255 
256 --可以回滚会话1的修改
257 --会话2的修改不会受影响
258 ROLLBACK TRAN
259 GO
260 
261 
262 ------------------------------------------结论-----------------------------------------------------------
263 --从上面的测试结果可以看到,原先会发生阻塞的两个会话在使用行版本控制的隔离级别后,都不会遇到阻塞了。
264 --但是两种行版本控制的结果又有不同
265 
266 --但是行版本控制并不是消除阻塞和死锁的万灵药。在决定使用之前,必须考虑下面两个问题
267 
268 --(1)最终用户是否接受行版本控制下的运行结果?  行版本控制:数据库级别
269 --上面的3个测试返回的结果都各有不同。在不同的事务阶段,有的被阻塞住,有的读到的是旧版本值,
270 --有的读到新版本值。用户期望的行为是什么?他是希望哪怕被阻塞住也要读到最新版本数据,还是
271 --能容忍读到旧版本数据呢?某些应用程序依赖于读隔离的锁定和阻塞行为,例如生成一个串行的流水号
272 --之类的操作。改成行版本控制,原先的处理逻辑就不能正常工作了。所以在采用新的隔离级别之前,
273 --一定要做好测试,确保应用按预期的逻辑运行
274 
275 
276 
277 
278 --(2)SQL是否能支持行版本控制带来的额外负荷?
279 --开启了行版本控制之后,SQL会把行版本存放在tempdb里。修改的数据越多,需要存储的信息越多
280 --对SQL额外的负载就越大。所以如果一个应用要从其他隔离级别转向使用行版本控制,需要做特别
281 --的测试,以确保现有的软硬件配置能支持额外的负荷,应用程序能够达到相似的响应速度

 

对于库存类业务,严格按照串行方式执行的,那么建议使用SNAPSHOT(快照,也叫全快照隔离级别),在SNAPSHOT(快照)下面,别的事务要等修改数据的那个事务运行完才能修改,保证串行,缺点是别的事务没有重试机制的话
会造成更新丢失

对于库存类业务,不建议使用READ COMMITTED SNAPSHOT(已经提交读隔离),不保证串行方式执行,两个事务可以同时修改同一行数据,A事务读的是当前版本,B事务读的是历史版本,同时修改的时候,会造成数据错乱
因为B事务修改的是历史版本数据

RDS-SQLSERVER的READ COMMITTED与READ_COMMITTED_SNAPSHOT的区别及各自优缺点
解剖SQLSERVER 第一篇 数据库恢复软件商的黑幕(有删减版)
https://www.cnblogs.com/lyhabc/p/3900229.html
https://www.cnblogs.com/lyhabc/articles/3967438.html

 

更新丢失更清楚的解释:https://www.2cto.com/database/201609/547323.html

 

SQL Server实现了SQL99定义的四个标准隔离级别,并且额外实现了两个快照隔离级别
需要说明的是,不同的DBMS的默认隔离级别和对隔离级别的实现是不完全一样的,也不一定是完全按照SQL99定义的四个标准隔离级别来实现的

 

下面两种是mssql增加的为了兼容mvcc特性而增加的隔离级别,mysql默认就支持mvcc,所以不会有下面两种隔离级别

SNAPSHOT(快照)
READ COMMITTED SNAPSHOT(已经提交读隔离)

 

 

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