SQL 事务的隔离

为了遵守ACID规则，事务必须与其他事务相隔离。这意味着在一个事务中使用的数据必须与其他事务相隔离。为了实现这种分离，每一个事务会锁住它使用的数据以防止其他事务使用它。锁定义在需要锁定的资源上，这些资源可以是索引、数据行或者表。SQL Server总会尝试精细地锁住资源。在大多数情况下，它会首先基于行级加锁。如果锁住的行太多，会提升锁至在表级。这个过程是自动完成的。在SQL Server中锁定数据的最常见锁资源为：

RID 在没有聚集索引的堆中用于锁定指定行的行标识。
KEY 锁定的一个索引的索引键。表中存在聚集索引时，此类型的锁用于锁住表中的一行。因为在聚集索引中，数据是索引的一部分。可以参见第6章学习详细了解索引的内部工作机制。
PAGE 数据库中锁定的8 KB大小的一个页。它可以是一个索引或者一个数据页。
TABLE 表锁用于为一个操作锁定一个表。

其他锁资源：

DB-----数据库，由于 dbid 列已包含数据库的数据库 ID，所以没有提供任何信息
FIL----文件
IDX----索引
EXT----区域, 相邻的八个数据页或索引页构成的一组。正被锁定的扩展盘区中的第一个页码。页由 fileid:page 组合进行标识

使用锁一般都是为防止以下的情况发生：

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。
不可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
幻觉读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

此外，每一个锁都有一个特定的锁类型定义锁的行为。例如，如果事务希望防止其他事务更新数据但允许其他事务读取数据，那么在有些情况下可能为写操作锁住数据。在其他情况下，要求排他地锁定数据以防止其他事务对数据的任何访问。这种行为通过锁的兼容性来实现。每一种锁类型的定义都在同样的资源上与一些来自其他事务的特定锁兼容。由于一个特定的锁类型必须在SQL Server中授权所有数据访问操作，因此可以使用锁的兼容性来管理两个或两个以上的操作是否可以在同一时间用于同样的数据。SQL Server中最常用的锁类型为：

共享 (S) 锁共享锁用于为读访问锁住数据。它们会防止其他事务更改数据，但不阻止读数据。共享锁与其他共享锁相兼容，这就允许多个事务在同一个被锁的资源上拥有一个共享锁。因此，事务可以并行地读同一个数据。
排他(X) 锁排他锁用于每一次数据的更新。它们会阻止其他事务访问数据，因此一个排他锁与其他锁都不兼容。
更新 (U) 锁更新锁是共享锁的一种特例。它们主要用于对UPDATE语句的支持。在UPDATE语句中，数据必须在它被更新前读取。因此，这需要一种锁类型在它读自己的数据时不阻止其他事务读数据。然而，当SQL Server开始更新数据的时候，它必须提升锁类型为排他锁。对于这种读操作，SQL Server使用与共享锁兼容但与其他更新锁不兼容的更新锁。因此，其他事务在数据由于UPDATE语句而被读取的时候可以读取，但其他UPDATE语句必须等待直到更新锁被释放。
意向(I)锁意向锁是前面几种锁类型的变体，包括意向共享锁、意向排他锁等等。它们用于在低层次的锁上保护高层次的不接受的锁。考虑一下这种情况：一个事务在表中的行上有一个排他锁。此时不允许其他事务在整张表上获取排他锁。为了管理这种情况，会在高层次应用意向锁使其他事务知道一些资源已经在低层次上被锁定了。在这种情况下，事务会在行上保持一个排他锁，同时使用一个排他意向锁锁定页和表。

其他的锁：

