使用sql 2005事务(摘录)

来源：互联网发布：淘宝助理怎么回收单号编辑：程序博客网时间：2024/06/04 18:56

12，商业事务和SQL Server事务

每一个商业过程都包括一个或多个的事务。想象一下，您正在管理一个网上商店。一个消费者定购一个产品后，必须执行一个预先定义的进程才能确保交货的及时。这个进程还必须包括信用卡的处理过程以保证公司收到了货款。如果这些任务中有一个失败了并且不能更正，那么整个过程必须取消，以保证消费者在得到商品前不能给他开据发票，或者反之。在有些情况下，这些过程通过计算机系统处理，数据存储在数据库中。与一个商业事务相关的数据必须改为可靠、一致、完整的数据以符合实际的商业过程。这可以通过在数据库级使用事务来完成。一个事务被定义为作为一个单元执行的符合所谓ACID属性的一序列的操作。

l 原子性每一个事务是一个工作单元。它不能被分割成更小的部分。这个属性意味着在事务中定义的一切数据更改要么都完成，要么都不完成。

l 一致性一个事务不能违背定义在数据库中的任何完整性检查。为了维护一致性，所有的规则、约束、检查和触发都会应用在事务中。由于所有的数据更改在事务期间内进行，这些数据在事务开始和事务结束前会被确保为一致的。

l 隔离性事务必须与其他事务进行的数据更改相隔离。这意味着没有其他操作可以改变中间态(没有提交的)的数据。为了避免中间态数据被更改，事务必须要么等待来自其他事务的更改被提交，要么只能查看到处于上一个提交状态的数据。

l 持久性在一个事务完成，并且客户端应用程序已经被提示这个事务已经成功完成后，无论发生任何系统错误，这些更改的数据将永久存在。

1. 定义事务的外延。开发者必须定义事务从哪里开始，到哪里结束。一个事务总应该越短越好，但是同时要做到尽量符合实际业务过程的需求。

2. 定义错误管理。在事务中并不是所有的错误都会自动回滚。开发者有责任实现错误管理。

3. 定义隔离级别。隔离不同的事务总是会有与并行性相关的缺陷。如果完全地隔离事务并且其他事务希望读取相同但不为前一状态的数据，那么它会被阻塞直到您结束您的事务。这对于有许多并行连接的数据库系统来说是一个严重问题。SQL Server实现了一些不同种类的、需要正确选择的隔离级别。一般规则是选择在尽可能短的时间内锁住尽可能少的数据，并可以提供需要的事务安全性的隔离级别。

l 自动提交事务

l 显式事务

l 隐式事务

Ø 探究自动提交事务

1. 从“开始”菜单中，依次选择“所有程序”|“Microsoft SQL Server 2005”|“SQL Server Management Studio”。单击工具条上的“新建查询”按钮打开一个“新建查询”窗口。

2. 键入并执行以下“CREATE TABLE”语句来创建一个小表，将在下一步中用它来检验事务的行为。此例的代码包含在示例文件ExploringAutoCommit.sql中。

USE tempdb;

CREATE TABLE table1 (

i int NOT NULL PRIMARY KEY,

col1 varchar(20) NOT NULL,

col2 varchar(20) NULL);

3. 现在在这个表中插入三个新行。在程序窗口中键入并执行以下语句。

USE tempdb;

INSERT INTO table1 (i,col1,col2)

VALUES (1,'First row','First row');

INSERT INTO table1 (i,col1,col2)

VALUES (2,NULL,'Second row');

INSERT INTO table1 (i,col1,col2)

VALUES (3,'Third row','Third row');

4. 随后将出现如图10.1所示的消息，指出SQL Server不能将列NULL值插入列col1，因为这个列被定义为NOT NULL。

图10.1 执行结果

5. 键入并执行以下SELECT语句来检查是否记录已成功插入：

USE tempdb;

SELECT i,col1,col2

FROM table1;

