nolock在SQLServer中的作用

nolock?

Table hints are used to override the default behavior of the query optimizer during the data manipulation language (DML) statement. You can specify a locking method, one or more indexes, a query-processing operation such as a table scan or index seek, or other options. Table hints are specified in the FROM clause of the DML statement and affect only the table or view referenced in that clause.

从MSDN上的这段话不难看出这是一种对数据库事务隔离级别的重写定义操作语法

-- 三种写法 大同小异但是with(nolock)的写法更普遍些也更微软官方推荐这种写法
select username from user_table with(nolock) where name = 'john doe';
select username from user_table (nolock) where name = 'john doe';
select username from user_table nolock where name = 'john doe';

使用nolock在查询时不加共享锁(读锁/S锁) 虽说能够将读取速度提升但带来的问题也显而易见就是脏读

隔离级别

MSDN给出了SqlServer本身支持的五种隔离级别隔离等级从上到下依次递增 SERIALIZABLE等级最高也是成本最高的

-- Syntax for SQL Server and Azure SQL Database
  
SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

这里SqlServer的默认隔离界别为RC(Read-Committed/读已提交) 这种情况下不会产生脏读但是允许发生幻读的情况

脏读、幻读

脏读

脏读的发生意味着在一个事务读取数据的时候读取到了其他事务更改却未提交的数据

这种情况常常在多用户并发访问时发生用户A篡改了一行数据但是没有提交被用户B访问到后结果用户A发现数据不对又撤回了刚刚的操作这样用户A再次读取的时候会发现和第一次读取是两个值

脏读更多的发生在表中已存在的数据被更改(Update)

幻读

幻读的发生意味着一个事务在执行两次相同的查询时因另一事务在期间插入/删除的数据导致两次查询出的数据行不一致

例如同样是两个用户插入数据(假设为相同的id,但是其他列不同) 用户A插入时查询发现表中没有id=x的数据正打算插入却发现插入不了这是因为用户B捷足先登抢先插入了这条id=x的数据(已提交) 导致用户A插入时第二次读取到了本不存在的数据这就像是产生了幻觉一样

幻读更多的发生在表中查询到不存在的数据上(Insert/Delete)

脏读的性能提升以及弊端

环境

Windows10 21H2 19044.3570
SqlServer 2019 Developer Edition
生成数据

declare @count int = 0;

while @count < 100000
begin 
INSERT INTO [dbo].[demo_emp]
           ([id]
           ,[name]
           ,[age])
     VALUES
           ( replace(NEWID(),'-','')
           , 'John Doe'
           , cast(RAND() * 100 as int));
           
set @count = @count +1;
end
GO

模拟长耗时事务

declare @cnt int = 0;
-- 这里100次循环是为了方便模拟场景 主要是我手残点一次修改的速度过快都没来得及查
while @cnt < 100
begin
begin transaction
update dbo.demo_emp set name = '无名氏' where age > 10;
commit transaction
set @cnt = @cnt +1;
end

查询比较

set statistics time on;
set statistics IO on;

SELECT id , age
FROM [template].[dbo].[demo_emp] with(nolock);

SELECT name , id
FROM [template].[dbo].[demo_emp] ;

场景一: 无其他写事务干扰情况下

(100000 行受影响)
表“demo_emp”。扫描计数 1，逻辑读取次数 764，物理读取次数 0，页面服务器读取次数 0，预读读取次数 0，页面服务器预读读取次数 0，LOb 逻辑读取次数 0，LOB 逻辑读取次数 0，LOB 页面服务器读取次数 0，LOB 预读读取次数 0，LOB 页面服务器预读读取次数 0。

 SQL Server 执行时间:
   CPU 时间 = 31 毫秒，占用时间 = 493 毫秒。

(100000 行受影响)
表“demo_emp”。扫描计数 1，逻辑读取次数 764，物理读取次数 0，页面服务器读取次数 0，预读读取次数 0，页面服务器预读读取次数 0，LOb 逻辑读取次数 0，LOB 逻辑读取次数 0，LOB 页面服务器读取次数 0，LOB 预读读取次数 0，LOB 页面服务器预读读取次数 0。

 SQL Server 执行时间:
   CPU 时间 = 16 毫秒，占用时间 = 531 毫秒。

这么看其实和我们在RC级别下的读取速度没有太大差别

场景二: 长耗时事务占用

(100000 行受影响)
表“demo_emp”。扫描计数 1，逻辑读取次数 764，物理读取次数 0，页面服务器读取次数 0，预读读取次数 0，页面服务器预读读取次数 0，LOb 逻辑读取次数 0，LOB 逻辑读取次数 0，LOB 页面服务器读取次数 0，LOB 预读读取次数 0，LOB 页面服务器预读读取次数 0。

 SQL Server 执行时间:
   CPU 时间 = 62 毫秒，占用时间 = 656 毫秒。

(100000 行受影响)
表“demo_emp”。扫描计数 1，逻辑读取次数 764，物理读取次数 0，页面服务器读取次数 0，预读读取次数 0，页面服务器预读读取次数 0，LOb 逻辑读取次数 0，LOB 逻辑读取次数 0，LOB 页面服务器读取次数 0，LOB 预读读取次数 0，LOB 页面服务器预读读取次数 0。

 SQL Server 执行时间:
   CPU 时间 = 16 毫秒，占用时间 = 2242 毫秒。

从这里就可以明显看出加上nolock后的性能提升查询速度比RC级别快了将近3倍左右但是同时也使得读取的数据有可能不一致

这里说有可能不一致针对现在这种比较单一的修改来说是不存在的但是对于我们开发过程中写业务的那种几百行的存储过程来讲就是很有可能发生的

目录CONTENT

nolock在SQLServer中的作用

nolock?

隔离级别

脏读、幻读

脏读

幻读

脏读的性能提升以及弊端

环境

评论区