SQL Server优化：SQL Server中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