mysql事务相关

mysql的事务是经常使用到的，但是在并发过程中，事务相互不干扰需要消耗巨大的性能，所以事务有隔离级别。
事务有四个属性，称为ACID属性：
1、原子性(Atomicity):事务是一个原子单位，要么全部执行，要么全部不执行。

2、一致性(Consistent):事务的开始和结束，数据都必须保持一致状态。

3、隔离性(isolation):数据库系统提供隔离机制，保证并发事务之间是互相不干扰的。也就意味着事务处理过程中的中间状态对其他的事务是透明的。

4、持久性(Durable):事务完成之后，对数据的修改是永久性的，即使出现系统故障也能够保持

事务是一系列SQL语句的集合，如果没有事务，会出现什么问题？或者说SQL只能一条一条的单个执行，会出现什么问题？

这个很简单，如果没有事务，我们平时生活中的银行转账就无法操作。

二、数据库读现象
ACID属性里面有一个是隔离级别，即并发事务之间互相不干扰。互相不干扰只是一个终极状态，且需要消耗巨大的性能。在我们实际应用过程中，是存在很大的灰度空间的：隔离级别有程度的区分。所以如果隔离程度控制的比较弱的话，就会产生脏读、不可重复读以及幻读的现象。

1、脏读

事务T1修改某个字段的值，然后事务T2读取该值，此后T1撤销了对该字段的更新，或者更新成另外的值才commit到数据库中，这样T2读取的数据是无效的或者错误的。导致T2依据脏数据所做的操作也是错误的。

2、不可重复读

在数据库访问中，一个事务范围内的两次相同的查询却返回了不同的数据。事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行验证而重新读取，却发现得到了不同的结果

3.幻读

幻读是指事务T1对表中的数据进行修改，假设修改涉及了表中全部的数据行，同时第二个事务也修改这个表中的数据，这种修改是向表中插入一条新的数据。后面就会出现操作了T1事务的用户发现表中还有没有修改的数据行，仿佛出现了幻觉一样。

三，mysql的锁的机制(Innodb)
https://www.cnblogs.com/leedaily/p/8378779.html

https://segmentfault.com/a/1190000018470235

innodb锁类型
S-共享锁：又叫读锁，其他事务可以继续加共享锁，但是不能继续加排他锁。
X-排他锁: 又叫写锁，一旦加了写锁之后，其他事务就不能加锁
IS意向共享锁:表达一个事务想要获取一张表中某几行的共享锁。
IX意向排他锁:表达一个事务想要获取一张表中某几行的共享锁。
意向锁有什么用呢？为什么需要这个锁呢？
首先说一下如果没有这个锁，如果要给这个表加上表锁，一般的做法是去遍历每一行看看他是否有行锁，这样的话效率太低，而我们有意向锁，只需要判断是否有意向锁即可，不需要再去一行行的去扫描。

innodb在加行锁之前，会先对表加上意向锁，然后再加行锁。意向锁就可以在不用全部扫描的情况下知道这个表里面加了哪些行锁。

InnoDB锁算法
记录锁
记录锁是锁住记录的，这里要说明的是这里锁住的是索引记录，而不是我们真正的数据记录。

如果锁的是非主键索引，会在自己的索引上面加锁之后然后再去主键上面加锁锁住.
如果表上没有索引(包括没有主键)，则会使用隐藏的主键索引进行加锁。
如果要锁的列没有索引，则会进行全表记录加锁。
例如：select * from user where id= 1 for update; 会给user表加上IX，在主键索引1加上X锁。

间隙锁
锁间隙的意思就是锁定某一个范围，间隙锁又叫gap锁，其不会阻塞其他的gap锁，但是会阻塞插入间隙锁，这也是用来防止幻读的关键。

 

 例如：select * from user where id <=5 for update; 会锁定这个范围的索引，不允许其他事务插入或者更新这些记录。防止幻读的发生。但是需要注意，grap锁是相互兼容的，索引其他事务也可以在这个范围加grap锁。

update from user  set age = 30 where name = 'aa'。name加了辅助索引，这是一条当前读语句，会在name索引加上next-key锁，防止其他事务在间隙范围内进行插入。导致幻读。

next-key锁
这个锁本质是记录锁加上gap锁。在RR隔离级别下(InnoDB默认)，Innodb对于行的扫描锁定都是使用此算法，但是如果查询扫描中有唯一索引会退化成只使用记录锁。为什么呢?

