写在前面：

大家好，我是强哥，一个热爱分享的技术狂。目前已有 12 年大数据与 AI 相关项目经验， 10 年推荐系统研究及实践经验。平时喜欢读书、暴走和写作。

业余时间专注于输出大数据、AI 等相关文章，目前已经输出了 40 万字的推荐系统系列精品文章，今年 6 月底会出版「构建企业级推荐系统：算法、工程实现与案例分析」一书。如果这些文章能够帮助你快速入门，实现职场升职加薪，我将不胜欢喜。

想要获得更多免费学习资料或内推信息，一定要看到文章最后喔。

内推信息

如果你正在看相关的招聘信息，请加我微信：liuq4360，我这里有很多内推资源等着你，欢迎投递简历。

免费学习资料

如果你想获得更多免费的学习资料，请关注同名公众号【数据与智能】，输入“资料”即可！

学习交流群

如果你想找到组织，和大家一起学习成长，交流经验，也可以加入我们的学习成长群。群里有老司机带你飞，另有小哥哥、小姐姐等你来勾搭！加小姐姐微信：epsila，她会带你入群。

2020 Learning SQL-中译

第十二章事务

到目前为止，本书中的所有示例都是独立的 SQL 语句，这可能是临时报表或者数据维护脚本的规范，但是应用程序逻辑往往会包含多个 SQL 语句，它们会作为一个逻辑工作单元整体一起执行。本章探讨事务（transaction），事务是用于将一组 SQL 语句组合在一起的机制，使得这些语句要么全部执行成功要么全部不执行。

多用户数据库

数据库管理系统允许单个用户查询和修改数据，但在当今世界，可能会有成千上万的人同时对数据库进行更改。如果每个用户都只执行查询（比如在正常工作时间使用数据仓库时），那么数据库服务器几乎不用处理什么额外问题了。但是如果一些用户正同时执行增加或修改数据的操作，那么服务器必须处理更多的簿记。

例如，假设你正生成一个报表，用于总结本周的电影租赁操作。但在生成报表的同时，发生了以下活动：

• 客户租赁电影；

• 客户在截止日期后归还电影并支付滞纳金；

• 库存中增加了五部新影片。

因此，当你生成报表的同时，有多个用户正在修改基础数据，那么报表上到底应该显示哪些数据呢？答案在某种程度上取决于服务器如何处理锁（locking），我会在下一节介绍它。

锁

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

• 数据库写操作必须向服务器申请和获得用于修改数据的写锁（write lock），而数据库读操作必须向服务器申请和获得用于查询数据的读锁（read lock）。多个用户可以同时读取数据，但是一个表（或其中的一部分）每次只能分配一个写锁，在该锁被释放之前，将拒绝读请求；

• 数据库写操作必须向服务器申请和获得用于修改数据的写锁，而读操作并不需要任何类型的锁就可以查询数据。此外，服务器要保证从查询开始一直到结束的整个过程中，读操作看到的数据视图一致（即使其他用户修改，数据也要看上去相同）。此方法称作版本控制（versioning）。

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

锁的粒度

在决定如何锁定资源时，还可以使用许多不同的策略。服务器可将锁应用于以下三个不同级别（或称粒度(granularities)）中：

• 表锁（Table locks）

 防止多个用户同时修改同一表中的数据。

• 页锁（Page locks）

 防止多个用户同时修改表中同一页上的数据（一页是通常在 2 KB 到 16 KB 范围内的内存空间）。

• 行锁（Row locks）

 防止多个用户同时修改表中的同一行的数据。

这些方法也是各有利弊的。表锁几乎不需要什么簿记就能锁定整个表，但是随着用户数量增加，它的等待时间会迅速增长到让人难以接受的地步。另一方面，行锁需要更多的簿记，但只要各个用户感兴趣的是不同的行，它就能允许多用户修改同一个表。在本书讨论的三种服务器中，Microsoft SQL Server 使用表锁、页锁和行锁，Oracle Database 只使用行锁，而 MySQL 使用表锁、页锁或行锁（同样取决于所选择的存储引擎）。在某些情况下，SQL Server 会将锁从行锁升级到页锁，再从页锁升级到表锁，而 Oracle Database 永远不会升级锁（因为只使用行锁）。

再谈上文提到的报表问题：报表上显示的数据要么反映报表开始生成时的数据库状态（如果服务器使用版本控制方法），要么反映服务器向报表程序发出读锁时数据库的状态（如果服务器使用读锁和写锁）。

什么是事务

