SQL Server优化：SQL Server中Nolock关键字的用法-sql语句nolock

[[428474]]

1、为什么SQL Server有NOLOCK关键字?

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

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

2、SQL Server有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

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

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

2.5 杀掉58会话进程

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

3、NOLOCK使用场景

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

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

4、nolock和with(nolock)的区别

三种查询写法

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

SQL Server 2005版本中，只支持with(nolock)关键字
with(nolock)的写法非常容易再指定索引
跨数据库服务器查询语句时不能用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)

本文转载自微信公众号「IT技术分享社区」，可以通过以下二维码关注。转载本文请联系IT技术分享社区公众号。

个人博客网站：https://programmerblog.xyz