MySQL （事务，锁，日志）

前言

  本文章为MySQL的学习笔记，文章中的图片，文字部分引用小林coding，阿秀的学习笔记，知识星球如有侵权，请联系删除。

事务的特性

  事务是由MySQL引擎实现的，常见的innoDB是支持事务的，但是MyISAM引擎就不支持事务，事务有四个特性，原子性，一致性，隔离性，持久性。原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，而且事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。一致性：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。比如，用户 A 和用户 B 在银行分别有 800 元和 600 元，总共 1400 元，用户 A 给用户 B 转账 200 元，分为两个步骤，从 A 的账户扣除 200 元和对 B 的账户增加 200 元。一致性就是要求上述步骤操作后，最后的结果是用户 A 还有 600 元，用户 B 有 800 元，总共 1400 元，而不会出现用户 A 扣除了 200 元，但用户 B 未增加的情况（该情况，用户 A 和 B 均为 600 元，总共 1200 元）。隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。也就是说，消费者购买商品这个事务，是不影响其他消费者购买的。持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。InnoDB引擎通过redo log(重做日志)保证事务的持久性，通过undo log（回滚日志）保证事务的原子性，通过MVCC（多版本并发控制）或锁机制保证隔离性，而一致性是通过持久性+原子性+隔离性来保证的。

事务的隔离性

  并行事务会存在一些问题一般包括脏读，不可重复读，幻读这些问题。脏读是指：一个事务A读到了另一个未提交事务B修改过的数据，就意味着发生了脏读现象。因为修改过得事务B因为还没有提交所以他可能发生回滚，那么A就可能读取到过期的数据。不可重复读：在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就是发生了不可重复读现象。幻读：在一个事务内多次查询某个符合查询条件的记录数量，如果出现前后两次查询到的记录数量不一样的情况，就发生了幻读。上述三种现象的严重性是脏读>不可重复读>幻读,SQL提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低，这四个隔离级别分别是读未提交，一个事务还没提交时，它做得变更就能被其他事务看到；读提交，一个事务提交之后，它做的变更才能被其他事务看到；可重复读，一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；串行化，会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；按照隔离水平高低排序可以分为串行化>可重复读>读已提交>读未提交。针对不同的隔离级别，并发事务可能发生的现象也会不同，

  所以，要解决脏读现象，就要升级到「读提交」以上的隔离级别；要解决不可重复读现象，就要升级到「可重复读」的隔离级别，要解决幻读现象不建议将隔离级别升级到「串行化」。不过InnoDB默认的隔离级别虽然是可重复读，但是它很大程度上能避免幻读现象（并不是完全解决）解决方案有两种：针对快照读（普通select语句），通过MVCC方式解决幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。针对当前读（select…for update等语句）通过next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好的避免了幻读问题。
  四种隔离级别分别是如何实现的，对于读未提交隔离级别的事务，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了。对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问。对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。
  ReadView在MVCC中是如何工作的：ReadView有四个重要的字段，m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值。max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；creator_trx_id ：指的是创建该 Read View 的事务的事务 id。此外聚簇索引记录中还会有两个隐藏列trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；roll_pointer ，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

  一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：1、如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。2、如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。3、如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：4、如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。5、如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。可重复读工作时是通过启动事务时生成一个ReadView,然后整个事务期间都是用ReadView，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的，所以就很好了避免幻读问题，保证了在事务期间读到的数据都是事务启动前的记录。读提交隔离级别是每次读取数据时，都会生成一个新的ReadView，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列」的比对，来控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。这里小林coding上有详细的例子讲述了两种隔离级别的不同实现过程。