如果数据库服务器正常运行时间为 100%、用户总是允许程序完成执行、并且应用程序不会遇到任何导致执行停止的致命错误，那么就没什么必要讨论并发数据库访问了。但我们并不能沉浸于我们的假设中，毕竟现实情况中上面的完美情形不会一直存在，所以要允许多用户访问同一数据则是需要考虑另一个因素。

另一个并发性难题是事务（transaction），它是一种将多个 SQL 语句组合在一起的机制，保证所有语句全部执行或者一个都不执行（该属性称为原子性(atomicity)）。假设你要把 500 美元从你的储蓄账户转到支票账户：你的钱成功地从你的储蓄账户取走了，但却没有成功存入支票账户，那你肯定会很沮丧。无论导致失败的原因是什么（服务器因维护而关闭，或者对 account 表的页锁定请求超时等），你都希望拿回你的 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 美元要么留在你的储蓄账户中，要么转到你的支票账户，而不至于凭空消失。无论事务是提交还是回滚，在事务执行期间获取的所有资源（例如写锁）都将在事务完成后被释放。

当然，如果程序成功地完成了两个 update 语句，但服务器在执行提交或回滚之前意外关闭了，那么事务将在服务器重新上线后回滚。（数据库服务器在上线之前必须完成的任务之一是查找服务器关闭时所有正在运行且未完成的事务，并将其回滚）。此外，如果你的程序完成事务并提交，但服务器在将变化持久到永久存储区前（即修改后的数据位于内存中，但尚未刷新到磁盘）宕机了，则数据库服务器必须在服务器重启时重新应用事务的变化（这种属性叫持久性(durability)）。

启动事务

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

• 一个活动事务始终与数据库会话相关联，因此没必要也没什么法子来显式地启动一个事务。当前事务结束时，服务器会自动为会话启动一个新事务；

• 除非显式地创建一个事务，否则每个 SQL 语句都会自动独立地提交。要启动事务前必须首先发出一个命令。

本书提及的三种服务器中，Oracle Database 采用第一种方法，而 Microsoft SQL Server 和 MySQL 采用第二种方法。Oracle Database 事务创建方法的优点在于：即使你只发出一个 SQL 命令，如果你不喜欢结果或者改变了主意，那么你也可以回滚所有变化。因此，如果你忘了在 delete 语句中添加 where 子句，你将有机会撤消该操作（假设你足够清醒到能认识该操作可能导致删除表中的所有 125000 行数据的结果）。然而，对于 MySQL 和 SQL Server，一旦按下 Enter 键，SQL 语句带来的更改将是永久性的（除非数据库管理员可以从备份中或通过其他方法获取原始数据）。

SQL:2003 标准包含了 start transaction 命令，它用于显式启动一个事务。MySQL 是符合该标准的，然而 SQL Server 用户需要使用替代命令 begin transaction。对于这两个服务器，在显式开始事务之前，你都处于所谓的自动提交模式（autocommit mode），这意味着服务器会自动提交单个语句。因此，你可以决定是否进入事务模式并提交启动事务命令，或只是简单地默认让服务器自动提交单个语句。

MySQL 和 SQL Server 都允许你关闭单个会话的自动提交模式，在这种情况下，对事务来说服务器就像 Oracle Database 一样工作。对于 SQL Server，可以发出以下命令来禁用自动提交模式：

SET IMPLICIT_TRANSACTIONS ON

MySQL 允许你通过以下方式禁用自动提交模式：

SET AUTOCOMMIT=0

离开自动提交模式后，所有 SQL 命令都将在事务范围内执行，必须显式提交或回滚。

注意：建议每次登录时关闭自动提交模式，并养成在事务中运行 SQL 语句的习惯。就算没什么额外好处，但它可以让你省去在意外删除所有数据后请求数据库管理员重建数据的尴尬。

结束事务

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

如果要撤消自启动事务以来所做的所有更改，则必须提交 rollback 命令，该命令指示服务器将数据返回到其事务开始前的状态。回滚完成后，同样会释放会话中使用的所有资源。

除了提交 commit 或 rollback 命令外，还有其他几种情况可以结束事务，这些情况可能是操作的间接结果，也可能是不属于你控制范围内的结果：

• 服务器宕机，在这种情况下，当服务器重新启动时，事务将自动回滚；

• 你发出一个 SQL 模式语句，例如 alter table，它将提交当前事务并启动一个新事务；

• 你发出另一个 start transaction 命令，这将导致上一个事务的提交；

• 由于服务器检测到死锁（deadlock）并确定当前事务是罪魁祸首，那么服务器会提前结束当前事务。在这种情况下，事务将回滚，并且你将收到一条错误消息。

