Mysql数据库事务的实现方式及并发相关

Mysql数据库事务的实现方式及并发相关

参考资料：

MySQL分区：https://www.bilibili.com/video/BV1E7411q7Nx

MySQL_基础+高级篇- 数据库 -sql -mysql教程_mysql视频_mysql入门_尚硅谷：https://www.bilibili.com/video/BV12b411K7Zu

实体书籍：数据库事务并发处理的艺术 事务管理与并发控制 李海翔 等著

博文中部分图片和文字源自网络。部分文字，因学习时记录的笔记时间已久远，无法找到出处，侵删。

---

不论是面试还是正常工作，sql因为其语言本身的阅读和书写性非常贴近人类语言的语法，所以面试的时候，并没有什么好问的，最多是写写sql多表查询或者子查询这种量级的sql语句，重点基本都集中在一下几个部门了：

ACID都是怎么实现的（其实就是变着花样问MVCC（Mysql使用快照隔离实现）、封锁技术、log（Redo/undo））；

大表优化（索引，分区，分表）

基本是从sql调优和Mysql的InnoDB的底层入手，展开各种各样的概念或者场景问题。

---

思维导图

 

 

 

目录

1，什么是事务

2，事务的特性

2.1 A原子性

2.2 C一致性

2.3 I隔离性

2.4 D持久性

3，锁机制

5，MVCC

6，log日志

6.1 undo log

6.2 redo log

---

1，什么是事务

Transaction 事务

现在我们假设几个场景：如果A向B转账。

第一步：A的钱数减去50元，第二步：B的钱数增加50元。

如果在第一步完成后，第二步开始前，断电了.......

再次开启时，A少了50，B并没有增加50，这找谁说理去。

为了数据库的安全性，提出了事务的概念。

 

事务的处理机制，就是要保证用户的数据操作对数据是“安全的”。

那么怎样才算是安全的，只有在带着ACID四个性质的事务处理机制是安全的。

那么ACID是如何实现的呢？是由MVCC，锁机制，log日志共同协作完成的。

 

事务相关的sql语句：

设置隔离级别：
set session transaction isolation level read uncommitted

开启事务：
begin / star transaction

提交事务：
commit

回滚：
rollback

保存点：
savepoint name

释放保存点：
release savepoint savepoint_name

事务回滚到保存点：
rollback to savepoint_name

设置禁止自动提交：
set autocommit = 0;//禁止自动提交
set autocommite = 1;//开启自动提交

只有DML语句才有事务的概念，DQL和DDL没有事务的概念

注意，当使用DDL语句的时候，整个表都会被锁住，无法使用DML语句进行修改

2，事务的特性

2.1 A原子性

事务要么成功，要么失败

背后的机制是undo log实现的，及逻辑日志，如果操作失败或事务未完成，即可进行回滚操作

 

2.2 C一致性

数据库中的数据从一个一致性状态转变成另一个一致性状态

事务除了ACID还有三个重要的属性：

可串行化：从理论上保证了并发事务的调度等价于一个串行调度，用串行结果必然，满足一致性来表示并发事务的调度带来的结果也是满足于一致性的。

可恢复性：事务不会读到其他事务未提交的内容（避免脏读），那么也可以引申为，事务的提交顺序对数据的一致性没有影响

严格性：发送写操作的事务提交或终止操作高于其他

 

2.3 I隔离性

事务和事务之间不会相互影响

隔离级别是由锁作为底层进行实现的，但是并发太低，此时使用快照隔离实现的MVCC（多版本并发控制）,来提高并发

所以在锁机制（隔离级别）和MVCC的共同作用下，实现了数据库的隔离性。

 

2.4 D持久性

事务对数据库的修改是持久的

是Redo log机制确保了这个性质，因为数据都是保存在磁盘中的，如果每次commit都去读写磁盘，那么一定会影响程序的并发和效率，所以有了Redo log日志保证数据的持久性。

3，锁机制

那么既然提出了事务的概念，我们看看事务在执行过程中有没有什么问题。

如果所有的事务都是串行执行的，那么就不会有任何的问题了，但是这样效率很低

如果事务并发执行，在没有任何包含措施下，是否还能有ACID的性质，是否还安全。

比如事务A读取了a，为1，并修改a = 10，此时事务B执行，读取a进行后续操作处理，如果事务Arollback，a变成了以前的值1，

事务B拿到的a确是10，这种现象就脏读，a为脏数据。

再看看下面这种情景：

事务A读取a = 1，事务B修改了a，令a = 10，事务A再次读取a时，发现a的值变了，这种现象称为不可重读，显然破话了隔离性和一致性。

这只是读取一行数据的情况。

如果事务A读取了N行，此时事务B出现，在A查询的范围内，增加或删除几行，事务A再次读取，读到了N+k（k<0||k>0）行