6. 可以看出，第二行没有被插入，但是第一行和第三行被成功插入。SQL Server使用自动提交事务时，每一个语句本身是一个事务。如果这个语句产生了错误，它的事务会自动回滚。如果这个语句成功执行而没有产生错误，它的事务会自动提交。因此，第一个和第三个语句将被提交，而第二个有错误的语句会回滚。注意，这种行为甚至会发生在这三个语句作为一个批一起提交的时候。批不会定义是否批中的语句是否按一个事务进行处理。

在显式事务中，开发者要定义一个事务在何处开始，并定义这个事务在什么时候需要提交或回滚。这通过T-SQL 语句BEGIN TRANSACTION, COMMIT TRANSACTION和ROLLBACK TRANSACTION来实现。一个显式事务是独立于批的。它可以跨越多个批，或者在一个批中可以有多个显式事务。

注意可以在关键字TRANSACTION的位置使用它的缩写TRAN。

Ø 定义显式事务

1. 启动SQL Server Management Studio并打开一个“新建查询”窗口。

2. 键入并执行以下语句来删除table1。此例的代码包含在示例文件DefineExplicitTransactions.sql中。

USE tempdb;

TRUNCATE TABLE table1;

3. 现在，在table1中插入与上例相同的三个行。这一次希望将三条记录要么都插入到表中，要么一条也不插入。因此，需要将语句组织在一个显式事务中。在查询窗口中键入并执行以下语句并一次执行所有这些语句。

USE tempdb;

BEGIN TRAN

INSERT INTO table1 (i,col1,col2)

VALUES (1,'First row','First row');

INSERT INTO table1 (i,col1,col2)

VALUES (2,NULL,'Second row');

INSERT INTO table1 (i,col1,col2)

VALUES (3,'Third row','Third row');

COMMIT TRAN;

4. 随后获得和以前一样的消息，指出SQL Server不能将NULL值插入列col1，因为它被定义为NOT NULL。

5. 键入并执行SELECT语句来检查是否已插入记录：

USE tempdb;

SELECT i,col1,col2

FROM table1;

6. 可以看出，结果与在自动提交模式时的结果相同。两行被插入了，违反NULL值约束的行则没有插入。发生了什么？正如先前所述的一样，开发者的职责不仅包括定义事务的长度，还要决定是否要进行事务回滚。所以需要在事务中加入一个错误处理程序。没有错误处理程序，由于这个批没有被取消，SQL Server将简单地在错误之后处理下一个语句。在上一个批中，SQL Server简单地处理每一个INSERT语句并随后处理了COMMIT语句。因此，得到和的结果与在自动提交模式时一样。

7. 为了加入一个错误处理程序，可以使用SQL Server 2005 T-SQL的新特性：TRY和CATCH语句块。再次删除这个表并执行如下所示具有错误处理程序的事务：

--truncate

TRUNCATE TABLE table1

--transaction with error handler

BEGIN TRY

BEGIN TRAN

INSERT INTO table1 (i,col1,col2)

VALUES (1,'First row','First row');

INSERT INTO table1 (i,col1,col2)

VALUES (2,NULL,'Second row');

INSERT INTO table1 (i,col1,col2)

VALUES (3,'Third row','Third row');

COMMIT TRAN;

END TRY

BEGIN CATCH

ROLLBACK TRAN

END CATCH;

这一次不会返回任何错误消息，因为错误已经被CATCH语句块捕获了。

8. 键入并执行以下SELECT语句来检查事务是否回滚：

USE tempdb;

SELECT i,col1,col2

FROM table1;

没有返回记录。可以看出，整个事务都回滚了。在INSERT语句中发生违规插入的时候，SQL Server跳到CATCH语句块并回滚了事务。

9. 这个代码存在的惟一问题是不能获取任何错误信息。可以在CATCH块中使用特定的函数来获取错误并对错误进行管理。还可以使用RAISERROR函数抛出一个自定义的错误。按如下代码所示更改CATCH语句：

BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() as ErrorState,

ERROR_PROCEDURE() as ErrorProcedure,

ERROR_LINE() as ErrorLine,

ERROR_MESSAGE() as ErrorMessage;

RAISERROR('Error in Transaction!',14,1)

