Mysql事务/锁/日志总结

前言:

最近在复现Mysql相关知识点，之前对事务的理解和锁的理解都是单个知识点的复习，今天聚合一下，遂摘抄学习形成本篇

事务介绍：

什么是事务？

多条sql语句，要么全部成功，要么全部失败。

数据库事务特性(简称ACID)：

• 原子性(Atomic)
• 一致性(Consistency)
• 隔离性(Isolation)
• 持久性(Durabiliy)

原子性：

​ 组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有操作都成功，整个事务才会提交。任何一个操作失败，已经执行的任何操作都必须撤销，让数据库返回初始状态。
一致性：

​ 事务操作成功后，数据库所处的状态和它的业务规则是一致的。即数据不会被破坏。如A转账100元给B，不管操作是否成功，A和B的账户总额是不变的。
隔离性：

​ 在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对彼此产生干扰
持久性：

​ 一旦事务提交成功，事务中的所有操作都必须持久化到数据库中。

Mysql锁说明：

1、引擎不同，锁的类型不同

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

1. MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
2. InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

2、锁的特性

MySQL这3种锁的特性可大致归纳如下。

开销、加锁速度、死锁、粒度、并发性能

1、表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

2、行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

3、页锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！

仅从锁的角度来说：

1）表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；

2）而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

3、锁的分类

mysql中的锁在不同维度分类不一样，以下图整理了整体的锁分类

Mysql日志说明：

MySQL中存在着以下几种日志：重写日志（redo log）、回滚日志（undo log）、二进制日志（bin log）、错误日志（error log）、慢查询日志（slow query log）、一般查询日志（general log）。

1. 重写日志（redo log）

   ​ redo log是一种基于磁盘的数据结构，用来在MySQL宕机情况下将不完整的事务执行数据纠正，redo日志记录事务执行后的状态。

   ​ 当事务开始后，redo log就开始产生，并且随着事务的执行不断写入redo log file中。redo log file中记录了xxx页做了xx修改的信息，我们都知道数据库的更新操作会在内存中先执行，最后刷入磁盘。

   ​ redo log就是为了恢复更新了内存但是由于宕机等原因没有刷入磁盘中的那部分数据。

2. 回滚日志（undo log）

   ​ undo log主要用来回滚到某一个版本，是一种逻辑日志。undo log记录的是修改之前的数据，比如：当delete一条记录时，undolog中会记录一条对应的insert记录，从而保证能恢复到数据修改之前。在执行事务回滚的时候，就可以通过undo log中的记录内容并以此进行回滚。

   ​ undo log还可以提供多版本并发控制下的读取（MVCC）

3. 二进制日志（bin log）

   ​ MySQL的bin log日志是用来记录MySQL中增删改时的记录日志。简单来讲，就是当你的一条sql操作对数据库中的内容进行了更新，就会增加一条bin log日志。查询操作不会记录到bin log中。bin log最大的用处就是进行主从复制，以及数据库的恢复。

4. 错误日志（error log）

   ​ error log主要记录MySQL在启动、关闭或者运行过程中的错误信息，在MySQL的配置文件my.cnf中，可以通过log-error=/var/log/mysqld.log 执行mysql错误日志的位置。

   ​ 通过MySQL的命令也可以获取到错误日志的位置

   show variables like "%log_error%";
   

5. 慢查询日志（slow query log）

   ​ 慢查询日志用来记录执行时间超过指定阈值的SQL语句，慢查询日志往往用于优化生产环境的SQL语句。可以通过以下语句查看慢查询日志是否开启以及日志的位置：

   show variables like "%slow_query%";

   慢查询日志的常用配置参数如下：

   slow_query_log=1 #是否开启慢查询日志，0关闭，1开启

   slow_query_log_file=/usr/local/mysql/mysql-8.0.20/data/slow-log.log #慢查询日志地址（5.6及以上版本）

   long_query_time=1 #慢查询日志阈值，指超过阈值时间的SQL会被记录

   log_queries_not_using_indexes #表示未走索引的SQL也会被记录

   ​ 分析慢查询日志一般会用专门的日志分析工具。找出慢SQL后可以通过explain关键字进行SQL分析，找出慢的原因。

