事务隔离级别

SQL Server通过在锁资源上使用不同类型的锁来隔离事务。为了开发安全的事务，定义事务内容以及应在何种情况下回滚至关重要，定义如何以及在多长时间内在事务中保持锁定也同等重要。这由隔离级别决定。应用不同的隔离级别，SQL Server赋予开发者一种能力，让他们为每一个单独事务定义与其他事务的隔离程度。事务隔离级别的定义如下：

• 是否在读数据的时候使用锁
• 读锁持续多长时间
• 在读数据的时候使用何种类型的锁

• 读操作希望读已经被其他事务排他锁住的数据时，怎么办？在这种情况下，SQL Server可以：
  • 一直等到其他事务释放锁
  • 读没有提交的数据
  • 读数据最后提交后的版本

ANSI 99定义了4种事务隔离级别，SQL Server 2005能够完全支持这些级别：

• 未提交读 在读数据时不会检查或使用任何锁。因此，在这种隔离级别中可能读取到没有提交的数据。
• 已提交读 只读取提交的数据并等待其他事务释放排他锁。读数据的共享锁在读操作完成后立即释放。已提交读是SQL Server的默认隔离级别。
• 可重复读 像已提交读级别那样读数据，但会保持共享锁直到事务结束。
• 可序列化 工作方式类似于可重复读。但它不仅会锁定受影响的数据，还会锁定这个范围。这就阻止了新数据插入查询所涉及的范围，这种情况可以导致幻像读。

此外，SQL Server还有两种使用行版本控制来读取数据的事务级别(本章后文将详细检验这些隔离级别)。行版本控制允许一个事务在数据排他锁定后读取数据的最后提交版本。由于不必等待到锁释放就可进行读操作，因此查询性能得以大大增强。这两种隔离级别如下：

• 已提交读快照 它是一种提交读级别的新实现。不像一般的提交读级别，SQL Server会读取最后提交的版本并因此不必在进行读操作时等待直到锁被释放。这个级别可以替代提交读级别。
• 快照 这种隔离使用行版本来提供事务级别的读取一致性。这意味着在一个事务中，由于读一致性可以通过行版本控制实现，因此同样的数据总是可以像在可序列化级别上一样被读取而不必为防止来自其他事务的更改而被锁定。

 

无论定义什么隔离级别，对数据的更改总是通过排他锁来锁定并直到事务结束时才释放。

很多情况下，定义正确的隔离级别并不是一个简单的决定。作为一种通用的规则，要选择在尽可能短的时间内锁住最少数据，但同时依然可以为事务提供它所需的安全程度的隔离级别

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  --未提交读
SET TRANSACTION ISOLATION LEVEL READ COMMITTED    --已提交读
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ   --获取一致的可重复读操作
SET TRANSACTION ISOLATION LEVEL SNAPSHOT          --已提交读快照级
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE      --系列化级别

(一)、参数SNAPSHOT的含义：

1.      指定事务中任何语句读取的数据都将是在事务开始时便存在的数据的事务上一致的版本。事务只能识别在其开始之前提交的数据修改。在当前事务中执行的语句将看不到在当前事务开始以后由其他事务所做的数据修改。其效果就好像事务中的语句获得了已提交数据的快照，因为该数据在事务开始时就存在。

2.      除非正在恢复数据库，否则 SNAPSHOT事务不会在读取数据时请求锁。读取数据的 SNAPSHOT事务不会阻止其他事务写入数据。写入数据的事务也不会阻止 SNAPSHOT事务读取数据。

3.      在数据库恢复的回滚阶段，如果尝试读取由其他正在回滚的事务锁定的数据，则 SNAPSHOT事务将请求一个锁。在事务完成回滚之前，SNAPSHOT事务会一直被阻塞。当事务取得授权之后，便会立即释放锁。

4.      必须将 ALLOW_SNAPSHOT_ISOLATION数据库选项设置为 ON，才能开始一个使用 SNAPSHOT隔离级别的事务。如果使用 SNAPSHOT隔离级别的事务访问多个数据库中的数据，则必须在每个数据库中将 ALLOW_SNAPSHOT_ISOLATION都设置为 ON。

5.      不能将通过其他隔离级别开始的事务设置为 SNAPSHOT隔离级别，否则将导致事务中止。如果一个事务在 SNAPSHOT隔离级别开始，则可以将它更改为另一个隔离级别，然后再返回 SNAPSHOT。一个事务从执行 BEGIN TRANSACTION 语句开始。

6.      在 SNAPSHOT隔离级别下运行的事务可以查看由该事务所做的更改。例如，如果事务对表执行 UPDATE，然后对同一个表发出 SELECT语句，则修改后的数据将包含在结果集中。

 

 (二)、参数REPEATABLE READ的含义：

1.  指定语句不能读取已由其他事务修改但尚未提交的行，并且指定，其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据。

2.  对事务中的每个语句所读取的全部数据都设置了共享锁，并且该共享锁一直保持到事务完成为止。这样可以防止其他事务修改当前事务读取的任何行。其他事务可以插入与当前事务所发出语句的搜索条件相匹配的新行。如果当前事务随后重试执行该语句，它会检索新行，从而产生幻读。由于共享锁一直保持到事务结束，而不是在每个语句结束时释放，所以并发级别低于默认的 READ COMMITTED 隔离级别。此选项只在必要时使用。

 

