理解SQL Server是如何执行查询的 (3/3)

• 页并发访问的保护：闩锁

  在多线程并发情况下，需要防止读线程读到写线程正在写的资源，在编程中，通过使用互斥器(Mutexes), 信号量(Semaphore), 临界区(Critical Section)和事件(Event Object)来保护资源，而这些机制在SQL Server中被统一表示为闩锁。

  闩锁本身是一种数据结构，用于保护并发访问中的资源。闩锁有多种模式，不同模式的兼容性不一。模式和兼容矩阵如下：

  • KP – Keep Latch 保证引用的结构不能被破坏
  • SH – Shared Latch, 读数据页的时候需要
  • UP – Update Latch 更改数据页的时候需要
  • EX – Exclusive Latch 独占模式，主要用于写数据页的时候需要
  • DT – Destroy Latch 在破坏引用的数据结构时所需要

  --	KP	SH	UP	EX	EX	DT
  KP	Y	Y	Y	Y	Y	N
  SH	Y	Y	Y	Y	N	N
  UP	Y	Y	N	N	N	N
  EX	Y	N	N	N	N	N
  DT	N	N	N	N	N	N

  关于闩锁，更多内容：关于闩锁

• 锁

  闩锁是线程并发时保护物理页的机制，锁(Lock)是事务并发时对逻辑页的保护机制。

  • 逻辑 VS. 物理
    锁是一个6字节长的字符串，它本身并不是被锁定的对象，只是描述了哪个对象被锁定了。闩锁是在内存中被锁住的一个实际对象。获取闩锁需要获取实际被锁住的对象，获取锁只需要组织相应的6字节字符串，然后向锁管理器请求某个锁。

  • 线程 VS. 事务
    闩锁由进程中的线程获取和释放，而锁由事务获取和释放。而一个事务可能使用多个线程，如并行查询，查询中的子查询被不同的工作线程执行等。

  锁模式有很多种，只通过其名称，有时很难明白其含义。下面简单解释一下各种锁模式。

  1. 架构稳定锁
     当查询计划执行时需要确保它所引用的各种对象(e.g 表、索引)的结构不会被其它查询修改，于是在查询开始执行时，需要获取所有被引用对象的”架构稳定锁“，实际是SCH-S锁。对于DDL语句，需要获取对象上SCH-M锁，它几乎与所有的锁模式都不兼容。

     SCH-S它通常是在查询编译时获取，编译完成后，通常只是看到IS锁。SCH-M在对象修改的整个过程都被持有，在这期间任何别的查询都无法获取这个对象上的锁。BY Joe .TJ

  2. 共享锁、更新锁和排它锁
     基本的锁模式包括共享锁(S),排它锁(X)和更新锁(U)。S和X锁，顾名思义。更新数据时，需要先找到将被更新的数据行，这些被找到的数据行就被加上U锁，然后将U锁升级成X锁，再更新行或者删除后插入行。U锁与数据读取类型的锁模式是兼容，即不阻塞读操作，同时减少了更新死锁的发生。

  3. 锁的层次结构和意向锁
     锁模式名称前有“I”的都是意向锁，理解意向锁前要先明白锁的层次结构。以扫描一个包含数百万行的表为例。如果每一行获取一个S锁，则需要数百万个S锁，显然开销会非常大。所以查询决定不获取行锁，而获取表上的S锁。这时，如果有一个并发的查询，需要删除表中的某一行，于是需要获取一个X行锁。好了，问题来了：因为有了表级的S锁，锁管理器如何才能知道不能分配X行锁给这个删除操作？锁只是一个描述什么对象被 锁住了的字符串，锁管理器没有办法能过删除操作发来的X行锁字符串判断出：表上已经有S表锁，不能分配请求的X行锁。为了解决这种问题，于是有了意向锁。删除操作知道要删除的行属于哪一个表，在获取X行锁前，需要获取行的上层对象的IX锁，即表的IX锁。而IX锁和S锁是兼容，于是两个事务中的其一必须等待另一个完成才能开始。这样就解决了这个问题。
     同样还有一类转换锁，如SIX,UIX等。比如SIX表示，用S锁模式锁定了表，并且可能锁定下级的某个行。

     关于转换锁的更多资料，分析SIX锁和锁分区导致的死锁

  4. 键范围锁

     锁模式名称前有“R”的都是键范围锁。键范围锁需要在可序列化隔离级别下，它隐式地保护当前事务中已经读取的行集范围不实被其它事务修改。其它也是可序列化的要求，事务中读取的数据，从头到尾都要一致，不允许幻读和幻插入。还有一点要注意，既然是”键“，那就是针对索引，即必需通过索引确定键范围，才能使用键范围锁。

     在默认的已提交读隔离级别下，有种情况会使用到键范围锁：主外键关系的表中，如果设置了外键的级联删除，则删除主键表时，被级联删除的外键表的行会使用键范围锁。BY Joe .TJ

