云顶娱乐平台 9

【云顶娱乐平台】SQL Server 中WITH (NOLOCK)

 一.  概述

  这次介绍实例级别资源等待LCK类型锁的等待时间,关于LCK锁的介绍可参考
“sql server
锁与事务拨云见日”。下面还是使用sys.dm_os_wait_stats
来查看,并找出耗时最高的LOK锁。

select wait_type,
waiting_tasks_count,
wait_time_ms ,
max_wait_time_ms,
signal_wait_time_ms
from sys.dm_os_wait_stats
where wait_type like 'LCK%' 
order by  wait_time_ms desc

 查出如下图所示:

云顶娱乐平台 1

   1.  分析介绍

   重点介绍几个耗时最高的锁含义:

    LCK_M_IX:
正在等待获取意向排它锁。在增删改查中都会有涉及到意向排它锁。
  LCK_M_U: 正在等待获取更新锁。 在修改删除都会有涉及到更新锁。
  LCK_M_S:正在等待获取共享锁。
主要是查询,修改删除也都会有涉及到共享锁。
  LCK_M_X:正在等待获取排它锁。在增删改中都会有涉及到排它锁。
  LCK_M_SCH_S:正在等待获取架构共享锁。防止其它用户修改如表结构。
  LCK_M_SCH_M:正在等待获取架构修改锁 如添加列或删除列
这个时候使用的架构修改锁。

      下面表格是统计分析

锁类型 锁等待次数 锁等待总时间(秒) 平均每次等待时间(毫秒) 最大等待时间
LCK_M_IX 26456 5846.871 221 47623
LCK_M_U 34725 425.081 12 6311
LCK_M_S 613 239.899 391 4938
LCK_M_X 4832 77.878 16 4684
LCK_M_SCH_S 397 77.832 196 6074
LCK_M_SCH_M 113 35.783 316 2268

  注意: wait_time_ms
时间里,该时间表包括了signal_wait_time_ms信号等待时间,也就是说wait_time_ms不仅包括了申请锁需要的等待时间,还包括了线程Runnable
的信号等待。通过这个结论也能得出max_wait_time_ms
最大等待时间不仅仅只是锁申请需要的等待时间。

 

2. 重现锁等待时间

--  重置
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);  

 云顶娱乐平台 2

--  会话1 更新SID=92525000, 未提交
begin tran 
update [dbo].[PUB_StockTestbak] set model='mmtest' where sid=92525000

-- 会话2 查询该ID, 由于会话1更新未提交 占用x锁,这里查询将阻塞
select * from [PUB_StockTestbak] where sid=92525000

   手动取消会话2的查询,占用时间是61秒,如下图:

云顶娱乐平台 3

  再来统计资源等待LCK,如下图 :

云顶娱乐平台 4

  总结:可以看出资源等待LCK的统计信息还是非常正确的。所以找出性能消耗最高的锁类型,去优化是很有必要。比较有针对性的解决阻塞问题。

3. 造成等待的现象和原因

现象:

  (1)  用户并发越问越多,性能越来越差。应用程序运行很慢。

  (2)  客户端经常收到错误 error 1222 已超过了锁请求超时时段。

  (3)  客户端经常收到错误 error 1205 死锁。

  (4)  某些特定的sql 不能及时返回应用端。

原因:

  (1) 用户并发访问越多,阻塞就会越来越多。

  (2) 没有合理使用索引,锁申请的数量多。

  (3) 共享锁没有使用nolock, 查询带来阻塞。 好处是必免脏读。

  (4) 处理的数据过大。比如:一次更新上千条,且并发多。

  (5) 没有选择合适的事务隔离级别,复杂的事务处理等。

4.  优化锁的等待时间

   在优化锁等待优化方面,有很多切入点 像前几篇中有介绍
CPU和I/O的耗时排查和处理方案。 我们也可以自己写sql来监听锁等待的sql
语句。能够知道哪个库,哪个表,哪条语句发生了阻塞等待,是谁阻塞了它,阻塞的时间。

  从上面的平均每次等待时间(毫秒),最大等待时间
