Transaction
SQL中的事务（Transaction）是指一组作为单个逻辑工作单元执行的操作。事务具有以下特性，通常简称为ACID特性：
ACID 特性详细介绍
1. 原子性（Atomicity）
定义：事务中的所有操作要么全部完成，要么全部不完成。即使事务失败，任何已经进行的操作都必须回滚，以确保数据库返回到事务开始之 前的状态。
实现：通过使用日志（log）或称为写前日志（Write-Ahead Logging, WAL）来实现。在事务开始之前，将要执行的操作记录到日志中。如果事务失败，系统使用这些日志来回滚未完成的操作。
2. 一致性（Consistency）
定义：事务开始和结束时，数据库必须处于一致的状态。所有的完整性约束（如外键约束、唯一性约束等）在事务结束时必须得到满足。
实现：数据库管理系统（DBMS）确保在每次事务开始和结束时，所有的约束和规则都得到验证和维护。例如，外键约束确保引用的记录存在，唯一性约束确保某个字段的值是唯一的。
3. 隔离性（Isolation）
定义：事务的执行彼此独立，不能互相干扰。隔离性确保并发事务不会互相干扰，从而避免数据不一致的问题。
实现：通过不同的隔离级别来管理事务之间的相互影响。常见的隔离级别包括：
- 读未提交（Read Uncommitted）：一个事务可以读取其他事务未提交的数据，可能会导致脏读（Dirty Read）。
- 读已提交（Read Committed）：一个事务只能读取其他事务已提交的数据，避免脏读。
- 可重复读（Repeatable Read）：一个事务在整个执行过程中，看到的数据是一致的，避免不可重复读（Non-repeatable Read）。
- 串行化（Serializable）：完全隔离的级别，事务按顺序一个接一个执行，避免幻读（Phantom Read）。
DBMS 使用锁（Locks）和多版本并发控制（MVCC）来实现这些隔离级别。
隔离级别不同导致的常见问题
在数据库管理系统中，为了保证数据的正确性和一致性，事务的隔离级别非常重要。事务隔离级别定义了一个事务与其他事务之间的隔离程度，从而控制并发事务可能带来的问题。脏读取、不可重复读和幻读是并发事务中常见的问题。
-
脏读取（Dirty Read）： 脏读取是指一个事务可以读取到另一个事务尚未提交的修改。当第二个事务修改了某些数据但还未提交时，第一个事务读取了这些未提交的数据，如果第二个事务随后回滚，那么第一个事务读取到的数据就是无效的。
例子：
- 事务A读取一条记录的值为100。
- 事务B将该记录的值修改为200，但尚未提交。
- 事务A再次读取该记录，得到的值为200，这是一个未提交的数据。
- 如果事务B回滚，事务A读取到的200就是脏数据。
-
不可重复读（Non-Repeatable Read）： 不可重复读是指在同一个事务中，多次读取同一条记录得到的结果不一致。这通常是因为在第一次读取和第二次读取之间，其他事务对该记录进行了修改并提交。
例子：
- 事务A读取一条记录的值为100。
- 事务B将该记录的值修改为200并提交。
- 事务A再次读取该记录，得到的值为200。
- 事务A前后两次读取的结果不一致。
-
幻读（Phantom Read）： 幻读是指在同一个事务中，当执行两次相同的查询时，第二次查询的结果集中包含了第一次查询所没有的记录，或者缺少了第一次查询中存在的记录。这通常是由于其他事务在第一次查询和第二次查询之间插入或删除了记录。
例子：
- 事务A查询所有满足某条件的记录，假设结果集包含10条记录。
- 事务B插入了一条满足该条件的新记录并提交。
- 事务A再次执行相同的查询，结果集包含了11条记录。
- 事务A发现多了一条“幻影”记录。
数据库事务隔离级别
为了避免上述问题，SQL标准定义了四种事务隔离级别，每种级别可以防止某些类型的问题：
-
读未提交（Read Uncommitted）：
- 允许脏读取。
- 允许不可重复读。
- 允许幻读。
-
读已提交（Read Committed）：
- 不允许脏读取。
- 允许不可重复读。
- 允许幻读。
-
可重复读（Repeatable Read）：
- 不允许脏读取。
- 不允许不可重复读。
- 允许幻读。
-
可串行化（Serializable）：
- 不允许脏读取。
- 不允许不可重复读。
- 不允许幻读。
不同的数据库系统可能会在实现上有一些差异，但大多数都遵循这些隔离级别标准。选择适当的隔离级别是权衡系统性能与数据一致性的重要决策。
SQL Server 中如何配置事务隔离级别
在 SQL Server 中，可以通过设置事务隔离级别来控制事务的并发行为和数据一致性。SQL Server 提供了多种事务隔离级别，可以通过 T-SQL 语句来设置。默认的事务隔离级别是 Read Committed（读已提交）。
设置事务隔离级别
可以使用 SET TRANSACTION ISOLATION LEVEL 语句来设置当前会话的事务隔离级别。可用的隔离级别有：
- READ UNCOMMITTED（读未提交）
- READ COMMITTED（读已提交）
- REPEATABLE READ（可重复读）
- SNAPSHOT（快照隔离）
- SERIALIZABLE（可串行化）
以下是一些示例：
读未提交（READ UNCOMMITTED）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
读已提交（READ COMMITTED）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
可重复读（REPEATABLE READ）
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
快照隔离（SNAPSHOT）
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
可串行化（SERIALIZABLE）
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
示例
以下是一个完整的示例，展示如何设置事务隔离级别并执行查询：
-- 设置事务隔离级别为可重复读
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
-- 执行查询
SELECT * FROM MyTable;
-- 提交事务
COMMIT;
默认事务隔离级别
SQL Server 默认的事务隔离级别是 Read Committed。这意味着一个事务只能读取到其他事务已经提交的数据，从而避免了脏读取问题。要检查当前的隔离级别，可以使用以下查询：
DBCC USEROPTIONS;
此命令将显示当前会话的各种设置，包括事务隔离级别。
快照隔离级别的配置
使用快照隔离级别需要在数据库级别启用快照隔离和读已提交快照隔离。可以使用以下命令：
ALTER DATABASE YourDatabaseName
SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE YourDatabaseName
SET READ_COMMITTED_SNAPSHOT ON;
4. 持久性（Durability）
定义：一旦事务提交，对数据库的改变是永久性的，即使系统故障也不会丢失这些改变。
实现：DBMS 使用持久存储（如硬盘）和日志文件来确保事务的持久性。事务提交前，所有的变更会记录在日志文件中，即使系统崩溃，通过这些日志文件可以恢复数据。
事务执行过程
1. 数据变更过程
内存中的数据缓冲区
- 当事务执行数据变更操作（如INSERT、UPDATE或DELETE）时，这些变更首先会应用到数据库的内存缓冲区（Buffer Pool）中。这个缓冲区中存储了数据页的副本，实际的数据页在磁盘上。
日志记录
- 在将数据页写入磁盘之前，数据库会先将这些变更记录到事务日志（Transaction Log）中。这一过程被称为写前日志（Write-Ahead Logging, WAL）。日志记录在内存中的日志缓冲区，并在适当的时间点刷新到磁盘。
2. 提交前的变更状态
隔离性
- 在事务提交之前，这些变更是不可见的。(一般情况下，数据库默认的隔离等级为 Read Committed) 其他事务在查询数据库时，看到的仍然是提交之前的数据状态。这是通过锁和多版本并发控制（MVCC）来实现的 。
暂存变更
- 数据变更暂时存储在内存的缓冲区中，只有在事务提交时，才会将这些变更永久地写入磁盘上的数据文件。
3. 事务提交过程
刷新日志
- 在提交事务时，首先将内存中的日志缓冲区内容刷新到磁盘，确保所有的变更操作都持久化在日志文件中。
写入数据
- 在日志持久化后，将内存缓冲区中的数据页写入磁盘上的实际数据文件。这个过程确保了数据的持久性。
4. 事务回滚
回滚操作
- 如果事务在提交前失败或被回滚，DBMS会使用Undo Log中的记录撤销已经执行的变更操作，确保数据库恢复到事务开始前的一致状态。
事务的底层实现
-
日志管理
- WAL（Write-Ahead Logging）：在事务提交前，将所有的变更写入日志文件。这些日志记录了事务的开始、每一步的变更以及事务的提交或回滚。
- Redo Log：在系统崩溃后，通过Redo Log重新应用已提交的事务，以确保数据的一致性。
- Undo Log：在事务失败后，通过Undo Log回滚未完成的事务，以确保数据的原子性。
-
锁机制
- 排它锁（Exclusive Lock）：用于写操作，确保其他事务不能读或写被锁定的资源。
- 共享锁（Shared Lock）：用于读操作，允许其他事务读，但不允许写。
- 意向锁（Intent Lock）：用于表级锁，以表明将要在表的某些行上获取锁。
-
多版本并发控制（MVCC）
- MVCC：为每个事务创建数据的多个版本，以实现高并发性。每个事务只能看到在其开始之前提交的版本，从而实现可重复读。
-
恢复机制
- 检查点（Checkpoint）：定期将内存中的数据写入磁盘，并记录当前状态，减少崩溃后恢复所需的时间。
- 崩溃恢复：在系统重启时，利用日志文件和检查点信息，重新应用未完成的事务，回滚失败的事务，确保数据库的一致性和持久性。 在事务提交前，数据的变更实际上是暂时存储在内存中的，并且这些变更对其 他事务不可见。这种暂时存储的机制主要是为了确保事务的原子性、一致性和隔离性。以下是详细的过程：
示例
假设有一个简单的事务更新银行账户余额：
BEGIN TRANSACTION;
-- 从账户A中减去100元
UPDATE Accounts SET balance = balance - 100 WHERE account_id = 'A';
-- 向账户B中加上100元
UPDATE Accounts SET balance = balance + 100 WHERE account_id = 'B';
-- 提交事务
COMMIT;
过程解释
-
数据变更应用到内存缓冲区：
UPDATE Accounts SET balance = balance - 100 WHERE account_id = 'A';UPDATE Accounts SET balance = balance + 100 WHERE account_id = 'B';- 这些操作在内存的缓冲区中进行，并记录在日志缓冲区中。
-
日志写入磁盘：
- 在事务提交时，将日志缓冲区的内容写入磁盘，确保所有变更操作都有日志记录。
-
数据页写入磁盘：
- 将内存缓冲区中的数据页写入磁盘上的实际数据文件。
-
提交完成：
- 提交事务，其他事务现在可以看到这些变更。
回滚操作示例
如果在事务提交前发生错误或手动回滚：
BEGIN TRANSACTION;
-- 从账户A中减去100元
UPDATE Accounts SET balance = balance - 100 WHERE account_id = 'A';
-- 向账户B中加上100元
UPDATE Accounts SET balance = balance + 100 WHERE account_id = 'B';
-- 出现错误，回滚事务
ROLLBACK;
过程解释
-
数据变更应用到内存缓冲区：
- 与上面的过程相同。
-
回滚操作：
- 使用Undo Log中的记录，将账户A的余额加回100元，将账户B的余额减去100元，恢复到事务开始前的状态。
通过这种机制，DBMS可以确保在事务提交前的数据变更是暂时的，并在事务提交后才永久生效，从而保证数据的一致性和持久性。
事务的原子性和一致性经常被混淆
- 原子性：强调事务要么全部成功，要么全部失败
- 一致性：强调事物开始前结束后，数据库状态和数据的一致（有时可能需要人为去保证）
假设我们有一个学生表Students和一个成绩表Grades，学生ID必须存在于学生表中才能插入到成绩表
BEGIN TRANSACTION;
-- 检查学生ID是否存在
SELECT student_id FROM Students WHERE student_id = 'S1';
-- 如果学生ID存在，插入成绩
IF EXISTS (SELECT student_id FROM Students WHERE student_id = 'S1') THEN
INSERT INTO Grades (student_id, grade) VALUES ('S1', 'A');
ELSE
-- 抛出异常或错误，学生ID不存在
ROLLBACK;
END IF;
COMMIT;
在这个例子中，一致性要求学生成绩的插入必须确保相关的学生ID在学生表中存在。如果学生ID不存在，事务会回滚，以保证数据的完整性。
这些例子展示了一致性在不同情景下的应用，强调了事务执行前后的数据规则和约束必须得到满足，从而确保数据库的有效状态。