了解SQL Server锁争用：NOLOCK 和 ROWLOCK 的秘密

http://blog.csdn.net/Atwind/archive/2007/10/19/1832844.aspx

关系型数据库，如SQL Server，使用锁来避免多用户修改数据时的并发冲突。当一组数据被某个用户锁定时，除非第一个用户结束修改并释放锁，否则其他用户就无法修改该组数据。

有些数据库，包括SQL Server，用锁来避免用户检索未递交的修改记录。在这些系统中，如果用户A在修改一组记录，则其他用户只有等用户A修改完毕了，才能检索。

数据库在每个物理层上设置锁：记录行(rows)， 数据页(pages, 上百万记录行)，扩展页(extends, 多个数据页)，整个表，甚至整个数据库。有些数据库(如Oracle等)只使用精细的行锁机制，而别的数据库，则使用在页面，扩展页，表和数据库上的较大 范围的锁机制。大多数数据库，包括SQL Server，同样支持行锁机制，但是经常使用的还是大范围锁机制。 这主要是因为管理锁需要付出高昂的代价。锁十分复杂而且数量很多，所以如果全都是 行锁的话，将是极为痛苦的：一百万行的数据更新就会轻易消耗巨大的内存，从而根本无法进行管理。

锁争用的描述

那些不仅仅使用行级锁的数据库使用一种称为混和锁(lock escalation)的技术来获取较高的性能。除非很明确知道是针对整个数据表，否则这些数据库的做法是开始使用行级锁， 然后随着修改的数据增多，开始使用大范围的锁机制。

不幸的是，这种混和锁的方法会产生和放大新的问题：死锁。如果两个用户以相反的顺序修改位于不同表的记录，而这两条记录虽然逻辑上不相关， 但是物理上是相邻的，操作就会先引发行锁，然后升级为页面锁。这样， 两个用户都需要对方锁定的东西，就造成了死锁。

例如：

用户A修改表A的一些记录，引发的页面锁不光锁定正在修改的记录，还会有很多其它记录也会被锁定。

用户B修改表B的一些记录，引发的页面锁锁定用户A和其它正在修改的数据。

用户A想修改用户B在表B中锁定(并不一定正在修改的)数据。

用户B想修改或者仅仅想访问用户A在表A中锁定(并不一定正在修改)的数据。

为 了解决该问题，数据库会经常去检测是否有死锁存在，如果有，就把其中的一个事务撤销，好让另一个事务能顺利完成。一般来说，都是撤销 那个修改数据量少的事务，这样回滚的开销就比较少。使用行级锁的数据库 很少会有这个问题，因为两个用户同时修改同一条记录的可能性极小，而且由于极其偶然的修改数据的顺序而造成的锁也少。

而且，数据库使用锁超时来避免让用户等待时间过长。查询超时的引入也是为了同样目的。我们可以重新递交那些超时的查询，但是这只会造成数据库 的堵塞。如果经常发生超时，说明用户使用SQL Server的方式有问题。正常 情况是很少会发生超时的。

在服务器负载较高的运行环境下，使用混合锁的SQL Server锁机制，表现不会很好。 原因是锁争用(Lock Contention)。锁争用造成死锁和锁等待问题。在一个多用户系统中，很多用户会同时在修改数据库，还有更多的用户在同时访问数据库，随时会产生 锁，用户 也争先恐后地获取锁以确保自己的操作的正确性，死锁频繁发生，这种情形下， 用户的心情可想而知。

确实，如果只有少量用户，SQL Server不会遇到多少麻烦。内部测试和发布的时候，由于用户较少， 也很难发现那些并发问题。但是当激发几百个并发，进行持续不断地INSERT,UPDATE，以及一些 DELETE操作时，如何观察是否有麻烦出现，那时候你就会不得不手忙脚乱地去阅读Oracle的文献。 不过我有一个解决办法，该方法只需要检查你的T-SQL代码，很少的调整和系统测试。用该方法教你进行适当的系统测试过程。

锁争用的解决方法

如果你在今年6月－8月之间访问Streamload.com，你可能会看到诸如“遇到死锁”，“锁超时”， “需要对象”等错误。这些错误都是由于锁争用引起的。在查阅大量文档和讨论后，我了解了这方面的知识，也就是上面所论述的内容，我再次叙述如下：

SQL Server开始是用行级锁的，但是经常会扩大为页面锁和表锁，最终造成死锁。

即使用户没有修改数据，SQL Server在SELECT的时候也会遇到锁。幸运的是，我们可以通过SQL Server 的两个关键字来手工处理：NOLOCK和ROWLOCK。

它们的使用方法如下：

SELECT COUNT(UserID)
FROM Users WITH (NOLOCK)
WHERE Username LIKE 'foobar'

和

UPDATE Users WITH (ROWLOCK)
SET Username = 'fred' WHERE Username = 'foobar'

NOLOCK的使用

NOLOCK可以忽略锁，直接从数据库读取数据。这意味着可以避开锁，从而提高性能和扩展性。但同时也意味着代码出错的可能性存在。你可能会读取到运行事务正在处理的无须验证的未递交数据。 这种风险可以量化。

如果是金融方面的代码或者一些非常规的总计(你 想绝对保证安全性)，你应该小心行事并且不使用这种技术。 但是我认为使用该技术会比你90%应用系统性能要好，当用户(或者是交互代码)发现一个未递交的修改时，使用技术会保证不会像未使用该技术那样引起大麻 烦。实际上，你可能发现你的大多数数据很少或者甚至不进行 修改的，这样我们就不会因为这些数据被锁住而浪费大量的时间。

