M1 MySQL事务知识点的总结

标题：MySQL事务知识点的总结

参考资料

1 事务的基本概念

定义：访问和更新数据库的程序执行单元,包含有多条SQL语句，这些语句要么一起执行，要么都不执行。

事务是由存储引擎实现的
MySQL支持事务的存储引擎有InnoDB、NDB Cluster等，其他不支持。

1-1 事务的提交与回滚

提交（commit）:事物内部所有的SQL语句全部执行。
回滚(rollback): 即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态

1-2 手动开启事务和事务的自动提交

手动开启事务就是用start transaction与commit将语句包裹起来：

start transaction;
    单条或者多条SQL语句
commit;

事务的自动提交（见参考01）

情况1：当MYSQL中autocommit默认为1，即开启状态。
--这种情况下，即便用户没有使用start transcation/commit语句对，用户每一条insert/update等语句都会当作事务处理，并且会帮你自动执行，让操作的结果生效。
情况2：当MYSQL中autocommit设置为0.
--这种情况下，用户每一条语句;  加上  commit;   系统才会执行语句并让语句生效。如果没有commit语句，SQL语句生效。

2 衡量事务的四个特性

2-1 四个特性概述

名称	说明
原子性(Atomicity)	原子性是指一个事务是一个不可分割的工作单位
一致性(Consistency)	事务执行的过程要确保数据的更改满足数据表的约束(参考03)
隔离性(Isolation)	并发执行的事务之间不能相互干扰
持久性(Durability)	事务提交后，对数据库的改变是永久性的，不受故障(断电)和其他操作影响（其他事务）

几个注意点：

数据库仅仅只能提供约束方面的一致性，业务层面的一致性应该由用户自己实现。

2-2 MySQL原子性的实现原理（存储引擎支持undo log）

原理：原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

2-3 MySQL持久性的实现原理（存储引擎支持redo log）

问题1：为什么数据库需要考虑持久性问题？

脏页：内存数据页跟磁盘上数据页内容不一致，将内存页称为脏页

干净页：内存数据写入磁盘后，内存页跟磁盘页数据一致，称内存页为干净页

主要原因是由于缓冲池的存在，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲。当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。

问题2：内存中的数据还没有写入磁盘，机器断电了，如何保证持久性？

MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

问题3：为什么redo log写入速度比磁盘快？

刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。、
刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少

2-4 MySQL中日志

日志名称	应用
重做日志（redo log）	事务的持久性
回滚日志（undo log）	事务的原子性
二进制日志（binlog）	在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。
错误日志（errorlog）
慢查询日志（slow query log）	记录执行时间过长和没有使用索引的查询语句
中继日志（relay log）

问题：binlog与redolog的区别？

作用不同：redo log是保证事务的持久性的，是事务层面的，binlog作为还原的功能，是数据库层面的（当然也可以精确到事务层面的），虽然都有还原的意思，但是其保护数据的层次是不一样的。

内容不同：redo log是物理日志，是数据页面的修改之后的物理记录，binlog是逻辑日志，可以简单认为记录的就是sql语句

可以理解为数据和操作的区别，类似于redis的RDB与AOF的关系
恢复数据时候的效率，基于物理日志的redo log恢复数据的效率要高于语句逻辑日志的binlog

产生时间：

在事务的执行过程中，便开始写入磁盘的redo log文件中。
事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中

MySQL通过两阶段提交过程来完成事务的一致性的，也即redo log和binlog的一致性的，理论上是先写redo log，再写binlog，两个日志都提交成功（刷入磁盘），事务才算真正的完成。

3 MySQL事务隔离性的保证

3-1 三种读错误以及隔离级别

问题1：事务之间的并发执行对事务的读取会有怎样的影响？

可能会引发下面三种读的错误（注意都是发生在一个事务中）：

名称	说明
脏读	当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据）
不可重复读	在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读
幻读	在事务A中按照某个条件先后两次查询数据库，两次查询结果的记录条数不同

脏读与不可重复读区别：脏读读到的是其他事务未提交的数据（没有在磁盘上生效），后者读到的是其他事务已提交的数据。
可重复读与幻读的区别可以通俗的理解为：不可重复读是数据的值变了，幻读是数据的记录数变了（可能新增了数据）。

