sqlserver事务详解

本文主要讲述sqlserver事务，重点关注事务ACID特性，隔离性是重中之重，事务带来的问题（脏读、不可重复读、幻读）以及事务隔离级别需要重点关注。

1.什么是事务？

事务是一组逻辑操作单元，这些操作要么全部成功，要么全部失败。SQL Server 中的事务可以包含多条 INSERT、UPDATE、DELETE 或其他语句，通常用于保证复杂操作的完整性。事务的目标是确保数据库在操作完成后保持一致性状态。

2.事务分类

2.1.显示事务

用 begin transaction 明确指定事务的开始，由 commit transaction 提交事务、rollback transaction 回滚事务到事务结束

2.2.隐式事务

通过设置 set implicit_transactions on 语句，将隐式事务模式设置为打开。当以隐式事务模式操作时，不必使用 begin transaction 开启事务，当一个事务结束后，这个模式会自动启用下一个事务。只需使用 commit transaction 提交事务或 rollback transaction 回滚事务即可。

2.3.自动提交事务

Sql Server的默认模式，它将每条单独的T-SQL语句视为一个事务。如果成功执行，则自动提交，否则回滚。

3.事务的ACID特性

SQL Server 事务遵循四个 ACID 属性，以确保数据的一致性和可靠性：

1. 原子性（Atomicity）：事务是不可分割的操作单元，所有操作要么全部执行成功，要么全部回滚失败。
2. 一致性（Consistency）：事务的执行保证了数据库从一个一致性状态转换到另一个一致性状态。
3. 隔离性（Isolation）：在事务执行过程中，不会被其他事务的操作影响，事务之间相互独立。
4. 持久性（Durability）：一旦事务提交，数据将被永久保存，即使系统崩溃也不会丢失。

4.事务的基本用法

在 SQL Server 中，事务控制语句包括 BEGIN TRANSACTION、COMMIT TRANSACTION 和
ROLLBACK TRANSACTION。

1. BEGIN TRANSACTION
   BEGIN TRANSACTION 用于启动一个事务。之后的所有操作都将包含在事务中，直到执行 COMMIT 或 ROLLBACK。
2. COMMIT TRANSACTION
   COMMIT TRANSACTION 用于提交事务，确保事务中的所有操作永久生效。如果提交成功，事务中的更改将被保存到数据库。
3. ROLLBACK TRANSACTION
   ROLLBACK TRANSACTION 用于回滚事务，将事务中的所有更改撤销，数据库恢复到事务开始前的状态。

4.1.事务基本使用示例

以下为模拟一个事务下新增员工->更新员工工资两步操作的sql示例

BEGIN TRANSACTION;

BEGIN TRY
    -- 插入新员工
    INSERT INTO Employees (EmployeeID, EmployeeName, Department, Salary)
    VALUES (6, 'Frank', 'Finance', 6000);

    -- 更新员工工资
    UPDATE Employees
    SET Salary = Salary * 1.1
    WHERE Department = 'IT';

    -- 提交事务
    COMMIT TRANSACTION;
    PRINT 'Transaction committed successfully.';
END TRY
BEGIN CATCH
    -- 回滚事务
    ROLLBACK TRANSACTION;
    PRINT 'Transaction rolled back due to an error.';
END CATCH;

在此示例中：

• 启动事务后，执行一系列插入和更新操作。
• 使用 BEGIN TRY…END TRY 和 BEGIN CATCH…END CATCH 捕获错误。
• 如果操作成功，执行 COMMIT 提交事务；如果发生错误，执行 ROLLBACK 回滚事务。

5.事务的隔离级别

★★★（重点）即事务的四大特性ACID中I隔离性

5.1.什么是脏读、不可重复度、幻读（虚读）

1. 脏读

脏读（Dirty Read）是指在数据库事务中，一个事务读取了另一个未提交事务中的数据。当一个事务修改数据但还没有提交时，另一个事务读取了这个未提交的数据。如果未提交的事务最终被回滚，那么另一个事务读取到的数据实际上是无效的或错误的。脏读可能导致不一致的数据状态和不正确的结果。

2. 不可重复度

不可重复读（Non-repeatable Read）是指在数据库事务中，在对数据进行读取的过程中，有其他事务对数据进行了修改（UPDATE、DELETE），导致第二次读取的结果与第一次不一致。

