MySQL事务以及MVCC详解

什么是事务

事务是由一条或者多条DML语句组成逻辑执行单元，这系列操作要么全部执行成功，要么全部执行失败。通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)

事务的特性（ACID）

原子性(atomicity)： 事务的最小工作单元，要么全成功，要么全失败。

一致性(consistency)： 事务开始和结束后，数据库的完整性不会被破坏。（最重要）

隔离性(isolation)： 不同事务之间互不影响，四种隔离级别为RU（读未提交）、RC（读已提交）、RR（可重复读）、SERIALIZABLE （串行化）。隔离级别由低到高，性能由高到低。隔离级别总是和性能相违背。

持久性(durability)： 事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失。

ACID之间的关系：

• 只有满足一致性，事务的结果才是正确的。

• 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。

• 事务满足持久化是为了能应对数据库崩溃的情况。

  

Innodb的隔离性有哪些

• 未提交读（READ UNCOMMITTED）

事务中的修改，即使没有提交，对其他事务也是可见的。

• 提交读（READ COMMITTED）

一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其他事务是不可见的。

又称为不可重复读，一个事务因为读取到另一个事务已提交的修改数据，导致在当前事务的不同时间读取同一条数据获取的结果不一致。

• 可重复读（REPEATABLE READ）

保证在同一个事务中多次读取同样数据的结果是一样的。

在当前事务中，不论读取多少次，数据任然是第一次读取的值，不会因为在第一次读取之后，其他事务再修改提交此数据而产生改变。

• 串行化（SERIALIZABLE）

强制事务串行执行。

所有的数据库的读或者写操作都为串行执行，读加读锁，写加写锁。当前隔离级别下只支持单个请求同时执行，所有的操作都需要队列执行。所以种隔离级别下所有的数据是最稳定的，但是性能也是最差的。

每个隔离性会造成什么问题

• 脏读：

（针对未提交数据）如果一个事务中对数据进行了更新，但事务还没有提交，另一个事务可以“看到”该事务没有提交的更新结果，这样造成的问题就是，如果第一个事务回滚，那么，第二个事务在此之前所“看到”的数据就是一笔脏数据。

• 不可重复读

**（针对其他提交前后，读取数据本身的对比）**不可重复读取是指同一个事务在整个事务过程中对同一笔数据进行读取，每次读取结果都不同。如果事务1在事务2的更新操作之前读取一次数据，在事务2的更新操作之后再读取同一笔数据一次，两次结果是不同的，所以，Read Uncommitted也无法避免不可重复读取的问题。

• 幻读

（针对其他提交前后，读取数据条数的对比） 幻读是指同样一笔查询在整个事务过程中多次执行后，查询所得的结果集是不一样的。幻读针对的是多笔记录。在Read Uncommitted隔离级别下， 不管事务2的插入操作是否提交，事务1在插入操作之前和之后执行相同的查询，取得的结果集是不同的，所以，Read Uncommitted同样无法避免幻读的问题。

不可重复读的重点是修改:同样的条件, 你读取过的数据, 再次读取出来发现值不一样了

幻读的重点在于新增或者删除 (数据条数变化)

比如：

select * from tableName where id = 1 ； 前后前次读取该id=1的数据字段值值不一致，不可重复读；

select * from tableName where id > 3 ; 前后两次读取数据条数不一致，幻读；

隔离级别	脏读	不可重复读	幻读
未提交读	√	√	√
提交读	×	√	√
可重复读	×	×	√（在MySQL中，加入临键锁可以防止）
可串行化	×	×	×

事务特性是怎么保证的

事务怎么保证一致性

这个问题分为两个层面来说。

从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。

但是，如果你在事务里故意写出违反约束的代码，一致性还是无法保证的。例如，你在转账的例子中，你的代码里故意不给B账户加钱，那一致性还是无法保证。因此，还必须从应用层角度考虑。

从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据！

事务怎么保证原子性

是利用Innodb的undo log。

undo-log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经执行成功的SQL语句，他需要记录你要回滚的相应日志信息。实际上就是记录SQL操作的相反SQL操作，SQL为insert，undolog中记录一条delete。rollback操作实际就是执行undolog的SQL起到覆盖作用。

例如

• (1)当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
• (2)当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
• (3)当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作

undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

// todo : undolog深入分析。

事务怎么保证持久性

在Mysql中，为了解决CPU和磁盘速度不一致问题，Mysql是将磁盘上的数据加载到内存，对内存进行操作，然后再回写磁盘。好，假设此时宕机了，在内存中修改的数据全部丢失了会怎么办？

