SQL Server事务隔离级别

SQL Server 提供了多个事务隔离级别，用于控制并发事务如何访问和修改数据时的可见性、锁定行为以及可能遇到的并发问题（如脏读、不可重复读、幻读）。这些级别在数据一致性、并发性能和锁定开销之间进行权衡。

以下是 SQL Server 支持的主要隔离级别，分为 标准 ANSI 隔离级别 和 SQL Server 特有的扩展隔离级别：

📌 一、标准 ANSI 隔离级别

1. READ UNCOMMITTED (读未提交)

   • 行为：允许事务读取其他事务尚未提交（可能被回滚）的数据（脏读）。
   • 锁定：SELECT 语句不加共享锁，因此不会阻塞其他事务的写操作（更新/删除），但可能被写操作阻塞。
   • 问题：可能发生脏读、不可重复读、幻读。
   • 适用场景：对数据准确性要求极低，追求最高并发性能且可容忍脏数据的场景（如近似统计）。
   • 语法：SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 或表提示 WITH (NOLOCK) 😅。

2. READ COMMITTED (读已提交) - SQL Server 默认级别

   • 行为：确保事务只能读取已提交的数据（避免脏读）。
   • 锁定：SELECT 语句在读取数据时加共享锁，读取完成后立即释放锁（即使事务未结束），不会阻塞其他事务的读，但会阻塞其他事务对相同数据的写（反之亦然）。
   • 问题：可能发生不可重复读（同一事务内两次读取同一行可能不同）、幻读（同一查询两次执行返回的行集不同）。
   • 变体：SQL Server 支持两种实现：
     • READ COMMITTED (基于锁 - Locking)：传统方式，使用共享锁。
     • READ COMMITTED SNAPSHOT (基于行版本 - RCSI)：见下文扩展级别。
   • 语法：SET TRANSACTION ISOLATION LEVEL READ COMMITTED;。

3. REPEATABLE READ (可重复读)

   • 行为：确保同一事务内多次读取相同行数据的结果一致（避免脏读和不可重复读）。
   • 锁定：SELECT 语句在读取的数据上加共享锁，并持有到事务结束（而不是读完就释放）。阻止其他事务修改这些行。
   • 问题：可能发生幻读（其他事务可以插入新行，导致同一查询返回更多行）。
   • 语法：SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;。

4. SERIALIZABLE (可序列化)

   • 行为：最高隔离级别，确保事务完全串行执行的效果（避免脏读、不可重复读和幻读）。
   • 锁定：SELECT 语句在查询涉及的数据范围（不仅仅是行）上加范围锁（Range Locks），并持有到事务结束。阻止其他事务修改已有数据，也阻止插入或删除影响查询结果的新数据。
   • 问题：锁定范围最大，并发性能最低，死锁风险最高。
   • 语法：SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;。

二、SQL Server 特有的扩展隔离级别 (基于行版本控制)

这些级别利用 tempdb 数据库存储数据的行版本，读操作不阻塞写操作，写操作也不阻塞读操作（非阻塞读取），大大提高了并发性。

5. SNAPSHOT (快照)

   • 行为：事务启动时看到的是一个一致的数据库快照（基于事务开始时间点）。在整个事务过程中，所有读取操作都基于该快照，不受其他事务修改的影响。
   • 锁定：SELECT 不加共享锁（读取的是版本）。写操作（UPDATE/DELETE）仍需获取锁并可能被阻塞/阻塞其他写操作。
   • 优点：避免脏读、不可重复读、幻读（因为基于快照）。读写不互相阻塞（高并发）。
   • 问题：
     • 更新冲突：如果事务尝试修改一个自其快照后被其他事务修改过的行，会收到 Update conflict 错误 (错误 3960)，事务会中止（需要应用程序重试）。
     • tempdb 开销：需要额外的空间和 I/O 来存储行版本。
   • 启用要求：数据库选项 ALLOW_SNAPSHOT_ISOLATION 必须设为 ON。
   • 语法：SET TRANSACTION ISOLATION LEVEL SNAPSHOT;。

6. READ COMMITTED SNAPSHOT (已提交读快照 - RCSI)

   • 本质：这是 READ COMMITTED 隔离级别的行版本控制实现变体，不是一个独立的 ANSI 级别。
   • 行为：每个 SELECT 语句看到的是该语句开始时（不是事务开始时）已提交的所有数据。避免了脏读。
   • 锁定：SELECT 不加共享锁（读取的是最新已提交的版本）。写操作仍需锁。
   • 优点：避免了脏读，读操作不阻塞写操作，写操作也不阻塞读操作。比 SNAPSHOT 更少的 tempdb 版本存储开销（版本在语句结束时可能被清理）。
   • 问题：仍然可能发生不可重复读和幻读（因为每个语句看到的是当前时间点的最新提交版本）。
   • 启用要求：数据库选项 READ_COMMITTED_SNAPSHOT 必须设为 ON。开启后，所有使用默认 READ COMMITTED 级别的事务自动使用 RCSI 语义。
   • 语法：开启选项后，使用 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 即生效（无需特殊语法指定 RCSI）。

📊 隔离级别总结表

🔧 选择建议

• 默认 (READ COMMITTED) / RCSI：适用于大多数场景，平衡一致性和性能。RCSI 是许多现代应用的首选，因其读写不阻塞的特性。
• SNAPSHOT：需要事务内读取完全一致且避免所有 ANSI 异常，并能处理更新冲突的场景。
• REPEATABLE READ / SERIALIZABLE：需要严格保证可重复读或避免幻读，且能接受较高锁开销和死锁风险的场景。
• READ UNCOMMITTED：仅用于对脏读不敏感、追求极致读取速度的场景（慎用）。

💡 关键点

• 设置级别：SET TRANSACTION ISOLATION LEVEL <level>; (会话级) 或使用表提示 (如 WITH (NOLOCK), WITH (SNAPSHOT))。
• READ COMMITTED SNAPSHOT 和 SNAPSHOT 需要先在数据库级别启用相应选项 (ALTER DATABASE ... SET ... ON)。
• 隔离级别主要影响 SELECT 语句的行为和锁定，写操作 (INSERT, UPDATE, DELETE, MERGE) 在任何级别下通常仍需要获取并持有适当的锁（如排他锁）。
• 选择合适的隔离级别对应用程序的正确性、性能和可伸缩性至关重要。