MySQl事务

1、ACID原则

• A(Atomictity) 原子性 要么成功要么失败
• C(Consistency) 一致性 事务中几条SQL操作，对应的表操作，要么都一起成功，要么都一起失败回滚。举例：订单+库存，增加3笔订单，必然库存扣减3
• I(Isolation) 隔离性 每个事务都是各自独立，互不影响
• D(Durability) 持久性 事务中数据的变化，一定会被持久化到磁盘，即使宕机也不会丢失数据

PS: $\color{red}{C(一致性)是目标，A(原子性)、I(隔离性)、D(持久性)是手段}$，换句话说MySQL通过AID这三个手段来确保C这个目标

1.1、Q: 如何确保A(Atomicity) 原子性

A：mysql通过undo-log来确保原子性原则

1.2、Q: 如何确保I(Isolation) 隔离性

A: mysql通过$\color{red}{锁机制和MVCC机制}$来确保隔离性原则

1.2.1、事务并发执行可能导致的问题

以下表student为例,

create table student(
    id int not null comment 'id',
    name varchar(10) not null comment '姓名',
    primary key(id)
);

id	name
1	张飞
2	关羽

1.2.1.1 脏写

一个事务回滚影响了另一个已提交事务的数据，那就是发生了脏写

举个例子

序号	trx1	trx2
①	begin;
②		begin;
③		update student set name = '关小羽' where id = 2
④	e student set name = '关老爷' where id = 2
⑤	commit;
⑥		rollback;

trx2先一步将id=2记录name修改为‘关小羽’，然后trx1将id=2记录修改为‘关老爷’并且commit；对于trx1而言已经完成，而此时trx2因为某种原因发生了回滚，将id=2记录的name重置为了‘关羽’。对于trx1而言，明明已经成功做了修改，结果啥也没发生，这种现象即为“脏写”。

1.2.1.2 脏读

一个事务读取到了另一个未提交事务修改过的数据，称之为“脏读”

举个例子

序号	trx1	trx2
①	begin;
②		begin;
③		update student set name = '刘备' where id = 2
④	select name from student where id = 2(此时返回name='刘备')
⑤	commit;
⑥		rollback;

trx1先开启事务，trx2将id=2记录更改为name=刘备，trx1执行查询得到id=2记录name=刘备，随后提交事务，至此trx1事务结束；trx2因为某些原因触发了回滚，又将id=2记录name重新改回“关羽”；最后的结果相当于trx1查询一个根本不存在的值，这种现象称之为“脏读”；

1.2.1.3 不可重复读

一个事务中读取到了相同记录不同的值，称之为“不可重复读”

举个例子

序号	trx1	trx2	trx3
①	begin;
②	select name from student where id = 2;(此时name='关羽')
③		update student set name = '孙权' where id = 2;
④	select name from student where id = 2;(此时name='孙权')
⑤			update student set name = '曹孟德' where id = 2;
⑥	select name from student where id = 2;(此时name='曹孟德')
⑦	commit;

上诉例子，trx2、trx3均为隐式事务，如上所示，在trx1中不同时间点，select的结果均不相同，这种现象称之为“不可重复读”

1.2.1.4 幻读

一个事务中相同条件，每次读取的记录变多，称之为“幻读”

举个例子

序号	trx1	trx2
①	begin;
②	select name from student where id > 1;(此时只有name=关羽)
③		insert into student(id, name) value(3, '黄忠');
④	select name from student where id > 1;(此时name=关羽、黄忠)
⑤	commit;

trx2为隐式事务，如图所示，trx1第二次执行查询语句时，结果变多了，得到trx2插入的记录，这种现象称为“幻读”；

PS：如果将trx2中的insert替换为delete语句，那么trx1中第二次执行查询语句时，结果会变少，这种情况不能称之为“幻读”。幻读着重强调在同一事务中，相同查询后续查询比之前的记录要多，查询出了之前不存在的记录。这种情况可以称为不可重复读，两次的查询结果不同。$\color{red}{不可重复读侧重在update和delete，幻读侧重在insert。}$

1.2.2、事务隔离级别

上诉并行事务中可能发生的问题，按照严重程度高低排序如下:

脏写 > 脏读 > 不可重复读 > 幻读

1.2.2.1、SQL标准中的事务隔离级别

