SQL的事务及其ACID属性
@TOC
推荐超级课程：
SQL中的事务简介
想象一个使用SQL作为数据库的银行系统。用户A想要把一些钱存入用户B的账户。如果他们发送了这笔钱，我们从他们的账户余额中取出这笔钱，然后想要把这笔钱存入用户B的账户，但突然我们的数据库崩溃了。
这意味着我们从用户A的余额中取出的钱消失了吗？不，因为SQL数据库使用SQL事务来处理这些情况。
事务和ACID属性
事务是作为单个原子单元执行的一个或多个SQL操作的序列。其目的是确保数据库中的数据一致性。一个事务具有以下属性，常常用ACID首字母缩写来提及:
- 原子性: 整个事务被视为一个单元，要么完全成功，要么完全失败。
- 一致性: 事务将数据库从一个有效状态转换为另一个有效状态，维持数据库的不变性。
- 隔离性: 并发事务所做的修改在彼此之间被隔离，直到被提交。
- 持久性: 一旦事务被提交，即使发生系统故障，也会保持提交状态。
SQL事务中的关键命令
我们使用BEGIN TRANSACTION关键字标记SQL事务的开始。
为了保存在事务期间所做的所有更改，我们将更改COMMIT到数据库中。
如果在事务期间出现问题，我们可以使用ROLLBACK命令回滚事务期间所做的所有更改，将数据库恢复到事务开始时的状态。
示例
让我们回到一个简单的银行应用程序示例，您需要从账户A转账100美元到账户B。这涉及两个步骤:
- 从账户A扣除金额
- 然后将其添加到账户B
只有完成了这两个步骤，事务才会成功。以下是如何将此作为SQL事务写入的方式:
BEGIN TRANSACTION;
-- 从账户A的余额中扣除100美元
UPDATE Accounts
SET balance = balance - 100
WHERE account_id = 'A';
-- 检查账户A是否有足够余额，如果没有则回滚
IF @@ROWCOUNT = 0
ROLLBACK;
-- 添加100美元到账户B的余额
UPDATE Accounts
SET balance = balance + 100
WHERE account_id = 'B';
-- 如果一切正常，则提交事务
COMMIT;
这个事务执行以下操作:
- 开始事务: 确保以下操作是一个单一的原子过程。
- 从账户A扣除100美元: 假设有一个名为
accounts的表，包含**account_id**和**balance**列. - 检查账户A是否有足够余额: 如果账户A没有足够的钱，使用
**ROLLBACK TRANSACTION**回滚事务，取消所有更改。 - 添加100美元到账户B: 如果账户A有足够的资金，那么100美元会被添加到账户B中。
- 提交事务: 如果两次更新都成功，将执行
**COMMIT TRANSACTION**命令，永久应用此事务期间所做的更改到数据库中。
这确保要么两个账户都适当更新，要么在任何时候出现问题时，都不会应用任何更改，保持数据的完整性。
SQL事务的隔离层级
数据库事务中的隔离级别决定如何维护事务完整性以及每个事务在多大程度上与其他事务隔离。
SQL标准定义了四个隔离级别，在一致性和性能之间进行权衡。
1. 未提交读取
- 描述: 最低的隔离级别。事务可以看到其他事务在提交前进行的更改。
- 对示例的影响: 在转账过程中，如果另一个事务正在更新账户A或B的余额，这个事务可能读取这些未提交的值。这可能导致问题，比如看到实际不存在的余额(如果另一个事务失败并回滚)。
2. 提交后读取
- 描述: 确保事务只能读取已提交的数据。
- 对示例的影响: 此级别通过确保只读取已提交的账户A和B的余额来避免“未提交读取”的问题。但是，如果在事务内部多次读取余额，而其他事务正在修改数据时，可能会看到不同的值(不可重复读取)。
3. 可重复读取
- 描述: 确保如果事务第二次读取数据，将找到相同的数据值(避免不可重复读取)。
- 对示例的影响: 此级别阻止事务在多次读取相同数据期间看到其他人做出的更改。在余额检查和更新操作期间维护一致的读取结果将有所帮助。然而，它可能无法防止虚读(其他事务添加的新行)。
4. 可序列化
- 描述: 最高级别的隔离。事务彼此完全隔离，就像它们是按顺序执行的一样。
- 对示例的影响: 这确保完全隔离。没有其他事务能干扰转账过程。它防止所有并发问题(脏读、不可重复读和虚读)，但会增加锁定导致的并发性降低和潜在性能问题。
使用不同的隔离级别，可能会发生几种现象，比如脏读、不可重复读或虚读。让我们看看这些术语的含义:
脏读、不可重复读或虚读
脏读取
当一个事务读取由并发未提交事务写入的数据时，会发生脏读。结果是，如果另一个事务回滚，第一个事务将读取从未正式存在于数据库中的数据。
示例:
- 事务1开始并将100美元从账户A转账到账户B。
- 在事务1提交之前，事务2开始并读取账户A的余额。
- 如果事务1失败并回滚，事务2读取了一个从未正式提交的余额。
不可重复读取(未提交读取)
当一个事务在执行过程中两次检索一行，而这两次检索之间行内的值不同时，就会发生不可重复读取。本质上，另一个事务在两次读取之间修改了这一行。
示例:
- 事务1开始并读取账户A的余额。
- 事务2将100美元从账户A转账到账户B并提交。
- 事务1再次读取账户A的余额，看到不同于之前的余额。
虚读取
当一个事务在执行过程中，另一个事务向正在读取的记录中添加了新行(或删除了现有行)时，会出现虚读。这意味着在同一事务中的随后读取可能返回包含新增行或排除的已删除行的一组行，这些行不是初始读取的一部分。
示例:
- 事务1开始一个查询，用于计算账户A的交易数量。
- 事务2插入了一个新的账户A的交易记录并且提交。
- 事务1再次计算账户A的交易数量，发现比之前更多的交易。
显然，更高的隔离级别会降低可能出现的现象类型，但会降低并发性并可能影响性能。