Null 没有得到资源的访问权限
Sch-S (Schema Stability) 对查询进行编译时。能防止加锁的对象被删除直到解锁
Sch-M (Schema Modification) 改变数据库结构时发生。能防止其他的事务访问加锁的对象
IS (Intent Shares) 意图共享锁。
SIU(Share Intent Update) 意图在维护资源的共享锁时，把更新锁放到锁层次结构的下层资源上
IS-S(Intent Share-shared) 复合键范围锁
IX(Intent Exclusive) 意图排他锁
SIX(Share Intent Exclusive)
S(Share) 共享锁
U(Update) 更新锁。防止死锁
Iin-Nul(Intent Insert-Null) 索引行层次的锁定，复合键范围锁
IS-X(Intent Share-Exclusive)
IU(Intent Update) 意图更新锁
IS-U(Intent Share Update) 串行更新扫描
X(Exclusive) 排他锁
BU 块操作使用的锁

监视锁

为了监视数据库中存在哪些锁，可以查询动态管理视图sys.dm_tran_locks。这个视图为数据库中当前存在的每一个单独的锁提供了一行数据。

BEGIN TRAN

SELECT

FirstName,LastName,EmailAddress

FROM

Person.Contact WITH (HOLDLOCK)

WHERE

ContactID = 15

为了检查事务使用了哪些锁，可以使用动态管理视图sys.dm_tran_locks。为了查询出只属于您的事务的锁，可以将这个视图与另一个称作sys.dm_tran_current_transaction的动态管理视图相联接。sys.dm_tran_current_transaction获取在当前连接中运行的事务的信息。在查询窗口中键入并执行以下SELECT语句来获取锁信息并提交事务：

SELECT

resource_type,

resource_associated_entity_id,

request_mode,request_status

FROM

sys.dm_tran_locks dml

INNER JOIN

sys.dm_tran_current_transaction dmt

dml.request_owner_id = dmt.transaction_id

COMMIT TRAN

查询结果如：

resource_type	resource_associated_entity_id	request_mode	request_status
OBJECT	309576141	IS	GRANT
KEY	72057594043236352	S	GRANT
PAGE	72057594043236352	IS	GRANT

它显示了在聚集索引的一个键上存在一个共享锁(request_mode = S)，在其相应的页和表Person.Contact上分别存在一个意向共享锁(request_mode = IS)。在request_status 列上的GRANT值意味着所有请求的锁都已经授权给这个事务。

现在查看更改WHERE子句以获取更多行时所发生的情况。按如下方式更改WHERE子句并执行整个事务：

BEGIN TRAN

SELECT

FirstName,LastName,EmailAddress

FROM

Person.Contact WITH (HOLDLOCK)

WHERE

ContactID < 7000

现在执行以下代码来检查锁：

SELECT

resource_type,

resource_associated_entity_id,

request_mode,

request_status

FROM

sys.dm_tran_locks dml

INNER JOIN

sys.dm_tran_current_transaction dmt

dml.request_owner_id = dmt.transaction_id

COMMIT TRAN

可以看出，对象资源类型上定义了一个共享锁，对于此例，这个对象资源是表Person.Contact。SQL Server认为对此事务保持一个表级锁比保持大约7000个键锁及其依赖的意向锁更简单、更快。由于SQL Server使用了一个表级锁，因此就不必使用意向锁。因为表在数据锁定层次结构中级别最高。为了找出哪个对象被锁定了，可以使用OBJECT_NAME函数。OBJECT_NAME将Object ID作为参数并会返回对象的名称。(如果resource_type是OBJECT的话，列resource_associated_entity_id存储锁定对象的Object ID)。

为了看到SQL Server如何在数据更改锁定数据，键入并执行以下事务来UPDATE Person.Contact表上的数据并查询相关的锁。在结束处，执行一个ROLLBACK TRAN语句来放弃更改。

USE AdventureWorks;

BEGIN TRAN

UPDATE

Person.Contact

SET

Phone ='+43 555 333 222'

WHERE

ContactID = 25

SELECT

resource_type,

resource_associated_entity_id,

request_mode,

request_status

FROM

sys.dm_tran_locks dml

INNER JOIN

sys.dm_tran_current_transaction dmt

dml.request_owner_id = dmt.transaction_id

ROLLBACK TRAN

结果如：

