SQL事务和ACID属性

引言：SQL中的事务

想象一下一个使用SQL作为数据库的银行系统。

用户A想将一些钱存入用户B的账户。

如果他们发送了钱，我们从他们的账户余额中扣除了这笔钱，并且我们想要将这笔钱存入用户B的账户，但突然间我们的数据库崩溃了。

这是否意味着我们从用户A的余额中扣除的钱消失了？嗯，并不是在SQL数据库中，因为它们使用SQL事务来处理这些情况。

事务和ACID属性

事务是一系列作为单一原子单元执行的一个或多个SQL操作。

其目的是确保数据库中的数据一致性。

事务具有以下属性，通常被称为ACID属性的首字母缩写：

• 原子性（Atomicity）： 整个事务被视为单个单元，要么完全成功，要么完全失败。
• 一致性（Consistency）： 事务将数据库从一个有效状态转换为另一个有效状态，保持数据库的不变性。
• 隔离性（Isolation）： 并发事务的修改在它们提交之前被隔离开来。
• 持久性（Durability）： 一旦事务已经提交，即使系统发生故障，它也将保持提交状态。

SQL事务中的关键命令

我们使用 BEGIN TRANSACTION 关键字标记SQL事务的开始。

为了保存事务中进行的所有更改，我们将更改 COMMIT 到数据库。

如果在事务中发生了错误，我们可以使用 ROLLBACK 命令回滚事务中进行的所有更改，将数据库恢复到事务开始时的状态。

示例

让我们回到我们简单的银行应用程序示例，其中您需要从账户A转账100美元到账户B。

这涉及两个步骤：

1.从账户A的余额中扣除金额2.将其添加到账户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;

此事务执行以下操作：

1、启动事务 以确保以下操作是单个原子过程的一部分。

2、从账户A中扣除100美元： 假设有一个名为 **accounts** 的表，其中包含列 **account_id** 和 **balance**。

3、检查账户A是否有足够的资金： 如果账户A没有足够的钱，使用 **ROLLBACK TRANSACTION** 回滚事务，取消所有更改。

4、将100美元添加到账户B中： 如果账户A有足够的钱，就向账户B添加100美元。

5、提交事务： 如果两次更新都成功，则执行 **COMMIT TRANSACTION** 命令，将在此事务期间进行的更改永久应用到数据库。

这确保了要么两个账户都适当地更新，要么在任何一点出现问题时都不应用任何更改，从而保持数据的完整性。

SQL事务的隔离层级

数据库事务的隔离级别确定事务的完整性如何维护以及在多大程度上每个事务与其他事务隔离。

SQL标准定义了四个隔离级别，它们在一致性和性能之间进行权衡。

1. 读未提交（Read Uncommitted）

• 描述： 隔离级别最低。事务甚至可以看到其他事务尚未提交的更改。
• 对示例的影响： 在转账过程中，如果另一个事务正在更新账户A或B的余额，这个事务可能读取这些尚未提交的值。这可能导致看到一个实际上并不存在的余额（如果其他事务失败并回滚）。

2. 读提交（Read Committed）

• 描述： 确保事务只能读取已提交的数据。
• 对示例的影响： 通过确保只读取已提交的账户A和B余额，避免了“读未提交”的问题。然而，在事务内多次读取余额时，如果其他事务正在修改数据，它可能看到不同的值（不可重复读）。

3. 可重复读（Repeatable Read）

• 描述： 确保如果事务第二次读取数据，它将找到相同的数据值（避免不可重复读）。
• 对示例的影响： 这个级别防止事务在事务内多次读取相同数据时看到其他事务所做的更改。在余额检查和更新操作期间，它有助于保持一致的读取结果。然而，它可能不会阻止幻读（其他事务添加的新行）。

4. 可序列化（Serializable）

• 描述： 最高级别的隔离。事务完全与其他事务隔离，就像它们是串行执行的一样。
• 对示例的影响： 这确保完全隔离。没有其他事务可以干扰转账过程。它阻止所有并发问题（脏读、不可重复读和幻读），但以减少并发性和可能引起锁定的性能问题为代价。

使用不同的隔离级别，可能会发生几种现象，比如脏读、不可重复读或幻读。让我们看看这些术语的含义：

脏读

脏读发生在一个事务读取由并发未提交事务写入的数据时。

因此，如果其他事务回滚，第一个事务将读取从未正式提交到数据库中的数据。

示例：

• 事务1开始并从账户A转账100美元到账户B。
• 在事务1提交之前，事务2开始并读取账户A的余额。
• 如果事务1失败并回滚，事务2已读取一个从未正式提交的余额。

不可重复读（读未提交）

当事务在其过程中两次检索同一行时，并且两次读取之间行内的值发生更改时，就会发生不可重复读。

实质上，另一个事务在两次读取之间修改了该行。

示例：

• 事务1开始并读取账户A的余额。•事务2从账户A转账100美元到账户B并提交。
• 事务1再次读取账户A的余额，并看到与之前不同的余额。

幻读

幻读发生在一个事务期间，另一个事务通过添加（或删除）行到正在读取的记录中来更改数据。

这意味着事务中的后续读取可能返回包含新添加行的一组行，或者不包括原始读取的已删除行。

示例：

• 事务1开始查询账户A的交易数量。
• 事务2为账户A插入了一条新的交易记录并提交。
• 事务1再次查询账户A的交易数量，发现比之前多出了一些交易。

显然，更高的隔离级别减少了可能发生的现象类型，但以减少并发性和潜在的性能影响为代价。