在这四种情况中，第一种和第三种情况比较好理解，但其他两种情况值得讨论一下。就第二种情况而言，对数据库的更改，无论是添加新表或新索引还是从表中删除列，都不能回滚，因此更改模式的命令必须在事务外部执行。如果事务当前正在进行，那么服务器将先提交当前事务，执行 SQL 模式语句命令，然后为会话自动启动一个新事务。服务器不会通知你发生了什么，因此你应该注意保护那些组成一个工作单元的语句不被服务器意外地分解为多个事务。

第四种情况处理死锁检测。当两个不同的事务正在等待另一个事务当前持有的资源时，就会发生死锁。例如，事务 A 可能刚刚更新了 account 表，正在等待 transaction 表上的写锁，而事务 B 已在 transaction 表中插入了一行，正在等待 account 表上的写锁。如果两个事务碰巧都在修改同一页或同一行（取决于数据库服务器使用的锁粒度），那么它们将永远等待另一个事务完成并释放所需的资源。数据库服务器必须时刻注意这些情况才能使吞吐量不陷入停滞。当检测到死锁时，需要任意地或根据某些标准来选择其中一个事务进行回滚，以便其他事务继续下去。大多数情况下，终止的事务可以重新启动，在不遇到其他死锁的情况下能成功执行。

与前面讨论的第二种情况不同，此时数据库服务器将抛出一个错误，通知你由于死锁检测，事务已回滚。例如，对于 MySQL 而言，你将收到 error 1213，并包含以下消息：

Message: Deadlock found when trying to get lock; try restarting transaction

正如错误消息所示，重试由于死锁检测而回滚的事务是一种合理的做法。但是，如果死锁变得太频繁，那么你可能需要修改访问数据库的应用程序以降低死锁的可能性（一个常见的策略是确保总是按顺序访问数据资源，例如总是在插入 transaction 数据之前修改 account 数据）。

事务保存点

在某些情况下，你可能会在事务中遇到需要回滚的问题，但你可能不希望撤消所有已完成的工作。此时，你可以在事务中创建一个或多个保存点（savepoint），如此一来就可以利用它们回滚到事务的特定位置，而不是一直回滚到事务的启动状态。

选择存储引擎

当使用 Oracle Database 或 Microsoft SQL Server 时，会有一组代码负责低级别数据库操作，例如根据主键值从表中检索特定行。然而，MySQL 数据库服务器设计成可以用多个存储引擎来提供低级别数据库功能，包括资源锁定和事务管理。从 8.0 版开始，MySQL 包括以下存储引擎：

• MyISAM

 一种采用表锁的非事务引擎。

• MEMORY

 一种内存表使用的非事务引擎。

• CSV

 一种以 CSV 文件存储数据的事务引擎。

• InnoDB

 一种采用行级锁定的事务引擎。

• Merge

 一种专用引擎，用于使多个相同的 MyISAM 表看起来像一个表（又称表分区）。

• Archive

 一种专用引擎，用于存储大量未索引的数据，主要用于存档。

尽管你可能认为自己必须为数据库选择某一个存储引擎，但 MySQL 足够灵活，允许你逐表选择一个存储引擎。然而对于那些可能参与事务的表，你应该选择 InnoDB 引擎，它使用行级锁定和版本控制提供所有存储引擎中最高级别的并行能力。

你可以在创建表时显式指定存储引擎，也可以更改现有表的存储引擎。如果不知道表当前使用的引擎，可以使用 show table 命令，如下所示：

mysql> show table status like 'customer' \G;

*************************** 1. row ***************************

Name: customer

Engine: InnoDB 

Version: 10 

Row_format: Dynamic 

Rows: 599 

Avg_row_length: 136 

Data_length: 81920

Max_data_length: 0 I

ndex_length: 49152 

Data_free: 0 

Auto_increment: 599 

Create_time: 2019-03-12 14:24:46 

Update_time: NULL 

Check_time: NULL 

Collation: utf8_general_ci

Checksum: NULL 

Create_options: 

Comment:

1 row in set (0.16 sec)

注意第二项，你可以看到 customer 表使用的是 InnoDB 引擎。如果没有指定引擎，你可以通过以下命令将 InnoDB 引擎分配给 customer 表：

ALTER TABLE customer ENGINE = INNODB;

所有保存点都必须有一个名字，这样你就在一个事务中有多个保存点了。要创建名为 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 命令回滚到保存点，每个命令后面都要跟有保存点的名字。