第12章 事务

本书到目前为止的所有示例都是单个独立的SQL语句。虽然这在即席报告或数据维护脚本中可能很常见，但应用程序逻辑通常会包含多个需要作为一个逻辑工作单元一起执行的SQL语句。本章将探讨并发执行多个SQL语句的必要性和所需的基础架构。

多用户数据库

数据库管理系统不仅允许单个用户查询和修改数据，还允许多人同时进行这些操作。如果每个用户都只是执行查询操作，就像数据仓库在正常工作时间内可能出现的情况那样，那么数据库服务器需要处理的问题就很少。然而，如果部分用户正在添加和（或）修改数据，那么服务器就必须处理更多的事务记录工作。

例如，假设你正在运行一份报告，显示你所在分行开设的所有支票账户的可用余额。然而，在你运行这份报告的同时，发生了以下活动：

• 你所在分行的一名柜员正在为一位客户办理存款业务。
• 一位客户正在大厅的自动取款机上完成取款操作。
• 银行的月末应用程序正在为账户计算利息。

因此，在你运行报告时，多个用户正在修改底层数据，那么报告上应该显示哪些数据呢？答案在一定程度上取决于服务器如何处理锁定（locking），下一节将对此进行介绍。

锁定

锁定是数据库服务器用于控制数据资源并发使用的机制。当数据库的某个部分被锁定时，任何其他想要修改（或可能读取）这些数据的用户都必须等待，直到锁被释放。大多数数据库服务器使用以下两种锁定策略之一：

• 数据库写入者必须向服务器请求并获得写锁才能修改数据，数据库读取者必须向服务器请求并获得读锁才能查询数据。虽然多个用户可以同时读取数据，但对于每个表（或其部分），一次只发放一个写锁，并且读请求会被阻塞，直到写锁被释放。
• 数据库写入者必须向服务器请求并获得写锁才能修改数据，但读取者查询数据时不需要任何类型的锁。相反，服务器确保读取者从查询开始到查询结束这段时间内，看到的数据是一致的（即使其他用户可能正在进行修改，数据看起来也保持不变）。这种方法被称为版本控制（versioning）。

这两种方法各有利弊。如果有许多并发的读写请求，第一种方法可能会导致长时间等待；如果在数据被修改时存在长时间运行的查询，第二种方法可能会出现问题。在本书讨论的三个服务器中，Microsoft SQL Server使用第一种方法，Oracle Database使用第二种方法，MySQL则两种方法都使用（具体取决于你选择的存储引擎，本章稍后会对此进行讨论） 。

锁定粒度

在决定如何锁定资源时，还有许多不同的策略可供选择。服务器可以在三个不同级别（即粒度）之一应用锁：

• 表锁（Table locks）：防止多个用户同时修改同一个表中的数据。
• 页锁（Page locks）：防止多个用户同时修改表中同一页（页是一段内存，通常在2KB到16KB之间）的数据。
• 行锁（Row locks）：防止多个用户同时修改表中的同一行数据。

同样，这些方法也各有利弊。锁定整个表所需的事务记录工作很少，但随着用户数量的增加，这种方法很快会导致不可接受的等待时间。另一方面，行锁需要更多的事务记录工作，但它允许许多用户修改同一个表，只要他们操作的是不同的行。在本书讨论的三个服务器中，Microsoft SQL Server使用页锁、行锁和表锁，Oracle Database只使用行锁，MySQL则使用表锁、页锁或行锁（同样取决于你选择的存储引擎）。在某些情况下，SQL Server会将锁从行锁升级到页锁，再从页锁升级到表锁，而Oracle Database永远不会升级锁。

回到你的报告，报告页面上显示的数据将反映你的报告开始时数据库的状态（如果你的服务器使用版本控制方法），或者反映服务器向报告应用程序发放读锁时数据库的状态（如果你的服务器同时使用读锁和写锁）。

什么是事务？

如果数据库服务器能保证100%的正常运行时间，如果用户总是让程序执行完毕，如果应用程序总是能在不遇到导致执行停止的致命错误的情况下完成运行，那么关于并发数据库访问就没有什么可讨论的了。然而，这些情况我们都无法保证，因此，为了允许多个用户访问相同的数据，还需要一个额外的元素。

这个并发难题中的额外部分就是事务（transaction），它是一种将多个SQL语句组合在一起的机制，使得这些语句要么全部成功执行，要么全部不执行（这一特性称为原子性，atomicity）。假设你试图从储蓄账户向支票账户转账500美元，要是这笔钱成功从储蓄账户取出，但却没有转到支票账户，你肯定会有点生气。不管失败的原因是什么（服务器因维护而关闭、对账户表的页锁请求超时等），你都希望拿回这500美元。

