MySql之深入分析MySql数据库的事务

1.1.事务概念

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

概念说明

1.这里的数据库操作序列主要是指的 增删改(insert，delete，update)等操作
  当然单条DDL（create drop）和 DCL（grant revoke）也会有事务；

1.2.支持事务的存储引擎

1.2.1.不同的存储引擎是否支持事务比较

https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

1.从上面的存储引擎之中我们可以看到，只有InnoDB和NDB存储引擎是支持事务的；

1.3事务的四大特性 [ACID]

1.3.1.原子性 [Atomicity]

1.这里的原子性，主要是指的对于数据库事务的一个提交结果来讲的的一个结果来讲的；
  要么提交成功，要么提交失败；（对于提交进去的数据是否正确这里不管）

1.3.2.一致性 [Consistent]

1.3.2.1.数据库自身定义的一致性

1.所谓的数据库自身定义的一致性指的是，主要是数据库自身的一个约束，比如 创建表的时候字段的长度为100，那么插入的数据就必须是这个范围之内的
  另外比如说主键，那么每一条记录的主键就不能重复；

1.3.2.1.用户自身定义的一致性

1.所谓用户自身定义的一致性，主要用户自身定义的数据的一致性，很简单的就是银行转账，比如从A账户转出1000元到B账户，
  账户显示的时候就必须是A账户-1000，B账户+1000；

1.3.3.隔离性 [Isolation]

1.所谓的隔离性，主要是指事务提交之间是相互隔离和互不影响的；
  比如对表的数据行的操作，各个事务的操作是互不影响的；

1.3.4.持久性 [Durability]

1.我们对数据库的任意 的操作，增删改，只要事务提交成功，那么结果就是永久性的，不可能因为我们系统宕机或者重启了数据库的服务器，
它又恢复到原来的状态了。这个就是事务的持久性。

持久性怎么实现呢？数据库崩溃恢复（crash-safe）是通过什么实现的？

持久性是通过redo log 和double write双写缓冲来实现的，我们操作数据的时候，
会先写到内存的buffer pool里面，同时记录 redo log，如果在刷盘之前出现异常，
在重启后就可以读取 redo log 的内容，写入到磁盘，保证数据的持久性。 当然，
恢复成功的前提是数据页本身没有被破坏，是完整的，这个通过双写缓冲 （double write）保证。

1.2.什么时候会出现事务

1.2.1.前置备注

查看数据库的版本

select VERSION()

查看数据库的存储引擎

show VARIABLES like '%engine%'

查看数据库事务相关配置

show VARIABLES like '%tx_isolation%'

1.根据查询结果，我们可以看到，已经提交成功并更改数据

1.2.2.事务的自动开启与自动提交

自动提交开启状态演示

查看数据库事务自动提交配置 (默认开启)

show VARIABLES like 'autocommit'

比如我们执行下面的SQL，会自动提交到数据库里面，进行更新数据结果

update employees e set e.last_name='gaoxinfu' where e.emp_no='1'

autocommit值的结果，开启 ON/true,关闭OFF/false

自动提交关闭状态演示

1.我们的自动提交的开关有两个级别，一个global级别，一个session级别；
2.现在我们将session级别的自动提交的开关进行关闭，然后查看数据库结果是否会自定提交
  set SESSION autocommit=off;
  show GLOBAL VARIABLES like 'autocommit' # 当前开关状态 ON
  show session VARIABLES like 'autocommit' # 当前开关状态 OFF

update employees e set e.last_name='gaoxinfu111' where e.emp_no='1';
select * from employees where emp_no='1';

在当前的客户端去查看数据库的结果是已经更新了，但是重新开一个查询的客户端界面查询，实际上还没有更新

重新开启一个客户端查询的结果如下

1.所以如果你的自动提交开关是关闭的话，那么就需要进行后续的操作，commit或者rollback操作

1.2.3.事务的手动开启与关闭

1.2.3.1.begin或者start transaction

BEGIN;
update employees e set e.last_name='gaoxinfu222' where e.emp_no='1';
COMMIT;

或者

start transaction;
update employees e set e.last_name='gaoxinfu333' where e.emp_no='1';
COMMIT;

1.2.3.2.commit,rollback 提交或者回滚

1.3.事务并发带来的问题

1.3.1.脏读

1.所谓脏读，主要是指同一个事物之中查询的数据结果不一致；

1.如上面我们有两个事务，一个事务transaction1，一个事务transaction2；
  第一步：事务1先进行查询，查询出的数据结构name=高新富；
  第二步：事务2对同一条记录进行更新，将name由“高新富”更新为“gaoxinfu"
  第三步：事务1对同一条记录再次查询，发现name已经变为”gaoxinfu",
  第四步：事务2这个时候又对之前做的更新做了回滚；
  在同一个事务里面，如果查询前后结果不一致，就是所谓的脏读，这个问题有些场景是比较严重的；

备注

1.大家这里注意下，先不要相当然的任务，在InnoDB存储引擎中上面这种情况不会出现，我们只是说现实的场景会有这种问题；
2.就跟银行转账是一样的，如果在转账的案例里面，我们第一个事务基于读取到的第二个事务未提交的余额进行了操作，
  但是第二个事务进行了回滚，这个时候就会导致数据不一致。这种读取到其他事务未提交的数据的情况，我们把它叫做脏读。