作为参考可以设置一个阀值。 通过sys.sysprocesses 提供的信息来统计,
关于sys.sysprocesses使用可参考”sql server 性能调优
从用户会话状态分析”。
通过该视图
监听一段时间内的阻塞信息。可以设置每10秒跑一次监听语句,把阻塞与被阻塞存储下来。

   思想如下:

-- 例如 找出被阻塞会话ID 如时间上是2秒 以及谁阻塞了它的会话ID
SELECT spid,blocked #monitorlock FROM sys.sysprocesses 
where blocked>0 and    waittime>2000 

-- 通过while或游标来一行行获取临时表的 会话ID,阻塞ID,通过exec动态执行来获取sql语句文本 进行存储
exec('DBCC INPUTBUFFER('+@spid+')') 

exec('DBCC INPUTBUFFER('+@blocked+')') 

 

云顶娱乐平台 5

BEGIN TRAN

二. 事务总结

   2.1   事务不同隔离级别的优缺点,以及使用场景 如下表格:

隔离级别         

优点

缺点 使用场景
未提交读                      读数据的时候,不申请共享锁,所以不会被阻塞 读到的数据,可能会脏读,不一致。 如做年度,月度统计报表,数据不一定要非常精确
已提交读       比较折中,而且是推荐的默认设置 有可能会阻塞,在一个事务里,多次读取相同的数据行,得到的结果可能不同。 一般业务都是使用此场景
可重复读 在一个事务里,多次读取相同的数据行,得到的结果可保证一致、 更严重的阻塞,在一个事务里,读取符合某查询的行数,会有变化(这是因为事务里允许新增)  如当我们在事务里需要,多次统计查询范围条件行数, 做精确逻辑运算时,需要考虑逻辑是否会前后不一致.
可序列化 最严重格的数据保护,读取符合某查询的行数,不会有变化(不允许新增)。 其它事务的增,删,改,查 范围内都会阻塞  如当我们在写事务时,不用考虑新增数据带来的逻辑错误。
行版本控制已提交读

阻塞大大减少(读与读不阻塞,读与写不阻塞)

阻塞减少,能读到新数据
大多情况下行版本控制的已提交读比快照隔离更受欢迎:
1、RCSI比SI占用更少的tempdb空间 。
2、RCSI支持分布式事务,而SI不支持 。
3、RCSI不会产生更新冲突 。
4、RCSI无需再应用程序端作任何修改。唯一要更改的只是一个数据库选项。

写与写还是会阻塞,行版本是存放在tempdb里,数据修改的越多,需要

存储的信息越多,维护行版本就

需要越多的的开销

如果默认方式阻塞比较严重,推荐用行版本控制已提交读,改善性能
快照隔离

阻塞大大减少(读与读不阻塞,读与写不阻塞)

阻塞减少,有可能读到旧数据
1、不太可能由于更新冲突而导致事务必须回滚得情况
2、需要基于运行时间长、能保证时间点一致性的多语句来生成报表的情况

维护行版本需要额外开销,且可能读到旧的数据 允许读取稍微比较旧版本信息的情况下

  2.2 锁的隔离级别(补充)

    了解了事务的隔离级别,锁也是有隔离级别的,只是它针对是单独的sql查询。下面包括显示如下

     select  COUNT(1) from dbo.product(HOLDLOCK)

HOLDLOCK

在该表上保持共享锁,直到整个事务结束,而不是在语句执行完立即释放所添加的锁。

与SERIALIZABLE一样

NOLOCK

不添加共享锁和排它锁,仅应用于SELECT语句

与READ UNCOMMITTED一样

PAGLOCK

指定添加页锁(否则通常可能添加表锁)。 

READPAST

跳过已经加锁的数据行, 仅应用于READ COMMITTED隔离性级别下事务操作中的SELECT语句操作

ROWLOCK

使用行级锁,而不使用粒度更粗的页级锁和表级锁

建议中用在UPDATE和DELETE语句中。

TABLOCKX

表上使用排它锁, 这个锁可以阻止其他事务读或更新这个表的数据

UPDLOCK

指定在读表中数据时设置更新锁(update lock)而不是设置共享锁,作用是允许用户先读取数据(而且不阻塞其他用户读数据),并且保证在后来再更新数据时,这一段时间内这些数据没有被其他用户修改