6. 一般查询日志（general log）

   ​ general log 记录了客户端连接信息以及执行的SQL语句信息，通过MySQL的命令

   show variables like '%general_log%';

   可以查看general log是否开启以及日志的位置。

general log 可通过配置文件启动，配置参数如下：

general_log = on

general_log_file = /usr/local/mysql/mysql-8.0.20/data/hecs-78422.log

普通查询日志会记录增删改查的信息，因此一般是关闭的。

事务/锁/日志之间关系详解：

事务特性关联

首先概述下事务，核心特性就是ACID,分别是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• 一致性：

  ​ 「一致性」可以理解为我们使用事务的「目的」，而「隔离性」「原子性」「持久性」均是为了保障「一致性」的手段，保证一致性需要由应用程序代码来保证

• 原子性指的是：

  ​ 当前事务的操作要么同时成功，要么同时失败。原子性由undo log日志来保证，因为undo log记载着数据修改前的信息,比如我们要 insert 一条数据了，那undo log 会记录的一条对应的 delete日志。我们要 update 一条记录时，那undo log会记录之前的「旧值」的update记录,如果执行事务过程中出现异常的情况，那执行「回滚」。InnoDB引擎就是利用undo log记录下的数据，来将数据「恢复」到事务开始之前
  

• 持久性指的就是：

  ​ 一旦提交了事务，它对数据库的改变就应该是永久性的。说白了就是，会将数据持久化在硬盘上。

  ​ 持久性由redo log 日志来保证，当我们要修改数据时，MySQL是先把这条记录所在的「页」找到，然后把该页加载到内存中，将对应记录进行修改。MySQL引入了redo log，内存写完了，然后会写一份redo log，这份redo log记载着这次在某个页上做了什么修改，即便MySQL在中途挂了，我们还可以根据redo log来对数据进行恢复。

  ​ redo log 是顺序写的，写入速度很快。并且它记录的是物理修改（xxxx页做了xxx修改），文件的体积很小，恢复速度也很快。
  

• 隔离性指的是：

  ​ 在事务「并发」执行时，他们内部的操作不能互相干扰。如果多个事务可以同时操作一个数据，那么就会产生脏读、重复读、幻读的问题

  ​ 于是，事务与事务之间需要存在「一定」的隔离。在InnoDB引擎中，定义了四种隔离级别供我们使用：

​ 不同的隔离级别对事务之间的隔离性是不一样的（级别越高事务隔离性越好，但性能就越低），而隔离性是由MySQL的各种锁来实现的(画重点--隔离机制实现了隔离性，隔离机制的实现是由mysql各种锁实现的)，只是它屏蔽了加锁的细节。

隔离级别及锁关系

​ 在InnoDB引擎下，按锁的粒度分类，可以简单分为行锁和表锁。行锁实际上是作用在索引之上的（索引上次已经说过了，这里就不赘述了）。当我们的SQL命中了索引，那锁住的就是命中条件内的索引节点（这种就是行锁），如果没有命中索引，那我们锁的就是整个索引树（表锁）。简单来说就是：锁住的是整棵树还是某几个节点，完全取决于SQL条件是否有命中到对应的索引节点。

​ 行锁又可以简单分为读锁（共享锁、S锁）和写锁（排它锁、X锁）

​ 读锁是共享的，多个事务可以同时读取同一个资源，但不允许其他事务修改。写锁是排他的，写锁会阻塞其他的写锁和读锁。

通过上面锁的划分，我们来看数据库的隔离级别

读未提交(Read uncommitted)-（异常情况：脏读/不可重复读/幻读）