****事务的隔离级别依照上面三种读是否会发生，进行了以下划分**：

隔离级别	脏读	不可重复读	幻读	说明
读未提交(read uncommitted)	可能	可能	可能	效率提升有限，错误多，很少用
读已提交(read committed)	不可能	可能	可能
可重复读(repeatable read)	不可能	不可能	可能
串行化serializable	不可能	不可能	不可能	并发度低，很少用

注意：

默认的隔离级别是读已提交（如Oracle)或可重复读（后文简称RR）
InnoDB默认的隔离级别是RR，后文会重点介绍RR。需要注意的是，在SQL标准中，RR是无法避免幻读问题的，但是InnoDB实现的RR避免了幻读问题！！！！！！！！！！！！！！！

3-2 MySQL的事务隔离机制

机制	基本介绍	特点	针对问题
MVCC机制
锁机制（行锁，间隙锁）

问题：数据库中的快照读与当前读的区别(见参考14)？

二种读取方式可以看作MySQL事务隔离机制的延伸

读的方式	实现方式
当前读通过 next-key 锁(行记录锁+间隙锁)
快照读	MVCC(隐藏列，undo log版本链，Readview)

1）当前读（悲观思想）：读取的是最新数据，并且需要先获取对应记录的锁

当前读的语句：

# 查询语句
SELECT ... FOR UPDATE            # 排他锁(X锁)，行级别锁，能够获取数据库最新的数据
SELECT ... LOCK IN SHARE MODE    # 共享锁(S锁)，行级别锁，能够获取最新数据
# 更新，删除，插入操作
update 、delete 、insert

for update是一种行级锁，又叫排它锁，一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行．如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁
for update使用场景：为了避免自己看到的数据并不是数据库存储的最新数据并且看到的数据只能由自己修改，需要用 for update 来限制。

作用：一个给查找的数据上一个共享锁（S 锁）的功能，允许其他的事务对该数据上S锁，但是不能够允许对该数据进行修改

当前读的实现方式：通过 next-key 锁(行记录锁+间隙锁)来是实现的。

行锁（Record Lock）：锁直接加在索引记录上面。
间隙锁（Gap Lock）：是 Innodb 为了解决幻读问题时引入的锁机制，所以只有在 Read Repeatable 、Serializable 隔离级别才有。
Next-Key Lock ：Record Lock + Gap Lock，锁定一个范围并且锁定记录本身

2）快照读（普通读）（默认查询采用的方式），不使用FOR UPDATE与IN SHARE MODE的普通查询方式。

基本思想：普通读的执行方式是生成 ReadView，直接利用 MVCC 机制来进行读取，并不会对记录进行加锁。

特点：实现读不加锁

快照读实现方式：MVCC(隐藏列，undo log版本链，Readview)

4 MVCC的介绍

问题2：MySQL的隔离级别时可重复读，是如何避免幻读问题？（见参考02）

MVCC（Multi-Version Concurrency Control）最大的优点是读不加锁，因此读写不冲突，并发性能好。InnoDB实现MVCC，多个版本的数据可以共存，主要基于以下技术及数据结构：

1）隐藏列：InnoDB中每行数据都有隐藏列，隐藏列中包含了本行数据的事务id、指向undo log的指针等。

2）基于undo log的版本链：前面说到每行数据的隐藏列中包含了指向undo log的指针，而每条undo log也会指向更早版本的undo log，从而形成一条版本链。

3）ReadView(快照)：通过隐藏列和版本链，MySQL可以将数据恢复到指定版本；但是具体要恢复到哪个版本，则需要根据ReadView来确定。所谓ReadView，是指事务（记做事务A）在某一时刻给整个事务系统（trx_sys）打快照，之后再进行读操作时，会将读取到的数据中的事务id与trx_sys快照比较，从而判断数据对该ReadView是否可见，即对事务A是否可见。

问题3：MVCC的使用注意点？

MVCC在大多数情况下代替了行锁，实现了对读的非阻塞，读不加锁，读写不冲突。缺点是每行记录都需要额外的存储空间，需要做更多的行维护和检查工作

补充：