锁

  MySQL中根据加锁的范围，可以分为全局锁，表级锁和行锁。全局锁使用flush tables with read lock进行加锁，加锁后整个数据库就处于只读状态了，如果要释放全局锁执行unlock tables，全局锁主要用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。但是全局锁的缺点就是整个数据库处于只读状态会造成业务停滞。但只会用作MyISA,引擎中，因为这种不支持事务的引擎，其他的引擎比如InnoDB支持可重复读的隔离，即使其他事务更新了表的数据，也不会影响数据库的备份时的ReadView。
  表级锁，主要有四种分别是表锁，元数据锁（MDL），意向锁，AUTO-INC锁。表锁会限制别的线程读写，也会限制本线程的读写，所以表锁颗粒度太大，会影响并发性能。**元数据（MDL）**所是我们对表进行CRUD（增删查改）操作时，加MDL读锁，对表做结构变更操作时，加的是MDL写锁。不需要显示的使用MDL，当我们对数据库表操作时，会自动加上MDL，在事务执行期间，MDL是一只持有的，事务结束后才释放。MDL保证当用户对表执行CRUD操作时，防止了其他线程对表结构进行变更。MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，所以一旦出现MDL写锁等待，会阻塞后续该表的所有CRUD操作。意向锁，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。表锁和行锁是满足读读共享、读写互斥、写写互斥的。如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。AUTO_INC锁，表里主键自增通常是通过AUTO_INCREMENT属性实现的，之后插入数据不指定主键值会自动递增，主要是通过AUTO-INC锁实现的，AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。但是对大量数据进行插入时，会影响插入性能，因为另一个事务的插入会被阻塞，所以在MySQL5.1.22版本开始，存储引擎提供了轻量级的锁来实现自增，一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。
  行级锁：InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。行级锁主要有三类，记录锁（record lock）把一条记录上锁；间隙锁（gap lock）锁定一个范围，但是不包含记录本身;记录锁+间隙锁（next-key lock，也叫临键锁）锁定一个范围，并且锁定记录本身。记录锁有S锁和X锁之分，当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。比如id中有3和5插入一个（3,5）的间隙锁，那么其他事务就无法插入id=4这条记录，就会防止幻读现象发生。间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。插入意向锁，一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁，如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁 临键锁，是间隙锁和记录锁的组合，锁定一个范围，并且锁定记录本身比如id列有（3，5]那么其他事务不能插入id=4也不能修改id=5.即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。
  唯一索引和非唯一索引加锁的规则：当我们用 唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。非唯一索引等值查询，当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
  当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。非唯一索引和 主键索引的 范围查询加锁规则的不同处在于唯一索引在满足一些条件的时候，索引的 next-key lock 退化为间隙锁或者记录锁。非唯一索引范围查询，索引的 next-key lock 不会退化为间隙锁和记录锁。如果执行的是update,delete,select…for update等有加锁性质的语句时需要检查语句是否使用了索引，因为如果是全表扫描的话，会对每一个索引加临键锁，相当于会把整个表锁住了。

日志

  主要常用的日志有三种，undo log（回滚日志），redo log（重做日志），binlog（归档日志）还有（BufferPool）缓存池。回滚日志是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC，通过ReadView + undo log 实现 MVCC（多版本并发控制）undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。
  重做日志是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复。当数据要更新时先更新内存，再把修改用重做日志记录下来，后续引擎会在适当的时候由后台线程将缓存刷新到磁盘里这叫WAL技术，而redo log是物理日志，记录了某个数据页做了修改的地方，开启事务后也会记录unod log中的旧值，所以redo log 和undo log的区别是redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值。此外redo log写进磁盘的方式是追加操作，所以磁盘操作时顺序写，而一般的写入数据是先找到写入入口，再写进磁盘，磁盘的操作是随机写，顺序写比随机写要高效的多。一般产生的redo log 放在自己的redo log buffer中，在后续刷盘到磁盘中。
  归档日志是 Server 层生成的日志，主要用于数据备份和主从复制。binlog文件记录了所有数据库表结构变更和表数据修改的日志，不记录查询类的操作比如SELECT和SHOW操作。它与redo log主要有四个区别，1、是适用对象binlog所有存储引擎都可以使用，redo log是innodb实现的日志，2、文件格式不同binlog有三种格式分别是STATEMENT（默认格式），ROW、MIXED。STATEMENT每条修改数据的SQL都会被记录到binlog(这种格式可以称为逻辑日志)但是存在动态函数问题，就是主库执行结果不是从库数据结果，会导致复制不一致。ROW没有动态函数问题，但是每行数据变化都会记录，会使binlog文件过大；MIXED包含了上述两种模式，会根据不同的情况使用两种模式。而redo log是物理日志，记录的是某个数据页做了哪些修改。3、写入方式不同binlog是追加写，写满文件就创建新的文件继续写，不会覆盖之前的，保存的是全量日志；redo log是循环写，日志空间是大小固定的，全部写完就从头开始，保存为被刷入磁盘的脏页日志。4、用途不一样binlog用于备份恢复，主从复制，redo log用于掉电等故障恢复。如果把整个数据库删除了要从binlog中恢复不能使用redo log恢复。
   缓存池是为了减少直接读取磁盘中的记录提高数据库的读写能力。当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页，但是Buffer Pool是基于内存的，如果断电了数据就会丢失。