ROLLBACK TRAN

END CATCH;

10. 执行步骤9的整个事务。现在获得一个返回所有错误信息的记录和一个自定义的、指出已发生错误的信息。当然，RAISERROR语句中包括实际的错误信息也是可能的。此时的CATCH语句块如下所示：

BEGIN CATCH

DECLARE @er nvarchar(max)

SET @er = 'Error: '+ ERROR_MESSAGE();

RAISERROR(@er,14,1);

ROLLBACK TRAN

END CATCH;

第三种模式称作隐式事务模式。因为在这种模式中，SQL Server在没有事务存在的情况下会开始一个事务，但不会像在自动模式中那样自动执行COMMIT 或ROLLBACK 语句。事务必须显式结束。以下语句在没有事务时隐式开始一个事务。

ALTER TABLE

GRANT

FETCH

DELETE

CREATE

REVOKE

INSERT

SELECT

DROP

OPEN

UPDATE

TRUNCATE TABLE

Ø 使用隐式事务

1. 启动SQL Server Management Studio并打开一个“新建查询”窗口。

2. 键入并执行以下语句来设置连接为隐式事务模式(此例的代码包含在示例文件UsingImplicitTransactions.sql中)：

SET IMPLICIT_TRANSACTIONS ON;

3. 执行以下代码创建一个表检验是否已启动事务：

CREATE TABLE T1

(i int PRIMARY KEY);

4. 用@@TRANCOUNT来测试是否已经打开一个事务。执行如下所示的SELECT语句：

SELECT @@TRANCOUNT AS [Transaction Count];

5. 结果是1，意思是当前连接已经打开了一个事务。0的意思是当前没有事务，一个大于1的数的意思是有嵌套事务(详见后文)。

6. 现在执行以下语句在表中插入一行并再次检查@@TRANCOUNT：

INSERT INTO T1 VALUES(5);

SELECT @@TRANCOUNT AS [Transaction Count];

@@TRANCOUNT的值仍然是1。由于已经有一个打开的事务，因此SQL Server没有开始一个新的事务。

7. 现在执行以下语句回滚这个事务并再次检查@@TRANCOUNT。可以看出，在ROLLBACK TRAN 语句执行之后，@@TRANCOUNT 的值变成了0。

ROLLBACK TRAN

SELECT @@TRANCOUNT AS [Transaction Count];

8. 尝试对表T1执行SELECT语句：

SELECT * FROM T1;

9. 由于表不复存在，所以会得到一个错误信息。这个隐式事务起始于CREATE TABLE语句，并且ROLLBACK TRAN语句取消了第一个语句后所做的所有工作。

10. 执行以下代码关闭隐式事务：

SET IMPLICIT_TRANSACTIONS OFF;

警告在使用隐式事务时要小心。不要忘记提交或回滚工作。由于没有显式的BEGIN TRANSACTION语句，这些步骤很容易被忘记，并导致事务长期运行；在连接关闭时产生的不必要的回滚；以及与其他连接之间产生的阻塞问题。

显式事务可以嵌套，即在显式事务中开始另一个显式事务是可能的。支持嵌套事务的最重要原因是为了允许在存储过程中使用事务而不必顾及这个事务本身是否是在另一个事务中被调用的。但是SQL Server是如何处理嵌套事务的？我们可以通过两个简单的示例来探究一下嵌套事务。

Ø 探究嵌套事务

1. 启动SQL Server Management Studio并打开一个“新建查询”窗口。

2. 通过@@TRANCOUNT来发现SQL Server是如何处理嵌套事务的。键入并执行以下批(此例的代码包含在示例文件NestingTransactions.sql中)：

PRINT 'Trancount before transaction: ' + CAST(@@trancount as char(1))

BEGIN TRAN

PRINT 'After first BEGIN TRAN: ' + CAST(@@trancount as char(1))

BEGIN TRAN

PRINT 'After second BEGIN TRAN: ' + CAST(@@trancount as char(1))

COMMIT TRAN

PRINT 'After first COMMIT TRAN: ' + CAST(@@trancount as char(1))

