MySQL事务

事务

当多个用户访问同一数据时，一个用户在更改数据的过程中可能有其它用户同时发起更改请求，为保证数据的一致性状态，MySQL 引入了事务。

在银行业务中，有一条记账原则，即有借有贷，借贷相等。为了保证这种原则，每发生一笔银行业务，就必须确保会计账目上借方科目和贷方科目至少各记一笔，并且这两笔账要么同时成功，要么同时失败。如果出现只记录了借方科目，或者只记录了贷方科目的情况，就违反了记账原则。会出现记错账的情况。

在银行的日常业务中，只要是同一银行(如都是中国农业银行，简称农行)，一般都支持账户间的直接转账。因此，银行转账操作往往会涉及两个或两个以上的账户。在转出账户的存款减少一定金额的同时，转入账户的存款就要增加相应的金额。

下面，在 MySQL 数据库中模拟一下上述提及的转账问题。

假如要从张三的账户直接转账 500 元到李四的账户。首先需要创建账户表，存放用户张三和李四的账户信息。创建账户表和插入数据的 SQL 语句和运行结果如下所示：

create databases mybank;
use mybank;

create table bank(
	customerName VARCHAR(20),   #用户名
  currentMoney DECIMAL(10,2)    #当前余额
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into bank (customerName,currentMoney) VALUES('张三',1000);
insert into bank (customerName,currentMoney) VALUES('李四',1);

张三和李四两个账户的余额总和为 1000+1=1001 元。

下面开始模拟实现转账功能。从张三的账户直接转账 500 元到李四的账户，可以使用 UPDATE 语句分别修改张三的账户和李四的账户。张三的账户减少 500 元，李四的账户增加 500 元， SQL 语句如下所示：

/*转账测试：张三转账给李四 500 元*/
#张三的账户少 500 元，李四的账户多 500 元
update bank set currentMoney = currentMoney-500 where customerName = '张三';
update bank set currentMoney = currentMoney+500 where customerName = '李四';

正常情况下，执行以上的转账操作后，余额总和应保持不变，仍为 1001 元。但是，如果在这个过程的其中一个环节出现差错，如在张三的账户减少 500 元之后，这时发生了服务器故障，李四的账户没有立即增加 500 元，此时，第三方读取到两个账户的余额总和变为 500+1=501 元，即账户总额间少了 500 元。

MySQL 为了解决此类问题，提供了事务。事务可以将一系列的数据操作捆绑成一个整体进行统一管理，如果某一事务执行成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务执行时遇到错误，则就必须取消或回滚。取消或回滚后，数据将全部恢复到操作前的状态，所有数据的更改均被清除。

MySQL 通过事务保证了数据的一致性。上述提到的转账过程就是一个事务，它需要两条 UPDATE 语句来完成。这两条语句是一个整体，如果其中任何一个环节出现问题，则整个转账业务也应取消，两个账户中的余额应恢复为原来的数据，从而确保转账前和转账后的余额总和不变，即都是 1001 元。

事务是访问并更新数据库各种数据项的一个程序执行单元。在MySQL的innoDB下，每一条语句都是事务，也可以有一组复杂的SQL组成。

数据库的事务(Transaction)是一种机制、一个操作序列，包含了一组数据库操作命令。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。

在数据库系统上执行并发操作时，事务是作为最小的控制单元来使用的，特别适用于多用户同时操作的数据库系统。例如，航空公司的订票系统、银行、保险公司以及证券交易系统等。

按我理解，无非是要做到可靠性以及并发处理

可靠性：数据库要保证当insert或update操作时抛异常或者数据库crash的时候需要保障数据的操作前后的一致，想要做到这个，我需要知道我修改之前和修改之后的状态，所以就有了undo log和redo log。

并发处理：也就是说当多个并发请求过来，并且其中有一个请求是对数据修改操作的时候会有影响，为了避免读到脏数据，所以需要对事务之间的读写进行隔离，至于隔离到啥程度得看业务系统的场景了，实现这个就得用MySQL 的隔离级别。

事务具有 4 个特性.

• 原子性(Atomicity)
• 一致性(Consistency)
• 隔离性(Isolation)
• 持久性(Durability)

这 4 个特性通常简称为 ACID。

1. 原子性

事务是一个完整的操作。事务的各元素是不可分的(原子的)。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。

以银行转账事务为例，如果该事务提交了，则这两个账户的数据将会更新。如果由于某种原因，事务在成功更新这两个账户之前终止了，则不会更新这两个账户的余额，并且会撤销对任何账户余额的修改，事务不能部分提交。

2. 一致性

当事务完成时，数据必须处于一致状态。也就是说，在事务开始之前，数据库中存储的数据处于一致状态。在正在进行的事务中. 数据可能处于不一致的状态，如数据可能有部分被修改。然而，当事务成功完成时，数据必须再次回到已知的一致状态。通过事务对数据所做的修改不能损坏数据，或者说事务不能使数据存储处于不稳定的状态。

以银行转账事务事务为例。在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少了，而另一个账户余额尚未修改。因此，所有账户余额的总额处于不一致状态。事务完成以后，账户余额的总额再次恢复到一致状态。

3. 隔离性

对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

事务隔离分为不同级别，包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：

transaction-isolation = REPEATABLE-READ

可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

(1)不可重复读 ：一个事务对同一行数据重复读取两次，但是却得到了不同的结果

(2)脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

(3)幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，

这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

另外，当事务修改数据时，如果任何其他进程正在同时使用相同的数据，则直到该事务成功提交之后，对数据的修改才能生效。张三和李四之间的转账与王五和赵二之间的转账，永远是相互独立的。

4. 持久性

事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。

一个事务成功完成之后，它对数据库所作的改变是永久性的，即使系统出现故障也是如此。也就是说，一旦事务被提交，事务对数据所做的任何变动都会被永久地保留在数据库中。

事务的 ACID 原则保证了一个事务或者成功提交，或者失败回滚，二者必居其一。因此，它对事务的修改具有可恢复性。即当事务失败时，它对数据的修改都会恢复到该事务执行前的状态。

执行事务的语法和流程

MySQL 提供了多种存储引擎来支持事务。支持事务的存储引擎有 InnoDB 和 BDB，其中，InnoDB 存储引擎事务主要通过 UNDO 日志和 REDO 日志实现，MyISAM 存储引擎不支持事务。

拓展：任何一种数据库，都会拥有各种各样的日志，用来记录数据库的运行情况、日常操作、错误信息等，MySQL 也不例外。例如，当用户 root 登录到 MySQL 服务器，就会在日志文件里记录该用户的登录时间、执行操作等。

为了维护 MySQL 服务器，经常需要在 MySQL 数据库中进行日志操作：

• UNDO 日志：复制事务执行前的数据，用于在事务发生异常时回滚数据。
• REDO 日志：记录在事务执行中，每条对数据进行更新的操作，当事务提交时，该内容将被刷新到磁盘。

默认设置下，每条 SQL 语句就是一个事务，即执行 SQL 语句后自动提交。为了达到将几个操作做为一个整体的目的，需要使用 BEGIN 或 START TRANSACTION 开启一个事务，或者禁止当前会话的自动提交。

# 开启事务
begin
或者
start transaction

# 提交事务 即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。
commit;

提交事务，意味着将事务开始以来所执行的所有数据都修改成为数据库的永久部分，因此也标志着一个事务的结束。一旦执行了该命令，将不能回滚事务。只有在所有修改都准备好提交给数据库时，才执行这一操作。

# 回滚事务 
# 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态。这里的操作指对数据库的更新操作。
rollback;

当事务执行过程中遇到错误时，使用 ROLLBACK 语句使事务回滚到起点或指定的保持点处。同时，系统将清除自事务起点或到某个保存点所做的所有的数据修改，并且释放由事务控制的资源。因此，这条语句也标志着事务的结束。

下面模拟在张三的账户减少 500 元后，李四的账户还未增加 500 时，有其他会话访问数据表的场景。由于代码需要在两个窗口中执行，为了方便阅读，这里我们称为 A 窗口和 B 窗口。

# 在 A 窗口中开启一个事务，并更新 mybank 数据库中 bank 表的数据，
use mybank;
begin;
update bank set currentMoney = currentMoney-500 where customerName='张三';

# 在 B 窗口中查询 bank 数据表中的数据
select * from mybank.bank;

从结果可以看出，虽然 A 窗口中的事务已经更改了 bank 表中的数据，但没有立即更新数据，这时其他会话读取到的仍然是更新前的数据。

# 在 A 窗口中继续执行事务并提交事务
update bank set currentMoney = currentMoney+500 where customerName='李四';

# 提交事务
 commit;

# 在 B 窗口中再次查询 bank 数据表的数据
 select * from mybank.bank;

在 A 窗口中执行 COMMIT 提交事务后，对数据所做的更新将一起提交，其他会话读取到的是更新后的数据。从结果可以看出张三和李四的总账户余额和转账前保持一致，这样数据从一个一致性状态更新到另一个一致性状态。

前面提到，当事务在执行中出现问题，也就是不能按正常的流程执行一个完整的事务时，可以使用 ROLLBACK 语句进行回滚，使用数据恢复到初始状态。

# 张三的账户余额已经减少到 500 元，如果再转出 1000 元，将会出现余额为负数，因此需要回滚到原始状态

# 将张三的账户余额减少 1000 元，并让事务回滚，
begin;
update bank set currentMoney=currentMoney-1000 where customeName = '张三';
rollback;

select * from mybank.bank;

从结果可以看出，执行事务回滚后，账户数据恢复到初始状态，即该事务执行之前的状态。

在数据库操作中，为了有效保证并发读取数据的正确性，提出了事务的隔离级别。在 MySQL 中，事务的默认隔离级别是 REPEATABLE-READ (可重读)隔离级别，即事务未结束时(未执行 COMMIT 或 ROLLBACK)，其它会话只能读取到未提交数据。

设置事务自动提交(开启和关闭)

MySQL 默认开启事务自动提交模式，即除非显式的开启事务(BEGIN 或 START TRANSACTION)，否则每条 SOL 语句都会被当做一个单独的事务自动执行。但有些情况下，我们需要关闭事务自动提交来保证数据的一致性。

# 查看当前事务自动提交模式
show variables like 'autocommit';

# 使用 SET autocommit 语句设置事务的自动提交模式
set autocommit = 0|1|ON|OFF;

• 值为 0 和值为 OFF：关闭事务自动提交。如果关闭自动提交，用户将会一直处于某个事务中，只有提交或回滚后才会结束当前事务，重新开始一个新事务。
• 值为 1 和值为 ON：开启事务自动提交。如果开启自动提交，则每执行一条 SQL 语句，事务都会提交一次。

# 使用 SET autocommit 语句关闭事务自动提交，且张三转给李四 500 元
set autocommit = 0;
select * from mybank.bank;

update bank set currentMoney = currentMoney-500 where customerName='张三' ;
update bank set currentMoney = currentMoney+500 where customerName='李四';

# 重新打开一个 cmd 窗口，查看 bank 数据表中张三和李四的余额
select * from mybank.bank;

结果显示，张三和李四的余额是事务执行前的数据。

# 在之前的窗口中使用 COMMIT 语句提交事务，并查询 bank 数据表的数据
commit;
select * from mybank.bank;

结果显示，bank 数据表的数据更新成功。

在案例中，关闭自动提交后，该位置会作为一个事务起点，直到执行 COMMIT 语句和 ROLLBACK 语句后，该事务才结束。结束之后，这就是下一个事务的起点。

关闭自动提交功能后，只用当执行 COMMIT 命令后，MySQL 才将数据表中的资料提交到数据库中。如果执行 ROLLBACK 命令，数据将会被回滚。如果不提交事务，而终止 MySQL 会话，数据库将会自动执行回滚操作。

使用 BEGIN 或 START TRANSACTION 开启一个事务之后，自动提交将保持禁用状态，直到使用 COMMIT 或 ROLLBACK 结束事务。之后，自动提交模式会恢复到之前的状态，即如果 BEGIN 前 autocommit = 1，则完成本次事务后 autocommit 还是 1。如果 BEGIN 前 autocommit = 0，则完成本次事务后 autocommit 还是 0。

在高并发下的数据库事务隔离级别

MySQL 事务的四大特性，其中事务的隔离性就是指当多个事务同时运行时，各事务之间相互隔离，不可互相干扰。事务并发时就容易出现脏读、不可重复读和幻读等情况。

为了保证并发时操作数据的正确性，数据库都会有事务隔离级别的概念。

1. 脏读

脏读是指一个事务正在访问数据，并且对数据进行了修改，但是这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。简单来说，表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

2. 不可重复读

不可重复读是指在一个事务内，多次读取同一个数据。

在这个事务还没有结束时，另外一个事务也访问了该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。简单来说，一个事务对同一行数据重复读取两次，但是却得到了不同的结果。

3. 幻读

幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

为了解决以上这些问题，标准 SQL 定义了 4 类事务隔离级别，用来指定事务中的哪些数据改变是可见的，哪些数据改变是不可见的。

MVCC多版本并发控制，解决的事一致性非锁定读，也就是不加锁的情况，来解决读的问题。多版本的意思就是，当一个事务锁定某行数据，其他事务修改提交这一行数据，它会产生一个快照数据。

MySQL 包括的事务隔离级别如下：事务隔离级别依次增高

• 读未提交(READ UNCOMITTED)
• 读提交(READ COMMITTED)
• 可重复读(REPEATABLE READ)
• 串行化(SERIALIZABLE)

1. 读未提交 read uncomitted

顾名思义，读未提交就是可以读到未提交的内容。

如果一个事务读取到了另一个未提交事务修改过的数据，那么这种隔离级别就称之为读未提交。

在该隔离级别下，所有事务都可以看到其它未提交事务的执行结果。因为它的性能与其他隔离级别相比没有高多少，所以一般情况下，该隔离级别在实际应用中很少使用。

当 MySQL 的事务隔离级别为 READ UNCOMITTED 时，首先分别在 A 窗口和 B 窗口中开启事务，在 B 窗口中的事务更新但未提交之前， A 窗口中的事务就已经读取到了更新后的数据。但由于 B 窗口中的事务回滚了，所以 A 事务出现了脏读现象。

2. 读提交 read committed

使用读提交隔离级别可以解决实例中产生的脏读问题。

顾名思义，读提交就是只能读到已经提交了的内容。

如果一个事务只能读取到另一个已提交事务修改过的数据，并且其它事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那么这种隔离级别就称之为读提交。

该隔离级别满足了隔离的简单定义：一个事务从开始到提交前所做的任何改变都是不可见的，事务只能读取到已经提交的事务所做的改变。


这是大多数数据库系统的默认事务隔离级别(例如 Oracle、SQL Server)，但不是 MySQL 默认的。

当 MySQL 的事务隔离级别为 READ COMMITTED 时，首先分别在 A 窗口和 B 窗口中开启事务，在 B 窗口中的事务更新并提交后，A 窗口中的事务读取到了更新后的数据。在该过程中，A 窗口中的事务必须要等待 B 窗口中的事务提交后才能读取到更新后的数据，这样就解决了脏读问题。而处于 A 窗口中的事务出现了不同的查询结果，即不可重复读现象。

3. 可重复读 repeatable read ，rr

使用可重复读隔离级别可以解决实例中产生的不可重复读问题。

顾名思义，可重复读是专门针对不可重复读这种情况而制定的隔离级别，可以有效的避免不可重复读。

在一些场景中，一个事务只能读取到另一个已提交事务修改过的数据，但是第一次读过某条记录后，即使其它事务修改了该记录的值并且提交，之后该事务再读该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据。那么这种隔离级别就称之为可重复读。

可重复读是 MySQL 的默认事务隔离级别，它能确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。在该隔离级别下，如果有事务正在读取数据，就不允许有其它事务进行修改操作，这样就解决了可重复读问题。

在可重复读隔离级别中产生的幻读问题。

4. 串行化 serializable

使用串行化隔离级别可以解决实例中产生的幻读问题。

如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。那么这种隔离级别就称之为串行化。

SERIALIZABLE 是最高的事务隔离级别，主要通过强制事务排序来解决幻读问题。简单来说，就是在每个读取的数据行上加上共享锁实现，这样就避免了脏读、不可重复读和幻读等问题。但是该事务隔离级别执行效率低下，且性能开销也最大，所以一般情况下不推荐使用。