MySQL(七)：MySQL的事务

事务编程

事务是数据库区别于文件系统的重要特性之一，在文件系统中，如果用户正在写文件，但是如果操作系统突然崩溃了，这个文件就很有可能被破坏了，当然，有一些机制可以把文件恢复到某个时间点，不过，如果需要保证多个文件同步，这些文件系统可能就显得无能为力了。例如，当你更新两个文件时，更新完一个文件后，在更新完第二个文件之前系统重启了，你就会得到两个不同步的文件（一个更新完，另一个没有更新成功）。

事务概述

事务可由一条简单的SQL组成，也可以由一组复杂的SQL语句组成，事务是要么访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作，要么都执行修改，要么都不执行，这就是事务的目的，也是事务模型区别于文件系统的重要特性之一。

从理论上说，事务有着极其严格的定义，它必须同时满足4个特性，即通常所说事务的ACID特性。值得注意的是，虽然理论上定义了严格的事务要求，但是数据库厂商出于各种目的并没有严格满足事务的ACID标准。例如，对于MySQL的NDB CLUSTER引擎来说，虽然支持事务，但是不满足D的要求，即持久性的要求。对于Oracle数据库来说，默认的隔离级别为READ COMMITTED，不满足I的要求，即隔离性的要求。虽然在大多数的情况下，这并不会导致严重的结果，甚至可能会带来性能的提升，但是用户首先需要了解严谨的事务标准，对于INNODB存储引擎而言，其默认的事务隔离级别为READ REPEATABLE（RepeatAble），完全遵循和满足事务的ACID特性。

• A（atomicity）-> 原子性，在计算机系统中，每个人都将原子性视为理所当然的。原子性指整个数据库事务是不可分割的工作单位，只有使事务中所有的数据库操作都执行成功，整个事务的执行才算成功，也就是说事务中的任何一个SQL语句执行失败，那么前面已经执行成功的SQL语句必须进行撤销，数据库状态应该退回到执行事务前的状态（比如提交事务时候，突然断电，那么整个事务都会失败，即什么都没有改变）。
• C（consistency）-> 一致性。一致性指事务将数据库从一种状态转变为另一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束并没有被破坏。举个栗子，在表中有一个字段为名字，它是一个唯一约束，即在表中姓名不能重复，如果一个事务对表进行了修改（但事务还没有提交，表那里真实数据还没有变），但是在事务提交或当事务操作发送回滚后，表中的姓名数据变得非唯一了，那么就将这个表的约束打破了，变为了另一种状态，也就打破了一致性。因此，事务是一致性的单位，如果事务中某个动作失败了，系统可以自动地撤销事务使其返回初始化的状态。
• I（isolation）->隔离性。隔离性还有其他的称呼，比如并发控制、可串行化、锁。事务的隔离性要求每个读写事务的对象与其他其他事务的操作对象能互相分离，即该事务在提交前对其他事务都是不可见的（即如果数据库这里同时提交了两个事务A和B，那么这两个事务都是互相隔离的，即A不知道B在干什么，B也不知道A在干什么，都是各干各的，互相不对彼此产生影响，如果他们操纵相同的数据时，他们都有自己完整的数据空间，此时的数据空间是还未被A和B进行修改的），这通常使用锁来实现，当前数据库系统中提供了一种粒度锁的策略，允许事务仅锁住一个实体对象的子集，以此来提高事务之间的并发度。
• D（durability），持久性。事务一旦提交，其结果就是永久性的，即使发生了各种严重的事故，比如说宕机等故障，数据库也能将数据恢复。需要注意的是，持久性只能从事务本身的角度来保证结果的永久性，如事务提交后，所有的变化都是永久的，即使当数据库由于崩溃而需要恢复时，也能保证恢复后提交的数据都不会丢失。但如果不是数据库本身的问题，而是一些外部原因，比如机器损坏，那么可能所有的数据都会丢失。

事务的分类

从理论的角度来说，可以把事务分为以下几种类型

• 扁平事务

• 带有保存点的扁平事务

• 链事务

• 嵌套事务

• 分布式事务

扁平事务

扁平事务是事务类型中最简单的一种，而在实际生产环境中，这可能是使用最为频繁的事务，在扁平事务中，所有操作都处于同一层次，其由BEGIN WORK开始，由COMMIT WORK或ROLLBACK WORK结束，处于之间的操作是原子的，要么都执行，要么都失败然后进行回滚。因此扁平事务是应用程序成为原子操作的基本组成模块。

