开发者社区> 嗯哼9925> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL Server-聚焦NOLOCK、UPDLOCK、HOLDLOCK、READPAST你弄懂多少?(三十四)

简介:
+关注继续查看

前言

时间流逝比较快,博主也在快马加鞭学习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
复制代码

总结 

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




本文转自Jeffcky博客园博客,原文链接:http://www.cnblogs.com/CreateMyself/p/6512692.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL常见SQL语句用法
标签(linux): mysql 笔者Q:972581034 交流群:605799367。有任何疑问可与笔者或加群交流 表字段类型 TINYINT 微小整数类型,可存储的容量为1字节 INT 整数类型,可以存储容量为4个字节(4294967296) CHAR(M) 定长字符串类型,当存储时,总是...
1087 0
MySQl之最全且必会的sql语句
原文链接:http://blog.csdn.net/qq_32059827/article/details/51763950 创建一个名称为mydb1的数据库,如果有mydb1数据库则直接使用,如果无则创建mydb1数...
798 0
MySQl数据库必会sql语句(提升版)
原文链接:http://blog.csdn.net/qq_32059827/article/details/51778816 这一篇属于加强版,问题和sql语句如下。
639 0
SQL语句大小写是否区分的问题,批量修改整个数据库所有表所有字段大小写
一、实例介绍 SQL语句大小写到底是否区分呢?我们先从下面的这个例子来看一下: 例: --> 创建表,插入数据: declare @maco table (number int,myvalue varchar(2)) insert into @maco select 1,'aa' ...
1229 0
存储过程与SQL语句如何选择
数据库擅长存储与索引,在目前的互联网系统架构中,服务器的扩展要比存储的扩展更简单, 需要考虑系统可能的瓶颈在服务器还是数据存储,存储过程有它的优点,应该在开发中合理的选用。
2846 0
MySQL千万级多表关联SQL语句调优
本文不涉及复杂的底层数据结构,通过explain解释SQL,并根据可能出现的情况,来做具体的优化,使千万级表关联查询第一页结果能在2秒内完成(真实业务告警系统优化结果)。   需要优化的查询:使用explain      出现了Using temporary;      有分页时出现了Using filesort则表示使用不了索引,需要根据下面的技巧来调整语句      rows过多,或者几乎是全表的记录数;      key 是 (NULL);      possible_keys 出现过多(待选)索引。
2540 0
ORACLE性能优化之SQL语句优化
ORACLE性能优化之SQL语句优化
2611 0
sql 语句之 case
case语句语法: --简单Case函数 CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END --Case搜索函数 CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE ...
652 0
T-SQL查询:语句执行顺序
原文:T-SQL查询:语句执行顺序 读书笔记:《Microsoft SQL Server 2008技术内幕:T-SQL查询》   ===============  T-SQL查询的执行顺序 ===============      =============== T-SQL查询的示意图...
792 0
+关注
4715
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载