1.MVCC手段只适用于Msyql隔离级别中的读已提交（Read committed）和可重复读（Repeatable Read）。

2.Read uncimmitted由于存在脏读，即能读到未提交事务的数据行，所以不适用MVCC.

原因是MVCC的创建版本和删除版本只要在事务提交后才会产生。

3.串行化由于是会对所涉及到的表加锁，并非行锁，自然也就不存在行的版本控制问题。

4.通过以上总结，可知，MVCC主要作用于事务性的，有行锁控制的数据库模型。

5 MySQL中的锁机制

为什么需要关注锁？

实际业务场景下，数据库中的加锁情况是业务优化的一个重要方向。我们希望该加锁的时候加锁，避免不恰当的加锁，影响数据库性能，比如
1）数据库中行锁频繁的升级为表锁（表锁通常我们不希望其发生，很恐怖）。
2）数据库中应用层有着大范围的数据查询（间隙锁也会影响性能）

4-1 锁的分类

按照锁的范围划分：

名称	特点	适用场景
表级锁	开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低	适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用
行级锁	开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高	适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用
页面锁	开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

补充：

上述表格中的每种锁从共享的角度可以划分为共享锁与排他锁。
乐观锁与悲观锁是锁使用的思想，并不是具体的锁。

4-2 表级锁定

MySQL数据库的存储引擎

表锁的模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）

MyISAM的表锁的特点：

1）MyISAM引擎区别与Innodb仅支持表锁，不支持行锁。

2）MyISAM的表锁分为表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock），特点如下

a) 对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
b) 对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；
c) MyISAM表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

Innodb的表锁的特点：

1）也支持共享

2）InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁

4-3 行级锁定

为什么需要行锁？

1）多个事务对单行数据更新时，采用读锁避免脏读(不会读到其他事务没有提交的数据)与采用写锁避免不可重复读的发生(当前事务执行的过程中其他事务线程都得阻塞)。
2）当前读情况下通过索引进行范围查询统计数据的数目时，需要借助间隙锁避免幻读问题

Innodb的支持的行锁

锁	对应语句
排他锁（X锁，MyISAM 叫做写锁）	FOR UPDATE
共享锁（S锁，MyISAM 叫做读锁）	LOCK IN SHARE MODE

问题：间隙锁(Gap Locks)与next-key lock的概念辨析？

为什么需要间隙锁与next-key lock？

目的：解决当前读的幻读问题，快照读由于MVCC机制是不会产生幻读问题的。

具体原因：幻读的问题存在是因为新增或者更新操作，这时如果进行范围查询的时候（加锁查询），会出现不一致的问题，这时使用不同的行锁已经没有办法满足要求，需要对一定范围内的数据进行加锁，

具体案例：

间隙锁则分为两种：Gap Locks和Next-Key Locks。

Gap Locks会锁住两个索引之间的区间，比如select * from User where id>3 and id<5 for update，就会在区间（3，5）之间加上Gap Locks。
Next-Key Locks是Gap Locks+Record Locks形成闭区间锁select * from User where id>=3 and id=<5 for update，就会在区间[3,5]之间加上Next-Key Locks。

总结：MySQL在PR机制下对于幻读的处理策略如下

情况	方法
加锁情况（行级锁）	next-key lock
不加锁情况	MVCC机制确保读取都是同一份快照，不会产生幻读问题

个人理解：间隙锁以及next-ley lock有点类似于页面级的锁（多条记录但不是整个表，具有一个范围），不过目前没有看到这种叫法，待考证。

4-4 MySQL的死锁问题

知识点1：Innodb中主键索引与聚簇索引的区别？

有主键的情况下 , 主键就是聚簇索引
没有主键的情况下 , 第一个非空null的唯一索引就是聚簇索引
如果上面都没有 , 那么就是有一个隐藏的row-id作为聚簇索引

DDL：Data Definition Languages 数据定义语言，用来维护数据库对象,对数据库内部的对象进行创建、删除、修改的操作语言。
常用的语句关键字主要包括 create、drop、alter等
DML:Data Manipulation Language 数据操纵语句
常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查）
DCL: Data Control Language 数据控制语句
主要的语句关键字包括 grant、revoke 等。