sqlserver自旋锁,sqlserver行锁

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

如何判断MSSQL数据库磁盘出现了瓶颈

具体问题具体分析，举例来说明为什么磁盘IO成瓶颈数据库的性能急速下降了。

为什么当磁盘IO成瓶颈之后, 数据库的性能不是达到饱和的平衡状态，而是急剧下降。为什么数据库的性能有非常明显的分界点，原因是什么？

相信大部分做数据库运维的朋友，都遇到这种情况。数据库在前一天性能表现的相当稳定，数据库的响应时间也很正常，但就在今天，在业务人员反馈业务流量没有任何上升的情况下，数据库的变得不稳定了，有时候一个最简单的insert操作，需要几十秒，但99%的insert却又可以在几毫秒完成，这又是为什么了？

dba此时心中有无限的疑惑，到底是什么原因呢? 磁盘IO性能变差了？还是业务运维人员反馈的流量压根就不对？还是数据库内部出问题？昨天不是还好好的吗？

当数据库出现响应时间不稳定的时候，我们在操作系统上会看到磁盘的利用率会比较高，如果观察仔细一点，还可以看到，存在一些读的IO. 数据库服务器如果存在大量的写IO,性能一般都是正常跟稳定的，但只要存在少量的读IO,则性能开始出现抖动，存在大量的读IO时（排除配备非常高速磁盘的机器），对于在线交易的数据库系统来说，大概性能就雪崩了。为什么操作系统上看到的磁盘读IO跟写IO所带来的性能差距这么大呢？

如果亲之前没有注意到上述的现象，亲对上述的结论也是怀疑。但请看下面的分解。

在写这个文章之前，作者阅读了大量跟的IO相关的代码，如异步IO线程的相关的，innodb_buffer池相关的，以及跟读数据块最相关的核心函数buf_page_get_gen函数以及其调用的相关子函数。为了将文章写得通俗点，看起来不那么累，因此不再一行一行的将代码解析写出来。

咱们先来提问题。 buf_page_get_gen函数的作用是从Buffer bool里面读数据页，可能存在以下几种情况。

提问. 数据页不在buffer bool 里面该怎么办？

回答：去读文件，将文件中的数据页加载到buffer pool里面。下面是函数buffer_read_page的函数，作用是将物理数据页加载到buffer pool, 图片中显示

buffer_read_page函数栈的顶层是pread64(),调用了操作系统的读函数。

buf_read_page的代码

如果去读文件，则需要等待物理读IO的完成，如果此时IO没有及时响应，则存在堵塞。这是一个同步读的操作，如果不完成该线程无法继续后续的步骤。因为需要的数据页不再buffer 中，无法直接使用该数据页，必须等待操作系统完成IO .

再接着上面的回答提问：

当第二会话线程执行sql的时候，也需要去访问相同的数据页，它是等待上面的线程将这个数据页读入到缓存中，还是自己再发起一个读磁盘的然后加载到buffer的请求呢？代码告诉我们，是前者，等待第一个请求该数据页的线程读入buffer pool。

试想一下，如果第一个请求该数据页的线程因为磁盘IO瓶颈，迟迟没有将物理数据页读入buffer pool, 这个时间区间拖得越长，则造成等待该数据块的用户线程就越多。对高并发的系统来说，将造成大量的等待。等待数据页读入的函数是buf_wait_for_read，下面是该函数相关的栈。

通过解析buf_wait_for_read函数的下层函数，我们知道其实通过首先自旋加锁pin的方式，超过设定的自旋次数之后，进入等待，等待IO完成被唤醒。这样节省不停自旋pin时消耗的cpu,但需要付出被唤起时的开销。

再继续扩展问题：如果会话线程A 经过物理IO将数据页1001读入buffer之后，他需要修改这个页，而在会话线程A之后的其他的同样需要访问数据页1001的会话线程，即使在数据页1001被入读buffer pool之后，将仍然处于等待中。因为在数据页上读取或者更新的时候，同样需要上锁，这样才能保证数据页并发读取/更新的一致性。

由此可见，当一个高并发的系统，出现了热点数据页需要从磁盘上加载到buffer pool中时，造成的延迟，是难以想象的。因此排在等待热点页队列最后的会话线程最后才得到需要的页，响应时间也就越长，这就是造成了一个简单的sql需要执行几十秒的原因。

