SQL Server 的 nolock 介绍
Tags： MSSQLnolockSQL Server
前言
相信绝大部分用 SQL SERVER 作为数据库的程序员都知道 nolock 关键词，即使当时不知道也会在踩过若干阻塞坑之后果断的加上 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 提示。
SQL阻塞示例
为了方便讲述，先创建一个 post 表，插个 6 条记录，参考代码如下：
1 2 3 4 5 6 7 8 9 | CREATE TABLE post(id INT IDENTITY,content char(4000)) GO INSERT INTO dbo.post VALUES('aaa') INSERT INTO dbo.post VALUES('bbb') INSERT INTO dbo.post VALUES('ccc'); INSERT INTO dbo.post VALUES('ddd'); INSERT INTO dbo.post VALUES('eee'); INSERT INTO dbo.post VALUES('fff'); |
这里为了简单我没有创建索引，所以会出现 Table Scan 的情况，毕竟生产环境下的sql也避免不了 Table Scan 和 Clustered Index Scan 的存在，接下来还原下阻塞场景，开启两个 session 会话, session1 为正在运行的 update 事务, session2 为一个简单的 select 操作，这种场景下会导致 session2 阻塞，参考代码如下：
- session1
1 2 | BEGIN TRAN UPDATE post SET content='xxxxx' WHERE id=3 |
- session2
1 | SELECT * FROM post WHERE id=4 |
从图中可以看到，这个 select 已经阻塞 9 分钟了。
查看锁阻塞情况：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | -- 当前正在执行的SQL(包括等待的SQL、当前正在堵塞与被堵塞SQL) SELECT spid, blocked blocking, P.dbid, db_name(P.dbid) dbname, (waittime/1000) waittime, lastwaittype, waitresource, open_tran, P.status, cpu, physical_io, memusage, login_time, last_batch, hostname, [program_name], hostprocess, cmd, nt_domain, nt_username, net_address, net_library, loginame, P.sql_handle, SUBSTRING (s.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), s.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) internal_SQL_TEXT, TEXT Parent_SQL_TEXT, 'kill '+cast(spid as varchar) kill1 FROM master.dbo.sysprocesses P LEFT JOIN sys.dm_exec_requests er ON er.session_id = P.spid CROSS apply sys.dm_exec_sql_text ( P.sql_handle ) s WHERE P.spid <>@@spid and (P.status !='sleeping' or (P.status='sleeping' and P.spid IN(select blocked from master.dbo.sysprocesses nb where blocked > 0 )) or (P.status='sleeping' and P.open_tran >0 ) ) order by waittime desc GO |
可以看到，当前107会话为SELECT查询，正在等待锁LCK_M_S ，被103会话阻塞了，而103会话有一个事务未提交，正在等待后续命令。(执行脚本1前，可以用SELECT @@spid查看会话ID)
那为什么会被阻塞呢？可以观察 SQLSERVER 内部的统计信息，比如锁相关的动态视图 sys.dm_tran_locks ，参考代码如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SELECT t.request_session_id, CASE WHEN t.resource_type = 'OBJECT' THEN OBJECT_NAME(t.resource_associated_entity_id) WHEN t.resource_associated_entity_id = 0 THEN '/' ELSE OBJECT_NAME(p.object_id) END AS resource_name, index_id, t.resource_type, t.resource_description AS description, t.request_mode AS mode, t.request_status AS status FROM sys.dm_tran_locks AS t LEFT JOIN sys.partitions AS p ON p.hobt_id = t.resource_associated_entity_id WHERE t.resource_database_id = DB_ID() |
从图中看，session55 准备在 1:489:0 这个槽位指向的记录上附加 S 锁时被阻塞，因为 1:489:0 已经被附加了 X 锁，很显然这个 X 锁是 update 给的。
上面给出的是一个 静态视图，为了方便显示动态视图，这里把 sql profile 开起来观察两个 session 给锁的过程，事件选择上如下所示：
将 sqlprofile 开启后，重新运行下刚才的两个会话，观察 profile 的走势，截图如下：
图中的注释已经说的非常清楚了，和 sys.dm_tran_locks 显示的一致，有了这些基础后接下来观察下如果加上 with (nolock) 会怎么样？
1 | SELECT * FROM post(NOLOCK) WHERE id=4 |
你会发现结果是可以出来的，那为什么可以出来呢？继续观察下 profile 即可。
从 session 55 的 lock 输出来看，with(nolock) 会对 post 表附加 Sch-S 架构稳定锁，以及分区中的 堆或BTree 附加S锁， 而不再对 PAGE 附加任何锁了，所以就不存在阻塞的情况，但肯定会引起脏读。
到这里基本上就是 nolock 的底层玩法了吧，不过也有一个注意点，nolock 真的不会引发阻塞吗? 接下来我们好好聊一聊。
nolock 真的无视阻塞吗
从 sq lprofile 观察锁的走势图来看，nolock 只是在上限为 page 页级别上做到无视，但在 page 之上就无法做到了，比如你看到的 Sch-S,可能有些朋友要问了，为什么要加上 Sch-S 锁呢？其实很简单，在 query 的过程中一定要保持架构稳定嘛，不能在 query 的过程中，post 表突然被删了，这样大家多尴尬。
接下来也可以做个简单的测试。
1 2 3 4 5 6 | ----- session 1 BEGIN TRAN TRUNCATE TABLE post; ----- session 2 SELECT * FROM post(NOLOCK) WHERE id=4 |
可以发现 nolock 查询也被阻塞了，原因就在于拿不到 post 表的 Sch-S 锁，因为 TRUNCATE 已经给 post 附加了 Sch-M 架构修改锁，那有没有数据支撑呢？继续用动态视图 sys.dm_tran_locks 观察便可。
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)不推荐使用了，"不借助 WITH 关键字指定表提示”的写法已经过时了。 具体参见MSDN http://msdn.microsoft.com/zh-cn/library/ms143729%28SQL.100%29.aspx
2.1 至于网上说WITH(NOLOCK)在SQL SERVER 2000不生效，我验证后发现完全是个谬论。
2.2 在使用链接服务器的SQL当中，(NOLOCK)不会生效，WITH(NOLOCK)才会生效。如下所示
1 2 3 | 消息 4122，级别 16，状态 1，第 1 行 Remote table-valued function calls are not allowed. |
3.语法上有些许出入，如下所示
1 2 3 4 5 6 7 8 9 10 11 12 13 | 这种语法会报错 SELECT * FROM sys.indexes WITH(NOLOCK) AS i -Msg 156, Level 15, State 1, Line 1 -Incorrect syntax near the keyword 'AS'. 这种语法正常 SELECT * FROM sys.indexes (NOLOCK) AS i 可以全部改写为下面语法 SELECT * FROM sys.indexes i WITH(NOLOCK) SELECT * FROM sys.indexes i (NOLOCK) |
总结
综上所述，nolock 也仅在 page 级别上畅通无阻，在某些情况下也会有阻塞情况的发生，由于无锁自然就会读到别的会话已修改但还未提交的记录。
sqlserver 作为一个数据库应用程序，里面包含了大量的运行时统计信息，这些统计信息可以用 系统视图 和 动态视图 获取，完全可以基于它们做一个完善的 APM 监控。