resource_type	resource_associated_entity_id	request_mode	request_status
METADATA	0	Sch-S	GRANT
PAGE	72057594043236352	IX	GRANT
OBJECT	309576141	IX	GRANT
KEY	72057594043236352	X	GRANT

可以看出，SQL Server使用一个排他锁(request_mode = X)来锁住键。任何时候，只要数据发生变化，SQL Server就会使用一个排他锁并保持它直到事务结束。如前所述，SQL Server还在第一步执行UPDATE语句的时候使用了更新锁。由于在更新语句之后对锁进行了查询，因此行上的更新锁已经被提升为排他锁。还会再次看到页和表上有两个意向排他锁(request_mode = IX)，并且在METADATA资源类型上有一个称作Sch-S的锁。Sch-S锁是一种架构稳定性锁，用来防止其他事务在数据更新时更改表架构。在更新数据时，架构是不能更改的。

事务的隔离级别

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

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

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

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

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

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

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

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

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

已提交读

在SQL Server 2005中，已提交读隔离级别是建立连接时的默认隔离级别。这个级别存在两种类型：已提交读和已提交读快照隔离级别。应用哪种类型由数据库选项定义。已提交读级别会在读数据之前等待，直到阻塞锁被释放。已提交读快照级别会在数据被其他事务阻塞时使用行版本控制来读数据最后一次提交的版本。

使用已提交读级别：

BEGIN TRAN

SELECT

FirstName, LastName, EmailAddress

FROM

Person.Contact

WHERE

ContactID = 1

返回EmailAddress为gustavo0@adventure-works.com的联系人Gustavo Achong。

现在假设另一事务在事务打开状态下更改了EmailAddress。打开第二个查询窗口并执行以下批来UPDATE EmailAddress，但不提交事务：

USE AdventureWorks;

BEGIN TRAN

UPDATE

Person.Contact

SET

EmailAddress = 'uncommitted@email.at'

WHERE

ContactID = 1

这个UPDATE 语句会正常运行。一行受到了影响，即使数据在这个事务还没有运行完之前已被查询窗口1中的事务读取。因为已提交读级别并不会在事务结束前保持用于SELECT语句的共享锁。共享锁会在数据读取之后立即被SQL Server释放。需要一致读的时候这将是一个问题。我们将下面的"获取一致的可重复读操作"实现。

现在切换到查询窗口1并尝试再次读数据：

SELECT

FirstName, LastName, EmailAddress

FROM

Person.Contact

WHERE

ContactID = 1

由于SELECT语句被阻塞，因此这个查询并没有结束。SQL Server会尝试在ContactID= 1的键上获取一个共享锁，但是由于在查询窗口2中的UPDATE语句对其有一个排他锁，因此这个操作不可能完成。虽然查询窗口2处于已提交读级别(由于您没有更改默认级别)，但排他锁依然存在。这个阻塞将持续存在，因为数据更改的排他锁会一直保持直到事务结束。

切换到查询窗口2，让查询窗口1中的查询继续运行。键入并执行以下SELECT语句检查数据库中的授权和等待的锁。

可以看一个状态为WAIT的共享锁。这是查询窗口1中运行的查询。它在等待查询窗口2中的查询，后者在同样的资源上有一个排他锁。

在查询窗口2中执行一个ROLLBACK TRAN语句来回滚UPDATE语句。然后切换回查询窗口1。可以看到，查询窗口1中的查询完成了，并且其结果与以前的一样。查询窗口2中的事务结束的时候，锁被释放了，以至查询窗口1中的查询不再被阻塞。由于查询窗口2中的事务回滚，因此查询窗口1中得到的结果是原来的数据。如果查询窗口2中的事务被提交，则查询窗口1中会得到新的数据作为结果。

在查询窗口1中执行一个COMMIT TRAN语句并关闭所有的查询窗口。