上面给出了扁平事务的三种情况，同时也给出了在一个典型的事务处理应用中，每个结果大概占用的百分比，扁平事务虽然简单，但是在实际生产环境中使用最为频繁。因为其简单频繁，所以几乎每个数据库都支持扁平事务。

扁平事务的主要限制是不能提交或回滚事务的某一部分，或分几个步骤提交。也就是提交要全部SQL进行提交，发生错误进行回滚的话，状态就要变为一开始的状态，也就是说，无法实现有计划的回滚操作，因为整个事务会被终止掉。

带有保存点的扁平事务

普通的扁平事务，是无法实现有计划的回滚操作的，因此有了带保存点的扁平事务，支持有计划的回滚操作，不需要终止整个事务，带保存点的扁平事务，除了支持扁平事务的功能外，还允许在事务执行过程中回滚到同一事务中较早的一个状态，这是因为可能某些事务在执行过程中出现的错误并不是所有的操作都无效，或者所有操作都失误，因为一个小失误而放弃整个事务的话，开销是挺大的，也不合理。带保存点的扁平事务的保存点用来通知系统应该记住事务当前的状态（这个状态不一定是初始状态），以便发生错误时，事务可以回到该状态。保存点可以不止一个，可以设置多个，比如到某个SQL块出现错误时，返回最近的保存点（或者自定义回滚点也行）。

链事务

链事务可以视为保存点模式的一个变种，带有保存点的扁平事务，如果当系统发生崩溃时，所有的保存点都会消失，因为其保存点是易失的，而非持久的，这意味着想要恢复保存点（SAVE POINT()）时，要事务走到这步才会生效，所以就要事务从头开始重新执行，而不能直接从下一个最近的保存点开始执行（因为保存点都失效了）

链事务的思想是：将一个大事务分成几个小事务，当一个小事务执行完后，将释放不需要的数据对象，将必要处理的上下文传递给下一个要开始的小事务，注意，这里拆分出的所有小事务都要整体视为一个原子，也就是要保持原子性，必须要上一个小事务完成才可以执行下一个小事务，也就意味着下一个小事务要看到上一个小事务的结果，相当于全部小事务在一个事务中执行。

链事务与带有保存点的扁平事务不同的是，带有保存点的扁平事务能回滚到任意正确的保存点，而链事务中的回滚仅限于当前事务，即不能回滚到之前执行完的事务，也就是只能恢复到最近一个保存点，对于锁的原理（实现隔离性），两者也不相同。链事务在COMMIT后即释放了当前事务所持有的锁，即每个小事务完成后也会进行释放锁，也就是前面所说的释放掉不用的资源，而带有保存点的扁平事务不会影响至今为止所持有的锁（不会释放资源）。

嵌套事务

嵌套事务是一个层次结构框架（有点像封装对象一样），有一个顶层事务控制着各个层次的事务，顶层事务之下嵌套的事务被称为子事务，其控制着每一个局部的变换。

下面给出Moss对嵌套事务的定义：

• 嵌套事务可以看成是由若干事务组成的一棵树，子树可以是嵌套事务也可以是扁平事务
• 处在叶子结点的是扁平事务，内部结点是嵌套事务，但是每个叶结点到根的距离可以是可以不同的，也就可以不是平衡树
• 位于根节点的事务称为顶层事务，其他事务称为子事务，子事务的前驱是父事务，父事务的下一层为儿子事务
• 子事务既可以提交也可以进行回滚，但是它的提交并不会立马生效，除非由其父事务进行提交（注意这里父事务也是上一层的子事务，归根到底要顶层事务进行提交才可以提交），因此可以推论出，任何子事务都在顶层事务提交后才能真正提交
• 树中的任意一个事务的回滚会引起它的所有子事务一同进行回滚。故子事务只有A、C、I特性，不具有D特性，也就是有自己的原子性，一致性，隔离性，但并不能持久，因为会被父事务影响。

分布式事务

分布式事务通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。

假设一个用户在ATM机前进行银行的转账操作，要从招商银行的储蓄卡转账10000元到工商银行的储蓄卡（整个转账相当于一个事务）。在这种情况下，可以将ATM机视为节点A，然后招商银行的后台数据库视为节点B，还有工商银行的后台数据库视为节点C，整个业务可以视为以下几个步骤

1. 节点A发出转账命令
2. 节点B执行从储蓄卡中将余额值减去10000
3. 节点C执行从储蓄卡中将余额值增加10000
4. 节点A通知用户操作完成或者失败

