Skip to content

WITH(NOLOCK) & IsolationLevel.ReadUncommitted

🏷️ SQL Server

为了防止查询不被修改阻塞,在一些允许脏读的查询里使用了 WITH(NOLOCK) 关键字。

另外因为数据库使用了主从结构,从库作为只读库,按照编码规约,所有的查询都应该加上 WITH(NOLOCK) 关键字。但是在实际编码的过程中,有些地方并没有遵循该规约。

So,准备在读库的连接上通过设置隔离级别(IsolationLevel)为 IsolationLevel.ReadUncommitted 来防止被阻塞。

那么 WITH(NOLOCK) & IsolationLevel.ReadUncommitted 有什么区别呢?

下面摘自 StackOverflow

They are the same thing. If you use the set transaction isolation level statement, it will apply to all the tables in the connection, so if you only want a nolock on one or two tables use that; otherwise use the other.

Both will give you dirty reads. If you are okay with that, then use them. If you can't have dirty reads, then consider snapshot or serializable hints instead.