COMMIT TRAN

PRINT 'After second COMMIT TRAN: ' + CAST(@@trancount as char(1))

3. 在结果中，可以看到每一个BEGIN TRAN 语句都会使@@TRANCOUNT增加1并且每一个COMMIT TRAN语句都会使其减少1。如前所述，一个值为0的@@TRANCOUNT意味着没有打开的事务。因此，在@@TRANCOUNT值从1降到0时结束的事务发生在外层事务提交的时候。因此，每一个内部事务都需要提交。由于事务起始于第一个BEGIN TRAN并结束于最后一个COMMIT TRAN，因此最外层的事务决定了是否完全提交内部的事务。如果最外层的事务没有被提交，其中嵌套的事务也不会被提交。

4. 键入并执行以下批来检验事务回滚时所发生的情况：

USE AdventureWorks

BEGIN TRAN

PRINT 'After 1st BEGIN TRAN: ' + CAST(@@trancount as char(1))

BEGIN TRAN

PRINT 'After 2nd BEGIN TRAN: ' + CAST(@@trancount as char(1))

BEGIN TRAN

PRINT 'After 3rd BEGIN TRAN: ' + CAST(@@trancount as char(1))

UPDATE Person.Contact

SET EmailAddress = 'test@test.at'

WHERE ContactID = 20

COMMIT TRAN

PRINT 'After first COMMIT TRAN: ' + CAST(@@trancount as char(1))

ROLLBACK TRAN

PRINT 'After ROLLBACK TRAN: ' + CAST(@@trancount as char(1))

SELECT EmailAddress FROM Person.Contact

WHERE ContactID = 20;

5. 在这个示例中，联系人的电子邮件地址在一个嵌套事务中被更新，这会被立即提交。然后ROLLBACK TRAN被执行。ROLLBACK TRAN将@@TRANCOUNT减为0并回滚整个事务及其中嵌套的事务，无论它们是否已经被提交。因此，嵌套事务中所做的更新被回滚，数据没有任何改变。

始终牢记，在嵌套的事务中，只有最外层的事务决定着是否提交内部事务。每一个COMMIT TRAN语句总是应用于最后一个执行的BEGIN TRAN。因此，对于每一个BEGIN TRAN，必须调用一个COMMIT TRAN来提交事务。ROLLBACK TRAN语句总是属于最外层的事务，并且因此总是回滚整个事务而不论其中打开了多少嵌套事务。正因为此，管理嵌套事务很复杂。正如本节最初提到的那样，如果每一个嵌套存储过程都在自身中开始一个事务，那么嵌套事务大部分会发生在嵌套存储过程中。要避免嵌套事务，可以在过程开始处检查@@TRANCOUNT的值，以此来确定是否需要开始一个事务。如果@@TRANCOUNT大于0，因为过程已经处于一个事务中并且调用实例可以在错误发生时回滚事务。

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

l RID 在没有聚集索引的堆中用于锁定指定行的行标识。

l KEY 锁定的一个索引的索引键。表中存在聚集索引时，此类型的锁用于锁住表中的一行。因为在聚集索引中，数据是索引的一部分。可以参见第6章学习详细了解索引的内部工作机制。

l PAGE 数据库中锁定的8 KB大小的一个页。它可以是一个索引或者一个数据页。

l TABLE 表锁用于为一个操作锁定一个表。

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

l 共享 (S) 锁共享锁用于为读访问锁住数据。它们会防止其他事务更改数据，但不阻止读数据。共享锁与其他共享锁相兼容，这就允许多个事务在同一个被锁的资源上拥有一个共享锁。因此，事务可以并行地读同一个数据。

l 排他(X) 锁排他锁用于每一次数据的更新。它们会阻止其他事务访问数据，因此一个排他锁与其他锁都不兼容。