要完成这个事务就要使用分布式事务，因为分成了多个节点，不能只通过调用一个数据库就完成事务，这个过程需要访问网络中多个节点的数据库，而在每个数据库中执行的实务操作又都是扁平的。

对于分布式事务，同样需要满足ACID特性，要么都发生，要么都失效，对于上述栗子，2、3步骤任何一个失败都会发生严重的事情，所以一旦出现失误都要进行回滚，若不是这样，后果会很严重的。

对于MySQL数据库（InnoDB存储引擎）来说，其支持扁平事务、带有保存点的扁平事务、链事务、分布式事务。对于嵌套事务，MySQL数据库并不是原生的，因此对于有并行事务需求的用户来说MySQL是有点无能为力。

事务控制语句

在MySQL命令行的默认设置下，事务都是自动提交的（auto commit），即执行完事务里面的SQL后就会马上执行COMMIT操作，因此要显示地开启（即自己主动开启）一个事务必须使用命令BEGIN和START TRANSACTION，或者执行命令SET AUTOCOMMIT = 0，以禁用当前会话的自动提交。

下面介绍一些事务控制语句

START TRANSACTION 或者 BEGIN //显示地开启一个事务
COMMIT 或者 COMIT WORK //提交事务
ROLLBACK 或者 ROLLBACK WORK //进行回滚
SAVEPOINT identifier //创建一个保存点，一个事务可以创建多个保存点
RELEASE SAVEPOINT identifier //删除，释放一个保存点，如果指定保存点不在会抛出异常
ROLLBACK TO [SAVEPOINT] identifer //这命令与SAVEPOINT语句一起使用，将事务回滚到标记点，而不是回滚到此标记点之前的任何工作
SET TRANSACTION //这个语句用来设置业务隔离级别，InnoDB存储提供4种隔离级别

START TRANSACTION和BEGIN语句都可以在MySQL命令行下显式地开启一个事务，但是在存储过程中，MySQL数据库的分析器会自动将BEGIN识别为BEGIN … END，因此在存储过程中（已经有BEGIN开头了）只能用START TRANSACTION来进行开启一个事务。

COMMIT和COMMIT WORK语句基本上是一致的，都是用来提交事务，不同之处在于COMMIT WORK用来控制事务结束后的行为是CHAIN还是RELEASE的，如果是CHAIN方式，那么事务就变成了链事务（继续执行下一个小事务），如果是RELEASE，那就是会断开连接，事务完全结束，与MySQL的连接断开，要进行下次连接才能访问数据库，用户可以通过改变参数completion_type来进行控制，这个参数默认是0的，表示没有任何操作，在这种设置下，COMMIT和COMMIT WORK是等价的，当completion_type设置为1时，COMMIT WORK跟COMMIT就不一样了，COMMIT WORK就等同于COMMIT AND CHAIN，表示马上自动开启一个相同隔离级别的事务（链事务的下一个小事务）

//创建表
CREATE TABLE haha(
	a INT PRIMARY KEY
)ENGINE=INNODB,CHARSET=utf8;

//修改completion_type=1
SET @@completion_type = 1;

//执行一个事务
BEGIN;
INSERT INTO haha SELECT 1;
COMMIT WORK; //注意这里是COMMIT WORK喔

//插入2
INSERT INTO haha SELECT 2;
//继续插入2，因为2已经插入，而且是主键
INSERT INTO haha SELECT 2;

//进行回滚
ROLLBACK;

结果如上

在这个实验中，我们首先将completion_type设为1（此时commit work已经变成commit and chain了，链事务），然后我们执行一个事务插入1，这个事务没有任何问题，注意，下一步我们没有显示使用BEGIN或者START TRANSACTION来显示地开启事务（因为使用了COMMIT WORK进行提交，那么下一步还是一个事务，所以不需要进行开启），下一步我们插入2，显示成功，再继续插入2，显示失败，然后进行回滚，回滚到最初的正常状态，也就是完成了第一个事务，但第二个事务失败了（不满足原子性），回滚到了第一个事务结束时的状态。

现在completion_type为2时，COMMIT WORK等同于COMMIT AND RELEASE，即当事务提交后会自动断开与服务器的连接

这个使用mysql图形化是无法查出的，使用控制台才行

SET @@completion_type = 2;

BEGIN;
INSERT INTO haha SELECT 4;
COMMIT WORK;

SELECT @@version;

隐式提交的SQL语句