1 ALTER INDEX idx_Col1 ON Foo REBUILD
2 WITH
3 (
4    ONLINE = ON
5 )
6 GO

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

七.事务并发检查

  在检查并发方面,有很多种方式像原来的如sp_who,sp_who2等系统存储过程,perfmon计数器,sql
Trace/profiler工具等,检测和分析并发问题,还包括sql server
2005以及以上的:

   DMV  特别是sys.dm_os_wait_stats和sys.dm_os_waiting_tasks
,这里简单讲下并发检查

        例如:查询用户会话的相关信息

     SELECT  blocking_session_id FROM sys.dm_os_waiting_tasks
WHERE session_id>50

    blocking_session_id 阻塞会话值有时为负数: 

    -2 :被阻塞资源属于孤立分布式事务。

    -3: 被阻塞资源属于递延恢复事务。

    -4: 对于锁存器等待,内锁存器状态转换阻止了session的识别。

  例如:下面查询阻塞超5秒的等待

      SELECT blocking_session_id FROM sys.dm_os_waiting_tasks
WHERE wait_duration_ms>5000

  例如:只关注锁的阻塞,可以查看sys.dm_tran_locks
    SELECT * FROM sys.dm_tran_locks WHERE request_【云顶娱乐平台】SQL Server 中WITH (NOLOCK)。status=’wait’

        通过sys.dm_exec_requests查看用户请求

        通过sqlDiag.exe收集运行系统的信息

        通过errorlog里打开跟踪标识1222 来分析死锁

        通过sys.sysprocess 检测阻塞。

       

云顶娱乐平台 6

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

六.事务死锁

   6.1
在关系型数据库里都有死锁的概念,在并发访问量高时,事务里或者T-sql大批量操作(特别是修改删除结果集),都有可能导致死锁。死锁是由两个互相阻塞的线程组成也称为抱死。sql
server死锁监视器进程会定期检查死锁,默认间隔为5秒,会自动判断将回滚开销影响最少的事务作为死锁牺牲者,并收到1025
错误,消息模板来自master.dbo.sysmessages表的where
error=1205。当发生死锁时要了解两方进程的sessionid各是多少,
各会话的查询语句,冲突资源是什么。请查看死锁的分析排查。

   会产生死锁的资源主要是:锁
(就是上篇讲的数据行,页,表等资源),其它的死锁包括如:1.
工作者线程调度程序或CLR同步对象。2.两个线程需要更多内存,但获得授权前一个必须等待另一个。3.同一个查询的并行线程。4.多动态结果集(MARS)资源线程内部冲突。这四种很少出现死锁,重点只要关注锁资源带来的死锁。

    6.2 下面事务锁资源产生死锁的原理:

     1. 事务T1和事务T2 分别占用共享锁RID第1行和共享锁RID第2行。

     2. 事务T1更新RID2试图获取X阻塞,事务T2更新RID2试图获取X阻塞。

     3.  事务各自占有共享锁未释放,而要申请对方X锁会排斥一切锁

云顶娱乐平台 7

 6.3 死锁与阻塞的区别

  阻塞是指:当一个事务请求一个资源尝试获取锁时,被其它事务锁定,请求的事务会一直等待,直到其它事务把该锁释放,这就发生了阻塞,默认情况sqlserver会一直等下去。所以阻塞往往能持续很长时间,这对程序的并发性能影响很大。

  死锁是两个或多个进程之间的相互等待,一般在5秒就会检测出来,消除死锁。并发性能不像阻塞那么严重。

  阻塞是单向的,互相阻塞就变成了死锁。

 6.3 尽量避免死锁的方法

  按同一顺序访问对象

  避免事务中的用户交互

  保持事务简短

  合理使用隔离级别

  调整语句的执行计划,减少锁的申请数目。  

所有主要的等待类型(LCK_M_*)都有额外的锁优先级等待类型。这个非常酷,也非常强大,因为你很容易从中可以跟踪到为什么在线重建索引操作被阻塞。另外,对于分区切换(Partition
Switching)也适用同样的技术(锁优先级(Lock
Priorities)),因为在切换期间,操作也要在2个表(原表,目标表)上获取架构修改锁(Schema
Modification Lock (Sch-M))。

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

