我先前的SQL Server专栏中介绍了两种类型的数据表提示（table hints）：NOLOCK和READPAST。

这篇文章将探讨在SQL Server中查询的时候，使用其它三种类型的数据表提示的优点和缺点，这三种类型分别是：READCOMMITTED、REPEATABLEREAD和SERIALIZABLE。

下面我们将通过一些实际的操作来了解数据表提示，我将创建一个表，并在这个表中插入一些数据。创建表和插入数据的代码都在Listing A中。

READCOMMITTED

使用SQL Server数据库表默认的行为是locking。当读取数据的时候，这个隔离类型使用共享锁，以便确保只有在数据被提交到数据库之后才能读取。使用这些锁是因为当对数据更新的时候需要排它性的锁，这种锁可以阻挡那些试图使用共享锁的所有读者。

对于绝大部分查询，这个分离层次能够很好的平衡数据库的一致性和同时性；然而，它也是有缺点的，它的缺点就是不可重读（Non-Repeatable read）和幻读（phantom read）。

下面就是使用READCOMMITTED的一个例子。在这个例子中，你需要有查询编辑窗口或者数据库连接到Session 1和Session 2.

Session1:

BEGIN TRANSACTION

UPDATE SalesHistory

SET SalePrice = SalePrice + 1

WHERE SaleID = 201

Session2:

BEGIN TRANSACTION

SELECT * FROM SalesHistory WITH(READCOMMITTED)

这个查询大约返回表的前200条记录。（它可能不会准确的返回200条记录，那是因为第一个会话可能使用page锁，这种锁会把数据的某页锁住，而不是锁住单条记录。）一旦第一个200或者大约这么多条记录被返回了，查询将继续运行，但是不会返回另外的行了。

这个查询会等待，直到从第一个更新语句开始就被锁住的事务释放掉它的锁。我能看到第一个200或者大约这么多条记录的数据。注意，我创建的那个事务一直是打开的。

Session1:

UPDATE SalesHistory

SET SalePrice = SalePrice + 2

WHERE SaleID = 1

COMMIT TRANSACTION

这个更新语句一直等待最初的事务范围。我用SaleID=1来更新记录，这样在Session 2中的事务将返回一行记录。一旦我提交了这个事务，Session 2的查询操作就完成了。

Session2:

SELECT * FROM SalesHistory WITH(READCOMMITTED)

COMMIT TRANSACTION

我又一次进行同样的查询。虽然它一直在同一个事务中，但是这时候，当SaleID = 1时SalePrice的返回值就不同了。这就是所谓的不可重读（Non-Repeatable read）。

我在同一事务中操作的数据如果在事务外部被改变了的话，可能会对后面的事务产生不利影响。这就是这种事务隔离水平的两个可能的副作用中的一个——另外一个类型是“幻影”读（phantom read）。（我将在REPEATABLEREAD部分详细的介绍幻读。）

REPEATABLEREAD

REPEATABLEREAD数据表提示可以阻止不可重读（Non-Repeatable reads）。下面这个例子就向我们展示了这个数据表提示的操作。（这实际上是与READCOMMITTED部分相同的例子，所不同的只是它们的结果。）

Session1:

BEGIN TRAN

UPDATE SalesHistory

SET SalePrice = SalePrice + 1

WHERE SaleID = 201

Session2:

BEGIN TRAN

SELECT * FROM SalesHistory WITH(REPEATABLEREAD)

Session1:

UPDATE SalesHistory

SET SalePrice = SalePrice + 2

WHERE SaleID = 1

在Session 2中使用了REPEATABLEREAD数据表提示，这个查询引起了问题。当这个数据表提示发布的时候，对于已经由事务返回的记录，它确保这些事务不能再被外部的事务改变了。

这样就保证了这些记录可以被重复的读，也能保证数据不被改变。上面的语句可能会引起死锁，SQL Server数据库引擎将选出一个会话作为死锁的牺牲者。

虽然REPEATABLEREAD隔离层预防了不可重读（Non-Repeatable），但是它没有预防幻读。它通过事务处理保护那些已经被返回的数据，但是它不能在先前的返回结果集中保护新的记录。

