MySQL数据库从入门到精通—数据库事务

事务的管理

事务处理机制在程序开发中有着非常重要的作用，可以使整个系统更安全。接下来将针对事务的概念和相关管理操作进行详细讲解。

事务的概念和使用

在现实生活中，转账是很常见的操作，这实际上就是数据库中两个账户间的数据操作。例如，账户A给账户B转账100元，就是账户A的金额减去100，账户B的金额加上100，这个过程需要使用两条SQL语句完成操作，但是，若其中一条SQL语句出现异常没有执行，则会导致两个账户的金额不同步，数据就会出现错误。

为了防止上述情况的发生，在MySQL中引入了事务。事务是指数据库中的一个操作序列，它由一条或多条SQL语句所组成，这些SQL语句不可分割，只有当事务中的所有SQL语句都被成功执行后，整个事务引发的操作才会被更新到数据库，如果有至少一条语句执行失败，所有操作都将会被取消。

在使用事务前首先要开启事务，SQL语句如下。

以上语句用于开启事务，事务开启后就可以执行SQL语句。在SQL语句执行完成后需要提交事务，SQL语句如下。

以上语句用于提交事务，在MySQL中SQL语句是默认自动提交的，而事务中的操作语句都需要使用COMMIT语句手动提交，提交完成后事务才会生效。如果不想提交事务，ROLLBACK语句可以回滚事务，SQL语句如下。

以上语句用于事务回滚，但该语句只能回滚未提交的事务操作，不能回滚已提交的事务操作。

接下来通过具体案例演示转账的事务操作，首先需要创建一个账户表account。

在account表创建完成后向表中插入数据。

以上执行结果证明数据插入完成。然后查看account表中的数据。

从以上执行结果可以看出，总共有3个账户，存款金额都为1000。接下来通过具体案例演示如何实现转账功能。

通过SHOW VARIABLES语句验证自动提交是否关闭。

从以上执行结果可以看出，MySQL的自动提交事务已关闭。

然后通过事务操作实现账户A给账户B转账100元。

以上执行结果证明转账成功，首先使用START TRANSACTION语句开启事务，然后执行了更新操作，将账户A减少100元，账户B增加100元，最后使用COMMIT语句提交事务。

此时查看表中的数据进行验证。

从以上执行结果可以看出，通过事务操作实现了转账。值得注意的是，如果在执行转账操作的过程中数据库出现故障，为了保证事务的同步性，则事务不会提交。

接着通过事务操作实现账户A给账户C转账100元。当账户A的数据操作完成后关闭数据库客户端，模拟数据库宕机。

从以上执行结果可以看出，事务开启后账户A减去了100元。然后查看表中数据。

从以上执行结果可以看出，账户A的余额从900元变为了800元，账户A的转账操作完成。

此时关闭MySQL的客户端接着重新打开，再次查看account表中的数据。

从以上执行结果可以看出，账户A的余额恢复到900元。因为利用事务的转账操作没有全部完成，出现了错误，所以为了保证数据的同步性，没有提交前的数据操作都被回退，这就是事务的作用。

事务的回滚

在操作一个事务时，如果发现某些操作是不合理的，只要事务还没有提交，就可以通过ROLLBACK语句进行回滚。

通过事务操作实现账户B给账户C转账100元，当转账操作完成后使用ROLLBACK语句回滚转账操作。

首先通过事务操作实现账户B给账户C转账100元。

从以上执行结果可以看到，账户B给账户C转账100元。

然后查看表中数据。

从以上执行结果可以看出，账户B减少了100元，账户C增加了100元，转账操作完成，但此时没有进行事务提交，使用ROLLBACK语句可以回滚事务操作。

以上执行结果证明回滚事务操作成功。

然后查看表中数据。

从以上执行结果可以看出，账户B和账户C的金额又回到了转账操作之前，这是因为ROLLBACK语句回滚了事务操作。

事务的属性

事务有很严格的定义，必须同时满足4个属性，即原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。这4个属性通常称为ACID特性，具体含义如下。

l 原子性：事务作为一个整体被执行，包含在其中对数据库的操作都执行或都不执行。

l 一致性：事务应确保数据库的状态，从一个一致状态转变为另一个一致状态，一致状态的含义是数据库中的数据应满足完整性约束。

l 隔离性：当多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

l 持久性：一个事务一旦提交，它对数据库的修改应该永久保存在数据库中。

以上是事务的4个属性的概念，为了便于大家理解，接下来以转账的例子来说明如何通过数据库事务保证数据的准确性和完整性，例如账户A和账户B的余额都是1000元，账户A给账户B转账100元，则需要6个步骤，具体如下。

