SQL Server 阻塞分析
一、加锁（locking）、阻塞（blocking）、死锁（deadlock）定义
加锁：用于管理多个连接的进程。当连接需要访问一块数据时，在这些数据上放置某种类型的锁。
阻塞：指一个连接需要访问一块数据时，必须等待另一个连接的锁解除。
死锁：指两个连接形成被称为"僵局"的形式，它们互相等待对方的锁解除。
在 SQL Server 中，每个连接都可以看作是一个单独的会话。
二、数据库锁
1、锁粒度
为了改善并发性，SQL Server 实现如下资源级别上的锁粒度：
- 行（RID）
- 关键字（KEY）
- 页面（PAG）
- 区（EXT）
- 堆或 B 树（HoBT）
- 表（TAB）
- 数据库（DB）
（1）行级锁
该锁是在一个表的单独行上维护，是数据库表上最低级别的锁。当查询表中的一行数据时，查询被授予该行的 RID 锁。
因为阻塞被限制在所影响的行，所有行级锁提供了很高的并行性。
（2）键级锁
这是索引中的行级锁，被标识为一个关键字（KEY）锁。
对于具有聚集索引的表，表的数据页面和聚集索引的叶子页面相同。因为表和聚集索引的行相同，从表（或聚集索引）中访问行时，在该聚集索引行或有限范围的行中只能获得一个关键字锁。
该锁是针对有聚集索引的表，与行级锁类型，也有很高的并发性。
（3）页级锁
这种锁在表或索引的单一页面中维护，被标识为 PAG 锁。当查询请求一个页面中的多行时，请求的所有行的一致性可以通过获得单独行上的 RID/KEY 锁或整个页面上的一个 PAG 锁来维护。在查询计划中，锁管理器确定获得多个 RID/KEY 锁的资源压力，若果压力比较大，锁管理器请求一个 PAG 锁来代替。
页级锁减少了锁的开销，增进了查询的性能，但它阻塞了该页面上所有行的访问从而损害了数据库的并发性。
（4）区级锁
这种锁在区（一组连续 8 个数据或索引页面）上维护并且标识为 EXT 锁。
如，这种锁用于在一个表上执行 ALTER INDEX REBUILD 命令，并且该表从现有的区移动到新的区时。在这期间，区的完整性用 EXT 锁来保护。
（5）堆或 B- 树锁
堆或 B- 树锁用于描述这两种对象（堆 和 B- 树）被加锁的情况。这意味在一个无序的堆、没有聚集索引的表上的锁，或者一个 B- 树对象上的锁，通常指分区上的锁。因为分区被跨多个文件组存储，每个都有自己的数据分配定义。它的操作类似于表级锁，但是是在分区而不是表上进行。
（6）表级别
这是表上最高级别的锁。在一个表上的表级锁保留了对整个表及其所有索引的访问。
在执行一个查询时，锁管理器若果确定获取行级锁或是页级锁的资源压力较高，这时会直接为查询获取一个表级锁。
（7）数据库级锁
当应用程序建立一个数据库连接时，锁管理器分配一个数据库共享锁给对应的 SPID。这阻止用户意外地在其他用户连接时卸掉或者恢复数据库。
锁级别不需要由用户或数据库管理员指定，锁管理器会自动确定。在访问少量行时，它一般首先行级锁和键级锁以提高并发性。若多个行级锁的开销变得很高时，锁管理器会自动选择合适的较高级别的锁。
2、锁模式
根据所请求的类型，SQL Server 在锁定资源时使用不同的锁模式，下面是注意的三种模式：
- 共享（S）
- 更新（U）
- 排他（X）
（1）共享（S）模式
共享模式只用于只读查询。它不会阻止其他只读查询同时访问数据，因为查询不会破坏数据完整性。但是会阻止数据上的并发修改。
S 锁在数据上维持直到数据读出。默认情况下，SELECT 语句获取的 S 锁在数据读出之后会立即释放。
（2）更新（U）模式
U 锁与 S 锁不同，U 锁目的在于修改，因此为了维护数据完整性，在数据上不允许超过一个 U 锁。U 锁与 UPDATE 语句有关。
UPDATE 操作实际上有两个中间步骤：
- 读取需要修改的数据（加 U 锁）；
- 修改数据（加 X 锁）。
在这两个步骤中，会使用不同的锁模式以最大化并发性。第一步骤中，获取数据上一个 U 锁，第二步，U 锁被转换为一个 X 排他锁以进行修改。若不需要修改，U 锁会被释放，也就是说它不会被保存到事务结束。
既然为了提高并发性，为什么第一步获取的是 U 锁，而不是 S 锁？
这里说说第一步中用 S 锁代替 U 锁的缺点。
若有两个连接同时执行一个事务，事务中是对同一条数据进行更新。两个事务先都使用 S 锁读取需要修改的数据，然后在请求一个 X 锁进行修改。当第一个事务试图将 S 锁转换为 X 锁时，它被第二个事务保持的 S 锁阻塞。同样如此，第二个事务试图将 S 锁转换为 X 锁时，也会被第一个事务的 S 锁阻塞。这样导致循环阻塞，也就发送了死锁。
为了避免这种情况，UPDATE 语句在第一个中间阶段使用 U 锁代替 S 锁。U 锁不允许在相同的资源上使用另一个 U 锁，这样强制第二个并发的 UPDATE 语句必须等待第一个 UPDATE 语句完成才执行。
（3）排他（X）模式
X 锁提供用于数据操作查询，如 INSERT、UPDATE 和 DELETE 在数据库资源上修改的排他能力。它阻止其他事务访问修改之下的资源。INSERT 和 DELETE 在执行开始获取 X 锁， UPDATE 语句在被修改的数据读出之后转换为 X 锁。在事务中授予的 X 锁会保持到事务结束。
X 锁有两个目的：
- 阻止其他事务访问修改之下的资源，这样他们可以看到修改之前或之后的值，但不能是正在修改的值；
- 在需要时允许事务修改资源以安全地回滚到修改之前的原始值，因为没有其他事务被允许同时修改资源。
三种之间的区别和联系：
S 锁 和 U 锁 执行期很短，默认情况下在读出数据后会立即释放；
U 锁 和 X 锁 具有独占能力；
X 锁 执行期是在整个事务阶段。
SELECT 使用 S 锁；INSERT 和 DELETE 使用 X 锁；UPDATE 使用两阶段锁，读取阶段为 U 锁，修改阶段为 X 锁。
使用 UPDATE 更新某一数据时，在扫描阶段先对数据使用 U 锁，若不满足立即退出，满足条件才转换为 X 锁进入修改阶段。
思考：为什么 UPDATE 使用两阶段锁，而 DELETE 直接使用 X 锁 ？
三、隔离级别（ISOLATION）
SQL Server 有这几种隔离级别：
- 未提交读（READ UNCOMMITTED）
- 已提交读（READ COMMITTED）
- 可重复读（REPEATABLE READ）
- 可序列化（SERIALIZABLE）
其他两种隔离级别提供行版本控制（row versioning）。这种行的额外版本允许读查询访问数据而不需要获取锁：
- 已提交读快照（已提交读隔离的一部分）
- 快照
（1）未提交读
未提交读 是隔离级别中最低级的。它允许 SELECT 语句读取数据而不需要请求 S 锁，这样它就不会被 X 阻塞，也不会阻塞 X 锁。这样就允许 SELECT 语句读取正在修改（包括新增和删除）的数据。这种读出数据的方式被称为 脏读 。
有两种方式来设置：
使用 SET 语句配置数据库连接的隔离级别：
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
使用 NOLOCK 锁提示在查询上设置隔离级别：
SELECT * FROM Products WITH (NOLOCK);
注：脏读 可能造成不可预知的情况。因为读取数据时没加锁，索引可能分离，这导致查询返回的数据中多出或丢失行。
（2）已提交读
已提交读 可避免出现脏读的情况，这意味着该隔离级别指示 SELECT 语句会请求 S 锁。这也是数据库的默认隔离级别。
但是，S 锁不会被保留到事务结束，这样可能造成 不可重复读 或 幻读 的问题。
可以通过开启数据库选项 READ_COMMITTED_SNAPSHOT 来启用 已提交读 隔离级别。当看起这选项时，数据操作事务会使用行版本控制。这会给 tempdb 带来额外的负载，在事务未提交时被修改的行的前一个版本将保存在该数据库里。这使得其他事务可以读访问数据而不需要在数据上加锁，可能会改善查询的速度和效率（因为查询数据不需要加 S 锁）。
（3）可重复读
可重复读隔离级别使得一条 SELECT 语句保持它的 S 锁直到事务结束，这样阻止了其他事务在这段时间内修改该数据。
可重复读，某事务在执行期间可反复读取未被其他事务修改的数据的能力。
S 锁允许数据获取 U 锁，但是会阻止 U 锁转换为 X 锁。这样会有一种现象发生：
A 事务查询某数据，B 事务修改该数据。先执行 A 事务，在 A 事务结束之前执行 B 事务，该数据在 A 事务中加了 S 锁，在 B 事务中加了 U 锁。此时 B 事务 UPDATE 要修改数据，要转换为 X 锁，这时 A 事务还未退出，阻止 B 事务中数据由 U 锁转换为 X 锁。事务 A 稍后更数据，试图获取 U 锁，这样就进入死循环。
解决方法：在执行 SELECT 语句时使用 UPDLOCK 锁提示请求一个 U 锁，如：
SELECT * FROM Products WITH (UPDLOCK);
（4）可序列化
这是这几张隔离级别中最高的隔离级别。可序列化不仅在访问的行上获取一个锁，而且还获取在按照请求的数据级顺序的下一行上的范围锁。这阻止了在第一个事务所操作的数据中的周期另一事务增加行，避免第一个事务在其访问内的数据集查找新建的行。也就避免了幻读（在事务内的数据集中查找新的行）。
可序列化 隔离级别不仅和 可重复读 隔离级别一样保留 S 锁直到事务结束，而且还通过保持范围锁阻止数据集（或更多）中新建行。这可能很大的损害了数据库并行性，所有应该避免该隔离级别。
（5）快照（Snapshot）
快照隔离级别试图在打算修改的数据上使用一个排他锁。若数据已加锁，快照事务将失败。它提供了事务级读一致性。
数据库隔离级别：
隔离级别与并发性能的关系：
设置隔离级别原则
优先将数据库系统的隔离级别设置为 ReadCommitted（MSSQL 默认级别），它可以避免脏读，并且有较好的并发性。
四、多个事务并发产生的问题
- 第一类丢失更新：撤销一个事务时，把其他事务已提交的更新数据覆盖
- 脏读：一个事务读到另一个事务未提交的更新数据
- 虚读：一个事务读到另一个事务已提交的新插入的数据
- 不可重复读：一个事务读到另一个事务已提交更新的数据
- 第二类丢失更新：一个事务覆盖另一个事务已提交更新的数据（不可重复读的特例）
隔离级别与并发性能的关系：
设置隔离级别原则
优先将数据库系统的隔离级别设置为 ReadCommitted（MSSQL 默认级别），它可以避免脏读，并且有较好的并发性。
四、多个事务并发产生的问题
- 第一类丢失更新：撤销一个事务时，把其他事务已提交的更新数据覆盖
- 脏读：一个事务读到另一个事务未提交的更新数据
- 虚读：一个事务读到另一个事务已提交的新插入的数据
- 不可重复读：一个事务读到另一个事务已提交更新的数据
- 第二类丢失更新：一个事务覆盖另一个事务已提交更新的数据（不可重复读的特例）
参考书籍：
书籍：《SQL Server 2008 查询性能优化》
