外观
SQLServer 锁与并发
锁的基本概念
锁的定义
锁是 SQL Server 用于控制多个用户同时访问和修改数据的机制，确保数据的一致性和完整性。当用户执行数据操作时，SQL Server 会自动获取相应的锁，防止其他用户进行冲突的操作。
锁的作用
- 保护数据完整性：防止多个用户同时修改同一数据导致数据不一致
- 实现事务隔离：确保事务之间的隔离性，符合 ACID 特性
- 解决并发冲突：协调不同事务对同一资源的访问
- 防止脏读、不可重复读和幻读等并发问题
锁的粒度
锁的粒度指锁保护的资源范围，SQL Server 支持多种锁粒度，从行级到数据库级：
| 锁粒度 | 描述 | 并发度 | 开销 | 适用场景 |
|---|---|---|---|---|
| 行级锁 | 保护单个数据行 | 最高 | 最高 | 并发写入频繁，数据访问分散 |
| 页级锁 | 保护数据页（通常8KB） | 较高 | 较高 | 数据访问集中在少数页 |
| 键范围锁 | 保护索引键范围 | 较高 | 较高 | 范围查询和更新 |
| 表级锁 | 保护整个表 | 较低 | 较低 | 大规模数据操作，如批量导入 |
| 数据库级锁 | 保护整个数据库 | 最低 | 最低 | 数据库级操作，如备份、还原 |
锁的类型
共享锁 (S锁)
- 用途：用于读取操作，允许其他事务同时读取相同资源
- 兼容性：与其他共享锁兼容，与排他锁和更新锁不兼容
- 自动管理：SQL Server 自动获取和释放，读取完成后立即释放
- 示例：
SELECT * FROM table_name会对读取的资源加共享锁
排他锁 (X锁)
- 用途：用于修改操作（INSERT、UPDATE、DELETE），防止其他事务访问同一资源
- 兼容性：与任何锁类型都不兼容
- 自动管理：修改操作开始时获取，事务提交或回滚时释放
- 示例：
UPDATE table_name SET column = value会对修改的资源加排他锁
更新锁 (U锁)
- 用途：用于更新操作的初始阶段，防止死锁
- 兼容性：与共享锁兼容，与其他更新锁和排他锁不兼容
- 转换机制：更新操作执行时，U锁会转换为排他锁
- 示例：
UPDATE table_name SET column = value WHERE condition首先获取U锁，然后转换为X锁
意向锁
- 用途：表明事务意图在更细粒度资源上获取锁，用于提高锁检查效率
- 类型：意向共享锁（IS）、意向排他锁（IX）、意向排他共享锁（SIX）
- 示例：当事务需要对表中的某行加排他锁时，会先对表加IX锁，再对行加X锁
架构锁
- 用途：保护数据库架构不被并发修改
- 类型：架构修改锁（Sch-M）、架构稳定性锁（Sch-S）
- 示例：执行
ALTER TABLE时获取Sch-M锁，执行SELECT时获取Sch-S锁
批量更新锁
- 用途：用于批量数据操作（如 BULK INSERT），提高批量操作性能
- 兼容性：允许其他批量操作同时进行，但阻止其他类型的并发访问
- 示例：
BULK INSERT table_name FROM 'file_path'会获取批量更新锁
并发控制策略
乐观并发控制
- 核心思想：假设并发冲突很少发生，只在提交时检查数据是否被修改
- 实现方式：使用版本号、时间戳或校验和检测数据变更
- 适用场景：读多写少的系统，并发冲突率低
- 优点：高并发性能，减少锁竞争
- 缺点：冲突时需要回滚并重试，增加应用复杂度
- SQL Server 支持：通过
ROWVERSION数据类型实现
悲观并发控制
- 核心思想：假设并发冲突经常发生，操作前就锁定资源
- 实现方式：使用锁机制防止其他事务修改数据
- 适用场景：写多读少的系统，并发冲突率高
- 优点：实现简单，冲突处理直接
- 缺点：锁竞争增加，可能导致阻塞和死锁
- SQL Server 支持：通过各种锁类型和隔离级别实现
快照隔离级别
- 核心思想：为每个事务提供数据的一致性快照，事务看到的是事务开始时的数据版本
- 类型：
- 读提交快照隔离（RCSI）：基于行版本控制的读提交
- 快照隔离（SI）：完全基于行版本控制的隔离级别
- 优点：减少阻塞，提高并发性能
- 配置：需要启用数据库的行版本控制sql
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE YourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
锁的管理
锁的获取与释放
- 自动获取：SQL Server 通常根据隔离级别和操作类型自动获取锁
- 显式锁定：使用
LOCK_TIMEOUT、SET TRANSACTION ISOLATION LEVEL或WITH (TABLOCK)等提示控制锁行为 - 释放时机：
- 共享锁：通常在读取完成后立即释放（读提交隔离级别）
- 排他锁：事务提交或回滚时释放
- 意向锁：事务提交或回滚时释放
锁的查看
在生产环境中，查看锁信息有助于诊断阻塞和性能问题：
sql
-- 查看当前数据库的锁信息
SELECT
resource_type,
resource_description,
request_mode,
request_status,
request_session_id,
resource_associated_entity_id
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID();
-- 查看阻塞情况
SELECT
blocking_session_id,
session_id,
wait_type,
wait_duration_ms,
resource_description
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id > 0;锁的等待与阻塞
- 锁等待：事务等待获取锁的状态
- 阻塞：一个事务持有锁导致其他事务等待
- 检测阻塞：
- 使用 SSMS 活动监视器
- 查询
sys.dm_os_waiting_tasks和sys.dm_tran_locks - 启用阻塞报告
- 解决阻塞：
- 优化查询，减少锁持有时间
- 使用合适的隔离级别
- 调整锁粒度
- 终止阻塞会话（谨慎使用）
死锁管理
死锁的定义
死锁是指两个或多个事务互相等待对方释放锁，导致所有事务都无法继续执行的状态。
死锁的检测
- 自动检测：SQL Server 内置死锁检测器，默认每5秒运行一次
- 死锁图：SQL Server 会生成死锁图，显示死锁涉及的资源、会话和SQL语句
- 查看死锁：
- 事件查看器中的应用程序日志
- 扩展事件（Extended Events）
- SQL Server Profiler
死锁的避免
- 保持一致的访问顺序：所有事务按相同顺序访问资源
- 减少事务长度：将大事务拆分为小事务，减少锁持有时间
- 使用低隔离级别：如读提交或快照隔离，减少锁竞争
- 避免长事务：及时提交或回滚事务
- 使用索引：确保查询使用索引，减少锁范围
- 避免交互式事务：不在事务中等待用户输入
死锁的解决
- 自动选择牺牲品：SQL Server 会自动选择代价最低的事务作为牺牲品并终止
- 捕获死锁：使用扩展事件捕获死锁信息，分析根本原因
- 优化查询：重构导致死锁的查询，减少锁冲突
- 调整应用逻辑：修改应用程序代码，避免死锁场景
- 使用乐观并发控制：减少锁的使用
并发性能优化
减少锁的持有时间
- 缩短事务长度：及时提交或回滚事务，避免长时间持有锁
- 避免在事务中执行非数据库操作：如文件I/O、网络请求等
- 优化查询性能：减少查询执行时间，降低锁持有时间
- 使用批量操作：减少事务数量
降低锁的粒度
- 使用行级锁：通过优化索引和查询，使SQL Server使用行级锁而非表级锁
- 避免全表扫描：确保查询使用索引，减少锁范围
- 使用分区表：将大表分区，减少锁范围
使用合适的隔离级别
选择最适合业务需求的隔离级别，平衡一致性和性能：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 锁开销 | 适用场景 |
|---|---|---|---|---|---|
| 未提交读 | 允许 | 允许 | 允许 | 最低 | 仅读取，不关心数据一致性 |
| 读提交 | 防止 | 允许 | 允许 | 低 | 大多数OLTP系统 |
| 可重复读 | 防止 | 防止 | 允许 | 中 | 需要重复读取一致性 |
| 快照 | 防止 | 防止 | 防止 | 低（基于行版本） | 高并发OLTP系统 |
| 可串行化 | 防止 | 防止 | 防止 | 最高 | 严格数据一致性要求 |
优化查询语句
- 添加合适的索引：减少锁范围和持有时间
- 避免昂贵的查询：如复杂连接、子查询和函数调用
- 使用查询提示：如
WITH (NOLOCK)或WITH (READPAST)（谨慎使用） - 避免更新不必要的列：减少锁范围
- 使用批量更新：减少事务数量和锁冲突
最佳实践
锁的使用建议
- 优先使用自动锁管理：避免显式锁定，除非有特殊需求
- 谨慎使用查询提示：查询提示可能影响SQL Server的优化器决策
- 避免长时间事务：及时提交或回滚，减少锁持有时间
- 监控锁统计信息：定期检查锁等待和阻塞情况
并发控制建议
- 根据业务需求选择隔离级别：不盲目追求高隔离级别
- 使用快照隔离：对于高并发系统，考虑启用RCSI或快照隔离
- 实现乐观并发控制：对于读多写少的系统，减少锁竞争
- 设计无锁架构：如使用消息队列处理并发写入
生产环境监控
- 监控阻塞情况：设置阻塞阈值警报，及时发现问题
- 捕获死锁：使用扩展事件持续捕获死锁信息
- 分析锁统计信息：定期分析锁等待类型和频率
- 监控事务长度：识别长时间运行的事务
示例代码
锁的使用示例
1. 显式锁定示例
sql
-- 使用表级锁进行批量操作
BEGIN TRANSACTION;
-- 显式获取表级排他锁
SELECT * FROM table_name WITH (TABLOCKX);
-- 执行批量更新
UPDATE table_name SET column = value WHERE condition;
COMMIT TRANSACTION;2. 快照隔离示例
sql
-- 启用数据库快照隔离
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE YourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
-- 使用快照隔离级别
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
-- 读取数据（使用快照，不阻塞其他事务）
SELECT * FROM table_name;
-- 修改数据
UPDATE table_name SET column = value WHERE condition;
COMMIT TRANSACTION;并发控制示例
1. 乐观并发控制实现
sql
-- 添加版本号列
ALTER TABLE table_name ADD RowVersion ROWVERSION;
-- 更新时检查版本号
DECLARE @CurrentVersion BINARY(8);
-- 首先读取数据和版本号
SELECT @CurrentVersion = RowVersion
FROM table_name
WHERE id = @id;
-- 更新时验证版本号
UPDATE table_name
SET column = @newValue,
modified_date = GETDATE()
WHERE id = @id AND RowVersion = @CurrentVersion;
-- 检查是否更新成功
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('数据已被其他用户修改，请重试', 16, 1);
END2. 减少锁持有时间示例
sql
-- 不推荐：长事务持有锁时间长
BEGIN TRANSACTION;
-- 读取数据
SELECT * FROM table1 WHERE id = @id;
-- 执行耗时操作（如文件I/O、网络请求）
EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @http OUT;
-- ... 其他耗时操作 ...
-- 修改数据
UPDATE table2 SET column = @value WHERE id = @id;
COMMIT TRANSACTION;
-- 推荐：拆分事务，减少锁持有时间
-- 1. 先读取所需数据
SELECT @data = column FROM table1 WHERE id = @id;
-- 2. 执行耗时操作
EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @http OUT;
-- ... 其他耗时操作 ...
-- 3. 短事务执行数据修改
BEGIN TRANSACTION;
UPDATE table2 SET column = @value WHERE id = @id;
COMMIT TRANSACTION;版本差异
SQL Server 2008/2008 R2
- 基本锁机制和隔离级别支持
- 快照隔离级别可用但需手动启用
- 死锁检测和自动解决
- 锁监控通过DMV和Profiler实现
SQL Server 2012
- 引入扩展事件作为Profiler的替代方案，更高效地捕获锁和死锁信息
- 改进了锁管理器性能
- 增强了DMV的锁信息查询能力
SQL Server 2014
- In-Memory OLTP（Hekaton）引入乐观并发控制，减少锁的使用
- 改进了列存储索引的并发支持
- 增强了锁升级算法
SQL Server 2016
- 时态表支持，提供历史数据查询，减少锁竞争
- Query Store引入，便于分析查询性能和锁问题
- 增强了In-Memory OLTP的并发能力
SQL Server 2017
- 图形数据库支持，新的锁类型用于图形数据
- Linux版本支持，锁机制跨平台一致
- 自适应查询处理，减少锁持有时间
SQL Server 2019
- Intelligent Query Processing (IQP)，优化查询执行计划，减少锁竞争
- Always Encrypted with Secure Enclaves，增强安全性的同时减少锁开销
- 增强了In-Memory OLTP的扩展性
SQL Server 2022
- Ledger功能，提供不可篡改的数据库历史，减少锁需求
- Parameter Sensitive Plan Optimization，减少计划重用导致的锁问题
- 增强了Azure Synapse Link集成，提供近实时分析，减少对OLTP系统的查询压力
- 改进了批量操作的锁管理
常见问题 (FAQ)
如何查看当前数据库中的锁信息？
可以使用系统动态管理视图（DMV）查询锁信息：
sql
SELECT
resource_type,
resource_description,
request_mode,
request_session_id,
OBJECT_NAME(resource_associated_entity_id) AS object_name
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID();如何识别和解决死锁问题？
- 识别：使用扩展事件或SQL Server Profiler捕获死锁图
- 分析：查看死锁图，识别涉及的资源、会话和SQL语句
- 解决：
- 优化查询，减少锁竞争
- 调整事务顺序，保持一致的资源访问顺序
- 减少事务长度，及时提交或回滚
- 使用低隔离级别或快照隔离
- 修改应用逻辑，避免死锁场景
乐观并发控制和悲观并发控制有什么区别？
- 乐观并发控制：假设冲突很少发生，只在提交时检查数据是否被修改，适用于读多写少的场景
- 悲观并发控制：假设冲突经常发生，操作前锁定资源，适用于写多读少的场景
- 性能影响：乐观并发控制减少锁竞争，提高并发性能；悲观并发控制可能导致锁等待和阻塞
- 实现方式：乐观并发控制使用版本号或时间戳；悲观并发控制使用锁机制
快照隔离级别如何工作？
快照隔离级别基于行版本控制，为每个事务提供数据的一致性快照：
- 当数据被修改时，SQL Server会保留旧版本的数据
- 事务开始时，会获取当前数据库的事务序列号
- 事务读取数据时，会看到所有在其序列号之前提交的数据版本
- 这避免了读取操作阻塞写入操作，写入操作也不会阻塞读取操作
如何优化锁的性能？
- 减少锁持有时间：缩短事务长度，优化查询性能
- 降低锁粒度：使用行级锁，避免全表扫描
- 使用合适的隔离级别：根据业务需求选择隔离级别
- 启用快照隔离：减少锁竞争
- 添加合适的索引：减少锁范围
- 避免长时间运行的事务
什么是锁升级，如何控制？
锁升级是指SQL Server将多个细粒度锁（如行级锁）合并为粗粒度锁（如表级锁），以减少锁管理开销。可以通过以下方式控制：
- 修改数据库选项：
ALTER DATABASE SET LOCK_ESCALATION = TABLE/AUTO/DISABLE - 使用查询提示：
WITH (NOLOCK_ESCALATION) - 设计合理的索引和分区，减少锁数量
如何处理阻塞问题？
- 识别阻塞：使用活动监视器或DMV查询阻塞会话
- 分析原因：查看阻塞会话执行的SQL语句，确定锁类型和资源
- 解决方法：
- 优化阻塞查询，减少锁持有时间
- 终止阻塞会话（谨慎使用，可能导致数据不一致）
- 调整隔离级别
- 增加资源，如内存、CPU
- 优化数据库设计
什么时候应该使用NOLOCK提示？
NOLOCK提示允许查询读取未提交的数据，适用于以下场景：
- 读取静态或历史数据，不关心数据一致性
- 报表查询，允许脏读以提高性能
- 数据仓库或分析场景，读取大量数据时减少锁竞争
注意：NOLOCK可能导致脏读、不可重复读和幻读，生产环境中应谨慎使用。
如何设计高并发系统？
- 使用合适的隔离级别：如快照隔离或读提交
- 实现乐观并发控制：减少锁竞争
- 设计无锁架构：如使用消息队列处理写入请求
- 垂直和水平扩展：增加服务器资源或拆分数据库
- 使用缓存：减少数据库访问
- 异步处理：将耗时操作转为异步
- 数据库分片：将数据分布到多个服务器
SQL Server 2022在锁和并发方面有哪些新特性？
- Ledger：提供不可篡改的数据库历史，减少锁需求
- Parameter Sensitive Plan Optimization：为不同参数值生成最优计划，减少锁竞争
- Azure Synapse Link：近实时分析，减少对OLTP系统的查询压力
- 改进的批量操作锁管理：提高批量导入和更新的并发性能
- 增强的In-Memory OLTP：更好的扩展性和性能
- Intelligent Query Processing增强：进一步优化查询执行，减少锁持有时间