​ 首先来说下read uncommit(读未提交)。比如说：A向B转账，A执行了转账语句，但A还没有提交事务，B读取数据，发现自己账户钱变多了！B跟A说，我已经收到钱了。A回滚事务【rollback】，等B再查看账户的钱时，发现钱并没有多。简单的定义就是：事务B读取到了事务A还没提交的数据，这种用专业术语来说叫做「脏读」。

​ 对于锁的维度而言，其实就是在read uncommit隔离级别下，读不会加任何锁，而写会加排他锁。读什么锁都不加，这就让排他锁无法排它了

​ 脏读在生产环境下肯定是无法接受的，那如果读加锁的话，那意味着：当更新数据的时，就没办法读取了，这会极大地降低数据库性能。在MySQL InnoDB引擎层面，又有新的解决方案（解决加锁后读写性能问题），叫做MVCC(Multi-Version Concurrency Control)多版本并发控制

​ MVCC下，就可以做到读写不阻塞，且避免了类似脏读这样的问题。那MVCC是怎么做的呢？

​ MVCC通过生成数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取

读已提交(Read committed) --（异常情况：不可重复读/幻读）

可重复读(Repeatable read) --（异常情况：幻读）

​ 针对于 read commit (读已提交) 隔离级别，基于上述的MVCC它生成的就是语句级快照，而针对于repeatable read (可重复读)，它生成的就是事务级的快照。

​ 前面提到过read uncommit隔离级别下会产生脏读，而read commit (读已提交) 隔离级别解决了脏读。思想其实很简单：在读取的时候生成一个”版本号”，等到其他事务commit了之后，才会读取最新已commit的”版本号”数据。

​ 比如说：事务A读取了记录(生成版本号)，事务B修改了记录(此时加了写锁)，事务A再读取的时候，是依据最新的版本号来读取的(当事务B执行commit了之后，会生成一个新的版本号)，如果事务B还没有commit，那事务A读取的还是之前版本号的数据,通过「版本」的概念，这样就解决了脏读的问题，而「版本」其实就是对应快照的数据。

​ read commit (读已提交) 解决了脏读，但也会有其他并发的问题。「不可重复读」：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改。例子：A查询数据库得到数据，B去修改数据库的数据，导致A多次查询数据库的结果都不一样【危害：A每次查询的结果都是受B的影响的】

​ 了解MVCC基础之后，就很容易想到repeatable read (可重复复读)隔离级别是怎么避免不可重复读的问题了（前面也提到了）。

​ repeatable read (可重复复读)隔离级别是「事务级别」的快照！每次读取的都是「当前事务的版本」，即使当前数据被其他事务修改了(commit)，也只会读取当前事务版本的数据。

可序列化(Serializable)

​ serializable (串行)隔离级别，它是最高的隔离级别，相当于不允许事务的并发，事务与事务之间执行是串行的，它的效率最低，但同时也是最安全的。

MVCC原理

MVCC的主要是通过read view和undo log来实现的

​ undo log前面也提到了，它会记录修改数据之前的信息，事务中的原子性就是通过undo log来实现的。所以，有undo log可以帮我们找到「版本」的数据

​ 而read view 实际上就是在查询时，InnoDB会生成一个read view，read view 有几个重要的字段，分别是：

• trx_ids（尚未提交commit的事务版本号集合）
• up_limit_id（下一次要生成的事务ID值）
• low_limit_id（尚未提交版本号的事务ID最小值）
• creator_trx_id（当前的事务版本号）

在每行数据有两列隐藏的字段，分别是：

• DB_TRX_ID（记录着当前ID）
• DB_ROLL_PTR（指向上一个版本数据在undo log 里的位置指针）

铺垫到这了，很容易就发现，MVCC其实就是靠「比对版本」来实现读写不阻塞，而版本的数据存在于undo log中。针对于不同的隔离级别（read commit和repeatable read），无非就是read commit隔离级别下，每次查询都获取一个新的read view，repeatable read隔离级别则每次事务只获取一个read view