l 更新 (U) 锁更新锁是共享锁的一种特例。它们主要用于对UPDATE语句的支持。在UPDATE语句中，数据必须在它被更新前读取。因此，这需要一种锁类型在它读自己的数据时不阻止其他事务读数据。然而，当SQL Server开始更新数据的时候，它必须提升锁类型为排他锁。对于这种读操作，SQL Server使用与共享锁兼容但与其他更新锁不兼容的更新锁。因此，其他事务在数据由于UPDATE语句而被读取的时候可以读取，但其他UPDATE语句必须等待直到更新锁被释放。

l 意向(I)锁意向锁是前面几种锁类型的变体，包括意向共享锁、意向排他锁等等。它们用于在低层次的锁上保护高层次的不接受的锁。考虑一下这种情况：一个事务在表中的行上有一个排他锁。此时不允许其他事务在整张表上获取排他锁。为了管理这种情况，会在高层次应用意向锁使其他事务知道一些资源已经在低层次上被锁定了。在这种情况下，事务会在行上保持一个排他锁，同时使用一个排他意向锁锁定页和表。

SQL Server 2005中的锁类型和锁资源的完整列表，请参见SQL Server Books Online 中的主题 “Lock Modes。”

Ø 监视锁

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

1. 启动SQL Server Management Studio并打开一个“新建查询”窗口。

2. 键入并执行以下语句来开始一个事务并查询“Person.Contact”表。这个事务使用的锁提示HOLDLOCK用于告诉SQL Server在SELECT语句后不要释放锁。还要注意，这个事务没有被提交。因为提交事务的时候锁会自动释放。锁会在事务中持续多长时间将在本章后文解释。此例的代码包含在示例文件MonitoringLocks.sql中。

USE AdventureWorks;

BEGIN TRAN

SELECT FirstName,LastName,EmailAddress

FROM Person.Contact WITH (HOLDLOCK)

WHERE ContactID = 15

3. 为了检查事务使用了哪些锁，可以使用动态管理视图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

ON dml.request_owner_id = dmt.transaction_id;

COMMIT TRAN

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

图10.2 查询结果

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

BEGIN TRAN

SELECT FirstName,LastName,EmailAddress

FROM Person.Contact WITH (HOLDLOCK)

WHERE ContactID <7000;

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

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

ON 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)。

7. 为了看到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

ON dml.request_owner_id = dmt.transaction_id;

ROLLBACK TRAN

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

图10.3 查询结果

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

l 是否在读数据的时候使用锁

l 读锁持续多长时间

l 在读数据的时候使用何种类型的锁

l 读操作希望读已经被其他事务排他锁住的数据时，怎么办？在这种情况下，SQL Server可以：

u 一直等到其他事务释放锁

u 读没有提交的数据

u 读数据最后提交后的版本

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

l 未提交读在读数据时不会检查或使用任何锁。因此，在这种隔离级别中可能读取到没有提交的数据。

l 已提交读只读取提交的数据并等待其他事务释放排他锁。读数据的共享锁在读操作完成后立即释放。已提交读是SQL Server的默认隔离级别。

l 可重复读像已提交读级别那样读数据，但会保持共享锁直到事务结束。

l 可序列化工作方式类似于可重复读。但它不仅会锁定受影响的数据，还会锁定这个范围。这就阻止了新数据插入查询所涉及的范围，这种情况可以导致幻像读(参见SQL Server Books Online中的主题“Concurrency Effects”)。

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

l 已提交读快照它是一种提交读级别的新实现。不像一般的提交读级别，SQL Server会读取最后提交的版本并因此不必在进行读操作时等待直到锁被释放。这个级别可以替代提交读级别。

l 快照这种隔离使用行版本来提供事务级别的读取一致性。这意味着在一个事务中，由于读一致性可以通过行版本控制实现，因此同样的数据总是可以像在可序列化级别上一样被读取而不必为防止来自其他事务的更改而被锁定。

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

很多情况下，定义正确的隔离级别并不是一个简单的决定。作为一种通用的规则，要选择在尽可能短的时间内锁住最少数据，但同时依然可以为事务提供它所需的安全程度的隔离级别。在下一节中，我们将看到一些场景，详细展示这些隔离级别是如何工作的以及如何选择正确的隔离级别。

10.3.1.1 已提交读

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