五.分布式事务

      分布式事务是跨越两个或多个称为资源管理器的服务器。
称为事务管理器的服务器组件必须在资源管理器之间协调事务管理。在 .NET
Framework 中,分布式事务通过 System.Transactions 命名空间中的 API
进行管理。 如果涉及多个永久资源管理器,System.Transactions API
会将分布式事务处理委托给事务监视器,例如 Microsoft 分布式事务协调程序
(MS DTC),在Windows服务里该服务叫Distributed Transaction Coordinator
默认未启动。

  在sql server里 分布式是通过BEGIN DISTRIBUTED TRANSACTION
的T-SQL来实现,是分布式事务处理协调器 (MS DTC) 管理的 Microsoft 分布式事务的起点。执行 BEGIN
DISTRIBUTED TRANSACTION 语句的 SQL Server
数据库引擎的实例是事务创建者。并控制事务的完成。 当为会话发出后续 COMMIT TRANSACTION 或 ROLLBACK
TRANSACTION 语句时,控制事务实例请求 MS DTC
在所涉及的所有实例间管理分布式事务的完成(事务级别的快照隔离不支持分布式事务)。

在执行T-sql里
查询多个数据库主要是通过引用链接服务器的分布式查询,下面添加了RemoteServer链接服务器

USE AdventureWorks2012;  
GO  
BEGIN DISTRIBUTED TRANSACTION;  
-- Delete candidate from local instance.  
DELETE AdventureWorks2012.HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
-- Delete candidate from remote instance.  
DELETE RemoteServer.AdventureWorks2012.HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
COMMIT TRANSACTION;  
GO  
  • LCK_M_SCH_S_LOW_PRIORITY
  • LCK_M_SCH_M_LOW_PRIORITY
  • LCK_M_S_LOW_PRIORITY
  • LCK_M_U_LOW_PRIORITY
  • LCK_M_X_LOW_PRIORITY
  • LCK_M_IS_LOW_PRIORITY
  • LCK_M_IU_LOW_PRIORITY
  • LCK_M_IX_LOW_PRIORITY
  • LCK_M_SIU_LOW_PRIORITY
  • LCK_M_SIX_LOW_PRIORITY
  • LCK_M_UIX_LOW_PRIORITY
  • LCK_M_BU_LOW_PRIORITY
  • LCK_M_RS_S_LOW_PRIORITY
  • LCK_M_RS_U_LOW_PRIORITY
  • LCK_M_RIn_NL_LOW_PRIORITY
  • LCK_M_RIn_S_LOW_PRIORITY
  • LCK_M_RIn_U_LOW_PRIORITY
  • LCK_M_RIn_X_LOW_PRIORITY
  • LCK_M_RX_S_LOW_PRIORITY
  • LCK_M_RX_U_LOW_PRIORITY
  • LCK_M_RX_X_LOW_PRIORITY

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

  在锁与事务系列里已经写完了上篇中篇,这次写完下篇。这个系列俺自认为是有条不紊的进行,但感觉锁与事务还是有多很细节没有讲到,温故而知新可以为师矣,也算是一次自我提高总结吧,也谢谢大伙的支持。在上一篇的末尾写了事务隔离级别的不同表现,还没写完,只写到了重复读的不同隔离表现,这篇继续写完序列化,快照的不同隔离表现,事务隔离级别的总结。最后讲下事务的死锁,事务的分布式,事务的并发检查。

但是LCK_M_S_LOW_PRIORITY并不是新的等待类型。在SQL
Server 2014里,当你查看DMV sys.dm_os_wait_stats时,会看到21个新的等待类型:

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

一. 事务隔离不同表现

设置序列化

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

设置行版本控制已提交读

ALTER DATABASE  Test  SET  READ_COMMITTED_SNAPSHOT on; 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

设置快照隔离

ALTER DATABASE Test
SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

1.1 已重复读和序列化与其它事务并发,的区别如下表格: 

可重复读

序列化 其它事务

SET TRANSACTION ISOLATION

LEVEL REPEATABLE READ

SET TRANSACTION ISOLATION

LEVEL SERIALIZABLE

 

begin tran

