关于SQL Server事务隔离级别的探讨

摘 要： 隔离级别用于决定如何控制并发用户读写数据的操作，读操作可以是任何检索数据的语句，默认使用共享锁。写操作是指任何对表作出修改的语句，需要使用排他锁。对于操作获得的锁，以及锁的持续时间而言，虽然不能控制写操作的处理方式，但可以控制读操作的处理方式。当然，作为对读操作的行为进行控制的一种结果，也将隐含地影响写操作的行为方式。为此，可以在会话级别上用会话选项来设置隔离级别。

关键词： 并发； 隔离级别； 排他锁； 共享锁

中图分类号：TP311 文献标志码：A 文章编号：1006-8228（2015）12-38-03

Discussion on SQL server transaction isolation level

Liu Lijuan

（Zhejiang Agriculture and Forestry University， Lin'an， Zhejiang 311300， China）

Abstract： The isolation level is used to determine how to control the operation of concurrent users read and write data， the read operations can be a statement of any retrieved data， the default use of shared lock. The write operations can be a statement to update data， need to use the exclusive lock. For the lock and lock time， the processing mode of read can be controlled， although the processing mode of write can't be controlled. Of course， as a result of controlling the action of the read operation， the action of the write operation is also implicitly affected. So， the isolation level can be set at the session level with session options.

Key words： concurrency； isolation level； exclusive lock； shared locks

0 引言

并發控制是事务处理的一部分，就像交通信号灯一样，确保在多用户访问共享的数据库时，不会“碰撞”到其他用户。每个时刻只允许同一用户来访问数据，是避免所有问题的方法之一，但这个方案的最大问题是会延长其他用户的相应时间[2，5]。

根据SQL Server自身情况来定义事务的隔离级别，将会更好的满足需求，减少死锁和阻塞，提升性能和响应[4]。

1 基本概念

SQL Server可以设置的隔离级别有6个：read uncommitted（未提交读）、read committed（已提交读＼默认值）、repeatable read（可重复读）、serializable（可序列化）、snapshot（快照）和read committed snapshot（已经提交度隔离）。 最后两个级别是在SQL Server2005中引入的，可以把事务已经提交的上一个版本保存在tempdb数据库中，以这种行版本控制为基础。snapshot隔离级别在逻辑上和serializable隔离级别类似，read committed snapshot隔离级别和read committed隔离级别类似，它们能解决和不能解决的不一致问题是一样的。但是在基于快照的隔离级别下，读操作不需要使用共享锁，所以即使请求的数据被其他事务以排他锁锁定，读操作也不会等待。但是，无论使用哪种基于快照的隔离级别，都会对数据更新和删除操作的性能产生负面影响。因此，本文不对后两种隔离级别作深入探讨。设置整个会话的隔离级别时，可以使用以下命令：

set transaction isolation level

2 READ UNCOMMITTED未提交读

read uncommitted是最低的隔离级别。在这个隔离级别运行的事务，读操作不会请求共享锁。如果读操作不请求共享锁，就不会和持有排他锁的写操作发生冲突。这意味着读操作可以读取未提交的修改（也称为脏读）；同时也意味着读操作不会妨碍写操作请求排他锁。也就是说，当运行在read uncommitted隔离级别下的读操作正在读取数据时，写操作可以同时对这些数据进行修改。下面演示什么是未提交读（脏读，dirty read），打开两个查询窗口connection1和connection2：在connection1，运行以下代码，打开一个事务，更新学号为'201401010101'，课程号为'B1103011'的成绩（62分），增加8分（70分），然后查询该成绩。

begin tran

update sc set score=score+8 where snum=

'201401010101' and cnum='B1103011'

select * from sc where snum='201401010101'

and cnum='B1103011'

在以上代码中，事务保持打开，这意味着这一行被排他锁锁定。connection1中的代码会返回以下输出结果：

snum cnum score

----------- ------ -----

201401010101 B1103011 70

在connetion2中，运行以下代码，将会话的隔离级别设置为read uncommitted。

set transaction isolation level read uncommitted

select * from sc where snum='201401010101'

and cnum='B1103011'

因为这个读操作不用申请共享锁，所以它不会和其他事务发生冲突。该查询返回所在行修改后的状态，即使这一修改还没有被提交。

snum cnum score

----------- ------ -----

201401010101 B1103011 70

再在connection1中运行以下代码回滚事务：

rollback tran

撤销对成绩的更新，脏读产生了。

3 READ COMMITTED已提交读

能够防止脏读的最低隔离级别是read committed， 这也是SQL Server默认使用的隔离级别。这个隔离级别只允许读取已经提交过的修改。它要求读操作必须获得共享锁才能操作，从而防止读取未提交过的修改。这意味着，如果写操作持有排他锁，读操作提出的共享锁请求就会和写操作发生冲突，所以读操作不得不等待。一旦写操作提交了事务，读操作才能获得它请求的共享锁，而这时读到的只能是修改提交过的数据。

下面演示在read committed隔离级别下，读操作只能读取修改提交过的数据。

在connection1中运行以下代码，以排他锁锁定了数据行。

begin tran

update sc set score=score+8 where snum=

'201401010101' and cnum='B1103011'

select * from sc where snum='201401010101'

and cnum='B1103011'

這段代码将返回以下输出结果：

snum cnum score

----------- ------ -----

201401010101 B1103011 70

在connection2中运行以下代码，将会话的隔离级别设置为read committed，再查询：

set transaction isolation level read committed

select * from sc where snum='201401010101'

and cnum='B1103011'