仿佛出现了幻觉，这叫幻读。

显然我们是不想看到上面几种情况的出现的，那么此时就出现了锁机制。

读锁和写锁，在锁机制中，读-读并发，读-写，写-读，写-写都是阻塞的。

InnoDB使用锁机制，创造了隔离级别的概念，如下图所示：

当然了，在开启事务后，我们也可以自己去对要操作的内容进行上锁操作，sql语句如下：

InnoDB
读锁（共享锁）
select ..... lock in share mode
写锁（排它锁）
select ..... for update

MyISAM
读锁 
lock table_name Read
写锁
lock table_name Write
读锁
unlock tables

在RR隔离级别下，不会产生幻读，脏读，不可重读

不论是读锁还是写锁，统称为record lock，记录锁

锁住了目前已经读取的数据，在RR隔离级别下还会产生gap lock，即间隙锁

比如现在查到的数据有5,8,10

那么gap lock会自动锁住（负无穷，5），（5,8），（8,10），（10，正无穷）

这几个区域，其他事务无法插入数据

那么record lock 和 gap lock，共同组成了Next key lock，锁住了全部的数据，及包含区间也包换查到的数据

以此隔绝了幻读

 

锁机制解决了并发事务产生的种种问题，但是在锁机制的情况下，只有读-读是并发的

读-写，写-读，写-写都是阻塞的。

这样的并发效率并不高，所以引入了MVCC机制，来提高事务的并发。

可以将封锁机制认为是一种悲观锁机制，MVCC是一种乐观锁机制。

5，MVCC

MVCC全称：多版本并发控制

实现MVCC的方式很多，InnoDB使用快照隔离，来到达多版本并发控制的需求

快照隔离技术中的事务中的所有读操作，读到的数据一定是一致的

只要事务不发生写-写冲突，就会提交成功，并发效率高

从事务开始，处于当时的并发事务的状态被保存，利用这个快照可以判断本事务和其他事务之间启动的先后顺序，事务的读写数据情况，已确定是否存在写-写冲突

具体的实现手段：

为每个对象（事务）在写操作发生时，生成一个新的版本，在读操作发生时，读出最近的一个版本

（在锁机制的基础上，MVCC的读到的版本，和隔离级别有关系）

一个元祖就拥有很多的版本，每个事务读到的元祖版本的不一样的，相当于并发事务操作不同的元祖对象

使得不同事务对同一个数据项的读操作可以根据其读时刻的快照作用在不同的版本上，所以可以解决读-写，写-读操作

 

所以对于InnoDB来说，MVCC会产生冗余

 

在InnoDB中

隔离级别大于等于 可重复读（RR）：事务块内的所有select操作都要使用同一个快照，此快照是在第一个select操作时建立

隔离级别大于等于 读可提交（RC）：事务块内的所有select操作分别建立自己的快照，因此每次读都不同

6，log日志

只有InnoDB引擎下，才有unod log和redo log这两种日志，因为MyISAM不支持事务，没有必要。

6.1 undo log

undo log为一种逻辑日志

提供了回滚和多版本并发控制

undo log的具体工作机制：

在数据修改的时候，不仅记录了redo log日志，还会记录响应的undo log日志

如果因为某些原因导致事务失败，或者需要回滚，可以借助该undo进行回滚

可以认为当delete一条记录时，undo log中会记录一条对应的insert记录

当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚

 

undo log和MVCC的关系：

当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息

让用户实现非锁定一致性读取

这也就是可以在不同隔离级别下，读写和写读并发的时候，select读取的快照版本不一样的原因

 

在事务提交后，会将undo log放入删除队列中，后续在进行删除

6.2 redo log

不同于undo log是逻辑日志，redo log是物理日志

在概念层面，InnoDB通过force log at commit机制实现了事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志

写入磁盘上的redo/undo log file中进行数据持久化修改。

 

可以将这个过程理解为，高并发的时候，不可能实时去更新磁盘中的数据，磁盘IO效率低下，会影响高并发的响应时间

所以先写在red log上，然后red log有自己的机制去进行刷盘，将log buffer中/OS buffer中的redo log刷进磁盘的log file中。

 

一共有三种刷盘方式：

0：当设置为0的时候，事务提交时，先存放在log buffer中，每秒从log buffer写入 os buffer并调用fsync()写入到log file on disk中，也就是说设置为0时是每秒刷新写入到磁盘中，当系统崩溃，会丢失1秒的数据。

1：当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。

这种方式即使系统崩溃也不会丢失任何数据，但是因为提交都写入磁盘，IO的性能较差:

 2：当设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk

 

log刷盘规则

1：发出commit动作时

2：每秒刷一次，这个刷日志频率和commit动作无关

3：当log buffer中已经使用的内存超过一半时

4：当有checkpoint时，checkpoint在一定程度上代表了刷到磁盘时日志所处的LSN位