正如Java中并发问题一样，上诉脏写等问题产生的根源在于对于同一数据的并发操作，没有并发只有串行，也就不会产生问题，但是相对应的性能就会急剧下降。鱼与熊掌不可兼得，此处“鱼”和“熊掌”分别指“性能”和事务的“隔离性”，所以两者需要互相妥协，以期达到一个大家都相对满意的平衡点。

在SQL规范标准，把对于事务隔离性舍弃的程度划分了四种级别，分别为：

• READ UNCOMMITED : 未提交读
• READ-COMMITED : 已提交读
• REPEATABLE READ : 可重复读
• SERIALIZABLE : 可串行化 同时标准规定，四类级别可以容忍某些问题存在，具体如下：

隔离级别	脏写	脏读	不可重复读	幻读
READ UNCOMMITED	×	√	√	√
READ COMMITED	×	×	√	√
REPEATABLE READ	×	×	×	√
SERILIZABLE	×	×	×	×

上诉是SQL规范中要求的最低标准，DB各大厂商对于隔离级别的支持以及级别对应标准可以不同；

1.2.2.2、MySQL中的事务隔离级别

MYSQL对于四种隔离级别均支持，但是REPEATABLE READ这个级别，MySQL可以做到不存在幻读问题。具体如下：

隔离级别	脏写	脏读	不可重复读	幻读
READ UNCOMMITED	×	√	√	√
READ COMMITED	×	×	√	√
REPEATABLE READ	×	×	×	×
SERILIZABLE	×	×	×	×

并发问题总结

并发事务访问相同记录无外乎以下三种情况：

• 1、读-读：并发事务先后读取相同记录

  此类情况，不会产生任何并发问题

• 2、写-写：并发事务先后对相同记录做写操作

  此类情况，可能会发生“脏写”

• 3、读-写：并发事务对相同记录，一个做写操作，一个做读操作

  此类情况，可能会发生 “脏读”、“不可重复读”、“幻读”

MySQL对于上诉2、3问题分别的应对之策：写操作 => 锁机制；读操作 => MVCC

1.2.3、如何解决“脏写”、“脏读”、“不可重复读”、“幻读”

1.2.3.1、解决“脏写”

MySQL通过锁机制解决，对于所涉及的行记录会加锁，先一步的事务拿到锁之后，其他事务想要操作，只能等锁释放才能操作，否则阻塞等待；

序号	trx1	trx2
①	begin;
②		begin;
③		update student set name = '关小羽' where id = 2
④	e student set name = '关老爷' where id = 2
⑤	commit;
⑥		rollback;

上诉例子，trx2会先一步获得id=2这条记录的行记录锁，trx1会被阻塞等待trx2所持有的锁释放，才能继续执行，故不会发生“脏写”。

1.2.3.2、解决“脏读”

MYSQL通过MVCC机制或者加锁来解决“脏读”问题。

1.2.3.3、解决“不可重复读”

REPEATABLE READ(RR) 事务级别下MVCC 可以解决快照读下的“不可重复读”问题；通过临键锁 next-key lock解决当前读的“不可重复读”； serializable 事务级别下 通过锁机制解决；

1.2.3.4、解决“幻读”

REPEATABLE READ(RR) 事务级别下MVCC 可以解决快照读下的“幻读”问题；通过间隙锁 gap lock解决当前读的“幻读” serializable 事务级别下 通过锁机制解决；

1.2.4、MVCC

MVCC-基于undo-log版本链、readview实现。

1.2.4.1、 undo-log版本链

undo-log 以student为例

a、向student表中插入一条记录

序号	trx 10
①	insert into student(id, name) value(1, '貂蝉');

此时undo-log版本链如下示意

b、id=1记录做了如下更新操作

序号	trx 100	trx 200
①	begin;
②		begin;
③	update student set name = '貂一蝉' where id = 1;
④		update student set name = '貂二蝉' where id = 2;
⑤	commit;
⑥		commit;

此时undo-log版本链如下示意

c、id=1记录做了删除操作

序号	trx 300
①	delete from student where id = 1;

此时undo-log版本链如下示意

1.2.4.2、 readview

readview包含以下4个主要部分

1. creator_trx_id：创建该readview的事务ID

ps：只有写操作（insert、update、delete）语句才会分配事务ID, 读操作的事务ID默认为0

