SQL Server 中WITH (NOLOCK)

 
以前遇到过,但仅限于听同事说加上NOLOCK好一些,今天仔细研究测试了下,终于理解了,那么加与不加到底区别在哪呢?

with(nolock)的功能:

    我先说下其区别,之后再做测试。

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

    大家都知道,每新建一个查询,都相当于创建一个会话,在不同的查询分析器里面进行的操作,可以影响到其他会话的查询,极端的情况可能会一直处于阻塞中,哪怕只是一个很简单的查询都“特别慢”。

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

    BEGIN TRAN
是开始一个事务的意思,开始之后可执行一些SQL语句,接着需要执行COMMIT进行提交或者ROLLBACK进行回滚,否则就会出现上面的情况。但如果使用NOLOCK进行查询的时候,就不会因为别的回话没有提交或回滚,而受阻塞。所以概括起来,可以用以下语句来总结:

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

    NOLOCK能使当前会话的查询,不受其它会话的事务所阻塞。但是这样做,就读取了其它事务的“修改后未提交的”数据。

这个东西是有利有弊,

    现在我们进行测试,一定要注意,必须在多个会话下才可以,也就是说,需要建三个查询分析器窗口。

使用with(nolock)时查询不受其他排它锁阻塞

    表用最简单的表,自己动手建一个。

举个例子:模拟事务正在进行
打开回话一:执行

    查询分析器一:执行

SELECT @@spid查看会话ID –查询当前会话

       SELECT * FROM dbo.test_main

BEGIN TRAN

       得到

UPDATE TEST SET NAME=’Timmy’ WHERE ID =1;

        id    value
        1     one
        2     two
        3     three
        4     four

–ROLLBACK — 不提交也不回滚
打开回话二:执行
SELECT * FROM TEST;

       接着执行如下:

打开回话三查询阻塞情况:
SELECT wt.blocking_session_id AS BlockingSessesionId
,sp.program_name AS ProgramName
,COALESCE(sp.LOGINAME, sp.nt_username) AS HostName
,ec1.client_net_address AS ClientIpAddress
,db.name AS DatabaseName
,wt.wait_type AS WaitType
,ec1.connect_time AS BlockingStartTime
,wt.WAIT_DURATION_MS/1000 AS WaitDuration
,ec1.session_id AS BlockedSessionId
,h1.TEXT AS BlockedSQLText
,h2.TEXT AS BlockingSQLText
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db
ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections ec1
ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2
ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses sp
ON SP.spid = wt.blocking_www.bifa9999com,session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS
h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS
h2
打开会话四:执行

       BEGIN TRAN
       INSERT INTO test_main VALUES(5, ‘five’)

SELECT * FROM TEST WITH(NOLOCK)–会发现数据马上出来

       一行受影响   

这是由于加了with(nolock)会话一事务设置的排他锁不会阻碍当前事务读取锁定数据,所以会话四不会被阻塞

    查询分析器二:执行

 

      SELECT * FROM dbo.test_main

但是:假如由于某种原因,该事务回滚了, SELECT * FROM Book AS b WHERE
b.BookName = ‘Timmy’ AND b.ID = 1
查询到的这边数据就是一条脏数据,又叫无效数据的读出,是指在数据库访问中,事务T1将某一直修改,然后事务T2读取该值,此后T1因为某种原因撤销对该值的修改,这就导致T2所读取到的数据是无效的

      则卡死,受上一会话所阻塞。查不出结果。

所以with(nolock)是有利有弊的
大体使用场景:

      补充:那么卡死怎么办呢?我们已经说过,要执行提交或者回滚操作才可以,那么在会话一中执行COMMIT即可。 之后此查询立刻显示结果。

基础数据表,这些表变更较少
历史数据库修改较少
业务允许出现脏读的情况
数据量超大的表,出于性能考虑,而允许脏读

    查询分析器三:执行

看下这三个区别:
SELECT * FROM TEST NOLOCK — nolock起到了表的别名的作用

       SELECT * FROM test_main(NOLOCK)

SELECT * FROM TEST (NOLOCK);

      则显示如下

SELECT * FROM TEST WITH(NOLOCK);

       id    value
        1     one
        2     two
        3     three
        4     four

(NOLOCK)与WITH(NOLOCK)其实功能上是一样的,但08版本就不推荐省略with

        5     five

在使用链接服务器的SQL当中,(NOLOCK)不会生效,WITH(NOLOCK)才会生效

       但最后一行并没有真正存储在数据库中,因为会话一还没有进行提交,我们用NOLOCK就查询出来了。

  也许你会想,那什么情况下用NOLOCK呢?经过我们的分析,用NOLOCK是为了避免出现卡死状态,那我们就可以分析其环境了。

  一个经常操作的表,并且每次操作都很重要,这样一般要用到事务进行处理,因为可以避免出错的几率,

我们查询时,要用NOLOCK,否则遇上卡死的几率很大。别人执行一个事务,还没处理完呢,你就查询了,那就卡死了。有了NOLOCK就可以解决这个问题了。