MyException - 我的异常网
当前位置:我的异常网» SQL » SQL Server 中的6种事务隔离级别简略总结

SQL Server 中的6种事务隔离级别简略总结

www.MyException.Cn  网友分享于:2013-08-06  浏览:0次
SQL Server 中的6种事务隔离级别简单总结

 

本文出处:http://www.cnblogs.com/wy123/p/7218316.html 
(保留出处并非什么原创作品权利,本人拙作还远远达不到,仅仅是为了链接到原文,因为后续对可能存在的一些错误进行修正或补充,无他)

 


数据库中的事物是具有原子性(Atomicity),一致性(Consistemcy),隔离性(Isolation),持久性(Durability)四个特征。
在上述四个特性中的一致性和隔离性的实现中,是通过锁来实现对相同数据的访问隔离的。
事物的隔离级别又可以影响锁的申请和时间的时机。
因此,不同的事物隔离级别又可以对锁的申请和释放产生不同的影响,因此,在对数据库做事物控制的时候需要了解隔离级别对事物的影响。
SQL Server实现SQL99标准规定的事务的四个隔离级别(未提交读,已提交读,可重复读,序列化)之外,另外增加了两个隔离级别(快照个基于行版本的已提交读隔离级别)。
不同的隔离级别对控制脏读,不可重复读,幻读有一定的控制,也会并发有一定程度的影响,
隔离级别越低,并发性越高,但是产生脏读,不可重复读,幻读等可能性越大;随着事物隔离级别的提交,可以控制脏读,不可重复读,以及幻读的现象,但是并发性也会随之降低。
事物隔离级别和执行计划都可以影响锁(范围)的申请和释放时机,本文暂不讨论执行计划对锁申请的影响,仅在隔离级别上说明锁的申请和释放。
以下简单介绍SQL Server中的六个隔离级别以及每个隔离级别的特征,在此基础上说明每个隔离级别可能存在的问题解决方法。


未提交读

运行当前Session读取其他事务已修改但是尚未提交的数据,也即当前Session可以读取到“脏数据”。
当前Session不会对读取的数据加共享锁。
set transaction isolation level read uncommitted;
或者
select * from table with(nolock)

特点:未提交读是最低的一种隔离级别。
存在的问题:脏读,不一致读,幻读等。


如下是未提交度的存在脏读一种演示(当然也会存在不一致读,幻读等情况)。

   

 


已提交读

set transaction isolation level read committed;
或者
select * from table  默认人就是已提交读
运行当前Session不能读取其他事务已修改但是尚未提交的数据。
如果其他事务提对当前Session读取的数据有修改且尚未提交,当前Session被阻塞。
原因是在以已提交读隔离级别情况下:当前Session会对读取的数据加共享锁,如果遇到读取的数据尚未提交,当前查询被阻塞。

特点:相比为提交读隔离级别,解决了未提交读隔离级别下的读取“脏数据”的问题,
存在的问题:存在不可重复度或者幻读的问题。

 

已提交读隔离级别下存在不可重复读的现象(两次读取的同一行数据结果不一致)

 

 

 不可重复读隔离级别下存在的幻读现象(一个事物中,同样的条件,读到的数据行数不一致)

   

 


可重复读

set transaction isolation level repeatable read;
运行当前Session不能读取其他事务已修改但是尚未提交的数据,并且当前Session运行期间,其他Session不能修改当前Session读取到的数据
也就是说,当前Session运行期间,读取到的数据是被加了共享锁的,所加的共享锁一直保持,直到事务提交的时候才释放。
相比已提交读最大的特点就是事务运行期间,共享锁将一直保持,直到当前Session事务提交,
因此可以保持当前Session读取到的数据不被其他Session修改,所以就不存在两次读取的数据不一致的现象。
可重复读隔离级别解决了不可重复读的问题,但依旧存在幻读的情况。

特点:相比前一种隔离级别,可重复读解决了已提交读隔离级别的不可重复读的问题,也即两次读取的同一行数据是一致的
存在的问题:相比已提交读,依旧存在幻读的问题。

 

  如下是可重复读隔离级别的幻读的现象,也即在同一个事物的两次读取期间,其他事物可以写入当前事物读取的数据(范围)

  


可序列化

当前Session不能读取其他Session已修改但未提交的数据(不允许脏读)
当前Session读取的数据上的共享锁一直保持直到事务提交(可重复读)
当前Session事务提交之前,其他Session不能插入当前Session中读取的键值(解决了幻读的问题)
set transaction isolation level serializable
或者开启事务之后对表加holdlock提示
select * from table with(holdlock) where id = n
可序列化解决了另外一个非常经典的问题,使用update table with(holdlock) 或者select * from table with(xlock,holdlock),并发情况下的“存在则更新不存在则插入”重复插入的问题。
参考:http://www.cnblogs.com/TeyGao/p/6929246.html

可序列化锁定的原理是加范围锁的方式来实现的,当一个Session发起了请求之后,对于当前Session范围内的数据,不管是否存在,都加一个共享锁。
比如在可序列化的隔离级别之下,select * from table with where id>=100 and id<= 120
在Session执行期间,SQL Server会锁定 100<=id<= 120这个范围的数据,不管表中这个区间是否存在数据, 都锁定这个Id的范围,不允许该Id范围的数据写入。
也即100<=id<= 120这个范围被所锁定(无法增加删除或者修改这个范围的数据)

 

可序列化隔离级别解决了幻读的问题,也就是说,当前事物的两次读中间,其他Session对当前Session读取数据范围之内的数据修改的时候,会被阻塞,直到当前事物提交。

   

 

 

