SQL Server 2005 管理并发数据访问

 

引用:

并发控制的类型  
当许多人试图同时修改数据库中的数据时，必须实现一个控制系统，使一个人所做的修改不会对他人所做的修改产生负面影响。这称为并发控制。
并发控制理论根据建立并发控制的方法而分为两类：

• 悲观并发控制
  一个锁定系统，可以阻止用户以影响其他用户的方式修改数据。如果用户执行的操作导致应用了某个锁，只有这个锁的所有者释放该锁，其他用户才能执行与该锁冲突的操作。这种方法之所以称为悲观并发控制，是因为它主要用于数据争用激烈的环境中，以及发生并发冲突时用锁保护数据的成本低于回滚事务的成本的环境中。
• 乐观并发控制
  在乐观并发控制中，用户读取数据时不锁定数据。当一个用户更新数据时，系统将进行检查，查看该用户读取数据后其他用户是否又更改了该数据。如果其他用户更新了数据，将产生一个错误。一般情况下，收到错误信息的用户将回滚事务并重新开始。这种方法之所以称为乐观并发控制，是因为它主要用于数据争用较少的环境中，以及回滚事务的成本偶尔高于读取数据时锁定数据的成本的环境中。

Microsoft SQL Server 2005 支持某个范围的并发控制。用户通过为游标上的连接或并发选项选择事务隔离级别来指定并发控制的类型。这些特性可以使用 Transact-SQL 语句或通过数据库应用程序编程接口（API，如 ADO、ADO.NET、OLE DB 和 ODBC）的属性和特性来定义。

数据库引擎隔离级别

SQL-99 标准定义了下列隔离级别，Microsoft SQL Server 数据库引擎 支持所有这些隔离级别：

• 未提交读（隔离事务的最低级别，只能保证不读取物理上损坏的数据）
• 已提交读（数据库引擎 的默认级别）
• 可重复读
• 可序列化（隔离事务的最高级别，事务之间完全隔离）

SQL Server 2005 还支持使用行版本控制的两个事务隔离级别。一个是已提交读隔离的新实现，另一个是新事务隔离级别（快照）。

• 将 READ_COMMITED_SNAPSHOT 数据库选项设置为 ON 时，已提交读隔离使用行版本控制提供语句级别的读取一致性。读取操作只需要 SCH-S 表级别的锁，不需要页锁或行锁。将 READ_COMMITED_SNAPSHOT 数据库选项设置为 OFF（默认设置）时，已提交读隔离的行为与在 SQL Server 的早期版本中相同。两个实现都满足已提交读隔离的 ANSI 定义。
• 快照隔离级别使用行版本控制来提供事务级别的读取一致性。读取操作不获取页锁或行锁，只获取 SCH-S 表锁。读取其他事务修改的行时，读取操作将检索启动事务时存在的行的版本。将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 时，将启用快照隔离。默认情况下，用户数据库的此选项设置为 OFF。

下表显示了不同隔离级别允许的并发副作用。
隔离级别 脏读 不可重复读取 幻读 未提交读
是
是
是
已提交读
否
是
是
可重复读
否
否
是
快照
否
否
否
可序列化
否
否
否

引用:

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

控制到 SQL Server 的连接发出的 Transact-SQL 语句的锁定行为和行版本控制行为。

语法


SET TRANSACTION ISOLATION LEVEL    { READ UNCOMMITTED    | READ COMMITTED    | REPEATABLE READ    | SNAPSHOT    | SERIALIZABLE    }[ ; ] 参数READ UNCOMMITTED 指定语句可以读取已由其他事务修改但尚未提交的行。
在 READ UNCOMMITTED 级别运行的事务，不会发出共享锁来防止其他事务修改当前事务读取的数据。READ UNCOMMITTED 事务也不会被排他锁阻塞，排他锁会禁止当前事务读取其他事务已修改但尚未提交的行。设置此选项之后，可以读取未提交的修改，这种读取称为脏读。在事务结束之前，可以更改数据中的值，行也可以出现在数据集中或从数据集中消失。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 NOLOCK 相同。这是隔离级别中限制最少的级别。
在 SQL Server 2005 中，您还可以使用下列任意一种方法，在保护事务不脏读未提交的数据修改的同时尽量减少锁定争用：

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

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

• 如果将 READ_COMMITTED_SNAPSHOT 设置为 OFF（默认设置），则数据库引擎 会使用共享锁防止其他事务在当前事务执行读取操作期间修改行。共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。语句完成后便会释放共享锁。
• 如果将 READ_COMMITTED_SNAPSHOT 设置为 ON，则数据库引擎 会使用行版本控制为每个语句提供一个在事务上一致的数据快照，因为该数据在语句开始时就存在。不使用锁来防止其他事务更新数据。