是利用Innodb中的redolog。

正如上面所说，MySQL是先把磁盘上的数据加载到内存当中。当做数据修改的时候，不仅在内存中操作，还会在redolog中记录这次的操作。当事务提交的时候，会将redolog日志进行刷盘（redolog一部分在内存中（redolog buffer ），一部分在磁盘中）。当数据库宕机重启的时候，会将redolog中的内容回复到数据库中。在根据binlog和undolog内容决定回滚数据还是提交事务。

为什么要采用redolog刷盘，而不是每次提交事务进行数据刷盘？

MySQL加载的内存中Buffer pool 的数据最小单位是页。一页有16K大小。只修改一个页的几个字节，就要将整个页面刷入磁盘，太浪费性能和资源。而且一个事务中的SQL可能牵涉到多个数据页的修改，而这次数据页可能也不是相邻的。也就是刷盘的时候要进行随机IO，速度会比较慢。

而redo log 体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。redo log是一直向末尾进行追加，输入顺序IO，效率快（这个刷盘指的是redo log内存中的数据刷盘到磁盘中的redolog。若MySQL服务器正常，则redolog不会对数据库进行操作，只有当mysql出现宕机重启，才会触发redolog写入数据库中。）

redo log刷盘机制

1.存在 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；
2.写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分
3.持久化到磁盘，对应的是 hard disk，也就是图中的绿色部分。
日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。

可以简单理解为：MySQL内存中redologBuffer --> 操作系统内存 page cache —> 通过fsync操作将数据写入磁盘。

redo log日志的大小是固定的，即记录满了以后就从头循环写。
innodb_flush_log_at_trx_commit

• 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;mysql 崩溃会丢失数据。
• 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘。
• 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache，OS宕机会丢失数据。
  InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。
  参考：https://blog.csdn.net/qq_19558795/article/details/80599560?utm_source=app&app_version=4.5.2

比如某一时刻数据库 DOWN 机了，有两个事务，一个事务已经提交，另一个事务正在处理。数据库重启的时候就要根据日志进行前滚及回滚，把已提交事务的更改写到数据文件，未提交事务的更改恢复到事务开始前的状态。即，当数据 crash-recovery 时，通过 redo log 将所有已经在存储引擎内部提交的事务应用 redo log 恢复，所有已经 prepared 但是没有 commit 的 transactions 将会应用 undo log 做 roll back。

Buffer Pool的刷盘机制：

1、当innodb中的脏页比例超过innodb_max_dirty_pages_pct_lwm的值时（默认值为75），这个时候就会开始刷新脏页到磁盘。

2、当innodb中的脏页比例超过innodb_max_dirty_pages_pct_lwm的值，而且还超过innodb_max_dirty_pages_pct时