当某个事务有某张表的某条记录或者整个表都在另一个事务中被锁住了的时候，就会出现幻读；或者是当隔离事务将一行记录插入到某个被锁的表中的时候，也会出现幻读。

虽然新记录以前并不存在于最初读取的记录中，但是由第一个事务产生的所有并发读都能看到新记录。现在，让我们来看看幻读是如何发生的。

Session1:

BEGIN TRANSACTION

SELECT COUNT(*) FROM SalesHistory WITH(REPEATABLEREAD)

上面的脚本定义了一个事务，在SalesHistory表中读取所有的数据，使用了REPEATABLEREAD数据表提示。这个事务将锁住整个表。

Session2:

BEGIN TRANSACTION

INSERT INTO SalesHistory(Product, SaleDate, SalePrice)

SELECT 'Computer', GETDATE(), 500

COMMIT TRANSACTION

这个脚本创建了一个事务，再将一条记录插入到锁定的表中，最后提交事务：

Session1:

SELECT COUNT(*) FROM SalesHistory WITH(REPEATABLEREAD)

COMMIT TRANSACTION

如果我在第一个会话窗口中运行同样的SELECT语句，我将得到所有的300条记录，这300条记录是由第一个查询得到的，但是在Session 2窗口中插入的附加记录也被查出来了。幻读发生了。

SERIALIZABLE

先前的例子从高并发性到低并发性，从低一致性到高一致性。在我的上一篇文章中，我讨论了如何使用NOLOCK不锁住任何正在被读的记录，这样就能在数据库中确保了最高的并发性（例如，可以指定能读和修改数据的用户数目）。NOLOCK也允许：最低的一致性，脏读，不可重读和幻读。

SERIALIZABLE数据表提示相对NOLOCK数据表提示来说是在频谱的另一端。当你使用SERIALIZABLE数据表提示时（也被称作HOLDLOCK），它确保在当前事务中没有其它事务能修改或者读取未提交的数据。

换句话说，事务在完成它的工作之前必须等待其它事务完成。这就彻底的限制了数据库的并发性，也强调了数据库的一致性。

再来看看我在上面所使用的例子：

Session1:

BEGIN TRANSACTION

SELECT COUNT(*) FROM SalesHistory WITH(SERIALIZABLE)

当用SERIALIZABLE数据表提示锁住表的时候，这个脚本返回了表中所有的300条记录。

Session2:

BEGIN TRANSACTION

INSERT INTO SalesHistory(Product, SaleDate, SalePrice)

SELECT 'Computer', GETDATE(), 500

COMMIT TRANSACTION

这个会话试图将一条记录插入到SalesHistory表中，而这个表当前在Session 1中被锁住了。由于阻塞，会话将继续等待直到Session 1中的事务完成。

这个数据表提示预防了我先前所讨论过的所有其它并发性的副作用——脏读，不可重复性和幻读。你为这个高数据一致性所花费的代价就是，其它事务必须等待被锁住的事务完成。由于降低了同步性，所以它会彻底影响性能。

Recap

较高同步性增加了让更多用户同时访问数据的能力，但是它也增加了可能发生的副作用的数目。然而，较高的一致性降低了同步性的影响，由于需要维持锁，它必须以更多的资源消耗为代价，同时，它也增加了一个事务将阻塞另一个事务的可能性。

责任编辑：德东

查看本文国际来源

IF OBJECT_ID('SalesHistory')>0      

DROP TABLE SalesHistory;

GO

CREATE TABLE [dbo].[SalesHistory]

(      

      [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,      

      [Product] [char](150) NULL,      

            [SaleDate] [datetime] NULL,      

            [SalePrice] [money] NULL

)

GO

DECLARE @i SMALLINT

SET @i = 1

WHILE (@i <=100)

BEGIN                  

      INSERT INTO SalesHistory               

      (Product, SaleDate, SalePrice)                  

      VALUES      

      ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))      

      INSERT INTO SalesHistory                

      (Product, SaleDate, SalePrice)      

      VALUES             

      ('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))                  

      INSERT INTO SalesHistory                  

      (Product, SaleDate, SalePrice)          

      VALUES            

      ('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))                  

      SET @i = @i + 1

END

GO