SQL Server 事务

SQL Server 中的事务是一系列连续的语句或查询，用于在数据库中执行一项或多项任务。每个事务可能包含单个读、写、更新或删除操作，或所有这些操作的组合。每个事务在 SQL Server 中都必须发生两件事：

• 要么在事务提交时所有修改都成功。
• 要么在事务回滚时所有修改都被撤销。

在事务中的所有操作都完成后，事务才算成功。这意味着如果任何一个语句失败，事务操作都会失败。每个事务都从第一个可执行的 SQL 语句开始，并在找到提交或回滚时结束，无论是显式还是隐式。它显式使用 COMMIT 或 ROLLBACK 语句，当使用 DDL 语句时也隐式使用。

下面的图示解释了事务过程

下面的示例将解释事务的概念

此示例将使用银行数据库系统来解释事务的概念。假设一位银行客户想通过 ATM 模式从其账户中取款。 ATM 可以通过三个步骤完成此操作：

1. 第一步是检查账户中是否有足够的可用余额。
2. 第二步如果金额可用，则从账户扣除金额，然后更新账户余额。
3. 第三步是将取款操作写入日志文件。此步骤记录事务是成功还是失败。如果成功，则将数据修改写入数据库。否则，事务将回滚到其之前的状态。

事务的基本原理是，如果其中一条语句返回错误，则整个更改集将被回滚，以确保数据完整性。如果事务成功，则所有更改都将在数据库中永久生效。因此，如果在 ATM 取款时发生停电或其他问题，事务可以确保我们的余额保持一致。事务语句可以最佳地执行这些操作，因为事务的四个关键属性使所有操作更加准确和一致。事务的四个属性被称为 ACID。

事务属性

事务属性被称为 ACID（原子性、一致性、隔离性、持久性）属性，下面将详细讨论。

原子性 (Atomicity)： 此属性确保事务中包含的所有语句或操作都必须成功执行。否则，如果任何一个操作失败，整个事务将被中止，并且所有操作都将回滚到其之前的状态。

一致性 (Consistency)： 此属性确保数据库仅在事务成功提交时才会更改状态。它还负责保护数据免受崩溃的影响。

隔离性 (Isolation)： 此属性保证所有事务与其他事务隔离，这意味着事务中的每个操作都是独立操作的。它还确保语句之间互不干扰。

持久性 (Durability)： 此属性保证即使系统崩溃或发生故障，已提交事务的结果也会永久保存在数据库中。

SQL Server 中的事务模式

SQL Server 可以使用三种不同的事务模式：

自动提交事务模式 (Auto-commit Transaction Mode)： 这是 SQL Server 的默认事务模式。它将每个 SQL 语句评估为一个事务，并相应地提交或回滚结果。因此，成功的语句立即提交，而失败的语句立即回滚。

隐式事务模式 (Implicit Transaction Mode)： 此模式允许 SQL Server 为每个 DML 语句开始隐式事务，但它明确要求在语句末尾使用 commit 或 rollback 命令。

显式事务模式 (Explicit Transaction Mode)： 此模式由用户定义，允许我们精确地标识事务的开始和结束点。在发生致命错误时，它会自动中止。

事务控制

以下是用于控制事务的命令：

• BEGIN TRANSACTION： 这是一个表示每个事务开始的命令。
• COMMIT： 这是一个用于将更改永久保存在数据库中的命令。
• ROLLBACK： 这是一个用于取消所有修改并返回到先前状态的命令。
• SAVEPOINT： 此命令在事务组内创建点，允许我们只回滚事务的一部分，而不是整个事务。
• RELEASE SAVEPOINT： 用于删除已存在的 SAVEPOINT。
• SET TRANSACTION： 此命令为事务命名，可用于将其设置为只读或读/写，或将其分配给特定的回滚段。

注意：我们只能对 DML 语句（INSERT、UPDATE 和 DELETE）使用事务控制语言命令。在使用它们创建或删除表时不能使用它们，因为这些操作会自动提交到数据库。

事务状态

它指示事务在其生命周期中的进行情况。它描述了事务的当前状态以及事务将如何在未来被处理。 这些状态定义了确定事务是提交还是中止的规则。

让我们描述 SQL Server 中的每种事务状态：

活动状态 (Active State)： 当事务的指令正在执行时，事务处于活动状态。如果所有“读写”操作都成功完成，则它将变为“部分提交状态”。如果任何指令失败，它将变为“失败状态”。

部分提交 (Partially Committed)： 当所有读写操作完成后，更改会写入主内存或本地缓冲区。如果更改永久保存在数据库中，则状态将变为“已提交状态”。否则，它将变为“失败状态”。

失败状态 (Failed State)： 当任何事务的指令失败或在数据库上进行永久修改失败时，事务会进入失败状态。

中止状态 (Aborted State)： 当发生任何类型的失败时，事务会从“失败状态”移动到“中止状态”。由于在之前的状态中这些更改仅写入本地缓冲区或主内存，因此这些更改将被移除或回滚。

已提交状态 (Committed State)： 当更改永久保存在数据库中并终止于“终止状态”时，事务即完成并进入此状态。

终止状态 (Terminated State)： 如果没有回滚且事务处于“已提交状态”，则系统是一致的，并准备好进行新的事务，而旧的事务则被终止。

在 SQL Server 中实现事务

让我们通过一些示例来理解如何在 SQL Server 中实现事务。在这里，我们将使用“Product”表来演示所有事务状态。

以下 SQL 脚本在选定的数据库中创建 Product 表。

接下来，执行以下脚本将数据插入此表中。