• 数据写入

  数据修改操作方式和数据查询操作类似，包括插入，更新和删除等。数据修改操作符调用next()时，先定位到要修改的数据的位置(对于插入，则是定位到插入数据的新位置)，然后做相应的修改操作。然后再调用next()修改下一行，如此往复。删除和更新操作通常被读操作所驱动，当读取操作符定位到要被删除或者更新的行，然后将行的书签传给删除或者更新操作符执行操作。SQL Server 使用日志预写( Write Ahead Logging )机制保证，每个数据修改操作完前要先写事务日志。数据修改的简单流程如下：

  1. 操作符先定位到将要修改的数据所在的页。同样，页必需要在缓存池中。

  2. 操作符需要获取页上的排它闩锁，避免其它操作符读取到些页。

  3. 操作符生成修改操作的事务日志，然后写入到日志文件(实际是写入到日志缓存区，还没有写入到磁盘上的日志文件)。

  4. 操作符修改缓存池中的数据页。

  5. 在开始数据修改前，将前面日志的LSN写入到数据页的页头作为”最后修改操作的LSN(Last Modified LSN)“。
  6. 释放页上的排它闩锁，其它的操作符可以读取这一页了。注意，直到这一步，所有操作都是发生在内存中，没有任何磁盘操作发生。
  7. 在数据修改事务提交前，必须生成一条日志用于表示此事务已经提交了。然后将这些日志写到磁盘上的日志文件，这样完成了事务的持久化。如果这时发生系统崩溃，在恢复时，恢复进程会REDO这些已经提交的事务。

  8. SQL Server 通过Checkpoint周期性将缓存池中的脏页写到数据文件。

  有一种数据写入的流程与上面描述的有一些差别：最小化日志化(minimally logged write)。只有插入新数据的操作能够被最小日志化，例如：INSERT，使用UPDATE中的 .WRITE()向blob类型的列追加数据。最小化日志操作还必须满足一些其它的条件，才能发生。它在写数据的简单流程如下：

  1. 分配新的页给正在执行执行最小化日志操作(BULK操作符)。
  2. 操作符从缓存池定位到分配给它的新页，然后获取它的排它闩锁。
  3. 生成一条日志记录此页已经用于最小化日志的大容量插入操作。然后这条日志被追加到日志中(在日志缓冲区)，再将这条日志的LSN写入到页头，作为最后修改操作的LSN.
  4. 页被添加到事务中的最小化日志页列表中。
  5. 现在操作符就开始向页中添加更多的行。这个过程中，不需要为每一行插入生成一条日志。只是写入缓存池中的页，现在还没有写入到磁盘。
  6. 当一页被写满，就重复前面的流程分配新页然后再写入数据。
  7. 在最小化日志操作事务提交前，必须将所有被修改的数据页先写入到磁盘。当数据页写入磁盘完成后，会生成一样用于表示事务已提交的日志，然后将这条日志追加到日志文件中(日志缓冲区)。再将所有相关的日志写入到磁盘上的日志文件。
  8. 为了避免“惊群效应”，即所有被修改的数据页在事务提交前同时写入数据文件。为了避免之种情况，SQL Server 使用一个叫积极写(Eager Write)的进程将脏页定入磁盘。积极写进程可以在事务提交前交脏页写到磁盘。

  最小日志操作也是事务性的，满足事务的ACID属性。

  关于最小化日志操作的更多资料：导数中的最小化日志记录：背景和理论

• DDL

  并不是所有的T-SQL语句都使用在执行计划树中迭代操作符的方式执行。最典型的就是DDL语句，例如CREATE TABLE。SQL Server 将数据库中所有对象的元数据存储在内部系统表中，所以任何数据库对象级的操作(如创建表，新增行，删除表等等)都是要通过操作这些元数据来实现。大约有80个内部系统表，它们涵盖了objects, procedures, functions, schemas, users, logins, certificates, views, partitions, permissions, databases, files等所有数据库对象的元数据。虽然DDL不直接使用操作符，但是它直接使用实现操作符的代码来高效地访问内部系统表数据。例如，DDL不调用Seek操作符的next(),而是使用实现Seek操作符中next()的代码去定位数据。DDL通过更新、删除和插入内部系统表的数据来完成自身的操作。有一些DDL还去完成一些系统表外的操作，如在磁盘上创建文件，与Windows集群API协作配置AG等。还有一些DDL需要维护用户表内的数据，如加载新添加的列的默认值，检查现有的数据数据是否符合新加的约束等。

• BACKUP, RESTORE 和 DBCC

  概括的说，BACKUP和RESTORE是复制一个文件到一个文件。BACKUP是把数据文件和日志文件复制到备份文件，RESTORE是把备份文件复制到数据文件和日志文件，当做它们还会处理一些内部系统表的工作。而DBCC 每个命令的行为都不太一样，推荐阅读 CHECKDB from every angle 来了解更多信息。

• 我知道这些有什么用？

  数据库开发者主要面对两种问题：性能调优和解决数据丢失问题。知道这些对后者没有什么多大的用处，但对前者有帮助。一旦你明白，服务器会给每一个客户端发来的请求创建一个任务(Task)线程，性能问题就会被简化为：任何时刻，任务要么在执行，要么在等待。而每一次等待的信息都会被SQL Server 内部会收集起来。推荐一个白皮书，其中有一个非常好的方法论指导如何使用等待信息排查性能瓶颈问题：Performance Tuning Waits Queues