read committed是SQL Server默认的隔离级别，不需要显示地设置该隔离级别。执行该查询时，SELECT语句会被阻塞，因为这时它需要获得共享锁才能进行读操作，而该共享锁请求与connection1中写操作的排他锁相冲突。接下来，在connection1中运行以下代码，即提交代码。

commit tran

按照锁的持有时间来说，在read committed隔离级别中，读操作一完成，就立即释放资源上的共享锁。若其他事务在两个读操作之间更改数据资源，会产生不可重复读。

清理数据，将成绩改回62。

4 REPEATABLE READ

如果想保证在事务内部进行的两个读操作之间，其他任何事务都不能修改由当前事务读取的数据，则需要把隔离级别升级为read repeatable。在这种隔离级别下，事务中的读操作不但需要获得共享锁才能读数据，而且该锁会一直保持到事务完成为止。这样就可以保证实现可重复的读取。

在connection1中运行以下代码，将会话级别设为repeatable read。

set transaction isolation level repeatable read

begin tran

select * from sc where snum='201401010101'

and cnum='B1103011'

这段代码将返回以下输出结果。

snum cnum score

----------- ------ -----

201401010101 B1103011 70

connection1这时仍然持有共享锁，在connection2中运行以下代码，尝试对这一行进行修改。

update sc set score=score+8 where snum=

'201401010101' and cnum='B1103011'

SQL Server会阻塞这一修改，因为修改操作请求的排他锁与前面读操作授予的共享锁冲突。

再回到connection1，运行以下代码，再次查询，并提交事务。

select * from sc where snum='201401010101'

and cnum='B1103011'

commit tran

前后两次的读取结果相同。现在读操作的事务已经提交了，共享锁也释放了，所以connection2中的修改操作就能获得它正等待的排他锁，进行更新。

清理数据，將成绩改回62。

repeatable read隔离级别能够防止的另一种并发负面影响是丢失更新，而较低的隔离级别不能防止这种问题。在repeatable read 隔离级别下，一个事务在第一次读数据操作之后都保留它们获得的共享锁，所以任何一个事务都不能为了更新数据而需要排他锁。这种情况最终会导致死锁，不过避免了更新冲突。

5 SERIALIZABLE可序列化

在repeatable read隔离操作级别下运行的事务，读操作获得的共享锁一直保持到事务完成为止。但是，事务只锁定查询第一次运行时找到的那些数据资源（例如，行），而不会锁定查询结果范围以外的其他行。因此，在同一事务中进行第二次读取之前，如果其他事务插入了新行，而且新行也能满足读操作的查询过滤条件，那么这些新行也会出现在第二次读操作返回的结果中。这些新行称为幻影，这种读操作也叫做幻读。

为了避免幻读，需要将隔离级别设置为更高级的serializable。大多数时候，serializable隔离级别的处理方式和repeatable read类似，不过，serializable隔离级别增加了一个新内容——逻辑上，这个隔离级别会让读操作锁定满足查询搜索条件的键的整个范围。这就意味着读操作不仅锁定了满足查询条件的现有的那些行，还锁定了未来可能满足查询条件的行。更准确地说，如果其他事务试图增加能够满足读操作的查询条件的新行，当前事务就会阻塞这样的操作。

以下例子演示如何用serializable隔离级别来避免幻读。

connection1：

set transaction isolation level serializable

begin tran

select * from sc where snum='201401010101'

可以得到21行输出结果。

在connection2中运行以下代码，尝试选一门新课。

insert into sc values（'201401010101'，'C01'，null）

在所有低于serializable隔离级别下，这样的插入操作将会成功。而在serializable隔离级别下，这样的操作将被阻塞。

再回到connection1，运行以下代码，并提交事务

*/select * from sc where snum='201401010101'

commit tran

得到的输出结果和前面的一样，没有幻影行。现在读操作事务已经提交了，共享锁的范围也随之释放，所以，connection2中的修改操作就获得了等候已久的排他锁，插入新行。

6 结束语

以下总结了每种隔离级别能否解决的各种一致性问题：

[隔离级别＼&脏读＼&不可重复读＼&丢失更新＼&幻读＼&Read uncommitted＼&是＼&是＼&是＼&是＼&Read committed＼&否＼&是＼&是＼&是＼&Repeatable read＼&否＼&否＼&否＼&是＼&serializable＼&否＼&否＼&否＼&否＼&]

较低的隔离级别可以增强许多用户同时访问数据的能力，但也增加了用户可能遇到的并发副作用的数量。相反，较高的隔离级别减少了用户可能遇到的并发副作用的类型，但需要更多的系统资源，并增加了一个事务阻塞其他事务的可能性。

应该平衡应用程序的数据完整性要求与每个隔离级别的开销，在此基础上选择相应的隔离级别。最高隔离级别（可序列化）保证事务在每次重复读取操作时都能准确检索到相同的数据，但需要通过执行某种级别的锁定来完成此操作，而锁定可能会影响多用户系统中的其他用户。最低隔离级别（未提交读）可以检索其他事务已经修改但未提交的数据。在未提交读中，所有并发副作用都可能发生，但因为没有读取锁定或版本控制，所以开销最少[4]。

参考文献（References）：

[1] 吴达胜，刘丽娟.《数据库原理与技术》的理论与实践教学的

整体优化研究[J].计算机时代，2005.11：31-32

[2] 王珊，萨师煊.数据库系统概论（第5版）[M].高等教育出版社，

2014.

[3] 刘丽娟，吴达胜.关于MySQL数据库中触发器的学习探讨[J].

计算机时代，2014，4：66-68.

[4] Itzik Ben-Gan. Microsoft SQL Server 2008技术内幕[M].电

子工业出版社，2009.

[5] Joe Celko.SQ权威指南（第4版）[M].人民邮电出版社，

2013.