MySQL 事务与事务处理

gaunthan Posted on Mar 6 2017

? MySQL ? ? Database ?

> **事务处理**(transaction processing)可以用来维护数据库的完整性，它保证成批的数据库操作要么完全执行，要么完全不执行。

## 概述
在关系数据库中，事务可以是一条SQL语句、一组SQL语句或整个程序。注意事务和程序是两个概念，一般来讲，一个程序中包括多个事务。

## 事务的特性
事务具有四个特性：**原子性**(Atomicity)、**一致性**(Consistency)、**隔离性**(Isolation)和**持续性**(Durablity)。这四个特性简称为**ACID**特性(ACID properties)：

特性|解释
--|--
原子性|事务是数据库的逻辑工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性|事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。一致性显然是和原子性密切相关的。
隔离性|一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
持续性|持续性也称为**永久性**(Permanence)，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障都不应该对其执行结果有任何影响。

### 隔离级别
隔离性比想象的要复杂。在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

隔离级别|说明
--|--
未提交读(READ UNCOMMITTED)|在未提交读级别，事务中的修改即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为**脏读**(Dirty Read)。这个级别会导致很多问题，从性能上来说，未提交读不会比其他的级别好太多，但却缺乏其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用。
提交读(READ COMMITTED)|大多数数据库系统的默认隔离级别都是提交读，但MySQL不是。提交读满足上文提到的隔离性的简单定义：一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时也叫**不可重复读**(nonrepeatable read)，因为两次执行同样的查询，可能会得到不一样的结果。
可重复读(REPEATABLE READ)|可重复读解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个**幻读**(Phantom Read)的问题：当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生**幻行**(Phantom Row)。InnoDB和XtraDB存储引擎通过**多版本并发控制**(MVCC, Multiversion Concurrency Control)解决了幻读的问题。
可串行化(SERIALIZABLE)|可串行化是最高的隔离级。它通过强制事务串行执行，避免了前面说的幻读的问题。简单来说，它会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑使用该级别。

下图是对这些隔离级别的一个总结：
![](https://leanote.com/api/file/getImage?fileId=58ca2a3cab64417e39006645)

## 事务日志
事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。

事务日志持久以后，内存中被修改的数据可以在后台慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为**预写式日志**(Write-Ahead Logging)。修改数据需要写两次磁盘，第一次是写日志，第二次是写数据。

如果数据的修改已经记录到事务日志并持久化，但数据本身还可以写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具体的恢复方式则视存储引擎而定。

## 事务处理相关术语
在使用事务和事务处理时，有几个术语是我们需要掌握的：

## MySQL中的事务处理
MySQL提供了两种事务型的存储引擎：*InnoDB* 和 *NBD Cluster*。另外还有一些第三方存储引擎也支持事务，比较知名的包括 *XtraDB* 和 *PBXT*。

为了让所操作的表支持事务，可以在创建该表时指定存储引擎：
```sql
CREATE TABLE MyTable
(
    ...
)ENGINE=InnoDB;
```

如果表已经创建，则可以使用`ALTER`语句将表的引擎更改为另一个：
```sql
ALTER TABLE MyTable
ENGINE = InnoDB;
```

注意，这种方式可能需要执行很长时间。MySQL会按行把数据从原表复制到一张新的表，在复制期间可能会消耗系统所有的I/O能力，同时原表上会加上读锁。

转换表的存储引擎，将会失去与原引擎相关的所有特性。例如，如果将一张InnoDB表转换为MyISAM，然后再转换为InnoDB，原InnoDB表上所有的外键将丢失。

## 控制事务处理
管理事务处理的关键在于将SQL语句分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。MySQL使用下面的语句来标识事务的开始：

START TRANSACTION;
    
### 使用ROLLBACK
MySQL的`ROLLBACK`命令用来回退（撤销）MySQL语句，下面是一个例子，其中Foo是一个已经被创建的表：
![](https://leanote.com/api/file/getImage?fileId=58bd62e3ab64411ece006903)

我们先是显示Foo的所有内容，然后清空整个表，然后再次显示该表。可以看到表已经为空。之后执行`ROLLBACK`操作使得从标识点到该语句前的所有的操作都被撤销，因此表格内容恢复到删除前的内容。

注意，被撤销的语句不含`SELECT`语句，因为这样做没什么意义。还需要留意的是，`CREATE`或`DROP`操作都不能被撤销。实际上，事务处理是用来管理`INSERT`、`UPDATE`和`DELETE`语句的，因此有这些要求也没什么好奇怪的。

### 使用COMMIT
一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的**隐含提交**(implicit commit)，即提交（写或保存）操作是自动进行的。但是，在进行事务处理块中，提交不会隐含地进行。为进行明确的提交，需要使用`COMMIT`语句：
![](https://leanote.com/api/file/getImage?fileId=58bd62e3ab64411ece006902)

`COMMIT`语句仅在不出错时写出更改。

你可能注意到我们没有使用任何东西来标识事务的结束，原因是**隐含事务关闭**在起作用：当`COMMIT`或`ROLLBACK`语句执行后，事务会自动关闭（将来的更改会隐含提交）。

### 使用保留点
简单的`ROLLBACK`和`COMMIT`语句就可以写入或撤销整个事务处理。但是，只是对简单的事务处理才能这样做。更复杂的事务处理可能需要部分提交或回退。

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，就可以回退到某个占位符。这些占位符称为**保留点**。为了创建占位符，可以使用`SAVEPOINT`语句：

SAVEPOINT delete1;  -- 在此处创建名称为delete1的保留点
    
每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。为了回退到特定点，需要在`ROLLBACK`语句中指定它：

ROLLBACK TO delete1;
    
保留点会在事务处理完成（执行一条`ROLLBACK`或`COMMIT`）后自动释放。自 *MySQL 5* 以来，也可以使用`RELEASE SAVEPOINT`语句明确地释放保留点：

RELEASE SAVEPOINT delete1;
    
你可以在MySQL中设置任意多的保留点。保留点越多，你就越能按自己的意愿灵活地进行回退。但这种跳转性的东西就跟C语言中的`goto`语句一样，乱用的话可能会非常影响可读性和可维护性。

## 默认提交行为
默认的MySQL行为是**自动提交**(AUTOCOMMIT)所有更改。也就是说，如果不是显式地开始一个事务，则每个操作都被当成一个事务执行提交操作，所做的更改立即生效。为指示MySQL不自动提交更改，需要使用以下语句：

SET AUTOCOMMIT=0;   -- 清零以关闭自动提交行为
    
**注意，AUTOCOMMIT标志是针对每个SQL连接而不是针对服务器的。**

可以使用`SHOW VARIABLES LIKE 'AUTOCOMMIT'`显示当前的默认提交行为。

修改AUTOCOMMIT对于非事务的表，比如MyISAM或者内存表，不会有任何影响。对这类表来说，没有COMMIT或者ROLLBACK的概念，也可以说是相当于一直处于AUTOCOMMIT启用的模式。

## References

- 福塔刘晓霞, 钟鸣. MySQL必知必会 : MySQL crash course[M]. 人民邮电出版社, 2009.
- Baron Schwartz … [等, 王小东, 李军. 高性能MySQL[M]. 电子工业出版社, 2010.