[[428474]]

1、為什么SQL Server有NOLOCK關(guān)鍵字?

SQL Server沒創(chuàng)建一個查詢，都相當(dāng)于創(chuàng)建一個查詢會話，在不同的查詢分析器里面進(jìn)行的查詢操作，可能會影響別的查詢會話。比較典型的一個例子，如果你正在使用事務(wù)執(zhí)行某一張表的插入或者操作而沒有正確關(guān)閉事務(wù)的情況下，會造成別的會話針對該數(shù)據(jù)表的查詢都會處于阻塞的狀態(tài)，從而不能完成查詢的操作。這個時候有兩個解決方案，第一種查詢到阻塞的會話id然后殺掉該會話id，第二種可以使用WITH(NOLOCK)關(guān)鍵字忽略掉阻塞的會話直接查詢出結(jié)果。

簡單來說NOLOCK關(guān)鍵字的作用是防止查詢的時候被別的會話阻塞，從而順利完成查詢的操作。

2、SQL Server有NOLOCK有什么問題

使用NOLOCK關(guān)鍵字可以避免阻塞造成無法查詢出數(shù)據(jù)，但使用該關(guān)鍵字會有造成數(shù)據(jù)臟讀的可能。下面舉個例子：

2.1 創(chuàng)建數(shù)據(jù)表

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 創(chuàng)建時候 會話id 為58 開啟事務(wù) 不關(guān)閉事務(wù)

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

2.3 當(dāng)前會話(58)還可以查詢出數(shù)據(jù)

事務(wù)還沒有提交 此時數(shù)據(jù)還在內(nèi)存中，未保存到數(shù)據(jù)庫當(dāng)中。

select * from userInfo 

2.4 新建一個查詢會話 當(dāng)前新建的id是51

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

2.5 殺掉58會話進(jìn)程

declare @spid  int  
Set @spid  = 58 --鎖表進(jìn)程 
declare @sql varchar(1000) 
set @sql='kill '+cast(@spid  as varchar) 
exec(@sql) 

3、NOLOCK使用場景

針對那些被頻繁操作(插入、更新、刪除)的表，使用NOLOCK是非常比較適合的，但要考慮到臟讀的情況。

• 不經(jīng)常修改的數(shù)據(jù)表，省掉鎖定表的時間來大大加快查詢速度。
• 數(shù)據(jù)量非常大的數(shù)據(jù)表，可以考慮犧牲數(shù)據(jù)安全性來提升查詢的效率;
• 允許出現(xiàn)臟讀現(xiàn)象的業(yè)務(wù)邏輯，對數(shù)據(jù)完整性要求比較嚴(yán)格的場景不適合，比如電商、銀行等系統(tǒng)。
• 當(dāng)使用NoLock時，它允許閱讀那些已經(jīng)修改但是還沒有結(jié)束事務(wù)的數(shù)據(jù)。因此要考慮transaction事務(wù)數(shù)據(jù)的實時完整性時，不建議使用。

4、nolock和with(nolock)的區(qū)別

三種查詢寫法

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

• SQL Server 2005版本中，只支持with(nolock)關(guān)鍵字
• with(nolock)的寫法非常容易再指定索引
• 跨數(shù)據(jù)庫服務(wù)器查詢語句時不能用with (nolock) 只能用nolock，同數(shù)據(jù)服務(wù)器查詢時 兩者都可以用-- 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'; 
--參數(shù)說明 spid   鎖表進(jìn)程 ；tableName   被鎖表名 
-- 解鎖語句 需要拿到spid然后殺掉縮表進(jìn)程 
declare @spid  int  
Set @spid  = 57 --鎖表進(jìn)程 
declare @sql varchar(1000) 
set @sql='kill '+cast(@spid  as varchar) 
exec(@sql) 

本文轉(zhuǎn)載自微信公眾號「IT技術(shù)分享社區(qū)」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請聯(lián)系IT技術(shù)分享社區(qū)公眾號。

個人博客網(wǎng)站：https://programmerblog.xyz