再回头来看上面的问题，mysql数据库出现性能下降时，可以看到操作系统有读IO。原因是，在数据库对数据页的更改，是在内存中的，然后通过检查点线程进行异步写盘，这个异步的写操作是不堵塞执行sql的会话线程的。所以，即使看到操作系统上有大量的写IO，数据库的性能也是很平稳的。但当用户线程需要查找的数据页不在buffer pool中时，则会从磁盘上读取，在一个热点数据页不是非常多的情况下，我们设置足够大的innodb_buffer_pool的size, 基本可以缓存所有的数据页，因此一般都不会出现缺页的情况，也就是在操作系统上基本看不到读的IO。当出现读的IO时，原因时在执行buf_read_page_low函数，从磁盘上读取数据页到buffer pool, 则数据库的性能则开始下降，当出现大量的读IO，数据库的性能会非常差。

相关问答

Q1: sql server 2005 2709 出错是什么意思

锁升级禁止升级　

锁定粒度是一个查询或更新所锁定的最小数据，粒度不同数据库的性能和并发能力是此消彼长的，怎么来理解呢？锁定的粒度越小并发的用户数越多，这是显而易的，如果这时发生一种情况，根据业务规律要锁定大量的记录行来进行更新，在保持并发用户的前提下，我们锁定的记录的行锁或键锁就很多，我们知道锁定不是免费的午餐，是要付出代价的，管理的锁定多越多系统资源开销就越大。还记得我们在前面介绍过锁块吧，锁块是一个64/128(128是64位操作系统)字节的内存块，另外对每一个申请或正持有锁块的进程还要准备一个32/64(64是64位操作系统)字节的内存块来描述这些进程，在这儿我们确定一个前提：不管锁定粒度的大小，每一个锁定都占用几乎同样的系统开销。好，比如我们要进行10W行数据更新，为了并发我们都采用行锁来锁定，按照锁块的定义那么我们就得需要64B * 100000+N*32B= 6400000B +32NB(理论更新我们取N=1相对于6400000可以忽略) 6.4M的RAM来管理这些行锁，假设并发进程(当然是不同资源上的)数量是X，那么当前数据库就得要X*6.4M的RAM用于管理锁定，显然这种对RAM需求的上升是系统无法忍受，不可能无限制的满足的这种增长，那么SQLSERVER得用一种办法来防止系统使用太多的内存来追踪锁定并且提高锁定的效率。这个任务交给了锁管理器，它负责平衡资源的使用(当然还负责从特定操作的开始到结束保持连续、逻辑完整性)，这时管理器就采取锁定升级这一明智造择，从行锁或键锁或页锁升级为表级锁定，比较6.4M和96B，显然获取一个表级锁定比持有许多行或键锁更有意义。

锁升级的意义是显而易见，使得锁定开销下降并避免系统资源耗尽。在结构引擎里我们提及锁管理器，系统分配给锁管理器的内存是有限的，锁的升级保证了锁定占用内存维持一个合理的限度。

锁升级发生的时机：

1、在一个对象上一个查询或更新持有锁的数量超过阀值。SQL2005缺省是5000个锁(记得SQL6.0只有200个，但是我们要记住SQL6.0只有页面锁定哦)。

2、锁资源占用的内存超过AWE或常规内存的40%，40%是一个约数。

时机一满足SQLSERVER就会尝试锁升级，当然升级不一定会成功，当失败后在同一个对象上的锁资源再次上升到一定程度时升级会再次发生，如果升级成功SQLSERVER会释放对象上先前获得的行、键、分页锁定。升级失败发生当另外一个进程对表有行或页有排它锁定时。

锁升级潜在的危险：

1、锁升级的结果一定是一个完全表级锁定，也就是不可能出现行锁升级为页锁的，最细的行级锁升级的直接结果一定是表锁定。

2、锁升级可能造成意外的阻塞(这个应该是很好理解的)

3、锁升级成功后无法降级

禁止升级我们知道锁升级是有潜在的危险，并且这种升级的结果是不可能现降级除非事务结束。所以升级不是对所有的应用都是一件好事，MS提供了两个开关项：1211和1224，我们可以通过设置跟踪标识来禁止升级。

7、行锁、页锁

7.0之前的版本锁定的最小粒度就是页锁，提醒大家一下那时的页面最小单位是2K，如果细心部署一定程度上是可以满足够大的呑吐量和可以接受的响应时间。然后7.0后把分页从2KB提升为8KB时(为什么要提升呢？嘿嘿，留一个疑问给大家)，这种页面锁定对并发能力是一种挑战，也就是锁定的范围是7.0之前的4倍，这时并发及响应时间都成一个问题。SQL2005完全实现行级锁定，显然这对并发响应是可喜的，可是正如我在锁升级里给大家算的一笔帐，在有限可利用的锁定资源前提下，大量行级锁定的代价还是让人无法接受的，特别在极限的状态下。

我们知道锁定操作是一个密集型操作，一个锁定不仅要看到内存的损耗，还要看到SQLSERVER管理这些锁定对其本身来也是一种负荷。虽然SQL内部使用闩或自旋锁来降低这种负荷，但我们很容易可以想像管理一个分页锁定比管理N个行级锁定(假设页面内有N行记录)更轻松、更有效率。

