调整事务隔离级别的方法

1、SQL Server 2005 联机丛书（2007 年 9 月）SET TRANSACTION ISOLATION LEVEL (Transact-SQL)语法 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED| READ COMMITTED| REPEATABLE READ| SNAPSHOT| SERIALIZABLE ; 参数READ UNCOMMITTED指定语句可以读取已由其他事务修改但尚未提交的行。在 READ UNCOMMITTED 级别运行的事务，不会发出共享锁来防止其他事务修改当前事务读取的数据。READ UNCOMMITTED

2、 事务也不会被排他锁阻塞，排他锁会禁止当前事务读取其他事务已修改但尚未提交的行。设置此选项之后，可以读取未提交的修改，这种读取称为脏读。在事务结束之前，可以更改数据中的值，行也可以出现在数据集中或从数据集中消失。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 NOLOCK 相同。这是隔离级别中限制最少的级别。在 SQL Server 2005 中，您还可以使用下列任意一种方法，在保护事务不脏读未提交的数据修改的同时尽量减少锁定争用： READ COMMITTED 隔离级别，并将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON。 SNAPSHOT 隔离级别

3、。READ COMMITTED指定语句不能读取已由其他事务修改但尚未提交的数据。这样可以避免脏读。其他事务可以在当前事务的各个语句之间更改数据，从而产生不可重复读取和幻像数据。该选项是 SQL Server 的默认设置。READ COMMITTED 的行为取决于 READ_COMMITTED_SNAPSHOT 数据库选项的设置： 如果将 READ_COMMITTED_SNAPSHOT 设置为 OFF（默认设置），则数据库引擎会使用共享锁防止其他事务在当前事务执行读取操作期间修改行。共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。语句完成后便会释放共享锁。 如果将 READ_COMM

4、ITTED_SNAPSHOT 设置为 ON，则数据库引擎会使用行版本控制为每个语句提供一个在事务上一致的数据快照，因为该数据在语句开始时就存在。不使用锁来防止其他事务更新数据。当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 时，您可以使用 READCOMMITTEDLOCK 表提示为 READ_COMMITTED 隔离级别上运行的事务中的各语句请求共享锁，而不是行版本控制。REPEATABLE READ指定语句不能读取已由其他事务修改但尚未提交的行，并且指定，其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据。对事务中的每个语句所读取的全部数据都设置了共享

5、锁，并且该共享锁一直保持到事务完成为止。这样可以防止其他事务修改当前事务读取的任何行。其他事务可以插入与当前事务所发出语句的搜索条件相匹配的新行。如果当前事务随后重试执行该语句，它会检索新行，从而产生幻读。由于共享锁一直保持到事务结束，而不是在每个语句结束时释放，所以并发级别低于默认的 READ COMMITTED 隔离级别。此选项只在必要时使用。SNAPSHOT指定事务中任何语句读取的数据都将是在事务开始时便存在的数据的事务上一致的版本。事务只能识别在其开始之前提交的数据修改。在当前事务中执行的语句将看不到在当前事务开始以后由其他事务所做的数据修改。其效果就好像事务中的语句获得了已提交数据的

6、快照，因为该数据在事务开始时就存在。 除非正在恢复数据库，否则 SNAPSHOT 事务不会在读取数据时请求锁。读取数据的 SNAPSHOT 事务不会阻止其他事务写入数据。写入数据的事务也不会阻止 SNAPSHOT 事务读取数据。在数据库恢复的回滚阶段，如果尝试读取由其他正在回滚的事务锁定的数据，则 SNAPSHOT 事务将请求一个锁。在事务完成回滚之前，SNAPSHOT 事务会一直被阻塞。当事务取得授权之后，便会立即释放锁。必须将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON，才能开始一个使用 SNAPSHOT 隔离级别的事务。如果使用 SNAPSHOT 隔离级别的

7、事务访问多个数据库中的数据，则必须在每个数据库中将 ALLOW_SNAPSHOT_ISOLATION 都设置为 ON。不能将通过其他隔离级别开始的事务设置为 SNAPSHOT 隔离级别，否则将导致事务中止。如果一个事务在 SNAPSHOT 隔离级别开始，则可以将它更改为另一个隔离级别，然后再返回 SNAPSHOT。事务在第一次访问数据时启动。在 SNAPSHOT 隔离级别下运行的事务可以查看由该事务所做的更改。例如，如果事务对表执行 UPDATE，然后对同一个表发出 SELECT 语句，则修改后的数据将包含在结果集中。SERIALIZABLE请指定下列内容： 语句不能读取已由其他事务修改但尚未

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

9、同。备注一次只能设置一个隔离级别选项，而且设置的选项将一直对那个连接始终有效，直到显式更改该选项为止。事务中执行的所有读取操作都会在指定的隔离级别的规则下运行，除非语句的 FROM 子句中的表提示为表指定了其他锁定行为或版本控制行为。事务隔离级别定义了可为读取操作获取的锁类型。针对 READ COMMITTED 或 REPEATABLE READ 获取的共享锁通常为行锁，尽管当读取引用了页或表中大量的行时，行锁可以升级为页锁或表锁。如果某行在被读取之后由事务进行了修改，则该事务会获取一个用于保护该行的排他锁，并且该排他锁在事务完成之前将一直保持。例如，如果 REPEATABLE READ 事务