为了防止这类错误，处理你转账请求的程序会先开始一个事务，然后发出将钱从储蓄账户转到支票账户所需的SQL语句。如果一切顺利，就通过发出提交（commit）命令结束事务。然而，如果发生了意外情况，程序会发出回滚（rollback）命令，指示服务器撤销自事务开始以来所做的所有更改。整个过程可能如下所示：

START TRANSACTION;
/*  withdraw money from first account, making sure balance is sufficient  */
UPDATE account SET avail_balance = avail_balance - 500
WHERE account_id = 9988
  AND avail_balance > 500;
IF <exactly one row was updated by the previous statement> THEN
    /*  deposit money into second account  */
    UPDATE account SET avail_balance = avail_balance + 500
    WHERE account_id = 9989;
    IF <exactly one row was updated by the previous statement> THEN
        /*  everything worked, make the changes permanent  */
        COMMIT;
    ELSE
        /*  something went wrong, undo all changes in this transaction  */
        ROLLBACK;
    END IF;
ELSE
    /*  insufficient funds,  or error encountered during update  */
    ROLLBACK;
END IF;

虽然前面的代码块看起来可能与主要数据库公司提供的某种过程式语言（如Oracle的PL/SQL或Microsoft的Transact-SQL）类似，但它是用伪代码编写的，并不试图模仿任何特定的语言。

前面的代码块首先开始一个事务，然后尝试从支票账户取出500美元并存入储蓄账户。如果一切顺利，事务将被提交；如果出现任何问题，事务将被回滚，这意味着自事务开始以来的所有数据更改都将被撤销。

通过使用事务，程序可确保你的500美元要么留在储蓄账户中，要么成功转到支票账户，而不会出现丢失的情况。无论事务是被提交还是被回滚，在事务执行期间获取的所有资源（例如写锁）都会在事务完成时被释放。

当然，如果程序成功完成了两个更新语句，但在执行提交或回滚操作之前服务器关闭了，那么当服务器重新上线时，事务将被回滚。（数据库服务器在上线之前必须完成的任务之一，就是找到服务器关闭时正在进行的任何未完成事务并将其回滚）。此外，如果你的程序完成了一个事务并发出了提交命令，但在更改被应用到永久存储之前（即修改后的数据还在内存中，尚未刷新到磁盘）服务器关闭了，那么数据库服务器在重新启动时，必须重新应用你事务中的更改（这一特性称为持久性，durability）。

开始事务

数据库服务器通过以下两种方式之一来处理事务创建：

• 一个活动事务始终与一个数据库会话相关联，因此无需也无法显式地开始一个事务。当前事务结束时，服务器会自动为你的会话开始一个新事务。
• 除非你显式地开始一个事务，否则各个SQL语句会自动独立提交。要开始一个事务，你必须先发出一个命令。

在这三个服务器中，Oracle Database采用第一种方法，而Microsoft SQL Server和MySQL采用第二种方法。Oracle处理事务的方法有一个优点，即即使你只发出一条SQL命令，如果你对结果不满意或者改变了主意，也能够回滚更改。因此，如果你在删除语句中忘记添加where子句，你还有机会挽回损失（前提是你喝了早咖啡，意识到自己并不想删除表中的125,000行数据）。然而，对于MySQL和SQL Server，一旦你按下回车键，SQL语句所带来的更改就会生效（除非数据库管理员（DBA）能够从备份或其他方式中恢复原始数据）。

SQL:2003标准包含一个start transaction命令，用于在你想要显式开始一个事务时使用。MySQL遵循该标准，而SQL Server用户必须使用begin transaction命令。对于这两个服务器，在你显式开始一个事务之前，你处于所谓的自动提交（auto-commit）模式，这意味着各个语句会由服务器自动提交。因此，你可以决定进入事务模式并发出start/begin transaction命令，也可以让服务器自动提交各个语句。

MySQL和SQL Server都允许你为单个会话关闭自动提交模式，在这种情况下，这两个服务器在事务处理方面的行为就会和Oracle Database一样。在SQL Server中，你可以使用以下命令禁用自动提交模式：

SET IMPLICIT_TRANSACTIONS ON

MySQL可以通过以下命令禁用自动提交模式：

SET AUTOCOMMIT=0

一旦你关闭了自动提交模式，所有SQL命令都将在一个事务的范围内执行，并且必须显式提交或回滚。

温馨提示：每次登录时都关闭自动提交模式，并养成在事务中运行所有SQL语句的习惯。这样做至少可以避免你因不小心删除数据，而不得不让数据库管理员重建数据的尴尬情况。

结束事务

一旦事务开始，无论是通过start transaction命令显式启动，还是由数据库服务器隐式启动，你都必须显式结束事务，才能使所做的更改成为永久性的。你可以通过commit命令来实现这一点，该命令会指示服务器将更改标记为永久性更改，并释放事务期间使用的任何资源（即页面锁或行锁）。