（1） 从账户A中读取余额为1000。

（2） 账户A的余额减去100。

（3） 账户A的余额写入为900。

（4） 从账户B中读取余额为1000。

（5） 账户B的余额加上100。

（6） 账户B的余额写入为1100。

对应以上6个步骤理解事务的4个属性，具体如下。

l 原子性：保证1~6步都执行或都不执行。一旦在执行某一步骤的过程中出现问题，就需要执行回滚操作。例如执行到第5步时，账户B突然不可用（比如被注销），那么之前的所有操作都应该回滚到执行事务之前的状态。

l 一致性：在转账之前，账户A和B中共有1000+1000=2000元。在转账之后，账户A和B中共有900+1100=2000元。也就是说，在执行该事务操作之后，数据从一个状态改变为另外一个状态。

l 隔离性（Isolation）：在账户A向B转账的整个过程中，只要事务还没有提交，查询账户A和B时，两个账户中金额的数量都不会有变化。如果在账户A给B转账的同时，有另外一个事务执行了账户C给B转账的操作，那么当两个事务都结束时，账户B中的金额应该是账户A转给B的金额加上账户C转给B的金额，再加上账户B原有的金额。

l 持久性：一旦转账成功（事务提交），两个账户中的钱就会真正发生变化（会将数据写入数据库做持久化保存）。

另外需要注意的是，事务的原子性与一致性是密切相关的，原子性的破坏可能导致数据库的不一致，但数据的一致性问题并不都和原子性有关。例如转账的例子中，在第5步时，为账户B只加了50元，该过程是符合原子性的，但数据的一致性就出现了问题。因此，事务的原子性与一致性缺一不可。

事务的隔离级别

数据库是多线程并发访问的，其明显的特征是资源可以被多个用户共享，当相同的数据库资源被多个用户（多个事务）同时访问时，如果没有采取必要的隔离措施，就会导致各种并发问题，破坏数据的完整性，这就需要为事务设置隔离级别。在MySQL中，事务有4种隔离级别，具体如下。

l READ UNCOMMITTED（读未提交）：是事务中最低的隔离级别，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果，也被称为脏读，这是非常危险的，所以很少用于实际应用。

l READ COMMITTED（读已提交）：这是大多数数据库管理系统的默认隔离级别，它满足了隔离的简单定义，即一个事务只能看见已经提交事务所做的改变，该隔离级别可以避免脏读，但不能避免重复读和幻读的情况。重复读就是在事务内重复读取了其他线程已经提交的数据，但两次读取的结果不一致，原因是查询的过程中其他事务做了修改数据的操作，幻读是指在一个事务内两次查询中数据条数不一致，这是因为查询过程中，其他事务做了插入或删除操作。

l REPEATABLE READ（可重复读）：这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时会看到同样的数据，可以避免脏读和重复读的问题，但不能避免幻读的问题。

l SERIALIZABLE（可串行化）：事务中最高的隔离级别，它通过强制事务排序使之不可能相互冲突，从而解决幻读问题，实际上它是在每个读的数据行上加了共享锁。在这个隔离级别，可能导致大量的超时现象和锁竞争，所以很少用于实际应用。

以上列出了数据库事务的4个隔离级别，它们会产生不同的问题，如脏读、不可重复读、幻读和超时等。在MySQL中实现这4个隔离级别可能产生的问题如表11.1所示。

表11.1 隔离级别及问题

隔离级别	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读	×	×	√
可串行化	×	×	×

在表11.1中列出了每个隔离级别可能出现的问题。接下来将分别演示这些问题，在演示之前首先了解一下隔离级别的相关操作，查看当前会话隔离级别的SQL语句如下。

SELECT @@tx_isolation;

设置当前会话隔离级别的SQL语句如下。

SET SESSION TRANSACTION ISOLATION LEVEL