比较行锁和页锁，行锁降低了并发冲突但是资源的损耗也是显然的，页锁减少必须存在锁的数量及管理这些锁定的资源损耗但是以并发能下降为代价的。到底哪个更合适，恐怕不是一句两句能说完的，因为针对不同应用、不同行业、不同并发模型、不同隔离两都各有各的优势。

在SQLSERVER2005可以用sp_indexoption来控制索引的锁定单位。关于这个设置我们可以看看联机帮助，但是一定要注意它只针对索引所以对堆表无法控制分页锁定。

8、动态管理锁定

SQL造择锁定类型、粒度是基于行数、可能扫描的页面数、分页上的行数，隔离级别、进行的何种操作、可使用的系统资源等因素的影响，根据这些影响因素SQLSERVER选择一种合适的锁定模式这个过程称动态锁定策略(我发现策略在MS很流行)，数据库引擎(还有印象我有引擎结构中介绍的存储引擎吧)动态的管理粒度和锁定模式，控制锁定与系统资源的最佳成本效率。一个范围内的锁定所要使用的系统资源肯定小，但是系统的并发性也就降低，如果选择小范围内的锁定，那管理锁定所使用的系统资源上升，然而并发性能却得到了淋漓发挥。

一般情况我们可使用系统缺省设置(行级锁定是系统缺省的)，让系统决定是否要进行锁定的升级。这样一来简化我们对库锁定的管理，系统根据实际情况平衡负载。

9、死锁

首先，我们得清楚死锁与等待是两回事。等待是当前进程所需要的资源让另一个进程排它了，只要另外一个进程释放，当时进程就可以继续执行(当然如果另外这个进程已经死锁那会进入无限期等待，但是这种情况一般不会发生，因为SQLSERVER会干预死锁的。另外我们还有一个锁定超时设置，这方面大家可以看联机丛书)。而死锁是发生在两个进程间，在没有人为干预两个锁定的进程是都无法继续工作的一种困境。另外一个显著的地方就是死锁一旦发生，SQLSERVER就会干预进来，我们所能感知比如接收到1205号错误，健壮的应用系统会人工干预1205错误，恰当的重新提交批处理，当1205错误发生没有终止的进程获得相应的资源并处理自己的事务直至释放资源，其实这种人为的干预潜在的又为死锁提供一个外在环境。当然我们前面写的一个过程也可以查询到相应的锁定信息。

接着，死锁是无法完全避免的。在一个并发的多用户系统，锁定、线程、内存、并行查询、MARS中死锁的发生是正常的、可以预见的，也是必然的。在我们能力范围内只能尽可能的在应用端或服务器上恰当的处理死锁，使得这种无法完全避免的事件给系统带来的影响降到最低。也就是我们应该明白：死锁是无法完全避免，但是我可以降低发生的次数。

第三，死锁是一种末日，没有人为干预时永远退不出这种状态。一个并发的多用户系统这种竞争资源的可能性是很大的，一有竞争就会有“矛盾”发生，双方等待对方释放自己所需要的资源，必然成了无限期等待，这种等待就是我们所说的死锁。我们通过上面的介绍知道这时SQLSERVER锁管理器会干预这个过程，试想如果没有SQLSERVER锁管理器的干预那么两个进程一根筯的结果就是无限期等待，对于应用系统来说就是一个末日。SQLSERVER2005更是提供了丰富的锁有关元数据，可以很方便的侦察出锁定信息，SQLSERVER锁管理器干预的结果就是根据牺牲品的优先等级及回滚代价，把优先级低和代价最小的进程当作牺牲品，杀掉这个进程并抛出1205错误。

第四，死锁大体分为三类：cycle死锁、conversion死锁、应用级死锁及不明死锁。

Q2: sql server 2014 sp2能不能加载sql server 2016的数据库

需要在sqlserver启动参数里添加 Trace Flag 8079 来打开这个功能，这也是SQL2016的新功能

DBCC CHECK* 系列命令可以使用MAXDOP查询提示：使用局部配置而不是全局配置的sp_configure 值来控制DBCC CHECK* 的并行度

buffer pool可以利用8TB内存：通过128TB的虚拟地址空间，SQL Server的buffer pool可以利用到8TB内存的buffer pool

SOS_RWLock 自旋锁增强：SOS_RWLock 是一个同步原语并且在SQL Server的代码库里的很多地方都有使用。

Q3: 求助sqlserver服务器压力问题

刚才参考了这个

看了你几个出现次数比较多的等待，下面可以参考，另外，症状和解决方案-LATCH_XX

这意味着

存在非页闩锁

使用sys.dm_os_latch_stats来分析哪一个闩锁等待时间过长