3. 幻读

幻读（Phantom Read）是指在数据库事务中，一个事务在相同的查询条件下多次执行范围查询时，另一个事务在该范围内进行新增操作（INSERT），导致前后范围查询的结果数目不一致。

5.2.隔离级别

事务隔离级别控制事务之间的相互影响，SQL Server 提供了四种主要的隔离级别：

1. READ UNCOMMITTED（未提交读）：允许读取未提交的数据，可能会产生脏读。
2. READ COMMITTED（已提交读）（默认级别）：只读取已提交的数据，防止脏读，但可能出现不可重复读。
3. REPEATABLE READ（可重复读）：锁定读取的数据，防止脏读和不可重复读，但可能产生幻读。
4. SERIALIZABLE（可串行化）：锁定整个范围，防止脏读、不可重复读和幻读，但性能最差。（最高级别）

此外，还存在两种隔离级别：
SNAPSHOT​​ 和 ​​READ COMMITTED SNAPSHOT​​ 是两种基于行版本控制（Row Versioning）的隔离级别，旨在提升并发性能并减少锁争用。了解

设置事务的隔离级别

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

-- 查询操作

COMMIT TRANSACTION;

5.3.事务隔离级别对比

5.4.事务的常见使用场景

1. 银行转账：转账操作包含扣款和存款两步，必须确保这两个操作要么全部成功，要么全部失败，防止资金丢失。
2. 订单处理：创建订单时，订单信息和库存更新等多个步骤必须原子执行，确保数据一致性。
3. 批量数据更新：例如，更新多个表的数据，需要确保所有表数据都更新成功，否则全部回滚。
4. 日志记录：记录用户操作日志时，可以使用事务保证日志和业务数据的一致性。

5.5.事务的隔离级别详解

5.5.1.READ UNCOMMITTED（未提交读）

READ UNCOMMITTED 是最低的隔离级别，允许读取未提交的数据（即脏读），不会为读取的数据加锁。

特点：

• 允许读取其他事务尚未提交的数据。
• 不会对读取的数据加锁，适合对数据一致性要求不高的场景。
• 可能出现 脏读、不可重复读 和 幻读 问题。
• 适用场景：适用于只读查询或对数据一致性要求不高的场景，如数据分析、报告生成等。

示例：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN TRANSACTION;

SELECT * FROM Employees WHERE Department = 'IT';

COMMIT TRANSACTION;

5.5.2.READ COMMITTED（提交读）

READ COMMITTED 是 SQL Server 的默认隔离级别。在此级别下，事务只能读取已提交的数据，防止脏读，但仍可能出现不可重复读和幻读。

特点：

• 只读取已提交的数据，防止脏读。
• 读取数据时会短暂加共享锁，读取完成后立即释放。
• 可能出现 不可重复读 和 幻读 问题。
• 适用场景：适用于大多数查询操作，兼顾数据一致性和性能。

示例：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;

SELECT * FROM Employees WHERE Department = 'IT';

COMMIT TRANSACTION;

在此隔离级别下，一个事务可以读取其他事务提交的最新数据，因此两次读取同一行时，数据可能会有所不同（不可重复读）。

5.5.3.REPEATABLE READ（可重复读）

REPEATABLE READ 隔离级别确保在同一事务中多次读取相同数据时，数据不会发生变化，防止脏读和不可重复读，但仍可能出现幻读。

特点：

• 防止脏读和不可重复读。
• 在读取数据时会加共享锁，直到事务结束后才释放。
• 可能出现 幻读 问题，即在事务中可能会有新数据插入或删除，导致下一次范围查询结果不同。
• 适用场景：适用于需要一致性较高的数据读取场景，确保数据在事务中不变。

示例：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

SELECT * FROM Employees WHERE Department = 'IT';

COMMIT TRANSACTION;

在此隔离级别下，事务读取的数据将被锁定，直到事务完成，确保在事务内数据一致。但新行的插入可能会导致幻读。

5.5.4.SERIALIZABLE（可串行化）

SERIALIZABLE 是最高的隔离级别，确保事务之间完全隔离，防止脏读、不可重复读和幻读。其实现方式是锁定读取的数据范围，防止其他事务插入或修改。

特点：

