了解SQL Server锁争用:NOLOCK 和 ROWLOCK 的使用

转载 2012年03月23日 13:48:16

锁争用的描述

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

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

例如:

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

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

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

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

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

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

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

确实,如果只有少量用户,SQLServer不会遇到多少麻烦。内部测试和发布的时候,由于用户较少,也很难发现那些并发问题。但是当激发几百个并发,进行持续不断地INSERT,UPDATE,以及一些 DELETE操作时,如何观察是否有麻烦出现,那时候你就会手忙脚乱地去解锁。

锁争用的解决方法

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语句中有指定的主键,那么就总是会引发行级锁的。但是当SQLServer对几个这种UPDATE进行批处理时,某些数据正好在同一个页面(page),这种情况在当前情况下是很有可能发生的,这就象在一个目录中,创建文件需要较长的时间,而同时你又在更新这些文件。当页面锁引发后,事情就开始变得糟糕了。而如果在UPDATE或者DELETE时,没有指定主键,数据库当然认为很多数据会收到影响,那样 就会直接引发页面锁,事情同样变得糟糕。

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

注意事项

我认为SQLServer倾向于使用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

SQL秘笈 : NOLOCK、ROWLOCK、UPDLOCK的使用!

NOLOCK的使用   NOLOCK可以忽略锁,直接从数据库读取数据。这意味着可以避开锁,从而提高性能和扩展性。但同时也意味着代码出错的可能性存在。你可能会读取到运行事务正在处理的无须验证的未递交数...
  • ufocode
  • ufocode
  • 2015年11月17日 20:40
  • 2720

有关数据库 行 锁 的几个问题(rowlock)

有关数据库 行 锁 的几个问题(rowlock) 行锁的基本说明: SELECT au_lname FROM authors WITH (NOLOCK) 锁定提示  ...

(走向DBA[MSSQL篇] - 从SQL语句的角度提高数据库的访问性能)一些SQL查询语句应加上nolock

http://kb.cnblogs.com/page/124787/#s8   最近公司来一个非常虎的DBA,10几年的经验,这里就称之为蔡老师吧,在征得我们蔡老同意的前提下 ,我们来分享一下蔡老...
  • cynhafa
  • cynhafa
  • 2012年08月06日 11:29
  • 2563

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

关系型数据库,如SQL Server,使用锁来避免多用户修改数据时的并发冲突。当一组数据被某个用户锁定时,除非第一个用户结束修改并释放锁,否则其他用户就无法修改该组数据。 有些数据库,包括SQL Se...
  • Atwind
  • Atwind
  • 2007年10月19日 15:00
  • 11122

了解SQL Server锁争用:NOLOCK 和 ROWLOCK 的使用

最近一年老是受到MSSQLSERVER数据库阻塞的困扰,单表记录已突破400万条,除了分库似乎没有别的方法,使用分区存储,数据库又要重新设计,考虑到程序中使用较多的就是查询,而查询会受到更新与删除的影...

大家一般写SQL语句会使用nolock 或 rowlock这种技术么 ?

版主 高级会员 精华贴数 4 专家积分 94 技术积分 22640 社区积分 4717 注册时间 2003-1-23 认证徽章 ...

王老师讲解:SQL Server 2005数据库nolock的使用【来自Aisino_BBS】

前两天去安徽航天信息解决A6系统的性能问题,去之前进销存项目组研发人员小苏和小卢把他们认为性能 有问题的SQL已经发给了我,我把优化方法提交给了他们。本以为到那之后会非常轻松,但还是有些插曲在其中。 ...

SQL Server 2008 with(nolock)的使用原因及数据库锁,隔离级别简介

首先先要了解并发访问:同一时间有多个用户访问同一资源,并发用户中如果有用户对资源做了修改,此时就会对其它用户产生某些不利的影响,例如: 1:脏读,一个用户对一个资源做了修改,此时另外一个用户正好读取...

SQL Server 中WITH (NOLOCK)浅析(大自然的搬运工)

概念介绍    开发人员喜欢在SQL脚本中使用WITH(NOLOCK), WITH(NOLOCK)其实是表提示(table_hint)中的一种。它等同于 READUNCOMMITTED 。 具体的...
  • DXB601
  • DXB601
  • 2017年06月20日 08:13
  • 315

SQL Server 中的 NOLOCK 到底是什么意思

http://blog.sina.com.cn/s/blog_7d3b18a50100rfwg.html 以前遇到过,但仅限于听同事说加上NOLOCK好一些,今天仔细研究测试了下,终于理解了...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:了解SQL Server锁争用:NOLOCK 和 ROWLOCK 的使用
举报原因:
原因补充:

(最多只允许输入30个字)