可以看出，在(默认)已提交读级别中SQL Server会等到排他锁释放之后再进行读操作，以此来获取真正的提交数据。还可以看出，共享锁会持续到数据被读取之后，而排他锁会持续到事务提交之后。在许多事务几乎同时更改数据的时候这种行为可能会造成问题。在这些情况下，由于排他锁造成的阻塞，读数据会非常慢。但在有些情况下，使用最后提交的数据版本是恰当的。在这些情况下，可以将已提交读级别更改为已提交读快照级别。

如果要在窗口1读取数据的话，可以使用这样的方法：

SELECT

FirstName, LastName, EmailAddress

FROM

Person.Contact WITH (NOLOCK)

WHERE

ContactID = 1

让它取消所有的锁机制，那么排他锁也不会影响到这句查询。

使用NOLOCK注意：在 SQL Server 中，NOLOCK 提示将启用"未提交读"行为。在 SQL Server Mobile 中，使用 NOLOCK 提示仍会赋予"提交读"隔离级别。SQL Server Mobile 将维护数据副本，以确保可以读取数据而不需要使用共享锁帮助保护数据。

使用已提交读快照级别

激活已提交读快照级别

USE master;

ALTER DATABASE AdventureWorks

SET READ_COMMITTED_SNAPSHOT ON

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

现在，执行以下代码开始一个事务并像前面一样更改EmailAddress(但要让事务处于打开状态)：

USE AdventureWorks;

BEGIN TRAN

UPDATE Person.Contact

SET EmailAddress = 'uncommitted@email.at'

WHERE ContactID = 1;

打开第二个查询窗口并执行以下语句来读取ContactID 1的列Name和EmailAddress列。

USE AdventureWorks;

BEGIN TRAN

SELECT FirstName, LastName, EmailAddress

FROM Person.Contact

WHERE ContactID = 1;

返回了联系人Gustavo Achong的EmailAddress gustavo0@adventure-works.com，这是这一行最后提交的版本。不像没有快照的已提交读级别那样，这个查询不会被阻塞。关闭查询窗口2并切换到查询窗口1。

执行以下语句来回滚事务并切换回已提交读级别(这个查询将等待直到关闭查询窗口2)：

ROLLBACK TRAN

USE master;

ALTER DATABASE AdventureWorks

SET READ_COMMITTED_SNAPSHOT OFF

重要提示 这个隔离级别可以用于减少阻塞。但要意识到这是一个数据库选项。当它发生了更改，将在数据库系统中使用已提交读级别的所有事务也会改变它们的行为。因此，只有在所有这些事务读最后提交的数据版本与读真正提交的数据版本在逻辑上同样正确的时候，使用这种级别才是明智的。

获取一致的可重复读操作

已提交读级别的一个缺点是，一个事务读取的数据在事务运行期间可能被另一个事务更改。因此，在两种已提交读级别下，不能保证一致性读。获取一致性读的意思是，在一个事务中，读取的数据始终是一样的。

已提交读在读数据的时候使用共享锁，但在读操作完成后会立即释放这个锁。因此，其他事务可以更改刚被读过的数据。
已提交读快照读取最后一次提交的数据版本。当它第二次读数据的时候，最后一次提交的版本可能由于第二个事务已经提交了对数据的更改而变成一个新版本。

在需要一致性读的时候(例如对于报表)，可能这种不一致性会导致问题。想象一下，您的事务通过数据计算了一些商业数值。在已提交读级别中进行这种计算的时候，可能由于基础数据在事务计算过程中发生了变化而导致这些值被错误计算。为了成功地执行这个计算，可以使用快照隔离级别。它会使用行版本管理来提供数据的提交版本，但与已提交读快照不同的是，它总会提供在开始事务时最后提交的数据版本。因此，SQL Server始终会在整个事务执行过程中获取同样的数据。

使用快照隔离级别

快照隔离级别需要在数据库中一次性地激活。激活之后，每个连接可以在需要的时候使用它。

USE master;

ALTER DATABASE AdventureWorks

SET ALLOW_SNAPSHOT_ISOLATION ON;

