SQL Server 中WITH (NOLOCK)浅析 - 文章 - 伯乐在线
& SQL Server 中WITH (NOLOCK)浅析
开发人员喜欢在SQL脚本中使用WITH(NOLOCK), WITH(NOLOCK)其实是表提示（table_hint）中的一种。它等同于 READUNCOMMITTED 。 具体的功能作用如下所示（摘自MSDN）：
1： 指定允许脏读。不发布共享锁来阻止其他事务修改当前事务读取的数据，其他事务设置的排他锁不会阻碍当前事务读取锁定数据。允许脏读可能产生较多的并发操作，但其代价是读取以后会被其他事务回滚的数据修改。这可能会使您的事务出错，向用户显示从未提交过的数据，或者导致用户两次看到记录（或根本看不到记录）。有关脏读、不可重复读和幻读的详细信息，请参阅并发影响。
2： READUNCOMMITTED 和 NOLOCK 提示仅适用于数据锁。所有查询（包括那些带有 READUNCOMMITTED 和 NOLOCK 提示的查询）都会在编译和执行过程中获取 Sch-S（架构稳定性）锁。因此，当并发事务持有表的 Sch-M（架构修改）锁时，将阻塞查询。例如，数据定义语言 (DDL) 操作在修改表的架构信息之前获取 Sch-M 锁。所有并发查询（包括那些使用 READUNCOMMITTED 或 NOLOCK 提示运行的查询）都会在尝试获取 Sch-S 锁时被阻塞。相反，持有 Sch-S 锁的查询将阻塞尝试获取 Sch-M 锁的并发事务。有关锁行为的详细信息，请参阅锁兼容性（数据库引擎）。
3: 不能为通过插入、更新或删除操作修改过的表指定 READUNCOMMITTED 和 NOLOCK。SQL Server 查询优化器忽略 FROM 子句中应用于 UPDATE 或 DELETE 语句的目标表的 READUNCOMMITTED 和 NOLOCK 提示。
功能与缺陷
使用WIHT(NOLOCK)有利也有弊，所以在决定使用之前，你一定需要了解清楚WITH(NOLOCK)的功能和缺陷，看其是否适合你的业务需求,不要觉得它能提升性能，稀里糊涂的就使用它。
1：使用WITH(NOLOCK)时查询不受其它排他锁阻塞
打开会话窗口1，执行下面脚本，不提交也不回滚事务，模拟事务真在执行过程当中
BEGIN TRAN
UPDATE TEST SET NAME='Timmy' WHERE OBJECT_ID =1;
--ROLLBACK
BEGIN TRAN&&&&&&& UPDATE TEST SET NAME='Timmy' WHERE OBJECT_ID =1;&&&&&&& --ROLLBACK
打开会话窗口2，执行下面脚本，你会发现执行结果一直查询不出来（其实才两条记录）。当前会话被阻塞了
SELECT * FROM TEST;
<div class="crayon-num" data-line="crayon-5afef
SELECT * FROM TEST;
打开会话窗口3，执行下面脚本，查看阻塞情况，你会发现在会话2被会话1给阻塞了，会话2的等待类型为LCK_M_S：“当某任务正在等待获取共享锁时出现”
SELECT wt.blocking_session_id
AS BlockingSessesionId
,sp.program_name
AS ProgramName
,COALESCE(sp.LOGINAME, sp.nt_username)
AS HostName
,ec1.client_net_address
AS ClientIpAddress
AS DatabaseName
,wt.wait_type
AS WaitType
,ec1.connect_time
AS BlockingStartTime
,wt.WAIT_DURATION_MS/1000
AS WaitDuration
,ec1.session_id
AS BlockedSessionId
AS BlockedSQLText
AS BlockingSQLText
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db
ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections ec1
ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2
ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses sp
ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
<div class="crayon-num" data-line="crayon-5afef<div class="crayon-num crayon-striped-num" data-line="crayon-5afef<div class="crayon-num" data-line="crayon-5afef<div class="crayon-num crayon-striped-num" data-line="crayon-5afef<div class="crayon-num" data-line="crayon-5afef<div class="crayon-num crayon-striped-num" data-line="crayon-5afef<div class="crayon-num" data-line="crayon-5afef<div class="crayon-num crayon-striped-num" data-line="crayon-5afef<div class="crayon-num" data-line="crayon-5afef<div class="crayon-num crayon-striped-num" data-line="crayon-5afef<div class="crayon-num" data-line="crayon-5afef<div class="crayon-num crayon-striped-num" data-line="crayon-5afef<div class="crayon-num" data-line="crayon-5afef<div class="crayon-num crayon-striped-num" data-line="crayon-5afef<div class="crayon-num" data-line="crayon-5afef<div class="crayon-num crayon-striped-num" data-line="crayon-5afef<div class="crayon-num" data-line="crayon-5afef<div class="crayon-num crayon-striped-num" data-line="crayon-5afef<div class="crayon-num" data-line="crayon-5afef<div class="crayon-num crayon-striped-num" data-line="crayon-5afef<div class="crayon-num" data-line="crayon-5afef<div class="crayon-num crayon-striped-num" data-line="crayon-5afef<div class="crayon-num" data-line="crayon-5afef<div class="crayon-num crayon-striped-num" data-line="crayon-5afef
SELECT wt.blocking_session_id&&&&&&&&&&&&&&&&&&&&AS BlockingSessesionId&&&&&&&&,sp.program_name&&&&&&&&&&&&&&&&&&&&&&&&&& AS ProgramName&&&&&&&&,COALESCE(sp.LOGINAME, sp.nt_username)&&&& AS HostName&&&&&&&&&&&&,ec1.client_net_address&&&&&&&&&&&&&&&&&&&&AS ClientIpAddress&&&&&&&&,db.name&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& AS DatabaseName&&&&&&&&&&&&&&&&,wt.wait_type&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&AS WaitType&&&&&&&&&&&&&&&&&&&&&&&&&&&&,ec1.connect_time&&&&&&&&&&&&&&&&&&&&&&&&&&AS BlockingStartTime&&&&&&&&,wt.WAIT_DURATION_MS/1000&&&&&&&&&&&&&&&&&&AS WaitDuration&&&&&&&&,ec1.session_id&&&&&&&&&&&&&&&&&&&&&&&&&&&&AS BlockedSessionId&&&&&&&&,h1.TEXT&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& AS BlockedSQLText&&&&&&&&,h2.TEXT&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& AS BlockingSQLText&&FROM sys.dm_tran_locks AS tl&&INNER JOIN sys.databases db&&&&ON db.database_id = tl.resource_database_id&&INNER JOIN sys.dm_os_waiting_tasks AS wt&&&&ON tl.lock_owner_address = wt.resource_address&&INNER JOIN sys.dm_exec_connections ec1&&&&ON ec1.session_id = tl.request_session_id&&INNER JOIN sys.dm_exec_connections ec2&&&&ON ec2.session_id = wt.blocking_session_id&&LEFT OUTER JOIN master.dbo.sysprocesses sp&&&&ON SP.spid = wt.blocking_session_id&&CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1&&CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
此时查看会话1（会话1的会话ID为53，执行脚本1前，可以用SELECT @@spid查看会话ID）的锁信息情况，你会发现表TEST(ObjId=)持有的锁信息如下所示
打开会话窗口4，执行下面脚本.你会发现查询结果很快就出来，会话4并不会被会话1阻塞。
SELECT * FROM TEST WITH(NOLOCK)
SELECT * FROM TEST WITH(NOLOCK)
从上面模拟的这个小例子可以看出，正是由于加上WITH(NOLOCK)提示后，会话1中事务设置的排他锁不会阻碍当前事务读取锁定数据，所以会话4不会被阻塞，从而提升并发时查询性能。
2：WITH(NOLOCK) 不发布共享锁来阻止其他事务修改当前事务读取的数据，这个就不举例子了。
本质上WITH(NOLOCK)是通过减少锁和不受排它锁影响来减少阻塞，从而提高并发时的性能。所谓凡事有利也有弊，WITH(NOLOCK)在提升性能的同时，也会产生脏读现象。
如下所示，表TEST有两条记录，我准备更新OBJECT_ID=1的记录，此时事务既没有提交也没有回滚
BEGIN TRAN
UPDATE TEST SET NAME='Timmy' WHERE OBJECT_ID =1;
--ROLLBACK
<div class="crayon-num" data-line="crayon-5afef<div class="crayon-num crayon-striped-num" data-line="crayon-5afef<div class="crayon-num" data-line="crayon-5afef<div class="crayon-num crayon-striped-num" data-line="crayon-5afef<div class="crayon-num" data-line="crayon-5afef
BEGIN TRAN &UPDATE TEST SET NAME='Timmy' WHERE OBJECT_ID =1; &--ROLLBACK
此时另外一个会话使用WITH(NOLOCK)查到的记录为未提交的记录值
假如由于某种原因，该事务回滚了，那么我们读取到的OBJECT_ID=1的记录就是一条脏数据。
脏读又称无效数据的读出，是指在数据库访问中，事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的。
WITH(NOLOCK)使用场景
什么时候可以使用WITH(NOLOCK)? 什么时候不能使用WITH(NOLOCK),这个要视你系统业务情况，综合考虑性能情况与业务要求来决定是否使用WITH(NOLOCK), 例如涉及到金融或会计成本之类的系统，出现脏读那是要产生严重问题的。关键业务系统也要慎重考虑。大体来说一般有下面一些场景可以使用WITH(NOLOCK)
1: 基础数据表，这些表的数据很少变更。
2：历史数据表，这些表的数据很少变更。
3：业务允许脏读情况出现涉及的表。
4：数据量超大的表，出于性能考虑，而允许脏读。
另外一点就是不要滥用WITH(NOLOCK),我发现有个奇怪现象，很多开发知道WITH(NOLOCK),但是有不了解脏读，习惯性的使用WITH(NOLOCK)。
WITH(NOLOCK)与 NOLOCK区别
为了搞清楚WITH(NOLOCK)与NOLOCK的区别，我查了大量的资料，我们先看看下面三个SQL语句有啥区别
SELECT * FROM TEST NOLOCK
SELECT * FROM TEST (NOLOCK);
SELECT * FROM TEST WITH(NOLOCK);
上面的问题概括起来也就是说NOLOCK、(NOLOCK)、 WITH(NOLOCK)的区别：
1： NOLOCK这样的写法，其实NOLOCK其实只是别名的作用，而没有任何实质作用。所以不要粗心将(NOLOCK)写成NOLOCK
2：（NOLOCK）与WITH(NOLOCK)其实功能上是一样的。(NOLOCK)只是WITH(NOLOCK)的别名,但是在SQL Server 2008及以后版本中，(NOLOCK)不推荐使用了，&#8221;不借助 WITH 关键字指定表提示”的写法已经过时了。 具体参见MSDN http://msdn.microsoft.com/zh-cn/library/msSQL.100%29.aspx
2.1 至于网上说WITH(NOLOCK)在SQL SERVER 2000不生效，我验证后发现完全是个谬论。
2.2 在使用链接服务器的SQL当中，(NOLOCK)不会生效，WITH(NOLOCK)才会生效。如下所示
消息 4122，级别 16，状态 1，第 1 行
Remote table-valued function calls are not allowed.
WITH(NOLOCK)会不会产生锁
很多人误以为使用了WITH(NOLOCK)后，数据库库不会产生任何锁。实质上，使用了WITH(NOLOCK)后，数据库依然对该表对象生成Sch-S（架构稳定性）锁以及DB类型的共享锁， 如下所示，可以在一个会话中查询一个大表，然后在另外一个会话中查看锁信息(也可以使用SQL Profile查看会话锁信息)
不使用WTIH(NOLOCK)
使用WITH(NOLOCK)
从上可以看出使用WITH(NOLOCK)后，数据库并不是不生成相关锁。 对比可以发现使用WITH(NOLOCK)后，数据库只会生成DB类型的共享锁、以及TAB类型的架构稳定性锁.
另外，使用WITH(NOLOCK)并不是说就不会被其它会话阻塞，依然可能会产生Schema Change Blocking
会话1：执行下面SQL语句，暂时不提交，模拟事务正在执行
BEGIN TRAN
ALTER TABLE TEST ADD Grade VARCHAR(10) ;
<div class="crayon-num" data-line="crayon-5afef<div class="crayon-num crayon-striped-num" data-line="crayon-5afef<div class="crayon-num" data-line="crayon-5afef
BEGIN TRAN &&&ALTER TABLE TEST ADD Grade VARCHAR(10) ;
会话2：执行下面语句，你会发现会话被阻塞，截图如下所示。
SELECT * FROM TEST WITH(NOLOCK)
SELECT * FROM TEST WITH(NOLOCK)
可能感兴趣的话题
关于伯乐在线博客
在这个信息爆炸的时代，人们已然被大量、快速并且简短的信息所包围。然而，我们相信：过多“快餐”式的阅读只会令人“虚胖”，缺乏实质的内涵。伯乐在线内容团队正试图以我们微薄的力量，把优秀的原创文章和译文分享给读者，为“快餐”添加一些“营养”元素。
新浪微博：
推荐微信号
（加好友请注明来意）
&#8211; 好的话题、有启发的回复、值得信赖的圈子
&#8211; 分享和发现有价值的内容与观点
&#8211; 为IT单身男女服务的征婚传播平台
&#8211; 优秀的工具资源导航
&#8211; 翻译传播优秀的外文文章
&#8211; 国内外的精选文章
&#8211; UI,网页，交互和用户体验
&#8211; 专注iOS技术分享
&#8211; 专注Android技术分享
&#8211; JavaScript, HTML5, CSS
&#8211; 专注Java技术分享
&#8211; 专注Python技术分享
& 2018 伯乐在线没有更多推荐了，
不良信息举报
举报内容：
有关数据库 行 锁 的几个问题（rowlock）
举报原因：
原文地址：
原因补充：
最多只允许输入30个字
加入CSDN，享受更精准的内容推荐，与500万程序员共同成长！①&&&&&锁：每条SQL语句
②&&&&&隔离：事务
未确认的读取（脏读）
不一致的分析（非重复读）：多次读取相同的数据（行）不一致（其他用户更改update）
幻像读：多次读取有不存在和新增的数据（其他用户插入insert或删除delete）
不可重复读取
未提交读(read&uncommitted)
如果其他事务更新，不管是否提交，立即执行
提交读(read&committed默认)
读取提交过的数据。如果其他事务更新没提交，则等待
可重复读(repeatable&read)
查询期间，不允许其他事务update
可串行读(serializable)
查询期间，不允许其他事务insert或delete
假设存在表A，如下所示
打开查询分析器并打开两个连接，分别输入如下两个事务：
SET&TRANSACTION&ISOLATION&LEVEL&READ&Committed
begin&tran
update&A&set&A2&=&20&where&A1&=&11
waitfor&delay&'00:00:10'
rollback&tran
SET&TRANSACTION&ISOLATION&LEVEL&READ&Committed
select&*&from&A&where&A1&=&11
如果先运行事务，然后紧接着运行事务，则事务要等待10秒钟（一个连接在修改数据块时别的连接也不能查询这个数据块，直到解锁。反之亦然：读的时候不能写和修改）。
如果把事务改为如下
SET&TRANSACTION&ISOLATION&LEVEL&READ&UNCommitted
select&*&from&A&where&A1&=&11
那么事务不需等待，立即执行（可以看出READ&UNCommitted事务select不对数据发出共享锁）
锁：(这里主要讲解共享锁和排他锁两种经常用到的锁)
共享锁主要是为了共享读（select），如果存在事务（一个或多个）拥有对表中数据（关于锁数据的多少，视锁的粒度而定）的共享锁，不允许对锁定的数据进行更新(update)（从锁的角度讲，即不允许事务获取排他锁，要等到所有的共享锁都释放掉）。反之，如果事务对数据已经具有排他锁（只能有一个），其他的事务就不能对锁定的数据获取共享锁和排他锁（即排他锁与共享锁不能兼容，更多信息请查看锁兼容性），在此特别强调一下锁定的数据，因为有的资料上讲解到一个连接写的时候，另一个连接可以写，实际上写的这种情况是各个连接的读写的数据不是相同的行，也就是说各个连接锁定的数据不同。
根据以上分析，我们总结为六个字为共享读，排他写。
了解了锁的情况之后，又涉及到一个问题。事务究竟要保持锁多久呢?
一般来说，共享锁的锁定时间与事务的隔离级别有关，如果隔离级别为Read&Committed的默认级别，只在读取(select)的期间保持锁定，即在查询出数据以后就释放了锁；如果隔离级别为更高的Repeatable&read或Serializable，直到事务结束才释放锁。另说明，如果select语句中指定了HoldLock提示，则也要等到事务结束才释放锁。
排他锁直到事务结束才释放。
做出了以上分析，现在我们可能会存在这样的疑问，到底在执行SQL语句的时候发出什么样的锁呢，这就由事务的隔离级别决定了。一般情况，读语句(select)发出共享锁，写语句(update,insert,delete)发出排他锁。但是，如果这样不能满足我们的要求怎么办呢，有没有更多选择呢，别急，SQLserver为我们提供了锁定提示的概念。
&&&&&&&锁定提示对SQL语句进行特别指定，这个指定将覆盖事务的隔离级别。下面对各个锁定提示分别予以介绍（更多资料请查看SQLserver的联机帮助），笔者做出了以下分类。
①&&&&&READUNCOMMITTED：不发出锁
②&&&&&READCOMMITTED：发出共享锁，保持到读取结束
③&&&&&REPEATABLEREAD：发出共享锁，保持到事务结束
④&&&&&SERIALIZABLE：发出共享锁，保持到事务结束
①&&&&&NOLOCK：不发出锁。等同于READUNCOMMITTED
②&&&&&HOLDLOCK：发出共享锁，保持到事务结束。等同于SERIALIZABLE
③&&&&&XLOCK：发出排他锁，保持到事务结束。
④&&&&&UPDLOCK：发出更新锁，保持到事务事务结束。（更新锁：不阻塞别的事物，允许别的事物读数据（即更新锁可与共享锁兼容），但他确保自上次读取数据后数据没有被更新）
⑤&&&&&READPAST：发出共享锁，但跳过锁定行，它不会被阻塞。适用条件：提交读的隔离级别，行级锁，select语句中。
①&&&&&ROWLOCK：行级锁
②&&&&&PAGLOCK：页级锁
③&&&&&TABLOCK：表锁
④&&&&&TABLOCKX：表排他锁
讲解完锁后，下面结合一个具体实例，具体看一下锁的使用。
&&&&&&&在很多系统中，经常会遇到这种情况，要保持一个编号的唯一，如会计软件中的凭证的编号。一种编号的处理是这样的，把表中的最大编号保存到表中，然后在这个编号上累加，形成新的编号。这个过程对并发处理要求非常高，下面我们就来模拟这个过程，看如何保持编号的唯一性。
&&&&&&&新建一张表code来保存凭证的最大编号。字段如下：编号:bh(numeric(18,0)),凭证表名pinzheng(varchar(50))
假设表中有这样的一条记录：
新建一个存储过程来生成新的凭证编号，如下：
CREATE&PROCEDURE&up_getbh&&AS
&&&&&&&Begin&Tran
&&&&&&&&&&&&&&Declare&@numnewbh&numeric(18,0)
&&&&&&&&&&&&&&select&&@numnewbh&=&bh&FROM&code&&WITH&(UPDLOCK,ROWLOCK)&where&pinzheng&=&'会计凭证'&
&&&&&&&&&&&&&&set&@numnewbh&=&@numnewbh&+&1
&&&&&&&&&&&&&&update&code&set&&bh&=&@numnewbh&where&pinzheng&=&'会计凭证'
&&&&&&&&&&&&&&print&@numnewbh
&&&&&&&Commit&tran
然后，打开查询分析器，并多开几个连接（笔者开了8个连接，模拟有8个人同时并发，读者可以开更多的连接进行试验），把类似以下这样的语句复制到每个连接窗口中，
declare&@i&numeric(18,0)
set&@i&=&1
while&@i&=&1
&&&&&&&if&getdate()&&&'&14:23'&&--设定一个时间，到此时间同时执行upgetbh存储过程
&&&&&&&&&&&&&&set&@i&=&0&&&&&&
exec&up_getbh
然后，接连运行各个连接，到&14：23&这一刻，各个连接同时运行up_getbh。从运行结果可以看出连接顺序出现18001开始个数字，并没有重号或丢号的现象。
分析：由于up_getbh中的select语句使用了更新锁，因更新锁之间不能兼容，所以各个连接要等到所有其他的连接释放掉锁才能执行，而更新锁的释放要等到事务结束，这样就不会发生号出错的现象了。
附：锁的兼容性表
现有的授权模式
阅读(...) 评论()MSSQL基础 &&&&最新内容
MSSQL基础 &&&&相关内容关于更新锁与行级锁
[问题点数：90分，结帖人csshow]
关于更新锁与行级锁
[问题点数：90分，结帖人csshow]
不显示删除回复
显示所有回复
显示星级回复
显示得分回复
只显示楼主
2010年7月 总版技术专家分月排行榜第二2010年6月 总版技术专家分月排行榜第二
2010年5月 总版技术专家分月排行榜第三
2010年7月 总版技术专家分月排行榜第二2010年6月 总版技术专家分月排行榜第二
2010年5月 总版技术专家分月排行榜第三
2010年5月 总版技术专家分月排行榜第二2010年4月 总版技术专家分月排行榜第二
2011年7月 荣获微软MVP称号2010年7月 荣获微软MVP称号
2010年7月 总版技术专家分月排行榜第二2010年6月 总版技术专家分月排行榜第二
2010年5月 总版技术专家分月排行榜第三
2010年7月 总版技术专家分月排行榜第二2010年6月 总版技术专家分月排行榜第二
2010年5月 总版技术专家分月排行榜第三
匿名用户不能发表回复！|}