• 防止脏读、不可重复读和幻读。
• 读取时不仅锁定数据行，还会锁定读取范围，直到事务结束。
• 性能最差，但数据一致性最高。
• 适用场景：适用于对数据一致性要求极高的场景，如金融系统中需要确保强一致性的场景。

示例：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

SELECT * FROM Employees WHERE Department = 'IT';

COMMIT TRANSACTION;

5.6.选择适合的隔离级别

选择合适的隔离级别需要在数据一致性和系统性能之间做权衡：

• READ UNCOMMITTED：适合于只读操作，或者对数据一致性要求不高的场景，如数据分析、报表生成等。
• READ COMMITTED（默认）：适合大多数应用，具有较好的性能和一致性保障，是 SQL Server 的默认隔离级别。
• REPEATABLE READ：适合需要确保同一事务中多次读取数据一致的情况，如银行账户余额查询等。
• SERIALIZABLE：适合对数据一致性要求极高的关键场景，但性能较差，应谨慎使用。

5.7.使用隔离级别的注意事项

• 并发与性能：隔离级别越高，对资源的锁定越多，性能开销也越大。在高并发环境中应平衡隔离级别与性能。
• 锁与死锁：高隔离级别会加大锁的范围和持锁时间，可能导致死锁。应合理规划事务的逻辑，以减少死锁风险。
• 避免不必要的高隔离级别：在大多数情况下，'READ COMMITTED’即可满足业务

5.8.隔离级别配置

5.8.1.查看数据库的默认隔离级别​

SQL Server 的默认隔离级别为 READ COMMITTED，但实际行为可能受行版本控制参数影响。
​​查询方法​​：

DBCC USEROPTIONS;

​​输出示例​​：

isolation level: read committed

​​说明​​：
该命令显示当前会话的默认隔离级别，但若数据库启用了行版本控制（如 READ_COMMITTED_SNAPSHOT），实际隔离行为可能为 READ COMMITTED SNAPSHOT。

5.8.2.查看数据库的行版本控制配置​​

通过查询系统视图 sys.databases，可查看数据库是否启用了行版本控制功能：

SELECT 
    name,
    is_read_committed_snapshot_on,  -- 是否启用 READ COMMITTED SNAPSHOT
    snapshot_isolation_state_desc    -- 快照隔离是否启用
FROM sys.databases
WHERE name = 'YourDatabaseName';

​​输出示例​​：

​​字段解释​​：

• is_read_committed_snapshot_on：

  1 表示启用 READ COMMITTED SNAPSHOT 模式。此时，READ COMMITTED 隔离级别默认使用行版本控制（非锁机制），避免阻塞。

• snapshot_isolation_state_desc：

  ON 表示启用 SNAPSHOT 隔离级别，允许事务级一致性读取。

5.8.3. 全局配置的生效规则​​

​​

• READ_COMMITTED_SNAPSHOT​​：

  启用后，所有使用 READ COMMITTED 隔离级别的事务自动使用行版本控制，无需显式设置。
  修改此参数需独占数据库连接（需临时设置为单用户模式）。

• ​​ALLOW_SNAPSHOT_ISOLATION​​：

  启用后，允许事务使用 SNAPSHOT 隔离级别，但需在会话中显式设置 SET TRANSACTION ISOLATION LEVEL SNAPSHOT。

5.8.4.隔离级别配置的作用范围对比​​

5.8.5.修改全局配置的注意事项​

​性能影响​​：

• 启用行版本控制会增加 tempdb 的负载，因行版本数据需存储在 tempdb 中。

​​兼容性​​：

• 修改 READ_COMMITTED_SNAPSHOT 需确保无长时间运行的事务，否则可能导致阻塞。

​​配置优先级​​：

• 若同时启用 READ_COMMITTED_SNAPSHOT 和
  ALLOW_SNAPSHOT_ISOLATION，两者均可使用，但需权衡资源消耗。

​​总结​​
​

• 全局默认隔离级别​​：通过 DBCC USEROPTIONS 查看，但实际行为可能受行版本控制参数影响。
• ​​行版本控制配置​​：通过 sys.databases 查看 is_read_committed_snapshot_on 和
  snapshot_isolation_state_desc。
• ​​修改配置​​：需谨慎操作，建议在低峰期执行，并备份数据库。