基于行版本控制的隔离级别

  默认隔离级别,也即已提交读隔离级别下,存在一个明显的问题就是写会阻塞读,也就是说,一个写数据的事物未提交之前,会阻塞其他事物对当前操作数据的读取,直到当前写事物的操作提交。
  基于行版本控制的已提交读隔离级别下,写不会阻塞读,写数据的事物未提交之前,会将修改的数据之前的版本,写入临时数据库,
  读数据的事物在读取的时候,发现要读取的数据被修改,会转向临时库中读取出来一个写事物修改数据之前的版本,这样可以在一定程度上提高并发性(当然临时库会承担一定的压力)。
  SQL Server有两种基于行版本控制的隔离级别:快照隔离级别(snapshot)和基于行版本控制的已提交读隔离级别(read_committed_snapshot)
     两种行版本控制分别要基于数据级别开启allow_snapshot_isolation和read_committed_snapshot

(1)快照隔离级别(snapshot)

数据库级别设置快照隔离级别
alter database Test set allow_snapshot_isolation on;

 

 Session级别设置快照隔离级别:set transaction isolation level snapshot

 

快照隔离级别最大的特点是,当前Session读取其他事物修改的数据的时候,不会被阻塞,读取的是其他事物已经修改,但是尚未提交的数据
但是当前事物尝试修改“在其他其他事物中提交修改之后的数据”,会报错(快照隔离事务由于更新冲突已终止)。
具体过程如下,从时间的维度来看,步骤如下
1)Session2 开启事物,修改Id =1的数据,暂不提交
2)Session1 读取id=1的数据,不会被阻塞,读取到的是Session2修改之前的数据的版本
3)Session2修改Id =1的数据之后,事物提交
4)Session1尝试修改Id=1的数据,报错

  

  实际操作上看,如下

 

(2)基于行版本控制的已提交读隔离级别(read_committed_snapshot)

数据库级别设置为基于行版本控制的已提交读隔离级
alter database Test set read_committed_snapshot on;
go


--将当前事物设置为已提交读快照隔离级别
set transaction isolation level read committed

快照隔离级别最大的特点是,当前Session读取其他事物修改的数据的时候,不会被阻塞,读取的是其他事物已经修改,但是尚未提交的数据
与快照隔离级别相对,当前Session尝试修改“在其他其他Session中提交修改之后的数据”,可以成功提交。
具体过程如下,从时间的维度来看,步骤如下
1)Session2 开启事物,修改Id =1的数据,暂不提交
2)Session1 读取id=1的数据,不会被阻塞,读取到的是Session2修改之前的数据的版本
3)Session2修改Id =1的数据之后,事物提交
4)Session1尝试修改Id=1的数据,成功提交,

  基于行版本控制的已提交读隔离级别最大的特点是,当前读取的数据是,其他Session已修改尚未提交之前的版本,但是当前事物尝试修改时,可以成功提交
  这样一来,就忽略掉了当前事物运行期间,其他事物修改且提交的那个版本的数据,有点绕,需要慢慢理解。

  行版本控制的已提交读隔离级别的问题也很明显,当前Session读取数据的时候,是其他事物修改之前的版本,当前Session对读取到的数据可以在其他事物提价之前的版本上执行修改,
  而忽略了当前Session在读和写的间隔期间,其他Session修改并且提交事物的影响,为此可能会产生一定程度的影响。

 

   从时间维度上看如下图所示

 

 

具体执行现象如下:
存在的问题就是,Session1第一次读取的时候,读取的Id = 1数据的那么是AAA,实际上此时其他Session2已经将Id = 1的那么修改为了Update_AAA,
随后Session2事物提交,当前Session执行修改的时候,忽略了Session2修改后的数据,可以直接将数据修改为AAA+++
需要注意的是,Session1修改成功的前提是Session2的事物提交,如果Session2修改事物没有提交,Session1的修改操作被阻塞。

截图中第一行的备注没有修改过来,应该是快照已提价读隔离级别

   

  

 总结:

  本文简单阐述了SQL Server中的几种隔离级别,SQL Server实现了SQL99定义的四个标准隔离级别,并且额外实现了两个快照隔离级别。
  需要说明的是,不同的DBMS的默认隔离级别和对隔离级别的实现是不完全一样的,也不一定是完全按照SQL99定义的四个标准隔离级别来实现的,
  因此在做事物控制的时候,需要了解具体的隔离级别以及具体特性。

文章评论

“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
 程序员的样子
程序员的样子
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
每天工作4小时的程序员
每天工作4小时的程序员
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
一个程序员的时间管理
一个程序员的时间管理
Java程序员必看电影
Java程序员必看电影
程序员的鄙视链
程序员的鄙视链
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
10个调试和排错的小建议
10个调试和排错的小建议
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
老程序员的下场
老程序员的下场
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
代码女神横空出世
代码女神横空出世
那些争议最大的编程观点
那些争议最大的编程观点
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
中美印日四国程序员比较
中美印日四国程序员比较
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
我的丈夫是个程序员
我的丈夫是个程序员
编程语言是女人
编程语言是女人
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
如何成为一名黑客
如何成为一名黑客
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
程序员和编码员之间的区别
程序员和编码员之间的区别
漫画:程序员的工作
漫画:程序员的工作
程序员必看的十大电影
程序员必看的十大电影
程序员都该阅读的书
程序员都该阅读的书
为什么程序员都是夜猫子
为什么程序员都是夜猫子
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
我是如何打败拖延症的
我是如何打败拖延症的
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
总结2014中国互联网十大段子
总结2014中国互联网十大段子
程序员应该关注的一些事儿
程序员应该关注的一些事儿
鲜为人知的编程真相
鲜为人知的编程真相
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有