首页 > 代码库 > SQL SERVER 2008 数据库隔离级别代码演示
SQL SERVER 2008 数据库隔离级别代码演示
SQL SERVER 2008 数据库隔离级别代码演示
?
在各种SQL 的教程上一直都强调SQL SERVER 的4个隔离级别(其实这是SQL 工业标)
未提交读 | ReadUncommited? |
已提交读 | ReadCommited? |
不可重复读 | RepeatableRead? |
序列化 | Serializable (为什么叫这个么名字？) |
这4种隔离级别，本身没有优劣之分，完全取决于应用的场景。
本质上，他们是在 隔离性（紊乱程度） 和 灵活性（并发性） 之间博弈。简单的说，灵活性越高，隔离性越差。反之亦然。
我的理解是：如果不太计较数据出现的少许紊乱，可以使用用最低级别的隔离性（保持高并发性） ，比如 刷微信 这类事情，你不能让用户人等着 朋友圈数据同步完了再出来结果。
同样的，发奖金这样的事情是极其敏感的。你不能因为来了几个新人，就要求把老员工的奖金拿去平均了(总奖金/员工数量=单人奖金?，计算奖金的时候应该隔离新员工的INSERT，请刚入职的人不要恨我），这时候要强调隔离性(绝不能乱！),并发慢一点我们无所谓（反正有时间等，大家也理解，当然也不能太慢了）。
世界就是如此，不存在又灵活又能保持一致的数据库解决方案 （可能我眼界窄，也许已经有了），即不存在： 超人+蝙蝠侠 这样的完美个体存在。于是，我们需要在超人(灵活性)和蝙蝠侠(隔离性)之间做出选择。
我按照自己的理解来说明，这些隔离级别存在的问题。
先建立原料,创建一个Testtb
CREATE TABLE TESTTB ( ID INT, NAME VARCHAR(32) ) |
未提交读的问题?
?
所谓未提交读，可以这么简单理解：查询语句不发出锁。这是个与世无争的存在，速度也是最快的。
BEGIN TRAN SELECT * FROM TESTTB WITH(NOLOCK) COMMIT |
?
但是，他存在一个问题(快是有代价的)，我们用2个并发查询（SQL SERVER 新建2个查询，事务1先执行）
--脏读事务1 BEGIN TRAN INSERT INTO TESTTB VALUES(1,‘EFG‘) --这个时候运行事务2 WAITFOR DELAY ‘00:00:10‘ INSERT INTO TESTTB VALUES(1,‘EFG‘) ROLLBACK | --事务2 BEGIN TRAN SELECT * FROM TESTTB WITH(NOLOCK) COMMIT |
?
请注意这个ROLLBACK ，事务1 是回滚的。你猜事务2读到了什么，事务最后回滚的，他居然读出了一个数据，这种情况就叫脏读
（可以这么理解，你想在微信上吐槽，已经点了发送，但突然想到可能得罪某人，连忙点了取消；刚好此时某个家伙刷一下微信，居然读出那条吐槽。就这么个感觉）
前面说了，对于微信这样的要求高并发的系统，未提交读是无所谓的。但是作为企业应用你可能想解决这个问题。
?
?
?
?
我们做下面这个测试
--事务1 BEGIN TRAN INSERT INTO TESTTB VALUES(1,‘EFG‘) --这个时候运行事务2 WAITFOR DELAY ‘00:00:10‘ INSERT INTO TESTTB VALUES(1,‘EFG‘) ROLLBACK | --事务2 BEGIN TRAN SELECT * FROM TESTTB COMMIT |
?
我们的事务2的结果是怎么样的呢？
我们发现有趣的事情
- 事务2被阻塞了
- 结果是什么也没有
问题的关键就在这个阻塞上面(没错，如果你工作中碰到用户说现场阻塞了，就是这么回事)。SQL SERVER数据库使用了 已提交读 作为隔离级别的时候。会发出一个共享锁
（S锁，oracle ，mysql，sybase 我不知道，但估计是一个意思：一份文件正在被写的时候，其他人不能读。）
也就是说，这个SELECT 正在等待 INSERT 事务提交。 INSERT 完了，它才执行
（如果你有 windows 核心编程背景，就理解为INSERT 产生了一个信号量，SELECT 之前 要用WaitForSIngleObject 判断有无信号）
使用已提交读，就解决了这个脏读的问题。SQL SERVER 默认用这个隔离级别。所以，你没有看到WITH (xxx) 这样的锁\隔离级别声明。
?
?
已提交读的问题
已提交读能在大多数情况下工作良好，但是，他也有郁闷的缺陷。
我们先往Testtb中插入一条数据
INSERT INTO TESTTB VALUES(1,‘EFG‘) |
?
让我们来看下面的并发事务
--事务1 BEGIN TRAN SELECT NAME FROM TESTTB WHERE ID = 1 WAITFOR DELAY ‘00:00:10‘ --阻塞之后执行事务2 SELECT NAME FROM TESTTB WHERE ID = 1 COMMIT | --事务2 BEGIN TRAN UPDATE TESTTB SET NAME = ‘变！‘ WHERE ID = 1 COMMIT |
?
事务1执行结果是怎样的？
什么情况？事务1，读到2次不同的值。这就出现了前后不一致的情况。这就是不可重复读。
这种感觉就是：你通过淘宝买了iphone6 ，商家给你发了个iphone6 (事务开始了)，中间某个环节出了差错。你收到货了(事务结束)，打开一看，是iphon4。就这种感觉
?
在企业应用中，前后状态不一致，可能是不允许的。
那么我们看看如何解决
--事务1 BEGIN TRAN SELECT NAME FROM TESTTB WITH(REPEATABLEREAD) WHERE ID =1 --阻塞之后执行事务2 WAITFOR DELAY ‘00:00:10‘ SELECT NAME FROM TESTTB WITH(REPEATABLEREAD) WHERE ID =1 COMMIT | --事务2 BEGIN TRAN UPDATE TESTTB SET NAME = ‘给老子变！‘ WHERE ID = 1 COMMIT |
?
看看事务1的输出结果
而且执行事务2的时候，他一直在阻塞，直到事务1提交，才运行。
这个阻塞，一样的，就好读比一个文档，我加了一条规矩（隔离级别）：哥们在读的时候，旁的人都给我等着！于是事务2就老老实实的等着。
对于前面 已提交读就没这个功能，他的规矩是：别人在写的时候，我等着。
而未提交读的规矩是啥呢？: 写啥呢？我瞅瞅! 没写完不要紧的，我就拍个照，绝不浪费您的时间!
?
就这样，通过WITH(REPEATABLEREAD) 隔离级别提示，我们解决了不可重复读的问题。
?
可重复读的问题
很完美了 对吗？ 有了可重复读 隔离级别，我们能保持前后数据一致。
现在我们来修改需求，我们要求计算表中记录数量。我们看下面的代码
--事务1 BEGIN TRAN SELECT COUNT(1) FROM TESTTB WITH(REPEATABLEREAD) WAITFOR DELAY ‘00:00:10‘ --阻塞之后执行事务 SELECT COUNT(1) FROM TESTTB WITH(REPEATABLEREAD) COMMIT | --事务2 BEGIN TRAN INSERT INTO TESTTB VALUES (2,‘A‘) INSERT INTO TESTTB VALUES (3,‘B‘) INSERT INTO TESTTB VALUES (4,‘C‘) COMMIT |
至今为止，我们数据库里面只有一条记录对吗？
我们看看事务1输出结果
?
即使我们增加了REPEATABLEREAD 关键字，但是得到结果前后数量不一致。这就是幻读
运用你的直觉(我认为这是一种天赋)！你感觉到了 REPEATABLEREAD
好像没有阻塞INSERT ,但在上一个例子中，他成功的阻塞了UPDATE.
why?
我是这么理解的，UPDATE 和 DELETE 有一个共同点是，对一个已经存在的记录进行操作。也就是说能被SELECT 到。因此可以加锁。但是INSERT在执行的时候数据是不存在的。你无法给记录加锁！所以，你无法阻塞任何INSERT语句。虽然INSERT 语句也发出一个排它锁，但是数据产生之前，你只能尝试阻塞这空气一般的存在。因为，你不知道它在哪儿。
?
解决幻读
?
SQL 在4个隔离级别中，最高级隔离别就是Serializable （可序列化），老实说我真不理解这个名字。他和JAVA /C# 中的 序列化好像完全没关系。根本就无法通过名字理解这个隔离级别的意思。因此，我私下给他重新取了个名"不可新增读"。 顾名思义：在SELECT执行的时候，不可INSERT.
来，我们修改我们的隔离级别。
--事务1 BEGIN TRAN SELECT COUNT(1) FROM TESTTB WITH(SERIALIZABLE) WAITFOR DELAY ‘00:00:10‘ --阻塞之后执行事务 SELECT COUNT(1) FROM TESTTB WITH(SERIALIZABLE) COMMIT | --事务2 BEGIN TRAN INSERT INTO TESTTB VALUES (5,‘AA‘) INSERT INTO TESTTB VALUES (6,‘BB‘) INSERT INTO TESTTB VALUES (7,‘CC‘) COMMIT |
?
再次运行你的直觉！ 上一次INSERT 之后，我们有4条数据了！ 这次依次执行执行事务1，2之后，我们得到结果是多少呢？
我认为是2条都是4.
果然，WITH(SERIALIZABLE) 隔离级别解决了幻读的问题。保证了前后读取一致。这个隔离级别是最严格的。他连INSERT 都给阻塞了（它是怎么做到的，原理是什么？参考 《SQL SERVER 2008 查询性能优化》P340 页 老实说它也解释的不够详细，(没有证实，纯属猜测)我是这么认为的， SQL SERVER 不是有一个自动增长设置吗？ 像不像C++中的vector 的 capacity 属性？ 预分配一些空间，这样不用等到insert 的时候才去分配空间，那么我分配给这个表的预留空间 是不是也有ID? 即有许多没有用的空间的集合？ 当我指定了SERIALIZABLE 隔离级别时，我用一个 信号量把 这块集合个阻塞起来！）
总结
上面的例子分析了 脏读，不可重复度，幻读 。
分别解释了 他们是什么，以及简单的原因。并用代码做出了演示。
事实上，这些隔离级别是层层递进的。下面这幅图来源于《企业应用架构模式》P 52
SQL SERVER 2008 数据库隔离级别代码演示