四 事务隔离级别(本质是通过锁机制或者MVCC来实现)
好了，我们的主体终于到了，因为事务执行可能有很多操作（例如一个事务里面有多个操作，如果都是最高级别隔离，在并发时将是很糟糕的性能，所以对事务隔离进行分级）

1、读未提交（READ_UNCOMMITED）（最低隔离级别）

事务T在读数据的时候并未对数据进行加锁，事务T在修改数据的时候对数据增加行级共享锁

T1在读取数据时，T2可以对相同数据进行读取、修改。因为T1没有进行任何锁操作；当T2对记录进行修改时，T1再次读取数据可以读取到T2修改后的数据。因为T2对数据进行修改只增加了行级共享锁，T1可以再增加共享读锁进行数据读取（尽管T2没有提交事务），导致脏读（T2回滚导致T1数据错误）。

2、读已提交（READ_COMMITED）RC

事务T在读取数据时增加行级共享锁，读取一旦结束，立即释放；事务T在修改数据时增加行级排他锁，直到事务结束才释放。

T1在读取数据的过程中，T2也可以对相同数据进行读取，但是不能进行修改（T1增加的是共享锁，T2也可以增加共享锁，但是不能增加排他锁）。T1读取结束后，会立即释放共享锁，这时T2可以增加排他锁，对数据进行修改，而此时T1既不能对数据进行读取也不能进行修改，直到T2事务结束。如上所述，这种隔离级别，解决了脏读问题，但是不能解决不可重复读现象。（一个事务范围内的两次相同的查询却返回了不同的数据）

3.可重复读（REPEATABLE_READ）RR(mysql默认)

事务T在数据读取时，必须增加行级共享锁，直到事务结束；事务T在修改数据过程中，必须增加行级排他锁，直到事务结束才释放。

T1在读取数据的过程中，T2也可以对相同数据进行读取，但是不能进行修改（T1增加的是共享锁，T2也可以增加共享锁，但是不能增加排他锁）。直到T1事务结束后，才会释放共享锁，这时T2才可以增加排他锁，对数据进行修改。解决了不可重复读现象，但是这种隔离级别解决不了幻读的问题

但是在实际过程中，Innodb使用多版本并发控制 MVCC（是行级锁的一个变种，为每一行数据保存当前版本号，类似实现了行级锁）事务T读取数据时，事务1可以修改数据（但是这行数据版本号变了），事务T再次读取这行数据时，只会读取旧的版本数据，所以两次读取的数据时一致的。

4、可串行化（SERIALIZABLE）

产生幻读是由于没有进行范围查询时没有增加范围锁。

事务T在读取数据时，必须先增加表级共享锁，直到事务结束才释放；事务T在修改数据时，必须先增加表级排他锁，直到事务结束才释放。

T1在读取A表时，增加了表级共享锁，此时T2也可以读取A表，但是不能进行任何数据的修改，直到T1事务结束。随后T2可以增加对A表的表级排他锁，此时T1不能读取A表中的任何数据，更不能进行修改。

如上所述，可序列化解决了脏读、不可重复读、幻读等读现象，但是隔离级别越来越高的同时，在并发性能上也就越来越低。

MVCC 多版本并发控制
用锁机制可以保证不同隔离级别的要求，但是会降低数据库的并发性能。比如在可重复读级别，读表的时候不能同时插入，必须是在事务完成后才能插入数据，如果某个事务执行比较长，就严重影响其他事务的执行。Innodb会使用mvcc来实现一致性读不加锁，其实很多引擎都是用mvcc来实现，只是不同的引擎mvcc实现的方式可能不一样。现在讲的是innodb的实现

网上看到大量的文章讲到MVCC都是说给没一行增加两个隐藏的字段分别表示行的创建时间以及过期时间，它们存储的并不是时间，而是事务版本号。事实上，这种说法并不准确，严格的来讲，InnoDB会给数据库中的每一行增加三个字段，它们分别是DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID。但是，为了理解的方便，我们可以这样去理解，索引接下来的讲解中也还是用这两个字段的方式去理解。

在InnoDB中，给每行增加两个隐藏字段来实现MVCC，一个用来记录数据行的创建时间，另一个用来记录行的过期时间（删除时间）。在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。

于是乎，默认的隔离级别（REPEATABLE READ）下，增删查改变成了这样：