例如，如果你想统计在2000 年6月份到8月份之间加入Streamload.com的所有用户，就没有理由去锁住任何记录： 2000年9月1号一到来，这个用户数就是确定的。又例如要列举在Streamload.com的文件列表：这种结果即使 不是100%的正确，也不是大问题。因为你要么不拥有该文件，当然也无所谓你是否能找到它，或者你确实拥有该文件，这种情况下你当然知道你是否修改了该文 件，以及该文件是否已经上传完毕了。

但是，如果这些数据的修改，对数据库来说是基础性的修改，或者这些数据对于用户来说，必须是百分之百保证 是修改正确的(例如帐单或者余额数据)，那么你不要使用该技术。

ROWLOCK的使用

ROWLOCK 告诉SQL Server只使用行级锁。ROWLOCK语法可以使用在SELECT,UPDATE和DELETE语句中，不过 我习惯仅仅在UPDATE和DELETE语句中使用。如果在UPDATE语句中有指定的主键，那么就总是会引发行级锁的。但是当SQL Server对几个这种UPDATE进行批处理时，某些数据正好在同一个页面(page)，这种情况在当前情况下 是很有可能发生的，这就象在一个目录中，创建文件需要较长的时间，而同时你又在更新这些文件。当页面锁引发后，事情就开始变得糟糕了。而如果在 UPDATE或者DELETE时，没有指定主键，数据库当然认为很多数据会收到影响，那样 就会直接引发页面锁，事情同样变得糟糕。

通过指定使用行级锁，这种情况可以得到避免。但是需要小心的是，如果你错误地使用在过多行上，数据库并不会聪明到自动将行级锁升级到页面锁，服务器也会因为行级锁的开销而消耗大量的内存和CPU， 直至无法响应。尤其主要留意的是 企业管理器中"管理/当前活动"(Management/Current Activity)这一项。该项会花较长的时间来载入锁的信息。这些信息 时十分有用的，当你使用行级锁后，你如果在"锁/处理"(Locks/Processes)下看到几百个锁，一点都不奇怪，而恰恰应该庆幸锁超时和死锁的 问题减少了。

注意事项

我认为SQL Server倾向于使用NOLOCK关键字，而ROWLOCK关键字由用户根据情况自行决定。你可以仅仅在 SELECT语句中使用NOLOCK，这些SELECT语句场合包括INNER查询，以及在INSERT语句中的SELECT使用，在连接查询下也可以使 用，例如：

SELECT COUNT(Users.UserID)
FROM Users WITH (NOLOCK)
JOIN UsersInUserGroups WITH (NOLOCK) ON
Users.UserID = UsersInUserGroups.UserID

NOLOCK 和 ROWLOCK的使用效果

很难去量化在使用NOLOCK 和ROWLOCK后，Streamload.com或者你的网站性能到底改善了多少。 不过在使用NOLOCK和ROWLOCK前，Streamload.com的速度很慢，而且经常无法使用，以及很不稳定。使用后，就变得快速、容易访问以 及稳定了。两者简直就是天壤之别。这些改变当然无法在 关于锁的文档中很难找到。那些文档会建议你重写你的应用，当表数据被使用，锁产生了(没错，就是这样)，然后你应该使用小事务并且以批处理的形式执行(不 错，实际经验就是如此)，使用低级别的隔离措施 (也没错，NOLOCK就是一个极端的例子)，还建议你有限的连接，从而让处理器进行合作(好复杂的描述，而且总觉得怪怪的不像个好点子)。我不知道是否 用数据库咨询师会提到本文中的技术(或类似的技术)， 但是我只想说的是，Streamload.com的运行状况的确因为该技术得到了改善。如果你遇到了锁争用的问题，也可以试试NOLOCK和 ROWLOCK。

申明

是否使用NOLOCK 和ROWLOCK，需要自行判断，并谨慎运用。我用该技术的方法是通过查看我的存储过程和即时查询语句，在我自己的理解上来觉得哪里用和如何用。我需要判 断如果用NOLOCK 而引起一些返回的不准确，或者ROWLOCK是否会造成太多的锁，这些情况出现时，对于访问者或者使用者来说，是否是可以接受的。在大多数情况下，我认为 是没有问题的，但是也许你的代码不适用， 你需要小心对待。你需要创建一些独立的过程，是否加锁，如何加锁，以作为对比。当UPDATE或者 DELETE查询影响到很多数据行时，你在使用PAGELOCK,TABLOCK时也会遇到别的问题。

 附：

－－－－－－－－－－－－－－－

　UPDLOCK

　　读取表时使用更新锁，而不使用共享锁，并将锁一直保留到语句或事务的结束。UPDLOCK 的优点是允许您读取数据（不阻塞其它事务）并在以后更新数据，同时确保自从上次读取数据后数据没有被更改。

　　这是SqlServer2000中对更新锁的说明.

　　当我们用UPDLOCK来读取记录时可以对取到的记录加上更新锁，从而加上锁的记录在其它的线程中是不能更改的只能等本线程的事务结束后才能更改，我如下示例：

BEGIN TRANSACTION --开始一个事务
SELECT Qty
 FROM myTable WITH (UPDLOCK)
 WHERE Id in (1,2,3)
 UPDATE myTable SET Qty = Qty - A.Qty
 FROM myTable  AS A 
 INNER JOIN  @_Table AS B ON A.ID = B.ID
COMMIT TRANSACTION --提交事务

　　这样在更新时其它的线程或事务在这些语句执行完成前是不能更改ID是１，２，３的记录的．其它的都可以修改和读，１，２，３的只能读，要是修改的话只能等这些语句完成后才能操作.从而保证的数据的修改正确.