面试准备之MySQL事务机制与锁

事务机制

关系型数据库是需要遵循ACID规则的，分别介绍如下。

• A( Atomic ）原子性：即事务要么全部做完，要么全部都不做。只要其中一个操作失败，就认为事务失败，需要回滚。

• C ( Consistency ）一致性：数据库要一直处于一致的状态。

• I ( Isolation ）独立性：并发的事务之间不会互相影响。

• D ( Durability ）持久性：一旦事务提交后，它所做的修改将会永久地保存在数据库中。

binlog和redo log的区别

存储的内容

binlog记载的是update/delete/insert这样的SQL语句，而redo log记载的是物理修改的内容（xxxx页修改了xxx）。

所以在搜索资料的时候会有这样的说法：redo log 记录的是数据的物理变化，binlog 记录的是数据的逻辑变化

功能

redo log的作用是为持久化而生的。写完内存，如果数据库挂了，那我们可以通过redo log来恢复内存还没来得及刷到磁盘的数据，将redo log加载到内存里边，那内存就能恢复到挂掉之前的数据了。

binlog的作用是复制和恢复而生的。

• 主从服务器需要保持数据的一致性，通过binlog来同步数据。
• 如果整个数据库的数据都被删除了，binlog存储着所有的数据变更情况，那么可以通过binlog来对数据进行恢复。

又看到这里，你会想：”如果整个数据库的数据都被删除了，那我可以用redo log的记录来恢复吗？“不能

因为功能的不同，redo log 存储的是物理数据的变更，如果我们内存的数据已经刷到了磁盘了，那redo log的数据就无效了。所以redo log不会存储着历史所有数据的变更，文件的内容会被覆盖的。

写入的细节

redo log是 MySQL 的 InnoDB 引擎所产生的。

binlog无论 MySQL 用什么引擎，都会有的。

InnoDB是有事务的，事务的四大特性之一：持久性就是靠redo log来实现的（如果写入内存成功，但数据还没真正刷到磁盘，如果此时的数据库挂了，我们可以靠redo log来恢复内存的数据，这就实现了持久性）。

先写redo log，再写binlog：

• 如果写redo log失败了，那我们就认为这次事务有问题，回滚，不再写binlog。
• 如果写redo log成功了，写binlog，写binlog写一半了，但失败了怎么办？我们还是会对这次的事务回滚，将无效的binlog给删除（因为binlog会影响从库的数据，所以需要做删除操作）
• 如果写redo log和binlog都成功了，那这次算是事务才会真正成功。

MySQL的原子性是怎么保证的吗？

undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。 例如

• (1)当你 delete 一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据

• (2)当你 update 一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作

• (3)当年 insert 一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作

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

关于 redo log 的讲解推荐阅读： MySQL几种常见的log

MySQL怎么保证一致性的？

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

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

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

MySQL怎么保证持久性的？

利用 Innodb 的redo log， redo log 是 innodb 引擎层实现的，并不是所有引擎都有。

正如之前说的，Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。

怎么解决这个问题？

简单啊，事务提交前直接把数据写入磁盘就行啊。

这么做有什么问题？

• 只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。

• 毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。

于是，决定采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。

采用 redo log的好处？

其实好处就是将redo log进行刷盘比对数据页刷盘效率高，具体表现如下

• redo log体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
• redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。

flush

redo log 每次刷盘这样一个操作我们称之为 flush。在更新之前，当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页“。

那什么时候会flush呢？

1. InnoDB 的 redo log 写满了，这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。

2. 系统内存不足，当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
3. MySQL 认为系统“空闲”的时候。

4. MySQL 正常关闭的时候。

推荐阅读：mysql日志系统之redo log和bin log

MySQL怎么保证隔离性的？

利用的是锁和MVCC机制。

关于锁，分为表级锁和行级锁，某一事务先获取到锁，则其他事务因获取不到锁，无法操作数据，进入阻塞状态。

至于MVCC,即多版本并发控制(Multi Version Concurrency Control),一个行记录数据有多个版本的快照数据，这些快照数据在undo log中。

如果一个事务读取的行正在做 DELELE 或者 UPDATE 操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。

并发事务带来哪些问题?

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

• 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

• 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

• 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：

不可重复读的重点是修改，比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除，比如多次读取一条记录发现记录增多或减少了。

事务隔离级别有哪些?MySQL的默认隔离级别是?

1. 未授权读取（Read Uncommitted ） ：会产生脏读，可以读取未提交的记录，实际情况下不会使用。

2. 授权读取（Read Committed ）：会存在不可重复读以及幻读的现象。 不可重复读重点在修改，即读取过的数据两次读的值不一样；幻读则侧重于记录数目变化，多次执行同一个查询返回的记录不完全相同。

3. 可重复读取（Repeatable Read ）： 解决了不可重复读的问题，会存在幻读现象。InnoDB使用MVCC+GapLock ( InnoDB行锁的一种）避免了幻读问题。

4. 串行（Serializable ）： 也称可串行读，此级别下读操作会隐式获取共享锁，保证不同事务间的互斥。 其消除了脏读、 幻读，但事务并发度急剧下降。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看，MySQL 8.0 该命令改为SELECT @@transaction_isolation;

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
复制代码

这里需要注意的是：与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使用的是 Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server) 是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读） 并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

锁机制与InnoDB锁算法

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)。
• InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

表级锁和行级锁对比：

• 表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。

• 行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB支持的行级锁，包括如下这几种:

• Record Lock: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项。

• Gap Lock： 对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条记录后的间隙加锁），不包含索引项本身。 其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。

• Next-keyLock： 锁定索引项本身和索引范围。 即 RecordLock 和 Gap Lock的结合，可解决幻读问题。

InnoDB 中还有如下两个表级锁。

• 意向共享锁（IS ）： 表示事务准备给数据行加入共享锁，事务在一个数据行加共享锁前必须先取得该表的IS锁。

• 意向排他锁（IX ）： 表示事务准备给数据行加入排他锁，事务在一个数据行加排他锁前必须先取得该表的IX锁。

这里的意向锁是表级锁，表示的是一种意向，仅仅表示事务正在读或写某一行记录，在真正加行级锁时才会判断是否冲突。 意向锁是InnoDB自动加的，不需要用户干预。

表级锁和行级锁可进一步划分为共享锁和排他锁，分别介绍如下。

• 共享锁（s）： 又被称为读锁，是读取操作创建的锁。 其他用户可以读取数据，可以再加共享锁，读取到的数据也是同一版本的；但任何事务都不能获取数据上的排他锁，不能对数据进行修改。 获取共享锁的事务只能读取数据而不能修改数据。 可以使用SELECT…LOCKIN SHARE MODE；来强制获取共享锁，否则绝大部分查询操作是不会获取锁的（串行事务级别除外）。

• 排他锁（x）： 又被称为写锁， 一个事务对数据加上排他锁后，其他事务不能再对此数据加任何其他类型的锁。 获取排他锁的事务既能读取数据也能修改数据。InnoDB对CUD(insert、 update、 delete）操作涉及的数据会默认加排他锁。 对于查询语句可以使用SELECT…FORUPDATE加排他锁。

InnoDB 的锁机制兼容表如下表所示。

当一个事务请求的锁模式与当前的锁兼容，InnoDB 就将请求的锁授予该事务；反之如果请求不兼容，则该事务就等待锁释放。

需要注意的是，InnoDB的行级锁是基于索引实现的，如果查询语句未命中任何索引，那么InnoDB会使用表级锁。 此外，InnoDB行级锁是针对索引加的锁，不针对数据记录，因此即使访问不同行的记录，如果使用到了相同的索引键仍然会1-H现锁冲突。 还需要注意的是，在通过SELECT . . .  LOCK  IN  SHARE MODE；或者SELECT ... FOR UPDATE 使用锁的时候，如果表没有定义任何索引，那么InnoDB会创建一个隐藏的聚簇索引并使用这个索引来加记录锁。

此外，不同于MyISAM总是一次性获得所需的全部锁， InnoDB的锁是逐步获得的。当两个事务都需要获得对方持有的锁，导致双方都在等待，这时就产生了死锁。 发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个则可以获取锁完成事务。 我们可以采取以下的方式避免死锁。

• 通过表级锁来减少死锁产生的概率。
• 多个程序尽量约定以相同的顺序访问表（这也是解决并发理论哲学家就餐问题的一 种思路）。
• 同一个事务尽可能做到一次锁定所需要的所有资源。

推荐阅读：MySQL innodb中各种SQL语句加锁分析

对 MVCC(多版本控制)的了解

• MVCC 被 MySQL 中 InnoDB 支持；
• 应对高并发事务，MVCC 比单纯的加锁更高效；
• MVCC 只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作；
• MVCC 可以使用乐观锁和悲观锁来实现；
• 各数据库中 MVCC 实现并不统一；
• InnoDB 的 MVCC 通过在每行记录后面保存三个字段来实现的。