SELECT
读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。
INSERT
将当前事务的版本号保存至行的创建版本号
UPDATE
新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号
DELETE
将当前事务的版本号保存至行的删除版本号
快照读和当前读
快照读(别名：一致非锁定读，一致性读)：读取的是快照版本，也就是历史版本（select语句都是快照读）

当前读：读取的是最新版本（即可能其他事务有插入，或者修改）

事务中第一次调用SELECT语句的时候才会生成快照，在此之前事务中执行的update、insert、delete操作都不会生成快照。

RC 隔离级别下，每次读取都会重新生成一个快照，所以每次快照都是最新的，也因此事务中每次SELECT也可以看到其它已commit事务所作的更改（每次读更新快照）；

RR隔离级别下，快照会在事务中第一次SELECT语句执行时生成，只有在本事务中对数据进行更改才会更新快照，只有第一次SELECT之前其它已提交事务所作的更改你可以看到；（不更新快照）

所以其实RR的效率会好于RC;

什么时候用快照读，什么时候用当前读？

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;

select * from table where ? for update;

insert into table values (…);

update table set ? where ?;

delete from table where ?;

 mysql update,delete执行原理

一个Update操作的具体流程。当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁 (current read)。待MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。因此，Update操作内部，就包含了一个当前读。同理，Delete操作也一样。Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。

针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后在读取下一条加锁，直至读取完毕。

事务id的创建
需要通过全局事务id产生器产生一个事务id，最后，把读写事务加入到全局读写事务链表(trx_sys->rw_trx_list)，把事务id加入到活跃读写事务数组中(trx_sys->descriptors)。

msyql的快照 read_view
http://www.mamicode.com/info-detail-2132360.html

快照其实就是保存事务id的对象。每次查询得到的记录，都会一一用快照readview进行对比和判断。

在InnoDB中，主要是通过使用readview的技术来实现判断。查询出来的每一行记录，都会用readview来判断一下当前这行是否可以被当前事务看到，如果可以，则输出，否则就利用undolog来构建历史版本，再进行判断，知道记录构建到最老的版本或者可见性条件满足。

当进行一个快照读（就是select语句）的时候(即创建新的readview时)，会把全局读写事务id（即当前活跃的事务id数组）拷贝一份到readview本地(read_view_t->descriptors)，read_view_t->up_limit_id是数组中最小的一个id，read_view_t->low_limit_id是数组中最大的id。

当查询出一条记录后(记录上有一个trx_id，表示这条记录最后被修改时的事务id)，可见性判断的逻辑如下：

trx_id小于read_view_t->up_limit_id最小id,则说明这条记录的最后修改在readview创建之前，因此这条记录可以被看见。

trx_id大于等于read_view_t->low_limit_id最大id，则说明这条记录的最后修改在readview创建之后，不可见.

trx_id在up_limit_id和low_limit_id之间，如果trx_id在read_view_t->descriptors之中，则表示这条记录的最后修改是在readview创建之时，被另外一个活跃事务所修改，所以这条记录也不可以被看见。如果trx_id不在read_view_t->descriptors之中，说明在创建readview时，修改记录的那个事务已经提交完了，所以可以看到。

所以这里有一个误解，快照读读取的是当前事务的快照（read_view），快照是在当前事务第一次查询的时候生成的，而不是事务一开始启动就生成的。快照读保证的是第一次读和第二次读的数据保持相同即可。

基于上述判断，如果记录不可见，则尝试使用undo去构建老的版本(row_vers_build_for_consistent_read)，直到找到可以被看见的记录或者解析完所有的undo。

RR隔离级别，在第一次创建readview后，这个readview就会一直持续到事务结束。也就是说在事务执行过程中，数据的可见性不会变，所以在事务内部不会出现不一致的情况。

RC隔离级别，事务中的每个查询语句都重新创建readview，所以如果两个查询之间有事务提交了，两个查询读出来的结果就不一样。所有RR级别的效率比RC级别更高。

五、事务操作实践
默认情况下，MYSQL是自动提交的，也就意味着平时我们执行一条update语句时，MYSQL是自动帮我们提交的，尽快我们没有显示执行commit命令。但是这种只适用于单条SQL的执行。

如果我们想要同时执行多条SQL，并且执行过程中有SQL执行异常，需要回滚前面已经成功执行的SQL或者最终想回滚全部，则必须显示的使用事务。

mysql事务的二阶段提交
https://blog.csdn.net/staforn/article/details/80423137