Ø 使用已提交读级别

1. 启动SQL Server Management Studio并打开一个“新建查询”窗口。

2. 键入并执行以下语句来在Person.Contact表中读取ContactID = 1 的Name及Email- Address列(本示例的代码包含在示例文件ReadCommitted1.sql及ReadCommitted2.sql中)：

USE AdventureWorks;

BEGIN TRAN

SELECT FirstName, LastName, EmailAddress

FROM Person.Contact

WHERE ContactID = 1;

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

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

USE AdventureWorks;

BEGIN TRAN

UPDATE Person.Contact

SET EmailAddress = 'uncommitted@email.at'

WHERE ContactID = 1;

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

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

SELECT FirstName, LastName, EmailAddress

FROM Person.Contact

WHERE ContactID = 1;

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

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

SELECT resource_type, resource_associated_entity_id,

request_mode, request_status

FROM sys.dm_tran_locks

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

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

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

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

Ø 使用已提交读快照级别

1. 启动SQL Server Management Studio并打开一个“新建查询”窗口。

2. 键入并执行以下语句来激活已提交读快照级别(此例的代码包含在示例文件ReadCommittedSnapshot1.sql and ReadCommittedSnapshot2.sql中)：

USE master;

ALTER DATABASE AdventureWorks

SET READ_COMMITTED_SNAPSHOT ON

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

USE AdventureWorks;

BEGIN TRAN

UPDATE Person.Contact

SET EmailAddress = 'uncommitted@email.at'

WHERE ContactID = 1;

4. 打开第二个查询窗口并执行以下语句来读取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，这是这一行最后提交的版本。不像没有快照的已提交读级别那样，这个查询不会被阻塞。

5. 关闭查询窗口2并切换到查询窗口1。

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

ROLLBACK TRAN

USE master;

ALTER DATABASE AdventureWorks

SET READ_COMMITTED_SNAPSHOT OFF

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

10.3.1.2 获取一致的可重复读操作

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

l 已提交读在读数据的时候使用共享锁，但在读操作完成后会立即释放这个锁。因此，其他事务可以更改刚被读过的数据。

l 已提交读快照读取最后一次提交的数据版本。当它第二次读数据的时候，最后一次提交的版本可能由于第二个事务已经提交了对数据的更改而变成一个新版本。

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

Ø 使用快照隔离级别

1. 启动SQL Server Management Studio并打开一个“新建查询”窗口。

2. 快照隔离级别需要在数据库中一次性地激活。激活之后，每个连接可以在需要的时候使用它。为了在AdventureWorks数据库中允许快照隔离级别，请执行以下语句(本示例的代码包含在示例文件SnapshotIsolation1.sql 和 SnapshotIsolation2.sql中)：

USE master;

ALTER DATABASE AdventureWorks

SET ALLOW_SNAPSHOT_ISOLATION ON;

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

USE AdventureWorks;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT SUM(LineTotal) as OrderTotal

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

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

USE AdventureWorks;

UPDATE Sales.SalesOrderDetail

SET OrderQty = 5

WHERE SalesOrderID = 43659

AND ProductID = 777

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

SELECT SUM(LineTotal) as OrderTotal

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

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

6. 提交这个事务并执行以下代码再次重复这个查询：

现在可看到，由于事务结束了，因此结果发生了变化。

COMMIT TRAN

SELECT SUM(LineTotal) as OrderTotal

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

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

ALTER DATABASE AdventureWorks

SET ALLOW_SNAPSHOT_ISOLATION OFF;

10.3.1.3 避免同时发生的数据更新

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

Ø 使用可重复读隔离级别

1. 启动SQL Server Management Studio并打开一个“新建查询”窗口。

2. 假设希望处理OrderID为43659的订单。首先，必须选择数据。为了防止其他事务更改正在读的数据，使用可重复读隔离。执行以下代码(此示例的代码包含在示例文件RepeatableReadIsolation1.sql 及RepeatableReadIsolation2.sql中)：

USE AdventureWorks;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

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

USE AdventureWorks;