select count(*) from product

where memberID=9708

这里显示500条数据,事务还没有结束 

begin tran

select count(*) from product

where memberID=9708

这里显示500条数据,事务还没有结束 

 
   

begin tran

insert into product

values(‘test2’,9708)

其它事务里,想增加一条数据。

如果并发的事务是可重复读,

这条数据可以插入成功。

如果并发的事务是序列化,

这条数据插入是阻塞的。

select count(*) from product

where memberID=9708

在事务里再次查询时,发现显示501条数据

 select count(*) from product

where memberID=9708

在事务再次查询时,还是显示500条数据

 

 commit tran

在一个事务里,对批数据多次读取,符合条件

的行数会不一样。

 commit tran

事务结束

 如果并发是可序列化并且commit,

其它事务新增阻塞消失,插入开始执行。

1.2
已提交读、行版本控制已提交读、快照隔离,与其它事务并发,的区别如下表格: 

已提交读

行版本控制已提交读 快照隔离 其它事务

SET TRANSACTION ISOLATION

LEVEL READ COMMITTED 

ALTER DATABASE Test SET
READ_COMMITTED_SNAPSHOT
ON;

SET TRANSACTION ISOLATION
LEVEL READ COMMITTED

ALTER DATABASE TEST SET
ALLOW_SNAPSHOT_ISOLATION
ON;

SET TRANSACTION ISOLATION
LEVEL SNAPSHOT

 

begin tran

select model from product
where sid=9708

得到值为test

begin tran

select model from product
where sid=9708

得到值为test

begin tran

select model from product
where sid=9708

得到值为test

 
     

begin tran
update product set
model=’test1′
where sid=1

select model from product
where sid=9708

事务里再次查询 阻塞

select model from product
where sid=9708

事务里再次查询值为test, 读到行版本

select model from product
where sid=9708
事务里再次查询值为test,读到行版本

 
 阻塞解除,再次查询返回 test1

再次查询 test1
其它事务提交后,这里读到的是新
(修改后的)数据

再次查询 test

其它事务提交后,这里读取还是旧数据
(行版本数据)

 commit tran
 事务里updaate修改 修改成功  事务里updaate修改 修改成功  事务里updaate修改, 修改失败报错

 

 为了触发阻塞,我在不同的会话开始一个新的事务,但不提交:

云顶娱乐平台,看下这三个区别:
SELECT * FROM TEST NOLOCK — nolock起到了表的别名的作用

 

打开回话三查询阻塞情况:
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_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
打开会话四:执行

 1 -- Perform an Online Index Rebuild
 2 ALTER INDEX idx_Col1 ON Foo REBUILD
 3 WITH
 4 (
 5    ONLINE = ON
 6    (
 7       WAIT_AT_LOW_PRIORITY 
 8       (
 9          MAX_DURATION = 1, 
10          ABORT_AFTER_WAIT = SELF
11       )
12    )
13 ) 
14 GO

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

如果你在这里指定了BLOCKERS选项,那么阻塞的会话就会回滚。当我们同时(在1分钟期间)查看DMV sys.dm_tran_locks,我们看到了有趣的东西:

这个东西是有利有弊,

我希望这篇文章可以让你理解SQL
Server 2014里的锁优先级(Lock Priorities),还有为什么SQL
Server里的“在线”操作实际上只是“部分在线”。

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

1 SELECT * FROM    sys.dm_tran_locks

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

参考文章:

https://www.sqlpassion.at/archive/2014/01/02/how-sql-server-2014-improves-online-operations-that-arent-online-operations/

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

在今天的文章里,我想谈下在线索引重建操作( Online Index Rebuild
operations)
,它们在SQL Server
2014里有怎样的提升。我们都知道,自SQL Server
2005开始引入了在线索引重建操作。但这些在线操作并非真正的在线操作,因为在操作开始时,SQL
Server需要获得共享表锁(Shared Table Lock
(S) ),在操作结束时需要在对应表上获得架构修改锁(Schema Modification
Lock (Sch-M) )。因此这些操作是真正的在线操作,只是营销技巧(marketing
trick)。但是,亲,“在线”肯定比“部分在线”好听多了。

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

