mssql 中 with(nolock)浅析

最新推荐文章于 2024-09-24 10:08:53 发布

loge001

最新推荐文章于 2024-09-24 10:08:53 发布

阅读量995

点赞数

分类专栏： mssql

本文链接：https://blog.youkuaiyun.com/loge001/article/details/41733117

版权

mssql 专栏收录该内容

1 篇文章

订阅专栏

本文深入探讨了在SQL查询中使用NOLOCK hint的性能提升及其潜在副作用，包括脏读、不可重复读、幻读等并发问题。建议通过READCOMMITTED隔离级别和SNAPSHOT隔离级别来最小化锁竞争，同时提供了关于事务、并发影响、锁行为和SQLServer未来版本变更的重要信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近在执行语句的时候，使用了hint--with(nolock)选项，提高了不少性能，但是经过查阅，发现这个hint的副作用有必要明白，

1、说明

READUNCOMMITTED
指定允许脏读。不发布共享锁来阻止其他事务修改当前事务在读的数据，其他事务设置的排他锁不会阻碍当前事务读取锁定数据。允许脏读可能产生较多的并发操作，但其代价是读取以后会被其他事务回滚的数据修改。这可能会使您的事务出错，向用户显示从未提交过的数据，或者导致用户两次看到记录（或根本看不到记录）。有关脏读、不可重复读和幻读的详细信息，请参阅并发影响。

READUNCOMMITTED 和 NOLOCK 提示仅适用于数据锁。所有查询（包括那些带有 READUNCOMMITTED 和 NOLOCK 提示的查询）都会在编译和执行过程中获取 Sch-S（架构稳定性）锁。因此，当并发事务持有表的 Sch-M（架构修改）锁时，将阻塞查询。例如，数据定义语言 (DDL) 操作在修改表的架构信息之前获取 Sch-M 锁。所有并发查询（包括那些使用 READUNCOMMITTED 或 NOLOCK 提示运行的查询）都会在尝试获取 Sch-S 琐时被阻塞。相反，持有 Sch-S 锁的查询将阻塞尝试获取 Sch-M 锁的并发事务。有关锁行为的详细信息，请参阅锁兼容性（数据库引擎）。

不能为通过插入、更新或删除操作修改过的表指定 READUNCOMMITTED 和 NOLOCK。SQL Server 查询优化器忽略 FROM 子句中应用于 UPDATE 或 DELETE 语句的目标表的 READUNCOMMITTED 和 NOLOCK 提示。

注意：
在 SQL Server 的未来版本中，将不再支持在 FROM 子句中使用应用于 UPDATE 或 DELETE 语句目标表的 READUNCOMMITTED 和 NOLOCK 提示。请避免在新的开发工作上下文中使用这些提示，并计划修改当前使用它们的应用程序。

可以通过使用以下任意一种方法，在保护事务避免对未提交的数据修改进行脏读的同时最大程度地减少锁争用：

READ COMMITTED 隔离级别，其中 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON。

SNAPSHOT 隔离级别。

2、如果想充分理解这部分内容，了解下事务

	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

一下部分内容摘自ms官方文档

修改数据的用户会影响同时读取或修改相同数据的其他用户。即这些用户可以并发访问数据。如果数据存储系统没有并发控制，则用户可能会看到以下负面影响：

丢失更新

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其他事务的存在。最后的更新将覆盖由其他事务所做的更新，这将导致数据丢失。

例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。

未提交的依赖关系（脏读）

read uncommited级别可能出现脏读问题

当第二个事务选择其他事务正在更新的行时，会发生未提交的依赖关系问题。第二个事务正在读取的数据还没有提交并且可能由更新此行的事务所更改。

例如，一个编辑人员正在更改电子文档。在更改过程中，另一个编辑人员复制了该文档（该副本包含到目前为止所做的全部更改）并将其分发给预期的用户。此后，第一个编辑人员认为目前所做的更改是错误的，于是删除了所做的编辑并保存了文档。分发给用户的文档包含不再存在的编辑内容，并且这些编辑内容应视为从未存在过。如果在第一个编辑人员保存最终更改并提交事务之前，任何人都不能读取更改的文档，则可以避免此问题。

不一致的分析（不可重复读）

说明：readuncommited和read commited级别可能出现不可重复度问题，read commited是大多数据库的隔离级别，比如oracle mssql等

当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。不一致的分析与未提交的依赖关系类似，因为其他事务也是正在更改第二个事务正在读取的数据。但是，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。此外，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都被其他事务更改，因此我们称之为“不可重复读”。

例如，编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果在编辑人员完成最后一次读取文档之前，作者不能更改文档，则可以避免此问题。

幻读

说明：除了序列化都可能出现的，mysql的默认隔离级别是repeatable read，也就是说mysql会出现幻读，但是不会出现脏读和不可重复读

当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻读问题。由于其他事务的删除操作，事务第一次读取的行的范围显示有一行不再存在于第二次或后续读取内容中。同样，由于其他事务的插入操作，事务第二次或后续读取的内容显示有一行并不存在于原始读取内容中。

例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主副本时，发现作者已将未编辑的新材料添加到该文档中。与不可重复读的情况相似，如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免此问题

序列化

serializable是最高的事务隔离级别，同时代价也是最高的，一般不用，事务顺序执行，避免了上边的所有问题。

由于行更新导致读取缺失和重复读

缺失一个更新行或多次看到某更新行

在 READ UNCOMMITTED 级别运行的事务不会发出共享锁来防止其他事务修改当前事务读取的数据。在 READ COMMITTED 级别运行的事务会发出共享锁，但是在读取行后会释放行锁或页锁。无论哪种情况，在您扫描索引时，如果另一个用户在您读取期间更改行的索引键列，则在键更改将行移至您的扫描位置之前的位置时，该行可能会再次出现。同样，在键更改将行移至您已读取的索引中的某位置时，该行将不会出现。若要避免这种情况，请使用 SERIALIZABLE 或 HOLDLOCK 提示，或者使用行版本控制。有关详细信息，请参阅表提示(Transact-SQL) 和数据库引擎中基于行版本控制的隔离级别。