{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

在以上语法格式中，SESSION代表设置的是当前会话的隔离级别，LEVEL后面有4个可选参数，分别对应4个隔离级别。接下来将通过具体案例分别演示4个隔离级别可能出现的问题。

1.脏读

当事务的隔离级别为READ UNCOMMITTED（读未提交）时，可能出现脏读的问题，即一个事务读取了另一个事务未提交的数据。

在实际应用中应尽量避免脏读的问题，即尽量不要将数据库的隔离级别设置为READ UNCOMMITTED（读未提交）。

接下来演示脏读的问题，打开两个MySQL客户端（客户端A和客户端B）模拟两个线程操作数据。首先查看客户端A的隔离级别。

从以上执行结果可以看出，客户端A的隔离级别为REPEATABLE-READ（可重复读）。

然后查询客户端B的隔离级别。

从以上执行结果可以看出，客户端B的隔离级别同样为REPEATABLE READ（可重复读），这是因为MySQL的默认隔离级别为REPEATABLE READ（可重复读）。接下来不断改变客户端A的隔离级别，在客户端B修改数据，演示各个隔离级别出现的问题。

首先将客户端A的隔离级别设置为READ UNCOMMITTED（读未提交）。

以上执行结果证明客户端A的隔离级别设置为READ UNCOMMITTED（读未提交）。在客户端A中查询account表中的数据。

接着在客户端B中进行事务操作，开启事务后，账户A给账户C转账100元，但不提交事务。

以上执行结果证明账户A成功给账户C转账100元，然后通过客户端A查看account表中的数据。

从以上执行结果可以看出，在客户端A中查询account表中的数据，账户A已经给账户C转账了100元，但此时客户端B中的事务还没有提交，客户端A读取到了客户端B还未提交事务修改的数据，这就是脏读的问题，这是非常危险的，因为此时客户端B是可以回滚事务的。将客户端B的事务回滚。

以上执行结果证明客户端B成功回滚了事务。然后通过客户端A再次查询account表中的数据。

从以上执行结果可以看到，客户端A又查询到了客户端B事务回滚后的数据。在实际应用中应尽量避免脏读的问题，即尽量不要将数据库的隔离级别设置为READ UNCOMMITTED（读未提交）。

2.不可重复读

当事务的隔离级别为READ COMMITTED（读已提交）时，可能出现不可重复读的问题，即事务中两次查询的结果不一致，这是因为在查询过程中其他事务做了更新操作。

将客户端A的隔离级别设置为READ COMMITTED （读已提交）。

以上执行结果证明客户端A的隔离级别设置成了READ COMMITTED （读已提交）。

然后在客户端A中开启一个事务，查询account表中的数据。

接着在客户端B中进行事务操作，开启事务后，账户A给账户C转账100元，提交事务。

从以上执行结果可以看出，客户端B中的事务操作完成，账户A给账户C转账100元，然后在客户端A未完成的事务中查询account表中的数据。

从以上执行结果可以看出，客户端A查询出了客户端B修改后的数据。也就是说，客户端A在同一个事务中查询同一个表，两次的查询结果不一致，这就是不可重复读的问题。不过在大多数场景中，这种问题是可以接受的，因此大部分数据库管理系统使用READ COMMITTED（读已提交）隔离级别，例如Oracle数据库管理系统。

3.幻读

当事务的隔离级别为REPEATABLE READ（可重复读）时，可能出现幻读的问题，即在一个事务内两次查询的数据条数不一致，与不可重复读的问题类似，这都是因为在查询过程中其他事务做了更新操作。

将客户端A的隔离级别设置为REPEATABLE READ （可重复读）。

以上执行结果证明客户端A的隔离级别设置为了REPEATABLE READ （可重复读）。

然后在客户端A中开启一个事务，查询account表中的数据。

接着在客户端B中进行更新操作，添加一个账户D，余额为500元。

从以上执行结果可以看出，客户端B中的添加操作完成。

然后在客户端A未完成的事务中查询account表中的数据。

从以上执行结果可以看出，在客户端A中查询account表中的数据并没有出现幻读的问题，这是因为MySQL的存储引擎通过多版本并发控制机制解决了幻读的问题。

4.可串行化

当事务的隔离级别为SERIALIZABLE（可串行化）时事务的隔离级别最高，在每一行读取的数据上都会加锁，不会出现相互冲突，但这样会导致资源占用过多，出现大量的超时现象。

将客户端A的隔离级别设置为SERIALIZABLE（可串行化)。

以上执行结果证明客户端A的隔离级别设置为了SERIALIZABLE （可串行化）。

然后在客户端A中开启一个事务，查询account表中的数据。

接着在客户端B中进行更新操作，添加一个账户E，余额为800元。

此时客户端B中的添加操作卡住不动，这是因为客户端A的事务隔离级别为SERIALIZABLE（可串行化），客户端A中的事务还没有提交，所以客户端B必须等待客户端A中的事务提交后才可以进行添加数据的操作。当客户端A长时间没有提交事务时，客户端B会报错。

从以上报错信息可以看出，因为操作超时，导致数据添加失败，这就是隔离级别SERIALIZABLE （可串行化）可能出现的超时问题，这是比较严重的性能问题。在实际应用中，事务的隔离级别一般不会设置为SERIALIZABLE （可串行化）。