1.3.2.不接重复度 <-----更新数据|删除数据导致

1.一个事务读取到了其他事务已提交的数据导致前后两次读取数据不一致的情 况，我们把它叫做不可重复读。

1.如上面我们有两个事务，一个事务transaction1，一个事务transaction2；
  第一步：事务1先进行查询，查询出的数据结构name=高新富；
  第二步：事务2对同一条记录进行更新，将name由“高新富”更新为“gaoxinfu",并进行提交事务2 commit
  第三步：事务1对同一条记录再次查询，发现name已经变为”gaoxinfu",

1.3.3.幻读 <-----插入数据导致

1.一个事务前后两次读取数据数据不一致，是由于其他事务插入数据造成的，这种情 况我们把它叫做幻读。

1.如上面我们有两个事务，一个事务transaction1，一个事务transaction2；
  第一步：事务1先进行查询，查询出的数据结构name=高新富；
  第二步：事务2对同一条记录进行插入，name=“frank”并 commit
  第三步：事务1对同一条记录再次查询，发现name已经变为”gaoxinfu",

备注

1.不可重复读和幻读，的区别在那里呢？ 不可重复读是修改或者删除，幻读是插入。
2.无论是脏读，还是不可重复读，还是幻读，它们都是数据库的读一致性的问题，都是在一个事务 里面前后两次读取出现了不一致的情况。 
  读一致性的问题，必须要由数据库提供一定的事务隔离机制来解决。就像我们去饭店吃饭，基本的设施和卫生保证都是饭店提供的。
  那么我们使用数据库，隔离性的问题也必须由数据库帮助我们来解决。

1.4.事务的隔离级别

1.4.1.SQL92 标准

官网地址
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
搜索_iso

1.P1 就是脏读
  P2 就是不可重复读
  P3 就是幻读

1.4.2.InnoDB存储引擎的事务隔离级别

1.基本上跟SQL92 标准一致；

1.5.解决数据库事务的两大方案

1.5.1.LBCC (锁的并发控制)

1.既然要保证前后两次读取数据一致，那么我读取数据的时候，锁定我要操作的数据，不允许其他的事务修改就行了。
  这种方案我们叫做基于锁的并发控制Lock Based Concurrency Control（LBCC）。 
2.如果仅仅是基于锁来实现事务隔离，一个事务读取的时候不允许其他时候修改，那就意味着不支持并发的读写操作，
  而我们的大多数应用都是读多写少的，这样会极大地 影响操作数据的效率。

1.5.2.MVCC(多版本的并发控制)

1.5.2.1.概念

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

1.5.2.2. InnoDB 为每行记录都实现了两个隐藏字段

这个快照什么时候创建？
读取数据的时候，怎么保证能读取到这个快照而不是最新的数据？
这个怎么实现呢？

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

1.5.2.3.实现原理演示

1.MVCC 的查找规则：
  只能查找创建时间小于等于当前事务 ID 的数据，
  和删除时间大 于当前事务 ID 的行（或未删除）

参考图示
https://www.processon.com/diagraming/5e1eb4e1e4b0169fb5211b28
创建表SQL

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

1.创建事务 transaction1

insert into emp 
values(3,"gaoxinfu555");
insert into emp
values(4,"gaoxinfu666");
commit;

1.这里的创建版本，是指的我们的事务Id，我们暂且用transaction 1这里命名的1这个数字识别版本

2.在transaction2中去查询当前emp表中的数据

begin;
select * from emp;

3.在transaction3中去插入新数据

insert into emp 
values(5,"gaoxinfu777");
commit;

1.这里注意下，我们插入的新数据gaoxinfu777，其创建的版本是当前事务的id 3

4.在transaction2中去再次去查询数据结果

select * from emp;#这里这次只需要执行这一条SQL即可，无需再去执行Begin那个语句，因为我们模拟的一个事务，所以只需要一次Begin

1.MVCC 的查找规则：
  只能查找创建时间小于等于当前事务ID的数据，----->每一次的事务都有一个先后顺序的事务Id
  和删除时间大于当前事务ID 的行（或未删除）------>对于插入更新删除等事务的id如果在当前事务id后面，是查询不到这些变更的数据的

5.在transaction4中去删除id=4的记录

delete from emp where id=4;
commit;

1.我们现在将id=4的这条记录将其删除，那么我们可以看到创建版本是在transaction1中插入，另外删除版本是4；
2.那么我们接下来在transaction2中去查询数据，看看会有什么样的结果？

6.在transaction2中去再次去查询数据结果

select * from emp;

1.很显然，查询的结果依然是id=3和id=4的数据，因为事务4都在是事务1之后

7.在transaction4中更新id=3的记录

update emp set name="gaoxinfu-up" where id=3;
commit;

1.大家要注意下，更新操作来讲实际上是两个步骤的操作，先删除，后新增；
  我们看新插入的gaoxinfu-up这个记录的创建版本是5，所以也是不满足MVCC的原则的，所以也不会查询出来的，
  下面我们看一下transaction2的查询结果；

5.在transaction2中查询

select * from emp;