执行 SELECT 语句以验证数据。

COMMIT 事务示例

将事务中使用的 SQL 语句分成多个逻辑部分是一个好主意。然后，我们可以决定是提交还是回滚数据。以下步骤说明了如何创建事务：

• 使用 BEGIN TRANSACTION 命令开始事务。
• 根据需要编写 SQL 语句并进行划分。
• 使用 COMMIT 语句完成事务并永久保存更改。

以下命令解释了 SQL Server 中的 COMMIT 操作：

如果没有发现错误，我们将看到以下输出，其中事务的每个 SQL 语句都独立执行。

INSERT 和 UPDATE 语句在事务提交后无法回滚。当我们在提交操作后验证表时，我们将看到以下数据。

ROLLBACK 事务示例

我们将使用 ROLLBACK 命令撤销尚未保存到数据库的任何事务，并返回到事务开始的那个点。以下示例解释了 SQL Server 中的 ROLLBACK 操作：

一旦执行了上述事务，我们将看到它已成功执行。但是，它不会对数据库中的任何更改产生影响，因为除非我们执行 COMMIT 或 ROLLBACK 语句，否则更改无法生效。因此，我们有权使用 ROLLBACK 事务命令回滚所有数据库操作。这是完整的事务语句：

使用 @@Error 全局变量进行事务处理

此变量用于检查是否存在错误。 以下示例解释了其概念。在这里，我们将首先使用 BEGIN 命令开始事务，然后编写两个 insert 语句。接下来，我们将使用全局系统变量 @@ERROR 在 IF 语句中检查错误。如果值为 0 以上，则表示存在错误。现在，事务将被回滚；否则，事务将被提交。

当执行上述事务时，我们将注意到它已被回滚。这是由于我们尝试在主键列中插入重复值。

自动回滚事务

大多数事务包含多个查询。在执行事务的过程中，如果任何 SQL 语句产生错误，数据库中将不发生任何修改，并且剩余的语句也不会执行。这个概念在 SQL Server 中被称为自动回滚事务。让我们使用一个简单的例子来演示这个过程。

此事务产生以下输出：

在此输出中，我们可以看到 insert 语句已成功执行。但是，执行 update 语句时因数据类型转换问题而遇到错误。在这种情况下，SQL Server 不允许对数据库进行任何更改，这意味着 insert 操作没有添加任何值，并且 select 语句也没有执行。

事务中的 Savepoint

savepoint 在事务中插入一个特殊标记，允许我们回滚在 savepoint 之后执行的所有更改。它还用于回滚事务的任何特定部分，而不是整个事务。我们可以通过使用 SAVE TRANSACTION sp_name 语句来定义它。以下示例将解释在事务中使用 savepoint 的方法，该方法提交 insert 语句并回滚 delete 语句。

在下面的结果中，我们可以看到 product id 116 被删除，117 被插入到第一个输出中。但是，在第二个输出中，由于 savepoint，删除操作已回滚。

如何在事务中释放 savepoint？

Release savepoint 用于从当前事务中删除已命名的 savepoint，而不会撤销在 savepoint 之后执行的查询结果。MySQL 具有此命令，但 SQL Server 不提供任何命令来释放 savepoint。相反，它们会在提交或回滚事务结束时自动释放，因此我们不必在中间担心它们。

SQL Server 中的隐式事务

我们可以通过启用 IMPLICIT_TRANSACTIONS 选项来定义隐式事务。以下示例将轻松解释此概念：

在此事务中，我们使用了两个选项 @@OPTION 和 @@TRANCOUNT。 @@OPTION 提供有关当前 SET 选项的信息，而 @@TRANCOUNT 提供当前会话中的 BEGIN TRANSACTION 语句的数量。

现在，执行事务将返回以下输出：

SQL Server 中的显式事务

显式事务必须通过 BEGIN TRANSACTION 命令定义，因为它标识了显式事务的起始点。我们可以如下定义 SQL Server 中的显式事务：

在语法中，trans_name 选项表示事务的唯一名称。@trans_name_var 表示存储事务名称的用户定义变量。最后，MARK 选项允许我们在日志文件中标记特定事务。

通过 BEGIN TRANSACTION 命令的显式事务会根据事务相关资源的隔离级别获取锁。这有助于减少锁定问题。请看下面的示例：

这是输出

SQL Server 中的标记事务

标记事务用于在日志文件中为特定事务添加描述。在将数据库恢复到之前的状态时，我们可以将其用作恢复点，而不是日期和时间。我们必须知道，仅当标记的事务修改了数据库时，才会将标记添加到日志文件中。我们可以使用以下示例来理解其概念。

假设我们意外修改了数据库，并且我们不知道数据更改的确切时刻；在这种情况下，数据恢复可能需要很长时间。但是，如果我们使用标记事务，它将成为确定数据更改确切时间的有用工具。

以下语法说明了 SQL Server 中的标记事务：

这里我们必须定义事务的名称，然后添加 WITH MARK 选项。在下面的示例中，我们将删除记录并在日志文件中添加标记。

logmarkhistory 表包含在 msdb 数据库中，并存储有关已提交的每个标记事务的信息。执行以下语句以从 logmarkhistory 表中获取详细信息：

SQL Server 中的命名事务

我们也可以在 SQL Server 中为我们的事务提供一个名称。在单个查询中处理多个事务时，始终建议使用命名事务。下面的示例解释了如何重命名事务。

这是输出

结论

本文将对 SQL Server 语句中的事务提供完整的概述。事务在关系数据库系统中很有用，因为它们可以确保数据库的完整性。