UPDATE Sales.SalesOrderDetail

SET OrderQty = 5

WHERE SalesOrderID = 43659

AND ProductID = 777

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

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

INSERT INTO Sales.SalesOrderDetail

(SalesOrderID,CarrierTrackingNumber,

OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount)

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

图10.4 “取消执行查询”按钮

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

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

SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

COMMIT TRAN

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

7. 关闭SQL Server Management Studio。

为了确保事务不会读取幻像数据，可以通过锁定正在检查的数据范围来阻塞数据。但这会导致死锁问题。

10.3.2.1 锁定相关数据的一致性块

为了防止幻像读，可以使用可序列化隔离级别。这种隔离级别比可重复读级别更严格，不仅会锁定事务所读取的数据，还会锁定事务的读取范围。这通过在索引上使用一种称作范围锁的特殊锁类型来实现。范围锁会锁定在SELECT语句的WHERE子句中定义的范围。这些锁只能在关联索引存在时使用。如果没有关联索引，SQL Server只能用表级锁来阻止范围内的插入行为。因此，不是万不得已，不可使用可序列化隔离级别。

Ø 使用可序列化隔离级别

1. 启动SQL Server Management Studio并打开一个“新建查询”窗口。

2. 假设希望和以前一样查看同样的SalesOrderID。现在使用可序列化隔离级别来阻止更新数据并且阻止幻像读。键入并执行以下语句。此例的代码包含在示例文件SerializableIsolation1.sql和SerializableIsolation2.sql中：

USE AdventureWorks;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

3. 打开第二个查询窗口并尝试在查询窗口1处理的订单中插入一个新行。

INSERT INTO Sales.SalesOrderDetail

(SalesOrderID,CarrierTrackingNumber,

OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount)

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

4. 关闭查询窗口2，因为这个查询已被阻塞。可以看出。正如您已经看到的，可序列化隔离级别阻止在事务读取的范围内插入新行。

5. 执行COMMIT TRAN语句，然后关闭所有的查询窗口。

10.3.2.2 处理阻塞

通过前面的示例可以知道，在多用户的数据库系统中，阻塞是一个大问题。最少化阻塞是事务设计中急需关注的重要问题。为了将阻塞减至最少，应该遵守以下规则：

l 事务要尽量短

l 不要在事务之中请求用户输入

l 在读数据的时候考虑使用行版本管理

l 在事务中尽量访问最少量的数据。

l 尽可能地使用低的事务隔离级别。

如果应用程序不能像预期的那样执行并且可能是阻塞导致的，可以通过动态查询视图(Dynamic Management Views (DMVs))来监视阻塞。

Ø 通过DMV监视阻塞

让我们监视前面介绍可重复读隔离级别时用过的例子中的阻塞。

1. 启动SQL Server Management Studio并打开一个“新建查询”窗口。

2. 执行以下批在可重复读模式中开始一个事务。此示例的代码包含在示例文件MonitoringBlocking1.sql，MonitoringBlocking2.sql和MonitoringBlocking3.sql中。

USE AdventureWorks;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

3. 打开第二个查询窗口并执行以下UPDATE语句。它将被查询窗口1中的事务阻塞：

USE AdventureWorks;

UPDATE Sales.SalesOrderDetail

SET OrderQty = 5

WHERE SalesOrderID = 43659

AND ProductID = 777

4. 让UPDATE语句继续运行，打开第三个查询窗口。

5. 在第三个查询窗口中键入并执行以下语句来获取所有等待时间超过5秒的用户进程：

SELECT * FROM sys.dm_os_waiting_tasks

WHERE session_id > 49

AND wait_duration_ms > 5000

通过这个语句获取了所有会话ID大于49并由于各种原因被阻塞5秒以上的用户进程的信息。blocking_session_id列包含阻塞会话的会话id。注意结果中的会话id，将在步骤7中用到它。