MySQL为了保证master和slave的数据一致性，就必须保证binlog和InnoDB redo日志的一致性（因为备库通过二进制日志重放主库提交的事务，而主库binlog写入在commit之前，如果写完binlog主库crash，再次启动时会回滚事务。但此时从库已经执行，则会造成主备数据不一致）。所以在开启Binlog后，如何保证binlog和InnoDB redo日志的一致性呢？为此，MySQL引入二阶段提交（two phase commit or 2pc），MySQL内部会自动将普通事务当做一个XA事务（内部分布式事物）来处理：

– 自动为每个事务分配一个唯一的ID（XID）。

– COMMIT会被自动的分成Prepare和Commit两个阶段。

– Binlog会被当做事务协调者(Transaction Coordinator)，由binlog决定prepare阶段和commit阶段。

 

1. 准备阶段（Storage Engine（InnoDB） Transaction Prepare Phase）

此时SQL已经成功执行，并生成xid信息及redo和undo的内存日志。然后调用prepare方法完成第一阶段，papare方法实际上什么也没做，将事务状态设为TRX_PREPARED，并将redo log刷磁盘（调用fsync函数）。

2. 提交阶段(Storage Engine（InnoDB）Commit Phase)

2.1 记录Binlog日志。

如果事务涉及的所有存储引擎的prepare都执行成功，则调用TC_LOG_BINLOG::log_xid方法将SQL语句写到binlog（write()将binary log内存日志数据写入文件系统缓存，fsync()将binary log文件系统缓存日志数据永久写入磁盘）。此时，事务已经铁定要提交了。否则，调用ha_rollback_trans方法回滚事务，而SQL语句实际上也不会写到binlog。

2.2 告诉引擎做commit。

最后，调用引擎的commit完成事务的提交。会清除undo信息，刷redo日志（调用fsync函数），将事务设为TRX_NOT_STARTED状态。

如果数据库系统发生崩溃，当数据库系统重新启动时会进行崩溃恢复操作，存储引擎中处于prepare状态的事务会去查询该事务是否也同时存在于binlog中，如果存在就在存储引擎内部提交该事务（因为此时从库可能已经获取了对应的binlog内容），如果binlog中没有该事务，就回滚该事务。上面三个步骤中，无论哪个步骤出问题，都会保证主从库数据一致。例如：当崩溃发生在第一步和第二步之间时，明显处于prepare状态的事务还没来得及写入到binlog中，所以该事务会在存储引擎内部进行回滚，这样该事务在存储引擎和binlog中都不会存在；当崩溃发生在第二步和第三步之间时，处于prepare状态的事务存在于binlog中，那么该事务会在存储引擎内部进行提交，这样该事务就同时存在于存储引擎和binlog中。

另外，MySQL内部两阶段提交需要开启innodb_support_xa=true，默认开启。这个参数就是支持分布式事务两段式事务提交。redo和binlog数据一致性就是靠这个两段式提交来完成的，如果关闭会造成事务数据的丢失

为了保障主从复制安全，故障恢复是如何做的？

开启Binary log的MySQL在crash recovery时：MySQL在prepare阶段会生成xid，然后会在commit阶段写入到binlog中。在进行恢复时事务要提交还是回滚，是由Binlog来决定的。

– 事务的Xid_log_event存在，就要提交。

– 事务的Xid_log_event不存在，就要回滚。

恢复的过程非常简单：

– 从Binlog中读出所有的Xid_log_event

– 告诉InnoDB提交这些XID的事务

– InnoDB回滚其它的事务

总结一下，基本顶多会出现下面是几种情况：

当事务在prepare阶段crash，数据库recovery的时候该事务未写入Binary log并且存储引擎未提交，将该事务rollback。
当事务在binlog阶段crash，此时日志还没有成功写入到磁盘中，启动时会rollback此事务。
当事务在binlog日志已经fsync()到磁盘后crash，但是InnoDB没有来得及commit，此时MySQL数据库recovery的时候将会读出二进制日志的Xid_log_event，然后告诉InnoDB提交这些XID的事务，InnoDB提交完这些事务后会回滚其它的事务，使存储引擎和二进制日志始终保持一致。
总结起来说就是如果一个事务在prepare阶段中落盘成功，并在MySQL Server层中的binlog也写入成功，那这个事务必定commit成功。

