事务

荒流2020年3月22日大约 6 分钟约 1812 字

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

1. 事务的内含

在执行 SQL 语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。

例如，一个转账操作，以下这两条 SQL 语句必须全部执行，或者，由于某些原因，如果第一条语句成功，第二条语句失败，就必须全部撤销：

-- 从 id=1 的账户给 id=2 的账户转账 100 元
-- 第一步：将 id=1 的 A 账户余额减去 100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 第二步：将 id=2 的 B 账户余额加上 100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

这种把多条语句作为一个整体进行操作的功能，被称为数据库。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些 SQL 一样，不会对数据库数据有任何改动。

可见，数据库事务具有 ACID 这 4 个特性：

Atomic（原子性）：将所有 SQL 作为原子工作单元执行，要么全部执行，要么全部不执行；
Consistent（一致性）：事务必须使数据库从一个一致性状态变到下一个一致性状态。一致性与业务规则有关，比如转账业务中 A 账户只要减去了 100，B 账户则必定加上了 100；
有点能量守恒的意思？
Isolation（隔离性）：如果有多个事务并发执行，一个事务的执行不能被其他事务干扰，即事务彼此之间隔离；
Duration（持久性）：即事务完成后，对数据库数据的修改被持久化存储。

2. 显式事务与隐式事务

2.1 显式事务

要把多条 SQL 语句作为一个事务执行，使用BEGIN来手动开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务。如果COMMIT语句执行失败了，整个事务也会失败。当然，如果某些情况下你希望主动让事务失败，这时可以用ROLLBACK来回滚事务：

即，这种显式事务的语法可总结为：

BEGIN开始一个事务
ROLLBACK事务回滚
COMMIT事务确认

显式事务的事例如：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

2.2 隐式事务

默认情况下，单个INSERT、UPDATE 或 DELETE 语句会被隐式包装在一个事务中并在执行成功后立即提交，这是一种自动提交（AUTOCOMMIT）模式，这种模式下执行的事务就称之为隐式事务。

在当前连接中，可以使用 SET 命令设置 AUTOCOMMIT 变量来启用或禁用自动提交。启用可以设置为1或ON，禁用可以设置为0或OFF。

SET AUTOCOMMIT=0禁止自动提交
SET AUTOCOMMIT=1开启自动提交

MySQL 如果开了set autocommit=0，那意味着所有的语句都在一个事务里，此时如果你使用连接池，并且在查询之前没有rollback或者set autocommit=1，那么你就杯具了。因为根据 MySQL 的默认事务级别——一致性读，你永远也取不到这个事务被开启前的数据。

此外，set autocommit=0，会自动提交前一个事务，因此正确的作法是rollback; set autocommit=0;，完成之后再set autocommit=1;

begin;

insert into t_transaction values(1, 'abc');

insert into t_transaction values(2, 'xyz');

commit;

3. 隔离级别

对于两个并发执行的事务，保证其隔离性在实际操作中比看起来复杂得多，数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离性，避免数据不一致的问题。

所谓数据的不一致性，包括脏读、不可重复读、幻读。

SQL 标准定义了 4 种隔离级别，分别来解决各种可能出现的数据不一致的情况：

Isolation Level （隔离级别）	脏读（Dirty Read）	不可重复读（Non Repeatable Read）	幻读（Phantom Read）
Read Uncommitted	×	×	×
Read Committed	√	×	×
Repeatable read	√	√	×
Serializable	√	√	√

Read uncommitted（读未提交）：最低级别，任何不一致性都无法避免。
Read committed（读已提交）：可避免脏读的发生。
Repeatable read（可重复读）：可避免脏读、不可重复读的发生。
Serializable（串行化）：可避免脏读、不可重复读、幻读的发生。

MySQL 默认的事务隔离级别是 Repeatable read。

4. 死锁

死锁是指两个或多个事务相互持有和请求相同资源上的锁，产生了循环依赖。当多个事务试图以不同的顺序锁定资源时就会导致死锁；当多个事务锁定相同的资源时，也可能会发生死锁。

例如，下面两个事务中，每个事务都开始执行第一个查询，在处理过程中会更新一行数据，同时在主键索引和其他唯一索引中将该行锁定。然后，每个事务将在第二个查询中尝试更新第二行数据，却发现该行已经被锁定。这两个事务将永远等待对方完成，除非有其他因素介入解除死锁。

事务 1

START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2020-05-01';
UPDATE StockPrice SET close = 19.18 WHERE stock_id = 3 and date = '2020-05-02';
COMMIT;

事务 2

START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2020-05-02';
UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = '2020-05-01';
COMMIT;

一旦发生死锁，如果不回滚其中一个事务（部分或全部），就无法打破死锁。对于事务型的系统，这是无法避免的，所以应用程序在设计时必须考虑如何处理死锁。

InnoDB 目前处理死锁的方式是将持有最少行级排他锁的事务回滚（这是一种最容易回滚的近似算法）。

5. 其他

MySQL 不在服务器层管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中，混合使用多种存储引擎是不可靠的。

InnoDB 使用两阶段锁定协议。在事务执行期间，随时都可以获取锁，但锁只有在提交或回滚后才会释放，并且所有的锁会同时释放。InnoDB 会根据隔离级别自动处理所。

MySQL 的大多数事务型存储引擎使用的都不是简单的行级锁机制。它们会将行级锁和可以提高并发性能的 多版本并发控制（MVCC） 技术结合使用。

可以认为 MVCC 是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。
MVCC 的工作原理是使用数据在某个时间点的快照来实现的。
MVCC 仅适用于 RPEATABLE READ 和 READ COMMITTED 隔离级别。

事务