SQL Server-聚焦NOLOCK、UPDLOCK、HOLDLOCK、READPAST你弄懂多少？

前言

[转发自：https://www.cnblogs.com/CreateMyself/p/6512692.html]

时间流逝比较快，博主也在快马加鞭学习SQL Server，下班回来再晚也不忘记更新下博客，时间挤挤总会有的，现在的努力求的是未来所谓的安稳，每学一门为的是深度而不是广度，求的是知识自成体系而不是零散，废话不多说本节我们来讲讲SQL Server基础系列最后几节内容，这话博主说了n次，呵呵。

NOLOCK和READPAST

NOLOCK

随便翻翻博客园对于各种锁的介绍真的是一个字【多】，仅仅介绍其概念，再要么就是转载其概念，不知道那些转载概念的园友是否已经弄懂了，稍微发下感慨。NOLOCK在概念上类似于READ UNCOMMITTED隔离级别，并且只针对于SELECT查询语句，它不会获取表的共享锁，换句话说不会阻止排它锁来更新数据行。当我们对表进行NOLOCK有什么好处呢？它能够提高并发性能，因为此时SQL Server数据库引擎不必去维护共享锁，由于不会对正在读取的表获取共享锁，所以可能导致未提交的事务也会被读取，所以此时缺点显而易见将导致脏读，至于脏读是何含义则无需我再多讲。我们重点的明白什么情况下应该用NOLOCK。我们看下实际例子来理解NOLOCK，建立测试表并插入300条测试数据：

IF OBJECT_ID('Example')>0      

DROP TABLE Example;

GO

CREATE TABLE [dbo].[Example]

(      
      [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,      
      [Product] [char](150) NULL,      
            [SaleDate] [datetime] NULL,      
            [SalePrice] [money] NULL
)

GO


DECLARE @i SMALLINT

SET @i = 1

WHILE (@i <=100)

BEGIN                  
      INSERT INTO Example              
      (Product, SaleDate, SalePrice)                  
      VALUES      
      ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))      


      INSERT INTO Example               
      (Product, SaleDate, SalePrice)      
      VALUES             
      ('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))                  


      INSERT INTO Example                  
      (Product, SaleDate, SalePrice)          
      VALUES            
      ('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))                  


      SET @i = @i + 1

END

GO

此时我们再来插入一条测试数据：

BEGIN TRANSACTION
      INSERT INTO Example
      (Product, SaleDate, SalePrice)          
      VALUES            
      ('PoolTable', GETDATE(), 500)

此时我们保持该事务窗口打开，所以此时在表中仍然会记录着对其所发出的锁，接下来我们在另外一个窗口查询表中数据总行数并使用NOLOCK提示。

SELECT COUNT(*) FROM Example WITH(NOLOCK)

此时显示数据总函数为301，因为上述插入语句的事务进入到了表中只是并未提交而已，此时我们不想插入那条数据进行撤销即回滚

BEGIN TRANSACTION
      INSERT INTO Example
      (Product, SaleDate, SalePrice)          
      VALUES            
      ('PoolTable', GETDATE(), 500) 
ROLLBACK TRANSACTION

此时我们回滚了之前插入的数据，我们再来利用NOLOCK提示来查询数据总函数。

此时返回的为实际总数据行，而我么第一次查询的数据并未提交这就是典型的-脏读。

READPAST

READPAST表提示相信很多童鞋用的比较少，但是实际上其作用非常大，当在表中用READPAST指定提示时此时SQL Server数据库引擎在返回结果集时将不会返回锁定的行或者数据页。它除了和NOLOCK一样不会导致查询阻塞外，因为不会返回锁定的行记录所以其优点好包括不存在脏读。但是其缺点则是因为不包含锁定的行记录但是很难保证结果集或者修改语句是否包含我们所必须需要返回的行。有可能在我们的业务逻辑中，需要返回我们必须需要的行。它的使用方式和NOLOCK一样，下面我们来看下实际例子，更新测试表中的SalePrice列，如下：

BEGIN TRANSACTION
      UPDATE TOP(1) Example
      SET SalePrice = SalePrice + 1

由于我们并未提交或者回滚事务所以此时更新的数据行已经被影响，下面我们利用READPAST提示来查询表中总数据行。

SELECT COUNT(*)

FROM Example WITH(READPAST)

在我们的测试表中数据行为300条，同时我们进行了上述更新，当我们利用READPAST提示进行查询总数据行时，因为更新而未提交或者回滚导致此时有一行记录被排它锁锁住，而READPAST的作用则是跳过锁住的行，所以此时很明显只返回299条数据，如下：

通过上述图显示由于更新数据行被锁定，所以此时利用READPAST来查询总数据行时导致更新数据行将被忽略。

UPDLOCK和HOLDLOCK

UPDLOCK

怎么会出现一个更新锁的呢，原来我们对于查询和更新死锁说到了排它锁，这个排它锁和更新锁不是一样的么，此言差矣，容我娓娓道来，这个UPDLOCK只是针对于表中的某一行记录来锁定从而阻止其他操作对该行的数据更新，说到这里想必我们已经明了，UPDLOCK是行级别，而排它锁则是表级别，二者不可同日而语。也就说当我们对某一行添加UPDLOCK提示时并不会阻塞其他查询操作，下面我们来看看，我们打开一个窗口来更新测试表中筛选条件为SaleID等于1的记录并用UPDLOCK锁住。

 BEGIN TRAN
 select * from Example WITH (UPDLOCK) where SaleID = 1

此时我们再来开一个窗口进行查询，如下：

select * from Example

此时我们将看到能够查询出所有数据，如下：

HOLDLOCK

这个又是什么玩意了，根据词达意翻译为厚住锁【哈哈】，这个翻译虽然有点勉强，但是非常明确的表达了其意思，有点强制性的意味，当我们使用HOLDLOCK提示时，此时查询将锁定表且被强制序列化，直到事务完成，才会被释放，其类似于SERIALIZABLE最高隔离级别。我们结合上述例子来看下，当我们对表进行HOLDLOCK后再进行查询

 BEGIN TRAN
 select * from Example WITH (UPDLOCK,HOLDLOCK) where SaleID = 1

此时我们再来运行查询

 select * from Example

什么情况还是能查询出数据，不知道看到本文的你是否心生疑窦，我们并未提交事务并用UPDLOCK和HOLDLOCK提示此时再查询时应该会出现阻塞，因为此时已有排它锁的存在。我们先搁置疑问，在我们创建测试表时毫无疑问会对主键创建聚集索引，此时我们删除聚集索引试试。

此时我们重新运行上述语句，此时将导致查询阻塞，如下：

我们简短的解释一下，如果我们对表建立了聚集索引或非聚集索引此时排它锁将消失代替的则是RangeS-U锁，所以当我们未添加聚集索引排它锁则存在导致查询阻塞，有关RangeS-S，RangeS-U，RangeX-X，RangeI-N我们将深入研究。所以上述由于导致了查询阻塞，我们结合本节所学内容，我们利用NOLOCK来查询数据。

 select * from Example WITH(NOLOCK)

此时毫无疑问将能够查询出数据，如下：

当然除非我们意识到NOLOCK导致脏读的问题，否则谨慎用。

实战拓展

关于NOLOCK和UPDLOCK以及HOLDLOCK则没有什么可讲的，我们来讲讲UPDLOCK和READPAST，通过UPDLOCK和READPAST的结合我们能够解决许多问题，比如我当前项目中对于更新预约人数，则用到了UPDLOCK和READPAST，因为考虑到并发如果固定预约人数为100，那么当出现并发时将有可能导致预约超出的情况，利用UPDLOCK则可以解决其他进程过来时对其进行修改的情况，同时结合READPAST解决脏读，同时不会阻塞，当有请求过来时我们直接利用表变量对预约人数进行更新，若更新失败我们再进行回滚，算是一个解决方案。同时利用UPDLOCK和READPAST还可以解决其他问题，比如，当有多个并发时我们要根据筛选条件获取第一值，也就是说第二个请求过来时获取到的值是下一个，那么这样的问题该如何处理呢，若我们只是简单进行处理，那么第二个请求同时过来时可能也会读取到之前读取的那个值，基于此场景，我们可以利用UPDLOCK和READPAST来解决。我们看如下代码就可以理解。

DECLARE @Next INTEGER
BEGIN TRANSACTION

-- 找到下一个满足条件的值
SELECT TOP 1 @Next = Id
FROM Test WITH (UPDLOCK, READPAST)
WHERE Flag = 0
ORDER BY Id ASC

--若找到利用标识更新，防止下一次被读取到
IF (@Next IS NOT NULL)
    BEGIN
        UPDATE Test
        SET Flag = 1
        WHERE Id = @Next
    END

COMMIT TRANSACTION

-- 返回我们查询到的值
IF (@Next IS NOT NULL)
    SELECT * FROM Test WHERE Id = @Next

当然上述可以避免阻塞，我们也可以在阻塞的情况下来处理利用ROWLOCK和HOLDLOCK来解决

BEGIN TRAN

SELECT 
FROM Test
WITH (HOLDLOCK, ROWLOCK)
WHERE Id = 1

--TODO

COMMIT TRAN

为了加深记忆，又转发一篇博客，如下：

转发至：https://www.cnblogs.com/sthinker/p/5922967.html

前段时间**公司DBA来我们这培训。讲了一大堆MYSQL的优化。 QA环节一程序员问“SQL语句中的 with nolock 除了不锁表外,是否能读其他锁住的数据"。

　　讲课的人嘟嘟了半天没解释清楚（有可能是MYSQL里没有这个机制），公司的另一程序员给出了一个很简洁明了的回答：

　　WITH NOLOCK 除了本身不锁表（不加任何锁）也不会受其他的已存在的锁影响，锁住的行数据也照样读，

个人认为这句话说得很清楚明了，一句话就能说明白的事，不过好奇怪的是程序员经常用这个语句竟然也不去试一下。这里顺便总结一下其他的 SQLSERVER 中的with锁级别：

　　WITH NOLOCK：无锁

　　WITH HOLDLOCK：挂一个保持锁

　　WITH UPDLOCK：挂一个更新锁

　　WITH XLOCK：挂一个排他锁

　　需要注意的是 with nolock 是不能用于update,delete insert 这种更新语句的，说绕了。简单的说 with nolock 只能用于select。

　　例如:update dbo.test with(NOLOCK) set username='wokofo' --这样的语句是错误的

　　弹回：INSERT、UPDATE、DELETE 或 MERGE 语句的目标表不允许使用 NOLOCK 和 READUNCOMMITTED 锁提示。

实际使用：

selecttop10*from dbo.test with(NOLOCK) selecttop10*from dbo.test with(HOLDLOCK) selecttop10*from dbo.test with(XLOCK) selecttop10*from dbo.test with(UPDLOCK) update dbo.test with(HOLDLOCK) set username='wokofo' update dbo.test with(XLOCK) set username='wokofo' update dbo.test with(UPDLOCK) set username='wokofo'

NOLOCK（不加锁）

此选项被选中时，SQL Server 在读取或修改数据时不加任何锁。在这种情况下，用户有可能读取到未完成事务（Uncommited Transaction）或回滚(Roll Back)中的数据, 即所谓的“脏数据”。

HOLDLOCK（保持锁）

此选项被选中时，SQL Server 会将此共享锁保持至整个事务结束，而不会在途中释放。

UPDLOCK（修改锁）

此选项被选中时，SQL Server 在读取数据时使用修改锁来代替共享锁，并将此锁保持至整个事务或命令结束。使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。

TABLOCK（表锁）

此选项被选中时，SQL Server 将在整个表上置共享锁直至该命令结束。这个选项保证其他进程只能读取而不能修改数据。

PAGLOCK（页锁）

此选项为默认选项，当被选中时，SQL Server 使用共享页锁。

TABLOCKX（排它表锁）

此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。

　　HOLDLOCK 持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放，等于SERIALIZABLE事务隔离级别

　　NOLOCK 语句执行时不发出共享锁，允许脏读，等于 READ UNCOMMITTED事务隔离级别

　　PAGLOCK 在使用一个表锁的地方用多个页锁

　　READPAST 让sql server跳过任何锁定行，执行事务，适用于READ UNCOMMITTED事务隔离级别只跳过RID锁，不跳过页，区域和表锁

　　ROWLOCK 强制使用行锁

　　TABLOCKX 强制使用独占表级锁，这个锁在事务期间阻止任何其他事务使用这个表

　　UPLOCK 强制在读表时使用更新而不用共享锁

注意: 锁定数据库的一个表的区别

　　SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除

　　SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删

总结

本节我们讲述了博主比较疑惑的几种锁例如READPAST，之前未接触过，项目中在老大的指导下才知道，本来打算今天结束SQL Server基础系列，谁知中途学习时遇到了其他问题，比如还有其他四种锁类型，我还得再研究研究，真的是SQL Server基础系列最后一篇，真的不骗你，同时.NET Core也会不定时更新，欢迎大家继续关注博客

posted @ 2020-10-22 14:01 天才卧龙阅读(1275) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

天才第一步，雀氏纸尿布

代码改变世界，努力吧！