保证Bin_log的写入顺序和InnoDB层事务提交顺序一致性
上面提到单个事务的二阶段提交过程，能够保证存储引擎和binary log日志保持一致，但是在并发的情况下怎么保证InnoDB层事务日志和MySQL数据库二进制日志的提交的顺序一致？

 

如上图，事务按照T1、T2、T3顺序开始执行，将二进制日志（按照T1、T2、T3顺序）写入日志文件系统缓冲，调用fsync()进行一次group commit将日志文件永久写入磁盘，但是存储引擎提交的顺序为T2、T3、T1。当T2、T3提交事务之后，若通过在线物理备份进行数据库恢复来建立复制时，因为在InnoDB存储引擎层会检测事务T3在上下两层都完成了事务提交，不需要在进行恢复了，此时主备数据不一致（搭建Slave时，change master to的日志偏移量记录T3在事务位置之后）。

为了解决以上问题，在早期的MySQL 5.6版本之前，通过prepare_commit_mutex锁以串行的方式来保证MySQL数据库上层二进制日志和Innodb存储引擎层的事务提交顺序一致，然后会导致组提交（group commit）特性无法生效。

 

这就是加了 prepare_commit_mutex之后事务执行的流程。但是这样不能使用group commit牺牲了性能。

mysql5.6后，移除了原来的锁prepare_commit_mutex，并且可以实现bin_log的group commit, undo_log的group_commit.

mysql 5.6 Binary Log Group Commit (BLGC)
 

事务日志-undo
1.undo日志存在undo段中，里面有很多undo页，undo段位于共享表空间中，undo日志是逻辑日志，事务回滚的时候，会执行相反的sql语句。比如一次插入10万条数据，申请了很多页，回滚后，并不会还原原来的页结构。

2.undo日志另外一个重要功能是实现MVCC,undo日志记录了各个版本的行记录，当查询到某个行记录被其他事务占用时，就可以去undo页中获取之前版本的记录，实现无锁查询。

3.undo日志在事务提交后并不会马上删除，因为可能会有其他事务需要这个版本的记录。所以事务提交时把undo log放到一个链表中，然后由purge线程来决定是否删除undo log。

4.一个事务申请一个undo 页会非常浪费空间，所以可能不同的事务会共用一个undo 页。

5.update undo log 事务提交后，会加入到history链表中，所以history链表的顺序是事务提交顺序。

undo日志结构，innodb中，有两种undo log.
insert undo log: insert操作只对事务自己可见，所以事务提交后可以直接删除，不需要purge线程删除。因为其他事务不会用到这个记录，一个insert undo log结构里面存储了事务id,表对象，主键值，方便回滚的时候定位到具体的记录。

update undo log:是update操作和delete操作产生的日志，日志提交后会加到一个链表中，最终由purge删除。

purge
我们知道delete一个记录后，并不会再B+树种删除这个记录，而是将这个记录的delete flage 设置为1，update操作也是，事务操作只是保存在undo log中，并且标记好版本。事务提交后，也不会马上修改B+树。真正的操作是由purge来完成的，他的作用就是处理undo 页里面的操作，然后回收undo 页。

 

 如图, 有两个undo page, 分别放着trx1-trx7记录. 根据事务提交顺序，组成history 链表。

六、Double Write
如果说 Insert Buffer带给 InnoDB存储引擎的是性能上的提升,那么 double write(两次写)带给 InnoDB存储引擎的是数据页的可靠性。当发生数据库宕机时,可能 InnoDB存储引擎正在写入某个页到表中,而这个页只写了一部分,比如16KB的页,只写了前4KB,之后就发生了宕机,这种情况被称为部分写失效(partial page write)。在 InnoDB存储引擎未使用 doublewrite技术前,曾经出现过因为部分写失效而导致数据丢失的情况。

 Double Write的思路很简单:

  A. 在覆盖磁盘上的数据前，先将Page的内容写入到磁盘上的其他地方(InnoDB存储引擎中的doublewrite  buffer，这里的buffer不是内存空间，是持久存储上的空间).

  B. 然后再将Page的内容覆盖到磁盘上原来的数据。

  如果在A步骤时系统故障，原来的数据没有被覆盖，还是完整的。
  如果在B步骤时系统故障，原来的数据不完整了，但是新数据已经被完整的写入了doublewrite buffer.  因此系统恢复时就可以用doublewrite buffer中的新Page来覆盖这个不完整的page。
原文链接：https://blog.csdn.net/littlexiaoshuishui/article/details/88573943