SQLServer优化:SQLServer中NOLOCK关键字的用法介绍

简介: SQLServer没创建一个查询,都相当于创建一个查询会话,在不同的查询分析器里面进行的查询操作,可能会影响别的查询会话。比较典型的一个例子,如果你正在使用事务执行某一张表的插入或者操作而没有正确关闭事务的情况下,会造成别的会话针对该数据表的查

image_30b44e3d.png

1、为什么SQLServer有NOLOCK关键字?

SQLServer没创建一个查询,都相当于创建一个查询会话,在不同的查询分析器里面进行的查询操作,可能会影响别的查询会话。比较典型的一个例子,如果你正在使用事务执行某一张表的插入或者操作而没有正确关闭事务的情况下,会造成别的会话针对该数据表的查询都会处于阻塞的状态,从而不能完成查询的操作。这个时候有两个解决方案,第一种查询到阻塞的会话id然后杀掉该会话id,

第二种可以使用WITH(NOLOCK)关键字忽略掉阻塞的会话直接查询出结果。

简单来说NOLOCK关键字的作用是防止查询的时候被别的会话阻塞,从而顺利完成查询的操作。

2、SQLServer有NOLOCK有什么问题

使用NOLOCK关键字可以避免阻塞造成无法查询出数据,但使用该关键字会有造成数据脏读的可能。下面举个例子

2.1 创建数据表

CREATE TABLE [dbo].[userInfo] (
  [id] varchar(32) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [userName] nvarchar(30) COLLATE Chinese_PRC_CI_AS  NULL,
  [birthday] [dbo].[birthday]  NULL,
  CONSTRAINT [PK__userInfo__3213E83F0505C75D] 
  PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)  
ON [PRIMARY]
GO
INSERT INTO [dbo].[userInfo] ([id], [userName], [birthday])
 VALUES ('123', N'小明', '2005-01-02 12:30:00.000');
INSERT INTO [dbo].[userInfo] ([id], [userName], [birthday])
 VALUES ('125', N'小孙', '2005-01-02 12:30:00.000');

2.2 创建时候 会话id 为58 开启事务 不关闭事务

begin tran 
insert into userInfo (id,userName,birthday)
values ('127','小张','2015-01-02 12:30:00.000')
--commit tran

2.3 当前会话(58)还可以查询出数据

事务还没有提交 此时数据还在内存中,未保存到数据库当中

select * from userInfo

image_c65ccf3f.png

2.4 新建一个查询会话 当前新建的id是51

select * from userInfo;
select * from userInfo WITH(NOLOCK);

image_6b40e4a3.png

image_70bd988a.png

2.5 杀掉58会话进程

declare @spid  int 
Set @spid  = 58 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid  as varchar)
exec(@sql)

image_26fe1424.png

3、NOLOCK使用场景

针对那些被频繁操作(插入、更新、删除)的表,使用NOLOCK是非常比较适合的,但要考虑到脏读的情况。

  • 不经常修改的数据表,省掉锁定表的时间来大大加快查询速度。
  • 数据量非常大的数据表,可以考虑牺牲数据安全性来提升查询的效率;
  • 允许出现脏读现象的业务逻辑,对数据完整性要求比较严格的场景不适合,比如电商、银行等系统。
  • 当使用NoLock时,它允许阅读那些已经修改但是还没有结束事务的数据。因此要考虑transaction事务数据的实时完整性时,不建议使用。

4、nolock和with(nolock)的区别

三种查询写法

SELECT * FROM A NOLOCK;
SELECT * FROM A (NOLOCK);
SELECT * FROM A WITH(NOLOCK);

1、SQLServer2005版本中,只支持with(nolock)关键字

2、with(nolock)的写法非常容易再指定索引

3、跨数据库服务器查询语句时不能用with (nolock) 只能用nolock,同数据服务器查询时 两者都可以用

-- SQL Server 2008版本之后建议采用WITH(NOLOCK)写法。

5、表解锁脚本

-- 查询被锁表
select request_session_id   spid
,OBJECT_NAME(resource_associated_entity_id) tableName   
from   sys.dm_tran_locks where resource_type='OBJECT';
--参数说明 spid   锁表进程 ;tableName   被锁表名
-- 解锁语句 需要拿到spid然后杀掉缩表进程
declare @spid  int 
Set @spid  = 57 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid  as varchar)
exec(@sql)
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
SQL 程序员 测试技术
SqlServer的with(nolock)关键字的用法介绍
数据库写查询语句的时候,为了提升查询性能,往往会在查询的表后面加一个nolock,或者是with(nolock),其目的就是查询的时候是不锁定表,从而提高查询速度的目的。但如果同一时间有多个用户访问同一资源的时候,如果并发用户对该资源做了修改。则会对其他用户访问该数据造成数据不一致的情况
SqlServer的with(nolock)关键字的用法介绍
|
BI 数据库
数据库:SQLServer 实现行转列、列转行用法笔记
官方解释:可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。
数据库:SQLServer 实现行转列、列转行用法笔记
|
SQL 存储 程序员
数据库:SQLServer中游标的用法笔记
游标可以理解为SQL Server的一种数据访问机制,它允许用户访问数据的维度是数据行。用户可以对每一行数据进行单独处理,从而降低系统开销和潜在的阻隔情况,
数据库:SQLServer中游标的用法笔记
|
SQL 存储 数据库连接
SQLServer知识:sqlcmd用法笔记
sqlcmd是一个 Microsoft Win32 命令提示实用工具,可以通过该命令工具实现SQL语句、脚本的执行,并且可以实现脚本任务的自动化。
SQLServer知识:sqlcmd用法笔记
|
SQL XML 程序员
数据库:SQLServer中with as 用法笔记
with as 也叫做子查询部分(subquery factoring),可以定义一个SQL段落,该SQL段落可以被整个SQL语句所用到类似于临时表的作用。with as 可以提高你的SQL语句的可读性,也有可以用在在UNION ALL的不同部分,作为提供临时数据的部分。
数据库:SQLServer中with as 用法笔记
|
关系型数据库 MySQL
mysql分页原理和sqlserver里面序列的用法
mysql分页原理和sqlserver里面序列的用法
138 0
|
SQL 数据库 索引
Sql Server数据库性能优化之索引
最近在做SQL Server数据库性能优化,因此复习下一索引、视图、存储过程等知识点。本篇为索引篇,知识整理来源于互联网。 索引加快检索表中数据的方法,它对数据表中一个或者多个列的值进行结构排序,是数据库中一个非常有用的对象。
1125 0