innodb就会进入勤快刷新模式(agressively flush）这个模式下innodb会把脏页更快的刷新到磁盘。

3、还有一种情况叫做sharp checkpoint ,当innodb要重用它之前的redo文件时，就会把innodb_buffer_pool中所有与这

个文件有关的页面都要刷新到磁盘；这样做就有可能引起磁盘的IO风暴了，轻者影响性能，重者影响可用性。

可以简单的理解为：每秒都会进行一次刷盘（checkpoint：定期将db buffer的内容刷盘）。当脏页数据太快的时候，会触发innodb的勤快刷新模式。

参考文章：

https://blog.csdn.net/molaifeng/article/details/113820047

https://www.cnblogs.com/JiangLe/p/7419835.html?utm_source=itdadao&utm_medium=referral

https://www.jianshu.com/p/06d09aa71a15

事务怎么保证隔离性

RC,RR–MVCC

READ UNCOMMITIED总是读取最新的数据行，而不是符合当前事务版本的数据行。

SERIALIZABLE则会对所有读取的行都加锁。

MVCC是什么

MVCC的概念

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

MVCC是一种用来解决读-写冲突的无锁并发控制，其基本思想是为每次事务生成一个新版本的数据，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。

MVCC 在mysql 中的实现依赖的是 undo log 与 read view 。

名词解释：

版本号

• 系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
• 事务版本号：事务开始时的系统版本号。

隐藏的列

MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：

• trx_id：每次一个事务对某条记录进行修改时，都会把该事务的事务id赋值给trx_id隐藏列；
• roll_pointer：每次对一个记录进行修改时，都会把旧版本写入undo日志中，然后把这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息；

创建版本号：指示创建一个数据行的快照时的系统版本号；

删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。

MVCC是怎么保证隔离级别的

以下实现过程针对可重复读隔离级别：

SELECT

InnoDB 会根据以下两个条件检查每行记录：

• InnoDB只查找版本早于当前事务版本的数据行（也就是，行的事务编号小于或等于当前事务的事务编号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。

• 删除的行要事务ID判断，读取到事务开始之前状态的版本，只有符合上述两个条件的记录，才能返回作为查询结果。

INSERT

InnoDB为新插入的每一行保存当前事务编号作为行版本号。

DELETE

InnoDB为删除的每一行保存当前事务编号作为行删除标识。

UPDATE

InnoDB为插入一行新记录，保存当前事务编号作为行版本号，同时保存当前事务编号到原来的行作为行删除标识。

保存这两个额外事务编号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

MVCC 在mysql 中的实现

MVCC 在mysql 中的实现依赖的是 undo log 与 read view 。

undo log根据行为的不同，undo log分为两种： insert undo log 和 update undo log

• insert undo log

insert 操作中产生的undo log，因为insert操作记录只对当前事务本身课件，对于其他事务此记录不可见，所以 insert undo log 可以在事务提交后直接删除而不需要进行purge操作。

purge的主要任务是将数据库中已经 mark del 的数据删除，另外也会批量回收undo pages

数据库 Insert时的数据初始状态：此时回滚指针是null;

• update undo log：

  update 或 delete 操作中产生的 undo log。 因为会对已经存在的记录产生影响，为了提供 MVCC机制，因此update undo log 不能在事务提交时就进行删除，而是将事务提交时放到入 history list 上，等待 purge 线程进行最后的删除操作。

  1. 数据第一次被修改时：
  

  2. 当另一个事务第二次修改当前数据：

     

为了保证事务并发操作时，在写各自的undo log时不产生冲突，InnoDB采用回滚段的方式来维护undo log的并发写入和持久化。回滚段实际上是一种 Undo 文件组织方式

ReadView

对于 RU(READ UNCOMMITTED) 隔离级别下，所有事务直接读取数据库的最新值即可，和 SERIALIZABLE 隔离级别，所有请求都会加锁，同步执行。所以这对这两种情况下是不需要使用到 Read View 的版本控制。

对于 RC(READ COMMITTED) 和 RR(REPEATABLE READ) 隔离级别的实现就是通过上面的版本控制来完成。两种隔离界别下的核心处理逻辑就是判断所有版本中哪个版本是当前事务可见的处理。针对这个问题InnoDB在设计上增加了ReadView的设计，ReadView中主要包含当前系统中还有哪些活跃的读写事务，把它们的事务id放到一个列表中，我们把这个列表命名为为m_ids。

对于查询时的版本链数据是否看见的判断逻辑：

• 如果被访问版本的 trx_id 属性值小于 m_ids 列表中最小的事务id，表明生成该版本的事务在生成 ReadView 前已经提交，所以该版本可以被当前事务访问。

• 如果被访问版本的 trx_id 属性值大于 m_ids 列表中最大的事务id，表明生成该版本的事务在生成 ReadView 后才生成，所以该版本不可以被当前事务访问。

• 如果被访问版本的 trx_id 属性值在 m_ids 列表中最大的事务id和最小事务id之间，那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

这个ReadView可以理解为一个读视图。

当隔离级别为RR时：读视图为创建事务的事务的读视图，在该级别的事务中，两次相同的select查询返回的结果是相同的，无关其他事务已经提交的修改结果。（保证可重复读）。当时进行update，insert，delete，以及select…for update 操作时，更新一次readView视图。（保证其他事务修改记录不会丢失）

当隔离级别为RC是：读视图为每条语句直接的读视图，即事务中每条SQL语句执行之前都会刷新一下ReadView。这时执行相同的select语句返回的结果可能不是相同的，因为两次执行之间可能存在别的事务提交了当前查询列的修改，第二次select时刷新到最新的一个ReadView。（造成不可重复读）

MVCC总结：
所谓的MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用 READ COMMITTD 、REPEATABLE READ 这两种隔离级别的事务在执行普通的 SEELCT 操作时访问记录的版本链的过程，这样子可以使不同事务的 读-写 、 写-读 操作并发执行，从而提升系统性能。

在 MySQL 中， READ COMMITTED 和 REPEATABLE READ 隔离级别的的一个非常大的区别就是它们生成 ReadView 的时机不同。在 READ COMMITTED 中每次查询都会生成一个实时的 ReadView，做到保证每次提交后的数据是处于当前的可见状态。而 REPEATABLE READ 中，在当前事务第一次查询时生成当前的 ReadView，并且当前的 ReadView 会一直沿用到当前事务提交，以此来保证可重复读（REPEATABLE READ）。

快照读和当前读

在可重复读级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，是不及时的数据，不是数据库当前的数据！这在一些对于数据的时效特别敏感的业务中，就很可能出问题。

对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库当前版本数据的方式，叫当前读 (current read)。很显然，在MVCC中：

快照读

MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。

select * from table ….;

当前读

MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。

INSERT;
UPDATE;
DELETE;
在进行 SELECT 操作时，可以强制指定进行加锁操作。以下第一个语句需要加 S 锁，第二个需要加 X 锁。

- select * from table where ? lock in share mode;
- select * from table where ? for update;

事务的隔离级别实际上都是定义的当前读的级别，MySQL为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读的概念，使得select不用加锁。而update、insert这些“当前读”的隔离性，就需要通过加锁来实现了。

RR隔离级别下解决幻读–临键锁

Record Lock 行锁
单个行记录上的锁我们通常讲的行锁，它的实质是通过对索引的加锁实现；只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。在事务隔离级别为读已提交下，仅采用Record Lock。

如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。

Gap Lock 间隙锁
锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。

SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

Next-Key Lock 临键锁
它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

在 InnoDB 存储引擎中，SELECT 操作的幻读问题通过 MVCC 的快照读得到了解决，

而 UPDATE、DELETE 的幻读问题通过 Record Lock 解决，INSERT 的不可重复读问题是通过 Next-Key Lock（Record Lock + Gap Lock）解决的。间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。 我们总是牺牲性能来换取安全稳定。

串行化是怎么加锁的

串行化就相当于给操作的记录上一个共享锁（读写锁），即当读某条记录时就占用这条记录的读锁，此时其它事务一样可以申请到这条记录的读锁来读取，但是不能写（读锁被占的话，写锁就不能被占；读锁可以被多个事务同时占有）

MySQL中锁

MySQL锁概述

相对其他数据库而言，MySQL的锁机制比较简单，其最 显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

死锁和死锁检测

在并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁

当出现死锁以后，有两种策略：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑

乐观锁和悲观锁

乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。

与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

共享锁和排它锁

共享锁(S锁)和排它锁（X锁）是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁。相当于对于同一把门，它拥有多个钥匙一样

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

对于悲观锁，一般数据库已经实现了，共享锁也属于悲观锁的一种，那么共享锁在mysql中是通过什么命令来调用呢。通过查询资料，了解到通过在执行语句后面加上lock in share mode就代表对某些资源加上共享锁了。

排它锁与共享锁相对应，就是指对于多个不同的事务，对同一个资源只能有一把锁。

如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

与共享锁类型，在需要执行的语句后面加上 for update就可以了

InnoDB默认采用行锁，在未使用索引字段查询时升级为表锁。MySQL这样设计并不是给你挖坑。它有自己的设计目的。即便你在条件中使用了索引字段，MySQL会根据自身的执行计划，考虑是否使用索引(所以explain命令中会有possible_key 和 key)。如果MySQL认为全表扫描效率更高，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

第一种情况：全表更新。事务需要更新大部分或全部数据，且表又比较大。若使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突。

第二种情况：多表查询。事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚。这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。

一致性锁定读和一致性非锁定读

在默认配置下innodb的隔离级别是repeatable read，innodb的select操作使用的是一致性非锁定读

一致性的非锁定行读（consistent nonlocking read，简称CNR）是指InnoDB存储引擎通过行多版本控制（multi versioning）的方式来读取当前执行时间数据库中运行的数据。如果读取的行正在执行delete、update操作，这时读取操作不会因此而会等待行上锁的释放，相反，InnoDB存储引擎会去读取行的一个快照数据。

之所以称为非锁定度，是因为不需要等待访问数据行上的X锁的释放。快照数据是指该行之前版本的数据，通过undo段来实现（undo用来在事务中回滚数据）。

在Read Committed和Repeatable Read模式下，innodb存储引擎使用默认的一致性非锁定读。在Read Committed隔离级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据；而在Repeatable Read隔离级别下，对于快照数据，一致性非锁定读总是读取事务开始时的行数据版本。

一致性锁定读
默认情况下，innodb存储引擎的select操作使用一致性非锁定读，但是在某些情况下，需要对读操作进行加锁以保证数据逻辑的一致性。Innodb存储引擎对select语句支持2种一致性锁定读(locking read)操作;

SELECT … FOR UPDATE对于读取的行记录加一个X排它锁，其他事务不能对锁定的行加任何锁。

SELECT … LOCK IN SHARE MODE对于读取的行记录添加一个S共享锁。其它事务可以向被锁定的行加S锁，但是不允许添加X锁，否则会被阻塞。

简单理解就是一致性非锁定读就是快照读 : select…;

一致性锁定读就是当前读: insert，update，delete，select…for update;

binlog，redolog，undolog的理解和区别

重做日志（redo log）

确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

回滚日志（undo log）

保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

二进制日志（binlog）

用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。
用于数据库的基于时间点的还原。

Mysql的日志系统主要有redo log（重做日志）和binlog （归档日志）。redo log是Innodb存储引擎层面的日志，binlog是MySQL Server层的记录日志。两者都是记录了某种操作的日志，自然会有写重复。

MySQL的逻辑架构图：

redo log日志模块

redo log是InnoDB存储引擎层的日志，又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和介质失败（media failure）时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻，以此来保证数据的完整性。

在一条更新语句进行执行的时候，InnoDB引擎会把更新记录写到redo log日志中，然后更新内存，此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘中，这里涉及到WAL即Write Ahead logging技术，他的关键点是先写日志，再写磁盘。

有了redo log日志，那么在数据库进行异常重启的时候，可以根据redo log日志进行恢复，也就达到了crash-safe。

redo log日志的大小是固定的，即记录满了以后就从头循环写。

binlog日志模块

binlog是属于MySQL Server层面的，又称为归档日志，属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠binlog是没有crash-safe能力的

binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。，如果update操作没有造成数据变化，也是会记入binlog。

MySQL binlog的三种工作模式：ROW（行模式）, Statement（语句模式）, Mixed（混合模式）

redo log和binlog区别

• redo log是属于innoDB层面，binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。
• redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑
• redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
• binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。

一条更新语句执行的顺序

二阶段提交

update T set c=c+1 where ID=2;

• 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
• 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
• 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
• 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
• 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

WAL

WAL（Write-ahead logging，预写式日志）是数据库系统提供原子性和持久化的一系列技术。

在使用 WAL 的系统中，所有的修改在提交之前都要先写入 log 文件中。

MySQL的WAL（Write Ahead Log）在InnoDB中被称作redo log

「修改并不直接写入到数据库文件中，而是写入到另外一个称为 WAL 的文件中；如果事务失败，WAL 中的记录会被忽略，撤销修改；如果事务成功，它将在随后的某个时间被写回到数据库文件中，提交修改。」

WAL 的优点

1. 读和写可以完全地并发执行，不会互相阻塞（但是写之间仍然不能并发）。
2. WAL 在大多数情况下，拥有更好的性能（因为无需每次写入时都要写两个文件）。
3. 磁盘 I/O 行为更容易被预测。
4. 使用更少的 fsync()操作，减少系统脆弱的问题。

二阶段提交

两阶段提交原理描述:

阶段1：InnoDB redo log 写盘，InnoDB 事务进入 prepare 状态

阶段2：如果前面prepare成功，binlog 写盘，那么再继续将事务日志持久化到binlog，如果持久化成功，那么InnoDB

事务 则进入 commit 状态(实际是在redo log里面写上一个commit记录)

备注: 每个事务binlog的末尾，会记录一个 XID event，标志着事务是否提交成功，也就是说，recovery 过程中，binlog

最后一个 XID event 之后的内容都应该被 purge。

最终:mysql在落盘日志的时候,先落盘binlog,再落盘redo.

组提交

** 组提交概念：将多个刷盘操作合并成一个，最大化每次刷盘手里，提升性能，降低资源开销。**

在没有开启binlog时：

Redo log的刷盘操作将会是最终影响MySQL TPS的瓶颈所在。为了缓解这一问题，MySQL使用了组提交，将多个刷盘操作合并成一个，如果说10个事务依次排队刷盘的时间成本是10，那么将这10个事务一次性一起刷盘的时间成本则近似于1。

当开启binlog时：

为了保证Redo log和binlog的数据一致性，MySQL使用了二阶段提交，由binlog作为事务的协调者。而 引入二阶段提交 使得binlog又成为了性能瓶颈，先前的Redo log 组提交 也成了摆设。为了再次缓解这一问题，MySQL增加了binlog的组提交，目的同样是将binlog的多个刷盘操作合并成一个，结合Redo log本身已经实现的 组提交，分为三个阶段(Flush 阶段、Sync 阶段、Commit 阶段)完成binlog 组提交，最大化每次刷盘的收益，弱化磁盘瓶颈，提高性能。

组提交参考文章：https://blog.csdn.net/n88Lpo/article/details/81187372