如果你决定撤销自事务开始以来所做的所有更改，则必须发出rollback命令，该命令会指示服务器将数据恢复到事务前的状态。回滚完成后，会话使用的任何资源都会被释放。

除了发出commit或rollback命令之外，还有其他几种情况会导致事务结束，这些情况可能是你操作的间接结果，也可能是你无法控制的因素导致的：

• 服务器关闭，在这种情况下，服务器重启时，你的事务将自动回滚。
• 你发出SQL模式（SQL schema）语句，如alter table，这将导致当前事务被提交，并启动一个新事务。
• 你发出另一个start transaction命令，这将导致前一个事务被提交。
• 服务器提前结束你的事务，因为服务器检测到死锁（deadlock），并判定你的事务是导致死锁的原因。在这种情况下，事务将被回滚，你会收到一条错误消息。

在这四种情况中，第一种和第三种相当直接明了，但另外两种情况值得讨论一下。就第二种情况而言，对数据库的更改，无论是添加新表、新索引，还是从表中删除列，都无法回滚，因此，更改模式的命令必须在事务之外执行。因此，如果当前有事务正在进行，服务器将提交当前事务，执行SQL模式语句命令，然后自动为你的会话启动一个新事务。服务器不会通知你发生了什么，所以你应该注意，组成一个工作单元的语句不会被服务器无意中拆分成多个事务。

第四种情况涉及死锁检测。当两个不同的事务都在等待对方当前持有的资源时，就会发生死锁。例如，事务A可能刚刚更新了account表，正在等待对transaction表的写锁，而事务B已经在transaction表中插入了一行数据，正在等待对account表的写锁。如果两个事务恰好都在修改同一页面或同一行（这取决于数据库服务器使用的锁粒度），那么它们将永远等待对方事务完成并释放所需资源。数据库服务器必须始终留意这些情况，以免系统吞吐量大幅下降；当检测到死锁时，会选择其中一个事务（随机选择或根据某些标准选择）进行回滚，以便另一个事务能够继续执行。大多数情况下，被终止的事务可以重新启动，并且不会再遇到死锁情况。

与前面讨论的第二种情况不同，数据库服务器会抛出一个错误，通知你由于检测到死锁，你的事务已被回滚。例如，在MySQL中，你会收到错误代码#1213，附带以下消息：

消息：尝试获取锁时发现死锁；请尝试重新启动事务

正如错误消息所建议的，对于因死锁检测而回滚的事务，重新尝试是一种合理的做法。但是，如果死锁相当频繁，那么你可能需要修改访问数据库的应用程序，以降低死锁发生的概率（一种常见策略是确保始终按相同顺序访问数据资源，例如总是先修改账户数据，再插入事务数据）。

事务保存点

在某些情况下，你可能会在事务中遇到需要回滚的问题，但你可能不想撤销已经完成的所有工作。对于这些情况，你可以在事务中建立一个或多个保存点，并使用它们回滚到事务中的特定位置，而不是一直回滚到事务的开始位置。

所有保存点都必须有一个名称，这样你就可以在单个事务中设置多个保存点。要创建一个名为my_savepoint的保存点，可以执行以下操作：

SAVEPOINT my_savepoint;

要回滚到特定的保存点，只需发出rollback命令，后跟to savepoint关键字和保存点的名称，例如：

ROLLBACK TO SAVEPOINT my_savepoint;

以下是一个使用保存点的示例：

START TRANSACTION;
UPDATE product
SET date_retired = CURRENT_TIMESTAMP()
WHERE product_cd = 'XYZ';
SAVEPOINT before_close_accounts;
UPDATE account
SET status = 'CLOSED', close_date = CURRENT_TIMESTAMP(), last_activity_date = CURRENT_TIMESTAMP()
WHERE product_cd = 'XYZ';
ROLLBACK TO SAVEPOINT before_close_accounts;
COMMIT;

该事务的最终结果是虚构的XYZ产品已退役，但没有任何账户被关闭。

使用保存点时，请记住以下几点：

• 尽管名为保存点，但创建保存点时并不会保存任何内容。如果你希望事务成为永久性的，最终必须发出commit命令。
• 如果你发出rollback命令时没有指定保存点名称，事务中的所有保存点都将被忽略，整个事务将被撤销。

如果你使用的是SQL Server，则需要使用专有命令save transaction来创建保存点，使用rollback transaction来回滚到保存点，每个命令后面都要跟保存点的名称。

测试你的知识

通过完成以下练习，测试你对事务的理解。完成后，将你的解决方案与附录C中的答案进行对比。

练习12-1

生成一个事务，将50美元从弗兰克·塔克（Frank Tucker）的货币市场账户转到他的支票账户。你需要在transaction表中插入两行数据，并在account表中更新两行数据。