(三) 、READ UNCOMMITTED

指定语句可以读取已由其他事务修改但尚未提交的行。

在 READ UNCOMMITTED级别运行的事务，不会发出共享锁来防止其他事务修改当前事务读取的数据。READ UNCOMMITTED事务也不会被排他锁阻塞，排他锁会禁止当前事务读取其他事务已修改但尚未提交的行。设置此选项之后，可以读取未提交的修改，这种读取称为脏读。在事务结束之前，可以更改数据中的值，行也可以出现在数据集中或从数据集中消失。该选项的作用与在事务内所有 SELECT语句中的所有表上设置 NOLOCK相同。这是隔离级别中限制最少的级别。

在 SQL Server 2005中，您还可以使用下列任意一种方法，在保护事务不脏读未提交的数据修改的同时尽量减少锁定争用：

1.  READ COMMITTED隔离级别，并将 READ_COMMITTED_SNAPSHOT数据库选项设置为 ON。

2.  SNAPSHOT隔离级别。

 

（四）、READ COMMITTED

指定语句不能读取已由其他事务修改但尚未提交的数据。这样可以避免脏读。其他事务可以在当前事务的各个语句之间更改数据，从而产生不可重复读取和幻像数据。该选项是 SQL Server的默认设置。

READ COMMITTED 的行为取决于 READ_COMMITTED_SNAPSHOT数据库选项的设置：

1.  如果将 READ_COMMITTED_SNAPSHOT设置为 OFF（默认设置），则数据库引擎会使用共享锁防止其他事务在当前事务执行读取操作期间修改行。共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。语句完成后便会释放共享锁。

2.  如果将 READ_COMMITTED_SNAPSHOT设置为 ON，则数据库引擎会使用行版本控制为每个语句提供一个在事务上一致的数据快照，因为该数据在语句开始时就存在。不使用锁来防止其他事务更新数据。

当 READ_COMMITTED_SNAPSHOT数据库选项设置为 ON时，您可以使用 READCOMMITTEDLOCK表提示为 READ_COMMITTED隔离级别上运行的事务中的各语句请求共享锁，而不是行版本控制。

    注意：设置 READ_COMMITTED_SNAPSHOT选项时，数据库中仅允许存在执行 ALTER DATABASE命令的连接。在 ALTER DATABASE完成之前，数据库中不允许有其他打开的连接。数据库不必处于单用户模式。

 

(五)、SERIALIZABLE

请指定下列内容：

1.  语句不能读取已由其他事务修改但尚未提交的数据。

2.  任何其他事务都不能在当前事务完成之前修改由当前事务读取的数据。

3.  在当前事务完成之前，其他事务不能使用当前事务中任何语句读取的键值插入新行。

范围锁处于与事务中执行的每个语句的搜索条件相匹配的键值范围之内。这样可以阻止其他事务更新或插入任何行，从而限定当前事务所执行的任何语句。这意味着如果再次执行事务中的任何语句，则这些语句便会读取同一组行。在事务完成之前将一直保持范围锁。这是限制最多的隔离级别，因为它锁定了键的整个范围，并在事务完成之前一直保持范围锁。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK相同。

 

 

需要注意的地方：

1.  一次只能设置一个隔离级别选项，而且设置的选项将一直对那个连接始终有效，直到显式更改该选项为止。事务中执行的所有读取操作都会在指定的隔离级别的规则下运行，除非语句的 FROM子句中的表提示为表指定了其他锁定行为或版本控制行为。

2.  事务隔离级别定义了可为读取操作获取的锁类型。针对 READ COMMITTED或 REPEATABLE READ获取的共享锁通常为行锁，尽管当读取引用了页或表中大量的行时，行锁可以升级为页锁或表锁。如果某行在被读取之后由事务进行了修改，则该事务会获取一个用于保护该行的排他锁，并且该排他锁在事务完成之前将一直保持。例如，如果 REPEATABLE READ事务具有用于某行的共享锁，并且该事务随后修改了该行，则共享行锁便会转换为排他行锁。

3.  在事务进行期间，可以随时将事务从一个隔离级别切换到另一个隔离级别，但有一种情况例外。即在从任一隔离级别更改到 SNAPSHOT隔离时，不能进行上述操作。否则会导致事务失败并回滚。但是，可以将在 SNAPSHOT隔离中启动的事务更改为任何其他隔离级别。

4.  将事务从一个隔离级别更改为另一个隔离级别之后，便会根据新级别的规则对更改后读取的资源执行保护。在更改前读取的资源将继续按照以前级别的规则受到保护。例如，如果某个事务从 READ COMMITTED更改为 SERIALIZABLE，则在该事务结束前，更改后所获取的共享锁将一直处于保留状态。

5.  如果在存储过程或触发器中发出 SET TRANSACTION ISOLATION LEVEL，则当对象返回控制时，隔离级别会重设为在调用对象时有效的级别。例如，如果在批处理中设置 REPEATABLE READ，并且该批处理调用一个将隔离级别设置为 SERIALIZABLE的存储过程，则当该存储过程将控制返回给该批处理时，隔离级别就会恢复为 REPEATABLE READ。