以下SQL会产生一个隐式的提交操作，即执行完这些语句后，会有一个隐式的COMMIT操作

DDL语句 //DDL是指数据库的架构，就是比如表，储存过程，游标这些
ALTER TABLE ..
UPGRADE DATA DIRECTORY NAME..
ALTER EVENT

用来隐式地修改Mysql架构的操作
CREATE USER
DROP USER

管理语句
ANALYZE TABLE
CACHE INDEX

事务的隔离级别

ANSL SQL标准定义的四个隔离级别

• READ UNCOMMITTED(read uncommitted)
• READ COMMITTED(read committed)
• REPEATABLE READ(repeatabel read)
• SERIALIZABLE(serializable)

隔离是针对并发事务情况的，下面来介绍一下并发事务会发生的三种情况，三种情况分别为脏读、不可重复读、幻读。

0、更新丢失：A事务进行读取数据，此时B事务对数据进行了更新，A事务再次更新了相同的行数据

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作（更新的数据没有了），那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果并不一致（有未更新的数据和更新后的数据，注意这里只是更新操作，不包括插入和删除，插入和删除导致的情况是下面的幻读）。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读（就是一个事务对数据的类型进行了更改，但另一个表不知道，继续插入原来的类型，注意，此时第一个事务进行更改是还没结束的，当更改完了之后，因为没有得到最新的插入数据，到后面更改结束时发现有一条数据没有进行更改）。

幻读也可以这样理解，比如B事务正在统计某表的数据总量，突然A事务对该表进行插入，B表此时统计操作还没有结束，继续进行统计（会将A事务插入的数据也统计进来），统计到后面突然发现数据总量多了（由于A表的插入），就好像产生了幻觉。

现在来介绍一下标准SQL的这4种事务隔离机制

（1）读未提交（Read Uncommitted）：只处理更新丢失。如果一个事务已经开始写数据，则不允许其他事务同时进行写操作，但允许其他事务读此行数据。可通过“排他写锁”实现。（解决了幻读）

（2）读提交（Read Committed）：处理更新丢失、脏读问题。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问改行（也就是已经提交的写事务，才可以被其他事务访问，未提交的写事务不可以被其他事务访问，这就避免了脏读）。可通过“瞬间共享读锁”和“排他写锁”实现。

（3）可重复读取（Repeatable Read）：处理更新丢失、脏读和不可重复读取。读取数据的事务将会禁止写事务，但允许读事务，写事务则禁止任何其他事务（读事务的时候写事务停止，写事务的时候所有事务停止包括读事务，具体来说就是读事务的时候，不可以提交写事务，写事务的时候不可以进行任何操作）。可通过“共享读锁”和“排他写锁”实现。

（4）序列化（Serializable）：，解决了更新丢失，脏读、不可重复读和幻读，提供严格的事务隔离。要求失去序列化执行，事务只能一个接一个地执行，不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到（最严格的事务隔离机制，事务直接不允许并发执行，必须一个一个去提交，一个提交完了才能到下一个）。

现在说说MySQL对这4种隔离机制的定义（每个数据库都不同）

读未提交：这种隔离级别,其他事务会读到当前所有事务未提交的数据,从而造成脏读,幻读,不可重复读

读已提交：这种隔离级别下,其他事务只能读到当前所有事务已经提交的数据。解决了脏读,但是会造成不可重复读和幻读。

可重复读：这种隔离级别下,其他事务不能进行update操作,但是可以进行add操作,解决了不可重复读,但是造成了幻读。

一般来说，可重复读取（Repeatable Read）已经可以解决大部分问题了，使用序列化会降低执行效率，不过能解决幻读问题。

InnoDB存储引擎默认支持的隔离级别是REPEATABLE READ,但是于标准的SQL不同的是，InnoDB存储引擎再REPEATABLE READ事务隔离级别下，使用Next-Key Lock的锁算法，避免了幻读的产生。也就是说InnoDB引擎在默认的REPEATABLE READ事务隔离级别下已经能完全保证事务的隔离性要求。

隔离级别越低，事务请求的锁就会越少或保持锁的时间就会越短，占用的资源就会减少，这也是很多数据库默认的隔离界别都是REPEATABLE READ的原因。

分布式事务编程

InnoDB存储引擎提供了对于XA事务（一种分布式事务）的支持，并通过XA事务来支持分布式事务的实现。

分布式事务指的是允许多个独立的事务资源（Transactional resources）参与到一个全局的事务中。