分布式事务

MySQL从5.0.3版本开始支持分布式事务（XA事务），目前分布式事务只支持InnoDB存储引擎，一个分布式事务会涉及多个行动，这些行动本身是事务性的，所有行动都必须一起成功完成，或者一起被回滚，接下来将详细讲解MySQL的分布式事务。

分布式事务的管理

在MySQL中，使用分布式事务的应用程序涉及一个或多个资源管理器和一个事务管理器。

l 资源管理器（resource manager）：用于提供通向事务资源的途径，数据库服务器是一种资源管理器。该管理器必须可以提交或回滚由RM管理的事务。例如，多台MySQL数据库作为多台资源管理器，或者多台MySQL和多台Oracle服务器作为资源管理器。

l 事务管理器（transaction manager）：用于协调分布式事务的一部分事务。TM与管理每个事务的RM进行通信。在一个分布式事务中，每个单个事务均是分布式事务的“分支事务”，分布式事务和各分支通过一种命名方法进行标识。

在MySQL执行分布式事务时，MySQL服务器相当于一个用来管理分布式事务的资源管理器，与MySQL服务器连接的客户端相当于事务管理器。

如果要执行一个分布式事务，必须知道分布式事务涉及的资源管理器，并把每个资源管理器的事务执行到事务可以被提交或回滚时，根据每个资源管理器报告的执行情况，所有分支事务必须作为一个原子性操作全部提交或回滚。

用于执行分布式事务的过程使用两个阶段提交，发生时间是分布式事务的各个分支需要进行的行动已经执行之后，两个阶段分别如下。

（1） 在第一阶段，TM告知所有RM进行PREPARE操作，即所有RM被告知即将要执行COMMIT操作，然后分支响应是否准备好进行COMMIT操作了。

（2） 在第二阶段，TM告知所有RM进行COMMIT或者回滚。如果在PREPARE时有任意一个RM响应无法进行COMMIT操作，那么所有的RM将被告知进行回滚操作，否则所有的RM将被告知进行COMMIT操作。

在某些情况下，如果一个分布式事务只有一个RM，那么使用第一阶段提交也可以，即该RM会被告知同时进行PREPARE和COMMIT操作。

分布式事务的语法和使用

MySQL中与分布式事务相关的SQL语句如下。

在以上SQL语句中，xid用于标识一个分布式事务，其组成如下。

在以上语法格式中，gtrid是必需的，为字符串类型，表示全局事务标识符；bqual是可选的，为字符串类型，默认是空串，表示分支限定符；formatID是可选的，默认值为1，用于标识由gtrid和bqual值使用的格式。

演示分布式事务将使用两台MySQL，分别为DB1和DB2。首先查看DB1是否支持分布式事务。

从以上执行结果可以看出，DB1支持分布式事务。接着查看DB2是否支持分布式事务。

从以上执行结果可以看出，DB2同样支持分布式事务。

在数据库DB1中启动一个分布式事务的一个分支事务，xid的gtrid为test，bqual为db1。

在数据库DB2中启动一个分布式事务的一个分支事务，xid的gtrid为test，bqual为db2。

在数据库DB1中向account表插入账户E，余额为100元。

以上执行结果证明数据插入完成。然后在数据库DB2中向account表插入账户F，余额为200元。

以上执行结果证明数据插入完成。然后在DB1中查看account表。

在DB2中查看表account。

从以上执行结果可以看出，因为分布式事务还没有提交，所以分支事务暂时无法查看到其他分支事务插入的数据。然后对数据库DB1进行第一阶段提交，且进入PREPARE状态。

对数据库DB2进行第一阶段提交，且进入PREPARE状态。

此时两个事务的分支都进入准备提交阶段，如果这之前的操作遇到任何错误，都应回滚所有分支的操作，以确保分布式事务的正确性。

然后再数据库DB1中提交事务。

在数据库DB2中提交事务。

以上执行结果证明两个事务分支都成功提交，此时可以在两个数据库中查询表中的数据，首先在数据库DB1中查询account表中的数据。

数据库DB2中查询表account的数据。

从以上执行结果可以看出，分布式事务成功提交，分支事务的插入数据操作成功。。

小结：MySQL数据库从入门到精通—数据库事务

https://www.zhihu.com/video/1570369624998445057

首先介绍了事务的概念和事务管理的相关操作（事务的使用、事务的回滚等），然后介绍了事务的属性和事务的隔离级别，最后介绍了分布式事务的原理与用法。大家需重点掌握事务管理，初步了解分布式事务的使用。