2. m_ids：创建该readview，mysql中活跃的事务ID集合
3. min_trx_id：创建该readview时活跃事务中，最小事务ID，即m_ids中的最小值
4. max_trx_id: 创建该readview时MySQL应该分配的下一个事务ID

$\color{red}{PS: }$ max_trx_id != m_ids的最大值，而是系统中待分配的下一个事务ID

$\color{red}{PS:}$ RR和RC，两个级别下mysql创建readview的时机不同； $\color{red}{RC级别下，事务中每次select都会单独创建一个readview}$; $\color{red}{RR级别下，事务中第一次select时创建一个readview, 后面的select共用同一个readview}$

1.2.4.3、 MVCC执行过程

结合上诉MVCC执行过程，可以发现select不管在RC还是RR级均无法获得其他未提交事务的修改数据；但是RC依旧存在不可重复读、幻读问题，RR级别则不存在。

• $\color{red}{Q：}$如何解释“RC级别依旧存在不可重复读、幻读问题而RR级别不存在” 举个例子

select * from student;

id	name
1	貂蝉

执行下述事务

序号	trx 100	trx 200
①	begin;
②		begin;
③		update student set name = '貂小蝉' where id = 1;
④	select name from student where id = 1;
⑤		commit;
⑥	select name from student where id = 1;
⑦	commit;

1. 如果是RC级别，每次select都会新创建一个readview,④语句执行时，trx 200为活跃事务，在readview的m_ids中，所以无法读取③号语句的变更内容；当执行⑥号语句时，又会新创建一个readview，此时trx 200已是结束事务，不会出现在m_ids中，所以select可以查看到③号语句的修改内容；

2. 如果时RR级别，因为同一事务中只会在第一次select时创建readview,上例而言，也就是④号语句创建readview, trx 200依旧为活跃事务，存在于readview的m_ids中，无法读取③号语句的更改，到执行⑥号语句时，因为readview依旧为之前那个，所以仍然无法读取③号语句的更改。 因此RC级别下，依旧存在不可重复读问题，而RR级别不存在不可重复读问题；

同理也可以解释RC存在幻读而RR不存在幻读

序号	trx 100	trx 200
①	begin;
②		begin;
③	select * from student where id > 1;
④		insert into student(id, name) value(2, '小乔');
⑤		commit;
⑥	select * from student where id > 1;
⑦	commit;

$\color{red}{PS:}$准确来说，RR级别下MVCC只是解决了快照读的不重复读和幻读，当前读场景下这两类问题依旧存在；对于RR级别下的当前读，MySQL采用锁机制来解决不可重复读（记录锁）和幻读（间隙锁）问题

1.2.4.4、 快照读与当前读

快照读：普通的select语句。

执行时生成readview，利用MVCC机制来读取记录。$\color{red}{SERIALIZABLE串行化级别下，普通读都会升级为当前读}$

当前读：

1. select * from table lock in share model;(共享锁)
2. select * from table for update;(排他锁)
3. insert、update、delete 涉及得都是当前读且加排他锁
4. serializable 串行化事务级别下得普通读

1.2.4.5、二级索引与MVCC

$\color{red}{Q：}$ 如果某个查询语句只会使用到二级索引且刚好符合覆盖索引的条件，那么理论上不会发生回表；我们已知undo-log版本链是关联在聚簇索引上，这类查询怎么使用MVCC？
$\color{red}{A：}$二级索引页的头部有个page_max_trx_id(修改过该页的最大事务ID)，执行select查询所创建的readview中的min_trx_id 大于 page_max_trx_id，则说明修改二级索引的事务已提交，二级索引对于本次查询可用；否则则认为二级索引的数据不可用，需要继续做回表操作。

1.2.5、MySQL 锁（innodb）

参见《MySQL锁》

1.2.6、MySQL死锁

参见《MySQL锁》、《MySQL死锁分析》

1.3、Q: 如何确保D（Durability）持久性原则

A: mysql通过redo-log来确保持久性原则

2、MySQL一条数据是怎么被存储的？

参见《MySQL整体概述》

3、java

参见《Java 连接MySQL二三事》

4、spring @Transaction

参见《Java 连接MySQL二三事》

5、分布式事务

参见《分布式事务》