事务资源通常是关系型数据库，全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，所以这对于事务原有的ACID要求又有了提高，另外，在使用分布式事务时，InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE（serialzable），即序列化模式，必须一个事务完成才可以执行下一个事务。

分布式项目一般出现在银行的转账系统上，比如某A住在上海，给北京的某B进行转账，银行在上海和北京使用的服务器肯定不是同一台，那么就要使用分布式事务，来让两个服务器的事务都归属于全局事务中，因为如果两边的事务不能一起回滚或者都成功，那么都会发生严重的后果。

下面介绍一下XA事务

XA事务是分布式事务中的一种，它允许不同数据库之间的分布式项目，比如一台是MySQL，而另一台是SQL Server的，前提是这两个数据库都支持XA事务。

XA事务是由一个或多个资源管理器、一个事务管理器以及一个应用程序组成的。

• 资源管理器：提供访问事务资源的方法，通常一个数据库就是一个资源管理器（分布式可能有多个数据库，所以可能有多个资源管理器）
• 事务管理器：协调参与全局事务中的各个事务，需要和参与到全局事务中的所有资源管理器进行通信（事务管理器通知事务是ROLLBACK还是COMMIT操作，事务是储存在资源管理器中的，所以必须要和各个资源管理器进行通信）
• 应用程序：定义事务的边界，指定全局事务中的操作（INSERT UPDATE DALETE SELECT）。

分布式事务使用两段式提交的方式，

• 第一段：所有参与全局事务的节点都开始准备（Prepare），告诉事务管理器他们准备好提交了（资源管理器准备好可以开始执行事务）。
• 第二段：由事务管理器通知所有的资源管理器需要执行的操作（COMMIT或者ROLLBACK），假如这里通知所有资源管理器都是COMMIT操作，如果出现任何一个节点显示COMMIT失败，则事务管理器会通知所有节点进行ROLLBACK

从上面的方式可以看到与本地事务的不同地方是，多了一个Prepare，即确定节点可以开始执行事务了的操作，收到所有节点的同意信息后，再进行COMMIT或者ROLLBACK操作。

不好的事务编程习惯

在循环中提交

CREATE PROCEDURE load1(count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80) DEFAULT REPEAT('a',80);
WHILE s <= count DO
START TRANSACTION;
INSERT INTO t1 SELECT NULL,c;
COMMIT;
SET s = s+1;
END WHILE;
END;

注意这里的START TRANSACTION写在了循环里，即循环提交事务，这是不好的习惯，例如，当我们要插入10000条记录时，但是在插入5000多时，发生了错误，这时前5000条记录已经在数据库中了，这要怎么处理呢？另一个问题就是性能问题，事务是要用锁的，锁是要占用资源的，事务越多占用的资源就越多。

下面进行优化这个存储过程

CREATE PROCEDURE load2(count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80) DEFAULT REPEAT('a',80);
START TRANSACTION
WHILE s <= count DO
INSERT INTO t1 SELECT NULL,c;
COMMIT;
SET s = s+1;
END WHILE;
END;

这里将START TRANSACTION写在了循环外，也就是只有一个事务，在事务中进行循环。

使用自动提交

MySQL数据库默认的提交方式就是自动提交事务，可以使用下面的语句来改变自动提交的方式

SET autocommit=0;

也可以通过显示开启事务来阻止自动提交，即使用下面的SQL开启事务

BEGIN
START TRANSACTION;

当使用了显示开启事务时，在默认设置下（即completion_type=0，COMMIT WORK=COMMIT），MySQL会自动执行SET autocommit = 0的命令，当执行完COMMIT或者ROLLBACK后，也就是结束事务时，会将autocommit参数改回原来的0，即执行SET autocommit = 0;

使用自动回滚

长事务

长事务，顾名思义，就是需要执行时间较长的事务，例如银行系统的数据库，没过一个阶段可能需要更新对应账户的利息，如果对应账号的数量非常大，比如1亿用户的表。。

这时，这个事务可能需要非常长的时间来完成，由于消耗时间长就导致了下面一个问题，如果在执行这个事务的时候，突然发生错误，比如超时，要进行回滚，重新开始事务，这会占用极大的资源，这种代价是不可接受的，因为回滚也是需要资源的

因此，对于长事务的问题，有时可以通过将其转化为小批量的事务来进行处理，当事务发生错误时，只需要回滚一部分数据（回滚当前小事务的数据），然后接着上次的已完成的事务继续进行。