1 BEGIN TRANSACTION
2 
3 UPDATE Foo SET Col2 = 2
4 WHERE Col1 = 1

 

在这个情况下,我们的ALTER INDEX语句会等待1分钟(MAX_DURATION),然后语句本身取消了(ABORT_AFTER_WAIT)。

with(nolock)的功能:

 1 -- Creates a new database
 2 CREATE DATABASE Test
 3 GO
 4 
 5 -- Use the database
 6 USE Test
 7 GO
 8 
 9 -- Create a simple table
10 CREATE TABLE Foo
11 (
12     Col1 INT IDENTITY(1, 1) NOT NULL,
13     Col2 INT NOT NULL,
14     Col3 INT NOT NULL
15 )
16 GO
17 
18 -- Create a unique Clustered Index on the table
19 CREATE UNIQUE CLUSTERED INDEX idx_Col1 ON Foo(Col1)
20 GO
21 
22 -- Insert a few test records
23 INSERT INTO Foo VALUES (1, 1), (2, 2), (3, 3)
24 GO

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

 

SELECT * FROM TEST (NOLOCK);

好了,我们来实操下。我们新建一个数据库,一个简单的表和一个聚集索引。 

SELECT * FROM TEST WITH(NOLOCK);

1 SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id='59'

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

当你查看DMV sys.dm_tran_locks时,你会看到那个需要共享锁(Shared
Lock(S))的会话需要等待。这个会话会永远等待。我刚才就说过:“部分在线”……

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

从图中可以看到,SQL
Server这里请求一个LOW_PRIORITY_WAIT的状态。因此3个请求状态(GRANT,WAIT,CONVERT)有了第4个选项:LOW_PRIORITY_WAIT。当我们查看DMV sys.dm_os_waiting_tasks时,事情变得有意思(59是执行语句的会话ID):

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

1 SELECT * FROM sys.dm_os_wait_stats WHERE wait_type LIKE '%LOW_PRIORITY%'

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

当我们执行带有锁优先级(Lock
Priority)的在线索引重建时,有趣的事情发生了: 

在线索引重建操作的等待会话报告了一个新的等待类型LCK_M_S_LOW_PRIORITY。这意味着当在线索引重建操作被阻塞时,我们可以从服务器级别(sys.dm_os_wait_stats)的等待统计信息里获得——不错!

当阻塞情况发生时,你可以用WAIT_AT_LOW_PRIORITY关键字定义如何处理。使用第1个属性MAX_DURATION指定你想要等待的时间——这里是分钟,不是秒!用ABORT_AFTER_WAIT属性你指定哪个会话需要被SQL
Server回滚。SELF意味着那个ALTER INDEX
REBUILD语句会回滚,当你指定BLOCKERS时,阻塞的会话会回滚。当然,当没有阻塞发生时,在线索引重建操作会立即执行。因此这里你只能配置当阻塞情况发生时要怎么处理。

云顶娱乐平台 8

云顶娱乐平台 9 

尽管如此,SQL Server
2014还是在在线索引重建的开始和结束发生的阻塞做了一些改进。因此,在你执行在线索引重建时,你可以定义所谓的锁优先级(Lock Priority)。来看看下面的代码,你会看到起作用的新语法: 

感谢关注!

 1 ALTER INDEX idx_Col1 ON Foo REBUILD
 2 WITH
 3 (
 4    ONLINE = ON
 5    (
 6       WAIT_AT_LOW_PRIORITY 
 7       (
 8          MAX_DURATION = 1, 
 9          ABORT_AFTER_WAIT = SELF
10       )
11    )
12 ) 
13 GO

这意味着我们在需要修改的记录上获得排它锁(Exclusive Lock
(X))
,在对应的页上获得意向排它锁(Intent-Exclusive Lock
(IX))
,在表本身获得意向排它锁(Intent-Exclusive Lock
(IX))
。我们刚刚在SQL Server里创建了典型的锁定层次(locking
hierarchy):表=>页=>记录。在表级别的意向排它锁(IX
Lock)和在线索引重建操作需要的共享锁(Shared
Lock)是不兼容的——典型的锁/阻塞情形发生了。当你现在执行在线索引重建操作时,会发生阻塞: