Mysql数据库事务

Mysql数据库事务

一. 事务的典型场景

比如下单，会操作订单表，资金表，物流表等等，这个时候我们需要让这些操作都在一个事务里面完成。在金融的系统里面事务配置是很常见的，比如行内转账的这种操作，如果我们把它简单地理解为一个账户的余额增加，另一个账户的余额减少的情况（当然实际上要比这复杂），那么这两个动作一定是同时成功或者同时失败的，否则就会造成银行的会计科目不平衡。

二. 事务的定义

事务是数据库管理系统（DBMS）执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

三. 哪些存储引擎支持事务

InnoDB支持事务。

四. 事务的四大特性

第一个，原子性，Atomicity，也就是我们刚才说的不可再分，也就意味着我们对数据库的一系列的操作，要么都是成功，要么都是失败，不可能出现部分成功或者部分失败的情况。
全部成功比较简单，问题是如果前面一个操作已经成功了，后面的操作失败了，怎么让它全部失败呢？这个时候我们必须要回滚。
原子性，在InnoDB里面是通过undo log来实现的，它记录了数据修改之前的值（逻辑日志），一旦发生异常，就可以用undo log来实现回滚操作。

第二个，一致性，Consistency，指的是数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。比如主键必须是唯一的，字段长度符合要求。
除了数据库自身的完整性约束，还有一个是用户自定义的完整性。

第三个，隔离性，Isolation，我们有了事务的定义以后，在数据库里面会有很多的事务同时去操作我们的同一张表或者同一行数据，必然会产生一些并发或者干扰的操作，那么我们对隔离性的定义，就是这些很多个的事务，对表或者行的并发操作，应该是透明的，互相不干扰的。通过这种方式，我们最终也是保证业务数据的一致性。
最后一个,持久性，durability。我们对数据库的任意的操作，增删改，只要事务提交成功，那么结果就是永久性的，不可能因为我们系统宕机或者重启了数据库的服务器，它又恢复到原来的状态了。这个就是事务的持久性。持久性是通过 redo log 来实现的，我们操作数据的时候，会先写到内存的 buffer pool里面，同时记录redo log，如果在刷盘之前出现异常，在重启后就可以读取redo log的内容，写入到磁盘，保证数据的持久性。

实际上还有一个双写缓冲的机制。因为存储引擎的页和操作系统的页大小不一致。一个存储引擎 page 的数据要写 4次，如果中间发生异常，或造成页数据的不可用。所以，必须把页的数据备份起来，这个就是双写缓冲（double write buffer）。原子性，隔离性，持久性，最后都是为了实现一致性。

https://www.cnblogs.com/deva/p/6214390.html

五. 数据库什么时候会出现事务

这条更新语句，有事务吗？

update student set sname='张三' where id=1;

它自动开启了一个事务，并且提交了。这个是开启事务的第一种方式，自动开启和自动提交。InnoDB里面有一个autocommit的参数（分成两个级别， session级别和global级别）。

show variables like 'autocommit';

它的默认值是ON。autocommit这个参数是什么意思呢？是否自动提交。如果它的值是true/on的话，我们在操作数据的时候，会自动开启一个事务，和自动提交事务。
手动开启事务也有几种方式，一种是用begin；一种是用start transaction。那么怎么结束一个事务呢？我们结束也有两种方式，第一种就是提交一个事务，commit；还有一种就是rollback，回滚的时候，事务也会结束。

六. 事务并发会带来什么问题？

脏读

在第一个事务里面，它首先通过一个 where id=1 的条件查询一条数据，返回name=Ada，age=16的这条数据。

第二个事务，它同样地是去操作id=1的这行数据，它通过一个update的语句，把这行id=1的数据的age改成了18，没有提交。

这个时候，在第一个事务里面，它再次去执行相同的查询操作，发现数据发生了变化，获取到的数据age变成了18。

在一个事务里面，由于其他的时候修改了数据并且没有提交，而导致了前后两次读取数据不一致的情况，叫做脏读。

脏读:一个事务读到了另一个事务没有提交的数据叫做脏读(未提交)。

不可重复读

同样是两个事务，第一个事务通过id=1查询到了一条数据。然后在第二个事务里面执行了一个update操作，通过一个 commit 提交了修改。然后第一个事务读取到了其他事务已提交的数据导致前后两次读取数据不一致的情况。那么这种事务并发带来的问题，叫做不可重复读。

不可重复读:发生在update/delete中,一个事务读取到另一个已经提交的update/delete事务。

幻读

在第一个事务里面我们执行了一个范围查询，这个时候满足条件的数据只有一条。在第二个事务里面，它插入了一行数据，并且提交了。重点：插入了一行数据。在第一个事务里面再去查询的时候，它发现多了一行数据。一个事务前后两次读取数据不一致，是由于其他事务插入数据造成的，这种情况我们把它叫做幻读。

幻读:一个事务读取到了另一个事务已经提交的Insert事务

七. SQL92 标准(事务的隔离级别)

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

第一个隔离级别叫做：Read Uncommitted（未提交读），一个事务可以读取到其他事务未提交的数据，会出现脏读，所以叫做RU，它没有解决任何的问题。

第二个隔离级别叫做：Read Committed（已提交读），也就是一个事务只能读取到其他事务已提交的数据，不能读取到其他事务未提交的数据，它解决了脏读的问题，但是会出现不可重复读的问题。

第三个隔离级别叫做：Repeatable Read (可重复读)，它解决了不可重复读的问题，也就是在同一个事务里面多次读取同样的数据结果是一样的，但是在这个级别下，没有定义解决幻读的问题。

最后一个就是：Serializable（串行化），在这个隔离级别里面，所有的事务都是串行执行的，也就是对数据的操作需要排队，已经不存在事务的并发操作了，所以它解决了所有的问题。

八. MySQL InnoDB 对隔离级别的支持

事务的隔离级别	并发产生的问题	脏读	不可重复读	幻读
未提交读(Read UnCommit)		×	×	×
已提交读(Read Commit)		√	×	×
可重复读(Repeatable Read)		√	√	×(但是对InnoDB存储引擎不可能)
串行化(Serializable)		√	√	√

九. 两大实现方案

LBCC

读取数据的时候，锁定我要操作的数据，不允许其他的事务修改就行了。 这种方案我们叫做基于锁的并发控制 Lock Based Concurrency Control（LBCC）。

MVCC

https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html

在修改数据的时候给它建立一个备份或者叫快照，后面再来读取这个快照就行了。 这种方案我们叫做多版本的并发控制 Multi Version Concurrency Control（MVCC）。 MVCC 的核心思想是： 我可以查到在我这个事务开始之前已经存在的已提交的数据，即使它在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。

InnoDB 为每行记录都实现了两个隐藏字段（还加上一个 ROWID）：

• DB_TRX_ID，6 字节：插入或更新行的最后一个事务的事务 ID，事务编号是自动递 增的（我们把它理解为创建版本号，在数据新增或者修改为新数据的时候，记录当前事务ID）。
• DB_ROLL_PTR，7 字节：回滚指针（我们把它理解为删除版本号，数据被删除或记 录为旧数据的时候，记录当前事务 ID）。

我们把这两个事务 ID 理解为版本号。

Mvcc测试

• 首先在第一个事务,初始化数据

  Transaction 1

  begin ;
  
  INSERT INTO `fireweed`.`student`(`student_id`, `name`, `age`) VALUES (1, '张三', 18);
  INSERT INTO `fireweed`.`student`(`student_id`, `name`, `age`) VALUES (2, '李四', 20);
  
  commit;
  
  

  此时的数据,创建版本是当前的事务ID,删除版本为空:

  id	name	age	创建版本(DB_TRX_ID)	删除版本(DB_ROLL_PTR)
  1	张三	18	1	undefined
  2	李四	20	1	undefined

• 在第二个事务,执行第1次查询,读取到两条原始数据,这个时候事务ID是2:

  Transaction 2

  begin;
  
  select * from student;
  

  

• 在第三个事务中插入数据

  Transaction 3

  begin;
  
  INSERT INTO `fireweed`.`student`(`student_id`, `name`, `age`) VALUES (3, '王五', 22);
  
  commit;
  

  此时多一条数据,它的创建版本号是当前事务编号:3

  id	name	age	创建版本(DB_TRX_ID)	删除版本(DB_ROLL_PTR)
  1	张三	18	1	undefined
  2	李四	20	1	undefined
  3	王五	22	3	undefined

  在第二个事务下,执行第2次查询

  select * from student;
  

  

  MVCC 的查找规则：只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大 于当前事务 ID 的行（或未删除）。 也就是不能查到在我的事务开始之后插入的数据，王五 的创建 ID 大于 2，所以还是 只能查到两条数据。

• 第四个事务，删除数据，删除了 id=2 李四 这条记录：此时事务编号: 4

  Transaction 4

  begin;
  
  delete from student  where student_id = 2;
  
  commit;
  

  id	name	age	创建版本(DB_TRX_ID)	删除版本(DB_ROLL_PTR)
  1	张三	18	1	undefined
  2	李四	20	1	4
  3	王五	22	3	undefined

  在第二个事务下,执行第3次查询

  

  查找规则：只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大于当前事 务 ID 的行（或未删除）。 也就是，在我事务开始之后删除的数据，所以 李四 依然可以查出来。所以还是这两 条数据。

• 第五个事务,执行update操作,这条事务id为5

  Transaction 5

  begin;
  
  update student set name = '瓜瓜醒' where student_id = 1;
  
  commit;
  

  此时的数据,更新数据的时候,旧数据的删除版本被记录为当前事务编号为 5

  id	name	age	创建版本(DB_TRX_ID)	删除版本(DB_ROLL_PTR)
  1	张三	18	1	5
  2	李四	20	1	4
  3	王五	22	3	undefined
  1	瓜瓜醒	18	5	undefined

  在第2个事务下,执行第4次查询

  select * from student;
  

  

  查找规则：只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大于当前事 务 ID 的行（或未删除）。 因为更新后的数据 瓜瓜醒 创建版本大于 2，代表是在事务之后增加的，查不出来。 而旧数据 qingshan 的删除版本大于 2，代表是在事务之后删除的，可以查出来。

  通过以上演示我们能看到，通过版本号的控制，无论其他事务是插入、修改、删除， 第一个事务查询到的数据都没有变化