6. 可以使用以下语句来获取阻塞进程正在尝试执行的语句的有关信息。这个语句联接了sys.dm_os_waiting_tasks和能够提供执行中请求后台信息的视图sys.dm_exec_requests。这个语句还使用了sys.dm_exec_sql_text()函数，该函数可以通过sys.dm_exec_request视图中提供的一个句柄来获取SQL 语句。执行以下代码：

SELECT (select SUBSTRING(text,statement_start_offset/2,

(case when statement_end_offset = -1 then

len(convert(nvarchar(max), text)) * 2 else

statement_end_offset end -statement_start_offset)/2)

from sys.dm_exec_sql_text(sql_handle)) as query_text

FROM

sys.dm_os_waiting_tasks wt

JOIN

sys.dm_exec_requests r

ON r.session_id = wt.session_id

WHERE r.session_id > 50

AND wait_duration_ms > 5000

7. 阻塞场景中牵涉到的连接的更多信息可以通过sys.dm_exec_connections视图获得。用您的阻塞或被阻塞会话中的会话id替换此查询的(在以下代码中是51)会话id：

SELECT *

FROM sys.dm_exec_connections

WHERE session_id = 51 --Replace with your id

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

更多信息有关动态查询视图的更多信息，请参见SQL Server Books Online中的主题 “Dynamic Management Views and Functions”。

10.3.2.3 处理死锁

死锁指的是会导致永久阻塞(如果不自动解决的话)的特殊阻塞场景。它发生在两个或更多的事务相互阻塞的时候。如果发生这种情况，每一个事务都在等待其他事务释放它们的锁。但是，这永远不会发生，因为其他事务也在等待。之所以称之为死锁，是因为事务永远不会释放它们所占用的锁。为了防止这种情况的发生，SQL Server会通过回滚其中一个事务并返回一个错误到连接的方式来自己解决这种问题以让其他的事务能够完成它们的工作。

Ø 制造一个简单的死锁场景

让我们制造一个简单的死锁场景来看一下SQL Server是如何管理死锁的。

1. 启动SQL Server Management Studio并打开一个“新建查询”窗口。

2. 键入并执行以下代码来创建一个小表并在不关闭事务的情况下插入数据。此示例的代码包含在示例文件Deadlock1.sql 和Deadlock2.sql中：

USE tempdb;

CREATE TABLE t1 (i int)

BEGIN TRAN

INSERT INTO t1 Values(1)

3. 打开第二个查询窗口并执行以下语句创建另一个小表，并在其中插入数据，然后尝试在查询窗口1中创建的表t1中更新数据。由于在查询窗口1中的事务没有被提交，因此这个事务将被阻塞。

USE tempdb;

CREATE TABLE t2 (i int)

BEGIN TRAN

INSERT INTO t2 Values(1)

UPDATE t1 SET i = 2

4. 让这个查询继续运行，我们切换回查询窗口1。执行以下UPDATE语句更新t2表，看一看会发生什么。

UPDATE t2 SET i = 2

在几秒钟后，其中一个事务被取消了并且返回了一个错误信息，如图10.5所示。

图10.5 返回错误消息

这种情况的发生是由于事务相互之间发生了阻塞。查询窗口1中的事务对表t1进行了锁定并且尝试更新表t2。在查询窗口2中的事务在表t2上有一个锁并尝试更新表t1。因此，这两个事务都将永远等待另一个先释放锁。SQL Server发现了这种情况并回滚死锁所涉及的事务的其中之一，然后向相应的连接抛出错误1205，借此解决了这个问题。

5. 关闭所有查询窗口。

为了防止并处理死锁，应该遵守以下原则：

l 遵守最少化阻塞的规则。阻塞越少，发生死锁的机会就越少。

l 在事务中要按一致的顺序访问对象。如果在以上示例中的两个事务都按一个顺序访问表，就不会发生死锁。因此，要在数据库中定义对所有表的访问顺序。

l 在错误处理程序中检查错误1205并在错误发生时重新提交事务。

l 在错误处理程序中加入一个过程将错误的详细信息写入日志。

如果遵守这些规则，就有机会阻止死锁。当死锁发生时，由于事务会自动提交，因此对于用户来说是未知的。但可以通过日志来监视死锁。