现在假设我们希望运行一些基于Sales.SalesOrderDetail表的报表，但需要一致性的读操作。执行以下语句为事务激活快照隔离级别并开始一个返回订单行合计的事务。记住OrderTotal的值。

USE AdventureWorks;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT SUM(LineTotal) as OrderTotal

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

参数SNAPSHOT的含义：

指定事务中任何语句读取的数据都将是在事务开始时便存在的数据的事务上一致的版本。事务只能识别在其开始之前提交的数据修改。在当前事务中执行的语句将看不到在当前事务开始以后由其他事务所做的数据修改。其效果就好像事务中的语句获得了已提交数据的快照，因为该数据在事务开始时就存在。
除非正在恢复数据库，否则 SNAPSHOT 事务不会在读取数据时请求锁。读取数据的 SNAPSHOT 事务不会阻止其他事务写入数据。写入数据的事务也不会阻止 SNAPSHOT 事务读取数据。
在数据库恢复的回滚阶段，如果尝试读取由其他正在回滚的事务锁定的数据，则 SNAPSHOT 事务将请求一个锁。在事务完成回滚之前，SNAPSHOT 事务会一直被阻塞。当事务取得授权之后，便会立即释放锁。
必须将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON，才能开始一个使用 SNAPSHOT 隔离级别的事务。如果使用 SNAPSHOT 隔离级别的事务访问多个数据库中的数据，则必须在每个数据库中将 ALLOW_SNAPSHOT_ISOLATION 都设置为 ON。
不能将通过其他隔离级别开始的事务设置为 SNAPSHOT 隔离级别，否则将导致事务中止。如果一个事务在 SNAPSHOT 隔离级别开始，则可以将它更改为另一个隔离级别，然后再返回 SNAPSHOT。一个事务从执行 BEGIN TRANSACTION 语句开始。
在 SNAPSHOT 隔离级别下运行的事务可以查看由该事务所做的更改。例如，如果事务对表执行 UPDATE，然后对同一个表发出 SELECT 语句，则修改后的数据将包含在结果集中。

打开第二个查询窗口并更新SalesOrderDetail表以更改查询窗口1中用到的基础数据。(如果希望重复这个示例，将OrderQty的值5更改为其他数字以使以下代码能真正地更改数据库中的数据)：

USE AdventureWorks;

UPDATE Sales.SalesOrderDetail

SET OrderQty = 5

WHERE SalesOrderID = 43659

AND ProductID = 777

关闭查询窗口2，切换到查询窗口1，然后重复下面的SELECT语句。

SELECT SUM(LineTotal) as OrderTotal

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

可以看出，由于快照隔离级别忽略了事务运行过程中数据的更改，因此结果与以前的相同。在快照级别下总会提供在事务开始时最后提交的值。

提交这个事务并执行以下代码再次重复这个查询：现在可看到，由于事务结束了，因此结果发生了变化。

COMMIT TRAN

SELECT SUM(LineTotal) as OrderTotal

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

执行以下代码关闭AdventureWorks数据库的快照隔离级别：

ALTER DATABASE AdventureWorks

SET ALLOW_SNAPSHOT_ISOLATION OFF;

避免同时发生的数据更新

如前所述，快照隔离级别并不在读操作的时候锁定数据，但能够在整个事务中提供一致性的视图。在某些情况下，有必要在整个事务的执行过程中锁定数据以避免其他事务对数据的更改。假设希望为一个订单开发票。首先需要获取数据并检查它，然后为其生成发票。在这种情况下，需要从事务起始就锁定数据以避免其他事务更改它。在这种情况下，快照隔离或者已提交读隔离级别都不是好的选择。对于这种情况，可以使用可重复读隔离级别。这个隔离级别与没有快照的已提交读级别的工作过程相似，但它会保持共享锁直至事务结束。因此，它防止了对数据的更新。

使用可重复读隔离级别

假设希望处理OrderID为43659的订单。首先，必须选择数据。为了防止其他事务更改正在读的数据，使用可重复读隔离。