当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 时，您可以使用 READCOMMITTEDLOCK 表提示为 READ_COMMITTED 隔离级别上运行的事务中的各语句请求共享锁，而不是行版本控制。
注意： 设置 READ_COMMITTED_SNAPSHOT 选项时，数据库中仅允许存在执行 ALTER DATABASE 命令的连接。在 ALTER DATABASE 完成之前，数据库中不允许有其他打开的连接。数据库不必处于单用户模式。
REPEATABLE READ 指定语句不能读取已由其他事务修改但尚未提交的行，并且指定，其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据。
对事务中的每个语句所读取的全部数据都设置了共享锁，并且该共享锁一直保持到事务完成为止。这样可以防止其他事务修改当前事务读取的任何行。其他事务可以插入与当前事务所发出语句的搜索条件相匹配的新行。如果当前事务随后重试执行该语句，它会检索新行，从而产生幻读。由于共享锁一直保持到事务结束，而不是在每个语句结束时释放，所以并发级别低于默认的 READ COMMITTED 隔离级别。此选项只在必要时使用。
SNAPSHOT 指定事务中任何语句读取的数据都将是在事务开始时便存在的数据的事务上一致的版本。事务只能识别在其开始之前提交的数据修改。在当前事务中执行的语句将看不到在当前事务开始以后由其他事务所做的数据修改。其效果就好像事务中的语句获得了已提交数据的快照，因为该数据在事务开始时就存在。
除非正在恢复数据库，否则 SNAPSHOT 事务不会在读取数据时请求锁。读取数据的 SNAPSHOT 事务不会阻止其他事务写入数据。写入数据的事务也不会阻止 SNAPSHOT 事务读取数据。
在数据库恢复的回滚阶段，如果尝试读取由其他正在回滚的事务锁定的数据，则 SNAPSHOT 事务将请求一个锁。在事务完成回滚之前，SNAPSHOT 事务会一直被阻塞。当事务取得授权之后，便会立即释放锁。
必须将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON，才能开始一个使用 SNAPSHOT 隔离级别的事务。如果使用 SNAPSHOT 隔离级别的事务访问多个数据库中的数据，则必须在每个数据库中将 ALLOW_SNAPSHOT_ISOLATION 都设置为 ON。
不能将通过其他隔离级别开始的事务设置为 SNAPSHOT 隔离级别，否则将导致事务中止。如果一个事务在 SNAPSHOT 隔离级别开始，则可以将它更改为另一个隔离级别，然后再返回 SNAPSHOT。一个事务从执行 BEGIN TRANSACTION 语句开始。
在 SNAPSHOT 隔离级别下运行的事务可以查看由该事务所做的更改。例如，如果事务对表执行 UPDATE，然后对同一个表发出 SELECT 语句，则修改后的数据将包含在结果集中。
SERIALIZABLE 请指定下列内容：

• 语句不能读取已由其他事务修改但尚未提交的数据。
• 任何其他事务都不能在当前事务完成之前修改由当前事务读取的数据。
• 在当前事务完成之前，其他事务不能使用当前事务中任何语句读取的键值插入新行。

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

备注一次只能设置一个隔离级别选项，而且设置的选项将一直对那个连接始终有效，直到显式更改该选项为止。事务中执行的所有读取操作都会在指定的隔离级别的规则下运行，除非语句的 FROM 子句中的表提示为表指定了其他锁定行为或版本控制行为。事务隔离级别定义了可为读取操作获取的锁类型。针对 READ COMMITTED 或 REPEATABLE READ 获取的共享锁通常为行锁，尽管当读取引用了页或表中大量的行时，行锁可以升级为页锁或表锁。如果某行在被读取之后由事务进行了修改，则该事务会获取一个用于保护该行的排他锁，并且该排他锁在事务完成之前将一直保持。例如，如果 REPEATABLE READ 事务具有用于某行的共享锁，并且该事务随后修改了该行，则共享行锁便会转换为排他行锁。
在事务进行期间，可以随时将事务从一个隔离级别切换到另一个隔离级别，但有一种情况例外。即在从任一隔离级别更改到 SNAPSHOT 隔离时，不能进行上述操作。否则会导致事务失败并回滚。但是，可以将在 SNAPSHOT 隔离中启动的事务更改为任何其他隔离级别。
将事务从一个隔离级别更改为另一个隔离级别之后，便会根据新级别的规则对更改后读取的资源执行保护。在更改前读取的资源将继续按照以前级别的规则受到保护。例如，如果某个事务从 READ COMMITTED 更改为 SERIALIZABLE，则在该事务结束前，更改后所获取的共享锁将一直处于保留状态。
如果在存储过程或触发器中发出 SET TRANSACTION ISOLATION LEVEL，则当对象返回控制时，隔离级别会重设为在调用对象时有效的级别。例如，如果在批处理中设置 REPEATABLE READ，并且该批处理调用一个将隔离级别设置为 SERIALIZABLE 的存储过程，则当该存储过程将控制返回给该批处理时，隔离级别就会恢复为 REPEATABLE READ。
注意： 用户定义函数以及公共语言运行时 (CLR) 用户定义类型不能执行 SET TRANSATION ISOLATION LEVEL。但是，可通过使用表提示来重写隔离级别。有关详细信息，请参阅 表提示 (Transact-SQL)。
当您使用 sp_bindsession 绑定两个会话时，每个会话都会保留它自身的隔离级别设置。使用 SET TRANSACTION ISOLATION LEVEL 更改某个会话的隔离级别设置时，不会影响与该会话绑定的其他任何会话的设置。
SET TRANSACTION ISOLATION LEVEL 会在执行或运行时生效，而不是在分析时生效。
对于保存为堆的表执行的优化大容量加载操作将阻塞查询，但在以下隔离级别下运行优化大容量加载操作时，通常查询不会受到阻塞。

• SNAPSHOT
• READ UNCOMMITTED
• 使用行版本控制的 READ COMMITTED

相反，在这些隔离级别下运行的查询会阻塞针对堆运行的优化大容量加载操作。有关大容量加载操作的详细信息，请参阅 大容量导入和大容量导出概述和 优化大容量导入性能。

示例


以下示例为会话设置了 TRANSACTION ISOLATION LEVEL。对于每个后续 Transact-SQL 语句，SQL Server 将所有共享锁一直保持到事务结束为止。

USE AdventureWorks;GOSET TRANSACTION ISOLATION LEVEL REPEATABLE READ;GOBEGIN TRANSACTION;GOSELECT *     FROM HumanResources.EmployeePayHistory;GOSELECT *     FROM HumanResources.Department;GOCOMMIT TRANSACTION;GO