如何使用SQL Server高级数据表提示

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

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

责任编辑：德东

查看本文国际来源