![cd9d62fd4b4a2607e6aed92746e3ab5a.png](https://i-blog.csdnimg.cn/blog_migrate/9721542ed17ac819865899d11d0b05d7.jpeg)
【数据库技术】| 作者 / Edison Zhou
这是恰童鞋骚年的第179篇原创文章
经过了一个“被”延长的春节假期,恰童鞋骚年正式地又和你每天见面了,从今天开始正式恢复更新技术推文和读书推文,保持关注哟!
年前发布的上一篇介绍了事务的基本概念、特性、锁和阻塞,本篇文章会介绍在MS SQL Server中事务的六个隔离级别(面试常考点,值得关注)中的前三个:未提交读、已提交读 和 可重复读。
1隔离级别快速介绍
隔离级别用于决定如何控制并发用户读写数据的操作。上一篇中说到,读操作默认使用共享锁,写操作需要使用排它锁。对于操作获得的锁,以及锁的持续时间来说,虽然不能控制写操作的处理方式,但可以控制读操作的处理方式。作为对读操作的行为进行控制的一种结果,也会隐含地影响写操作的行为方式。
为此,可以在会话级别上用会话选项来设置隔离级别,也可以在查询级别上用表提示(Table Hint)来设置隔离级别。
在MS SQL Server中,可以设置的隔离级别有6个:READ UNCOMMITED(未提交读)、READ COMMITED(已提交读)、REPEATABLE READ(可重复读)、SERIALIZEABLE(可序列化)、SNAPSHOT(快照)和READ COMMITED SNAPSHOT(已经提交读隔离)。最后两个SNAPSHOT和READ COMMITED SNAPSHOT是在MS SQL Server 2005中引入的。
要设置整个会话级别的隔离级别,可以使用以下语句:
SET TRANSACTION ISOLATION LEVEL ;
也可以使用表提示来设置查询级别的隔离级别:
SELECT ... FROM
WITH ;
2READ UNCOMMITED 未提交读
未提交读是最低的隔离级别,读操作不会请求共享锁。换句话说,在该级别下的读操作正在读取数据时,写操作可以同时对这些数据进行修改。
同样,使用两个会话来模拟:
Step1.在Connection A中运行以下代码,更新产品2的单价,为当前值(19.00)增加1.00,然后查询该产品:
-- Connection ABEGIN TRAN;UPDATE Production.ProductsSET unitprice = unitprice + 1.00WHERE productid = 2;SELECT productid, unitpriceFROM Production.ProductsWHERE productid = 2;
![845ce6e04ad6283b765cb0d5d9e8ba31.png](https://i-blog.csdnimg.cn/blog_migrate/8695304d501e60700d0c3f90de2f85a2.jpeg)
Step2.在Connection B中运行以下代码,首先设置隔离级别为未提交读,再查询产品2所在的记录:
-- Connection BSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SELECT productid, unitpriceFROM Production.ProductsWHERE productid = 2;
因为这个读操作不用请求共享锁,因此不会和其他事务发生冲突,该查询返回了如下图所示的修改后的状态,即使这一状态还没有被提交:
![845ce6e04ad6283b765cb0d5d9e8ba31.png](https://i-blog.csdnimg.cn/blog_migrate/8695304d501e60700d0c3f90de2f85a2.jpeg)
Step3.在Connection A中运行以下代码回滚事务:
ROLLBACK TRAN;
这个回滚操作撤销了对产品2的更新,这时它的价格被修改回了19.00,但是读操作此前获得的20.00再也不会被提交了。这就是脏读的一个实例!
![3d6da3f13ceee552df656f4e304c683a.png](https://i-blog.csdnimg.cn/blog_migrate/ff85f1593aea313629d529f0b27f674f.jpeg)
3READ COMMITED 已提交读
刚刚说到,未提交到会引起脏读,能够防止脏读的最低隔离级别是已提交读,这也是所有SQL Server版本默认使用的隔离级别。如其名称所示,这个隔离级别只允许读取已经提交的修改,它要求读操作必须获得共享锁才能操作,从而防止读取未提交的修改。
继续使用两个会话来模拟:
Step1.在Connection A中运行以下代码,更新产品2的价格,再查询显示价格:
BEGIN TRAN;UPDATE Production.ProductsSET unitprice = unitprice + 1.00WHERE productid = 2;SELECT productid, unitpriceFROM Production.ProductsWHERE productid = 2;
![845ce6e04ad6283b765cb0d5d9e8ba31.png](https://i-blog.csdnimg.cn/blog_migrate/8695304d501e60700d0c3f90de2f85a2.jpeg)
Step2.再在Connection B中运行以下代码,这段代码将会话的隔离级别设置为已提交读,再查询产品2所在的行记录:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT productid, unitpriceFROM Production.ProductsWHERE productid = 2;
这时该会话语句会被阻塞,因为它需要获取共享锁才能进行读操作,而它与会话A的写操作持有的排它锁相冲突。这里因为我设置了默认会话阻塞超时时间,所以出现了以下输出:
![fd42a2452a028f395b6bcdf8ca1e52bc.png](https://i-blog.csdnimg.cn/blog_migrate/3bd0a3342c987dbcee5a2eeb7424698c.jpeg)
Step3.在Connection A中运行以下代码,提交事务:
COMMIT TRAN;
Step4.回到Connection B,此时会得到以下输出:
![845ce6e04ad6283b765cb0d5d9e8ba31.png](https://i-blog.csdnimg.cn/blog_migrate/8695304d501e60700d0c3f90de2f85a2.jpeg)
在已提交读级别下,不会读取脏数据,只能读取已经提交过的修改。但是,该级别下,其他事务可以在两个读操作之间更改数据资源,读操作因而可能每次得到不同的取值。这种现象被称为 不可重复读。
4REPEATABLE READ 可重复读
如果想保证在事务内进行的两个读操作之间,其他任何事务都不能修改由当前事务读取的数据,则需要将隔离级别升级为可重复读。在该级别下,十五中的读操作不但需要获得共享锁才能读数据,而且获得的共享锁将一直保持到事务完成为止。换句话说,在事务完成之前,没有其他事务能够获得排它锁以修改这一数据资源,由此来保证实现可重复的读取。
Step1.为了重新演示可重复读的示例,首先需要将刚刚的测试数据清理掉,在Connection A和B中执行以下代码:
-- Clear Test DataUPDATE Production.ProductsSET unitprice = 19.00WHERE productid = 2;
Step2.在Connection A中运行以下代码,将会话的隔离级别设置为可重复读,再查询产品2所在的行记录:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;BEGIN TRAN; SELECT productid, unitprice FROM Production.Products WHERE productid = 2;
![3d6da3f13ceee552df656f4e304c683a.png](https://i-blog.csdnimg.cn/blog_migrate/ff85f1593aea313629d529f0b27f674f.jpeg)
这时该会话仍然持有产品2上的共享锁,因为在该隔离级别下,共享锁要一直保持到事务结束为止。
Step3.在Connection B中尝试对产品2这一行进行修改:
UPDATE Production.Products SET unitprice = unitprice + 1.00WHERE productid = 2;
这时该会话已被阻塞,因为修改操作锁请求的排它锁与前面会话授予的共享锁有冲突。换句话说,如果读操作是在未提交读或已提交读级别下运行的,那么事务此时将不再持有共享锁,Connection B尝试修改改行的操作应该能够成功。
同样,由于我设置了超时释放时间,因此会有以下输出:
![78952af1170a1bc7a12975e303d1de74.png](https://i-blog.csdnimg.cn/blog_migrate/aa9083d48dcc9d2e8aa52ec4c88baacb.jpeg)
Step4.回到Connection A,运行以下代码,再次查询茶品2所在的行,提交事务:
SELECT productid, unitprice FROM Production.Products WHERE productid = 2;COMMIT TRAN;
这时的返回结果仍然与第一次相同:
![3d6da3f13ceee552df656f4e304c683a.png](https://i-blog.csdnimg.cn/blog_migrate/ff85f1593aea313629d529f0b27f674f.jpeg)
Step5.这时再执行Connection B中的更新语句,便能够正常获得排它锁了,于是执行成功,价格变为了20.00。
优缺点一览
可重复读隔离级别不仅可以防止不可重复读,另外还能防止丢失更新。丢失更新是指两个事务读取了同一个值,然后基于最初读取的值进行计算,接着再更新该值,就会发生丢失更新的问题。这是因为在可重复读隔离级别下,两个事务在第一次读操作之后都保留有共享锁,所以其中一个都不能成功获得为了更新数据而需要的排它锁。但是,负面影响就是会导致死锁。
在可重复读级别下运行的事务,读操作获得的共享锁将一直保持到事务结束。因此可以保证在事务中第一次读取某些行后,还可以重复读取这些行。但是,事务只锁定查询第一次运行时找到的那些行,而不会锁定查询结果范围外的其他行。因此,在同一事务进行第二次读取之前,如果其他事务插入了新行,而且新行也能满足读操作额查询过滤条件,那么这些新行也会出现在第二次读操作返回的结果中。这些新行称之为幻影,这种读操作也被称为幻读。那么,如何避免幻读呢?请看下一篇文章,哈哈。
5小结
本文介绍了隔离级别的基本概念及MS SQL Server中的六种隔离级别,然后重点介绍了前三种隔离级别的出现场景及影响,分别是:未提交读、已提交读 和 可重复读。下一篇,我们将会学习可序列化、快照 和 已提交读隔离,最后还会对六种隔离级别做一个总结。
参考资料
![3435fc472f5c62561dc7e771cceb2c5a.png](https://i-blog.csdnimg.cn/blog_migrate/9bc5907efc321144830c3c7700257198.jpeg)
[美] Itzik Ben-Gan 著,成保栋 译,《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》