USE AdventureWorks;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

参数REPEATABLE READ的含义：

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

打开第二个查询窗口并执行以下代码尝试更新SalesOrderDetail表以更改查询窗口1中要使用的基础数据：

UPDATE Sales.SalesOrderDetail

SET OrderQty = 5

WHERE SalesOrderID = 43659

AND ProductID = 777

查询会等待。不像快照隔离级别，不可能更新数据，因为共享锁会保持以防止其他事务更改数据。这个锁可以通过前面用过的管理视图sys.dm_tran_locks查看。

单击工具条上的"取消执行查询"按钮取消在查询窗口2中的查询。而执行以下INSERT语句在订单中加入一个新行项。

INSERT INTO Sales.SalesOrderDetail

(

SalesOrderID,

CarrierTrackingNumber,

OrderQty,

ProductID,

SpecialOfferID,

UnitPrice,

UnitPriceDiscount

)

VALUES(43659,'4911-403C-98',1,758,1,874,0)

注意，即使正处于可重复读隔离级别，这个语句也会成功执行。因为可重复读会锁定数据以阻止数据的更新，但INSERT语句向数据库中插入新数据，这是允许的。新行处于查询窗口1中事务SELECT语句的查询范围之中，所以会在事务下一次获取相同数据的时候被读取到。这称作幻像读。

重复SELECT语句并提交这个事务，如下所示：

SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

COMMIT TRAN

可以观察到，新行被SELECT语句读取了，因为它处于这个语句的查询范围之内。可重复读级别会阻止现有数据被更改，但不会阻止新数据插入SELECT语句的查询范围内。

其他

SET TRANSACTION一共有以下几种级别：

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 隔离级别上运行的事务中的各语句请求共享锁，而不是行版本控制。

SERIALIZABLE

请指定下列内容：

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

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

需要注意的地方：

一次只能设置一个隔离级别选项，而且设置的选项将一直对那个连接始终有效，直到显式更改该选项为止。事务中执行的所有读取操作都会在指定的隔离级别的规则下运行，除非语句的 FROM 子句中的表提示为表指定了其他锁定行为或版本控制行为。
事务隔离级别定义了可为读取操作获取的锁类型。针对 READ COMMITTED 或 REPEATABLE READ 获取的共享锁通常为行锁，尽管当读取引用了页或表中大量的行时，行锁可以升级为页锁或表锁。如果某行在被读取之后由事务进行了修改，则该事务会获取一个用于保护该行的排他锁，并且该排他锁在事务完成之前将一直保持。例如，如果 REPEATABLE READ 事务具有用于某行的共享锁，并且该事务随后修改了该行，则共享行锁便会转换为排他行锁。
在事务进行期间，可以随时将事务从一个隔离级别切换到另一个隔离级别，但有一种情况例外。即在从任一隔离级别更改到 SNAPSHOT 隔离时，不能进行上述操作。否则会导致事务失败并回滚。但是，可以将在 SNAPSHOT 隔离中启动的事务更改为任何其他隔离级别。
将事务从一个隔离级别更改为另一个隔离级别之后，便会根据新级别的规则对更改后读取的资源执行保护。在更改前读取的资源将继续按照以前级别的规则受到保护。例如，如果某个事务从 READ COMMITTED 更改为 SERIALIZABLE，则在该事务结束前，更改后所获取的共享锁将一直处于保留状态。
如果在存储过程或触发器中发出 SET TRANSACTION ISOLATION LEVEL，则当对象返回控制时，隔离级别会重设为在调用对象时有效的级别。例如，如果在批处理中设置 REPEATABLE READ，并且该批处理调用一个将隔离级别设置为 SERIALIZABLE 的存储过程，则当该存储过程将控制返回给该批处理时，隔离级别就会恢复为 REPEATABLE READ。

posted on 2007-03-11 17:22 Sherrys 阅读(1291) 评论(0) 编辑收藏举报

刷新页面返回顶部

龍機傳承

SQL 事务的隔离

公告

导航