10、具有用于某行的共享锁，并且该事务随后修改了该行，则共享行锁便会转换为排他行锁。在事务进行期间，可以随时将事务从一个隔离级别切换到另一个隔离级别，但有一种情况例外。即在从任一隔离级别更改到 SNAPSHOT 隔离时，不能进行上述操作。否则会导致事务失败并回滚。但是，可以将在 SNAPSHOT 隔离中启动的事务更改为任何其他隔离级别。将事务从一个隔离级别更改为另一个隔离级别之后，便会根据新级别的规则对更改后读取的资源执行保护。在更改前读取的资源将继续按照以前级别的规则受到保护。例如，如果某个事务从 READ COMMITTED 更改为 SERIALIZABLE，则在该事务结束前，更改后所获取的共享

11、锁将一直处于保留状态。如果在存储过程或触发器中发出 SET TRANSACTION ISOLATION LEVEL，则当对象返回控制时，隔离级别会重设为在调用对象时有效的级别。例如，如果在批处理中设置 REPEATABLE READ，并且该批处理调用一个将隔离级别设置为 SERIALIZABLE 的存储过程，则当该存储过程将控制返回给该批处理时，隔离级别就会恢复为 REPEATABLE READ。当您使用 sp_bindsession 绑定两个会话时，每个会话都会保留它自身的隔离级别设置。使用 SET不会影响与该会话绑定的其他任何会话TRANSACTION ISOLATION LEVEL 更改

12、某个会话的隔离级别设置时，的设置。SET TRANSACTION ISOLATION LEVEL 会在执行或运行时生效，而不是在分析时生效。针对堆的优化大容量负载操作阻塞了运行在以下隔离级别下面的查询： SNAPSHOT READ UNCOMMITTED 使用行版本控制的 READ COMMITTED相反，运行在这些隔离级别下面的查询阻塞了针对堆的优化大容量负载操作。有关大容量加载操作的详细信息，请参阅关于大容量导入和大容量导出操作和优化大容量导入性能。示例以下示例为会话设置了 TRANSACTION ISOLATION LEVEL。对于每个后续 Transact-SQL 语句，SQL Ser

13、ver 将所有共享锁一直保持到事务结束为止。复制代码 USE AdventureWorks;GOSET TRANSACTION ISOLATION LEVEL REPEATABLE READ; GOBEGIN TRANSACTION;GOSELECT *FROM HumanResources.EmployeePayHistory; GOSELECT *FROM HumanResources.Department;GOCOMMIT TRANSACTION;GO请参阅参考表提示其他资源自定义事务隔离级别数据库引擎中的隔离级别帮助和信息获取帮助SQL Server 2005 联机丛书（2007 年

14、9 月）调整事务隔离级别隔离属性是原子性、一致性、隔离性和持久性 (ACID) 这四个属性之一，逻辑工作单元必须具备这四个属性才能称为事务。隔离属性能够使事务免受其他并发事务执行的更新的影响。每个事务的隔离级别实际上都是可以自定义的。设置事务隔离级别使程序员面临的风险因某些SQL Server 数据库引擎 支持 SQL-92 中定义的事务隔离级别。完整性问题而增加，但好处是可以支持更好的数据并发访问。事务隔离级别有： READ UNCOMMITTED READ COMMITTED REPEATABLE READ SNAPSHOT SERIALIZABLE通过将更严格的锁持有更长时间，每个隔离级

15、别都比上一个级别提供更好的隔离性，但有两个例外。这两个例外是将 READ_COMMITTED_SNAPSHOT 设置为 ON 时的 SNAPSHOT 和 READ COMMITTED。这两个隔离级别在读取操作期间并不获取数据行的共享锁。只在表级持有 SCH-S 锁。可以使用 Transact-SQL 或通过数据库 API 来设置事务隔离级别。Transact-SQLTransact-SQL 脚本使用 SET TRANSACTION ISOLATION LEVEL 语句。ADOADO 应用程序将 Connection 对象的 IsolationLevel 属性设置为 adXactReadUnco

16、mmittedadXactReadSerializable。ADO.NET使用 System.Data.SqlClient 托管命名空间的 ADO.NET 应用程序可以调用 SqlConnection.BeginTransaction 方法并将 IsolationLevel 选项设置为 Unspecified、Chaos、ReadUncommitted、ReadCommitted、RepeatableRead、Serializable 或 Snapshot。 OLE DB开始事务时，使用 OLE DB 的应用程序调用 ITransactionLocal:StartTransaction，其中

17、isoLevel 设置为 ISOLATIONLEVEL_READUNCOMMITTED、adXactReadCommitted、adXactRepeatableRead 或ISOLATIONLEVEL_READCOMMITTED、ISOLATIONLEVEL_REPEATABLEREAD、ISOLATIONLEVEL_SNAPSHOT 或 ISOLATIONLEVEL_SERIALIZABLE。在自动提交模式下指定事务隔离级别时，OLE DB 应用程序可以将 DBPROPSET_SESSION 属性 DBPROP_SESS_AUTOCOMMITISOLEVELS DBPROPVAL_TI_RE

18、ADUNCOMMITTEDDBPROPVAL_TI_CURSORSTABILITYDBPROPVAL_TI_REPEATABLEREAD、设、置为DBPROPVAL_TI_CHAOS、DBPROPVAL_TI_BROWSEDBPROPVAL_TI_READCOMMITTEDDBPROPVAL_TI_SERIALIZABLEDBPROPVAL_TI_ISOLATED 或 DBPROPVAL_TI_SNAPSHOT。ODBCODBC 应用程序调用 SQLSetConnectAttr，其中 Attribute 设置为 SQL_ATTR_TXN_ISOLATION，ValuePtr 设置为 SQL_TXN_READ_UNCOMMITTED、SQL_TXN_READ_COMMITTED