SQL Server 性能诊断（4）- 锁理论

资源使用率不高，甚至偏低，但业务存在卡慢现象时就需要排查锁了。这一篇偏理论，会涉及一些基础的知识，大佬可跳过。

锁阻塞常见现象如下：

1. 并发用户少的时候，一切都还正常。但是随着并发用户的增加，性能越来越慢
2. 客户端经常收到以下错误

1. 1. Error 1222（错误1222）：Lock request time out period exceeded.（已超过了锁请求超时时段。）
   2. Error 1205（错误1205）：Your transaction (process ID #XX) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun your transaction.（事务（进程ID XX）与另一个进程被死锁在XX资源上，并且已被选作死锁牺牲品。请重新运行该事务。）
   3. 超时错误：Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

3. 应用程序运行很慢，但是SQL Server这里CPU和硬盘利用率很低。管理员运行sp_who这样的短小命令能很快返回
4. 有些查询能够进行，但是有些特定的查询或修改总是不能返回
5. 重启SQL Server就能解决。但是有可能跑一段时间以后又会出问题

既然锁会产生阻塞为什么会有锁的存在？这就提到老问题关系型数据库中事务的概念了，其中有经常问到的ACID：

• 原子性(Atomicity):一个事务内的操作,要么全部成功,要么全部失败。
• 一致性(Consistency):事务成功前与失败后,数据要一致。
• 隔离性(Isolation):事务与事务之间是互不干扰的。
• 持久性(Durability):事务成功后,将被永久保存下来,不会再被回滚。

那么要实现ACID，有两方面：

1. 程序来实现
2. 数据库来实现

程序实现不做讨论，我们讨论数据库实现：

1. 锁定资源，使事务保持隔离。
2. 先写入日志方式，保证事务的持久性。
3. 事务管理特性，强制保证事务的原子性和一致性。

锁是SQL Server实现事务隔离的一部分，阻塞正是事务隔离的体现。可以理解阻塞是用户使用事务的代价（因为事务要遵循ACID特性）。也就是说，阻塞是实现事务的隔离所带来的不可避免的代价。

对于锁，通常要从下面这几个方面进行考虑：

1. 申请资源的互斥度
2. 锁的范围和数目多少
3. 事务持有锁资源的时间长短

为了达到以上的3个目的，需要研究一下SQL Server的锁资源模式和兼容性，以及它们是怎么被申请和释放的。

SQL Server数据库引擎具有多粒度锁定，允许一个事务锁定不同类型的资源。

数据库引擎可以锁定的资源

数据库引擎使用的资源锁模式

最常见的锁模式兼容性

什么行为会影响锁的粒度，以及持有锁的时间长短呢？

1. 一个事务内部要访问或者修改的数据量越大，它所要申请的锁的数目就会越多，粒度也就可能越大
2. 一个事务做的事情越复杂，它要申请的锁的范围也就会越大
3. 一个事务延续的时间越长，它持有的锁的时间也会越长
4. 事务的隔离级别能影响锁的申请以及释放的时间
5. 而语句的执行计划，也会影响到锁的粒度以及申请的数量

谈到事务隔离级别，就有经典的脏读、不可重复读和幻读的问题，这里不做解释。下面继续看SQL Server提供的事务隔离级别有哪些：

• 未提交读（隔离事务的最低级别，只能保证不读取物理上损坏的数据）。
• 已提交读（数据库引擎的默认级别，可以防止脏读）
• 可重复读（可以防止脏读和不可重复读，但有时候可能会出现幻影数据）
• 快照：使用行版本控制来防止幻读，同时减少阻塞。
• 可序列化（隔离事务的最高级别，它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行）

不同隔离级别允许的并发副作用

1. 未提交读（READ UNCOMMITTED）

• 概念：

• • 允许读取其他事务尚未提交的数据（脏读）。
  • 读操作不会申请共享锁，也不会阻塞其他事务的排他锁。
  • 对应于NOLOCK提示，隔离级别最低。

• 锁的获取：

• • 不申请共享锁或排他锁。

• 锁的释放：

• • 无锁，因此不存在释放时机的问题。

2. 已提交读（READ COMMITTED）

• 概念：

• • 默认隔离级别。
  • 只能读取其他事务已经提交的数据（避免脏读）。
  • 共享锁用于防止当前事务读取未提交的修改，语句执行完后共享锁立即释放。
  • 存在不可重复读和幻读的风险。

• 锁的获取：

• • 在读取数据时申请共享锁（S锁），防止其他事务修改当前正在读取的数据。

• 锁的释放：

• • 语句执行结束时，立即释放共享锁（语句级锁释放）。

3. 可重复读（REPEATABLE READ）

• 概念：

• • 避免脏读和不可重复读，但不能避免幻读。
  • 在事务完成之前，其他事务不能修改当前事务已读取的数据。
  • 对读取的数据行保持共享锁直到事务结束。

• 锁的获取：

• • 读取时申请共享锁（S锁），并在事务结束前保持。
  • 排他锁（X锁）防止其他事务修改当前事务已读取的数据。

• 锁的释放：

• • 事务提交或回滚后释放所有锁（事务级锁释放）。

4. 快照隔离（SNAPSHOT）

• 概念：

• • 基于行版本控制，无需加锁。
  • 读取操作访问行版本，不受写操作的影响。
  • 保证事务级别的一致性（事务期间读取的数据不会变化），但可能导致逻辑过期数据问题。

• 锁的获取：

• • 无需加锁，依赖版本控制。

• 锁的释放：

• • 无锁操作，不存在释放时机问题。

5. 可序列化（SERIALIZABLE）

• 概念：

• • 提供最高的隔离级别，完全避免脏读、不可重复读和幻读。
  • 除了读取行本身加锁外，还会对搜索范围（范围锁）加锁，阻止其他事务插入、修改或删除该范围内的数据。
  • 并发性最低。

• 锁的获取：

• • 在读取数据时申请共享锁（S锁）。
  • 在搜索范围内申请范围锁，防止其他事务插入或删除数据。

• 锁的释放：

• • 所有锁在事务提交或回滚时释放（事务级锁释放）。

不同隔离级别对共享锁的不同处理方式

也就是说，事务隔离级别越高，共享锁被持有的时间越长。而可序列化还要申请粒度更高的范围锁，并一直持有到事务结束。所以，如果阻塞发生在共享锁上面，可以通过降低事务隔离级别得到缓解。

需要说明的是，SQL Server在处理排他锁的时候，每个除SNAPSHOT的事务隔离级别都是一样的。都是在修改的时候申请，直到事务提交的时候释放（而不是语句结束以后立即释放）。如果阻塞是发生在排他锁上面，是不能通过降低事务隔离级别得到缓解的。

线上环境很少有用到未提交读和可序列化这两个事务隔离级别，所以下面比对已提交读、和快照隔离的差异；其中快照隔离又分为有行版本控制的已提交读（READ_COMMITTED_SNAPSHOT）和SNAPSHOT事务隔离级别。普通的RC和RR在SQL Server中都会申请共享锁，只是释放锁的时机不同：

1. 普通已提交事务：一个普通READ COMMITTED事务将读取数据，然后由另一事务修改此数据。执行完的读操作不阻塞由其他事务执行的更新操作。但是，在其他事务已经做了更新操作后，读操作会被阻塞住，直到更新操作事务提交为止。
2. 快照隔离：在快照隔离下运行的事务将读取数据，然后由另一事务修改此数据。快照事务不阻塞由其他事务执行的更新操作，它忽略数据的修改继续从版本化的行读取数据。也就是说，读取到的是数据修改前的版本。但是，当快照事务尝试修改已由其他事务修改的数据时，它将生成错误并终止。
3. 使用行版本控制的已提交读事务与其他事务并发运行。已提交读事务的行为与快照事务的行为有所不同。与快照事务相同的是，即使其他事务修改了数据，已提交读事务也将读取版本化的行。与快照事务不同的是，已提交读将执行下列操作

• • 在其他事务提交数据更改后，读取修改的数据。
  • 能够更新由其他事务修改的数据，而快照事务不能。

所以在选择事务隔离级别时需要先确定两个问题：

1. 最终用户是否接受行版本控制下的运行结果。
2. SQL Server是否能支持行版本控制带来的额外负荷

下面总结了不同隔离级别的优点、缺点、和使用场景。

不同隔离级别的优缺点和使用场景

我们已经铺垫了锁的相关知识点，现在进入正题。第一个问题是：如何监视锁的申请、持有和释放？

通过sp_lock和sys.dm_tran_locks可以观察连接持有的锁以及内容：

sp_lock

SELECT request_session_id, resource_type, resource_associated_entity_id,
        request_status, request_mode, resource_description
    FROM sys.dm_tran_locks

也可以结合其他动态性能视图，直接查出某个数据库上面的锁是在哪些表格，以及哪些索引上面

USE AdventureWorks2012
GO
SELECT request_session_id, resource_type, resource_associated_entity_id,
        request_status, request_mode, resource_description, p.object_id,
        OBJECT_NAME(p.object_id) AS object_name, p.*
    FROM sys.dm_tran_locks LEFT JOIN sys.partitions p
        ON sys.dm_tran_locks.resource_associated_entity_id = p.hobt_id
    WHERE resource_database_id = DB_ID('AdventureWorks2012')
    ORDER BY request_session_id, resource_type, resource_associated_entity_id

注意：必须在表格所属的那个数据库下运行，否则从object_id得不到正确的object_name。

有很多锁是在语句运行的过程中申请和释放的，语句运行结束之后这些锁就会消失。如果这些锁申请不到，也会产生阻塞。那么怎么看一个语句执行过程中锁的申请和释放过程呢？管理员必须借助SQL Server Profiler。在定义一个trace（跟踪）的时候，需要选取下面的Events（事件）：Lock:Accquired和Lock:Released

请注意要选上Show all columns（显示所有列），再选择Lock:Accquired和Lock:Released。

我们知道影响锁的力度以及持有时间跟访问或者修改的数据量、事务复杂度、事务持续时间、事务隔离级别相关以外，还跟执行计划相关。不过本次不对执行计划进行讨论，简单总结SELECT语句上锁以及释放的逻辑：

在非UC隔离级别下SELECT语句上锁规律：

1. 查询在运行的过程中，会对每一条读到的记录或键值加共享锁。如果记录不用返回，那锁就会被释放。如果记录需要被返回，则视隔离级别而定。如果是“已提交读”，则也释放，否则不释放。
2. 对每一个使用到的索引，SQL Server也会对上面的键值加共享锁。
3. 对每个读过的页面，SQL Server会加一个意向锁。
4. 查询需要扫描的页面和记录越多，锁的数目也会越多。查询用到的索引越多，锁的数目也会越多。

所以如果想减少锁阻塞，从设计上可以：

1. 尽量返回少的记录集。返回的结果越多，需要的锁也就越多。
2. 如果返回结果集只是表格所有记录的一小部分，要尽量使用Index Seek，避免全表扫描这种执行计划。
3. 设计好合适的索引。避免SQL Server通过多个索引才找到数据。

继续讨论UPDATE。对于UPDATE语句，可以简单理解为SQL Server先做查询，把需要修改的记录给找到，然后在这个记录上做修改。找记录的动作要加S锁，找到要修改的记录后会先加U锁，再将U锁升级成X锁。

在非UC隔离级别下UPDATE语句上锁规律：

1. 对每一个使用到的索引，SQL Server会对上面的键值加U锁。
2. SQL Server只对要做修改的记录或键值加X锁。
3. 使用到要修改的列的索引越多，锁的数目也会越多。
4. 扫描过的页面越多，意向锁也会越多。在扫描的过程中，对所有扫描到的记录也会加锁，哪怕上面没有修改。

所以如果想减少锁阻塞，从设计上可以：

1. 尽量修改少的记录集。修改的记录越多，需要的锁也就越多。
2. 尽量减少无谓的索引。索引的数目越多，需要的锁也可能越多。
3. 但是也要严格避免表扫描的发生。如果只是修改表格记录的一小部分，要尽量使用Index Seek，避免全表扫描这种执行计划。

继续看在非UC隔离级别下DELETE语句上锁规律：

1. DELETE的过程是先找到符合条件的记录，然后做删除。可以理解成先是一个SELECT，然后是DELETE。所以，如果有合适的索引，第一步申请的锁就会比较少。
2. DELETE不但是把数据行本身删除，还要删除所有相关的索引键。所以一张表上索引数目越多，锁的数目就会越多，也就越容易发生阻塞。

相对于SELECT、UPDATE和DELETE，单条记录的INSERT操作对锁的申请比较简单。SQL Server会为新插入的数据本身申请一个X锁，在发生变化的页面上申请一个IX锁。由于这条记录是新插入的，被其他连接引用到的概率会相对小一些，所以出现阻塞的概率也要小。

在非UC隔离级别下INSERT语句上锁规律：

1. 数据库上的S锁（resource_type = DATABASE）。
2. 表上的IX锁（resource_type = OBJECT）。
3. 每个索引上都要插入一条新数据，所以有一个KEY上的X锁。
4. 在每个索引上发生变化的那个页面，申请了一个IX锁（resource_type =PAGE）。

唯一不同的，是在Heap结构上还得申请一个RID锁。因为真正的数据不是放在索引上，而是放在Heap上的。

总结下来，如果想要降低锁阻塞的影响，可以从如下方面考虑：

1. 选择合适的事务隔离级别
2. 事务的长短和事务的复杂度
3. 从应用整体并发度考虑，单个事务一次处理的数据量不能过多，特别是TP类业务
4. 针对语句在表格上设计合适的索引