MySQL系列7—事务

最新推荐文章于 2024-11-14 08:47:58 发布

12345sdfad

最新推荐文章于 2024-11-14 08:47:58 发布

阅读量210

点赞数 2

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/clyss1234/article/details/120518275

版权

MySQL 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

本文详细介绍了MySQL中的事务，包括ACID属性、隔离级别和事务的使用步骤，特别是如何利用redo log确保事务的持久性。此外，还深入探讨了MySQL的MVCC机制，解释了MVCC如何实现事务隔离，解决了脏读、不可重复读等问题，以及其底层实现和优缺点。最后，文章提及了MySQL的锁机制，包括行锁、表锁、页锁以及各种锁类型，阐述了锁在防止幻读方面的作用，并简要讨论了死锁和加锁原则。

摘要由CSDN通过智能技术生成

1. 事务

1.1 概念

事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，在MySQL中，默认一条语句就是一个事务单元，除非关闭事务自动提交。
存储引擎：mysql中的数据通过何种技术存储在文件（或内存）中，通过show engines查看mysql支持的引擎，innodb支持事务。

1.2 ACID属性

事务满足以下四个属性：

原子性（Atomicity）：事务是一个不可分割的工作单位，要么做完要么不做
一致性（Consistency）：事务必须使数据库从一个一致性状态变化到另一个一致性状态：
隔离性（isolation）：一个事务的执行不能被其他事务干扰，并发的各个事务之间不能互相干扰
持久性（Durability）：一个事务一旦被提交，它对数据库中数据的改变是永久性的。

1.3. 隔离级别

当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性，在介绍数据库提供的各种隔离级别之前，我们先看看如果不考虑事务的隔离性，会发生的几种问题：

脏读：两个事务T1读取了已被T2更新但还没有提交的字段，之后若T2回滚，则T1读取的内容是临时且无效的
不可重复读：T1读取一个字段，T2更新该字段，T1再读同一字段，值就不同了
幻读：T1从表中读取了一个字段，T2插入新行，T1再读同一个表则多出几行了

为了解决上面三种问题，数据库MySQL为我们提供了四种隔离级别：

READ UNCOMMITETD(读未提交数据，最低隔离级别);
READ COMMITTED(读已提交数据，可避免脏读);
REPEATABLE READ(可重复读，默认，可避免脏读不可重复读);
SERIALIZABLE(串行化，均可避免);

select @@tx_isolation       #查看当前的隔离级别：
set session/globals transaction isolation level XXXXXX; # 更改隔离级别：

savepoint 节点名  # 设置保存点
rollback 保存点名 # 回滚到保存点

1.3. 演示事务的使用步骤

隐式事务：事务没有明显的开启和结束标记，如insert，update，delete语句
显式事务：事务具有明显的开启和结束标记前提：必须先设置自动提交功能为禁用
- 步骤1：开启事务 set autocommit=0；start transaction；（可选的）
- 步骤2：编写事务中的sql语句（select insert update delete ）
- 步骤3：提交事务 commit；提交事务 rollback；回滚事务

一般步骤

BEGIN;
UPDATE student SET Sdept = "2222222222" WHERE Sno = 1;
UPDATE student SET Sdept = "2222222222" WHERE Sno = 2;
COMMIT;

1.5 redo log保证事务的持久性

MySQL的innoDB存储引擎，使用Redo log保证了事务的持久性。当事务提交时，必须先将事务的所有日志写入日志文件进行持久化，就是我们常说的WAL(write ahead log)机制。这样才能保证断电或宕机等情况发生后，已提交的事务不会丢失，这个能力称为 crash-safe。

MySQL中有三种日志，分别是

redo log 是存储引擎层(innodb)生成的物理日志，主要为了保证数据的可靠性；
bin log 是 MySQL 数据库层面上生成的日志，主要用于 point in time 恢复和主从复制。
undo log 主要用于事务的回滚(undo log 记录的是每个修改操作的逆操作) 和一致性非锁定读

redo log 和 undo log 都是存储引擎层面上生成的日志，并且都记录了数据的修改：只不过 redo log记录的是"物理级别"上的页修改操作，比如页号xxx、偏移量yyy写入了’zzz’数据；而undo log 记录的是逻辑操作日志，比如对某一行数据进行了INSERT语句操作，那么 undo log就记录一条与之相反的DELETE操作。

MySQL作为一个存储系统，为了保证数据的可靠性，最终得落盘。但是，又为了数据写入的速度，需要引入基于内存的"缓冲池"。其实不止MySQL，这种引入缓冲来解决速度问题的思想无处不在。既然数据是先缓存在缓冲池中，然后再以某种方式刷新到磁盘，那么就存在因宕机导致的缓冲池中的数据丢失，为了解决这种情况下的数据丢失问题，引入了redo log。在其他存储系统，比如Elasticsearch中，也有类似的机制，叫translog。

执行流程：

1、执行器先从引擎中找到数据，如果在内存中直接返回，如果不在内存中，查询后返回

2、引擎获取数据，进行更改，并将操作记录到redo log buffer中

3、引擎将数据更新到内存，同时写数据到redo log中，此时redo log处于prepare阶段，并通知执行器执行完成，随时可以操作

4、执行器生成这个操作的binlog

5、执行器调用引擎的事务提交接口，引擎把刚刚写完的redo改成commit状态，更新完成

为什么需要两阶段提交：

这里它的2阶段是对应于不同类型的日志，所以两阶段为的就是让这个2个不同的日志做好处理与准备。

prepare阶段，写redo log
commit阶段，写binlog并且将redo log的状态改成commit状态；

一致性保证：

mysql发生崩溃恢复的过程中，会根据redo log日志，结合 binlog 记录来做事务回滚：

如果redo log 和 binlog都存在，逻辑上一致，那么提交事务；
如果redo log存在而binlog不存在，逻辑上不一致，那么回滚事务；

2. MySQL的MVCC

前面提到了事务和事务的隔离级别，那么四种隔离级别是怎么实现的呢？

RU是无法实现保证线程安全，因此不用实现，而RC和RR是通过MVCC机制实现的，SE则是通过锁机制实现的。

2.1 MVCC是什么？

MVCC（multiversion concurrency controll）是一种多版本并发控制机制，大家知道，锁机制能够控制并发操作，但是其系统开销大，而MVCC可以在大多数的情况下代替行锁，从而降低系统开销。

在学习MVCC多版本并发控制之前，我们必须先了解一下，什么是MySQL InnoDB下的当前读和快照读?

2.1.1 当前读

像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

2.1.2 快照读

快照读就是当前读取的并不是记录的最新版本，而可能是之前某几个版本的快照，是的，MySQL引入了快照作为版本控制工具，快照读的实现正是基于多版本并发控制，即MVCC

MVCC是通过保存数据在某个时间点的快照来实现的。不同存储引擎的MVCC实现是不同的，典型的有乐观并发控制和悲观并发控制。当我们创建表完成后，mysql会自动为每个表添加

数据版本号db_trx_id （最后更新数据的事务id）
删除版本号 db_roll_pt （数据删除的事务id），

事务id由mysql数据库自动生成，且递增。

2.1.3 MVCC的优点

多版本并发控制（MVCC）正是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。所以MVCC可以为数据库解决以下问题

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

2.2 MySQL的底层实现

MVCC的目的就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志，Read View 来实现的。所以我们先来看看这个三个point的概念

隐式字段：

在数据库底层数据结构中，每条记录不仅有用户定义的字段，还有数据库隐式定义的三个字段：

DB_TRX_ID
6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
DB_ROLL_PTR
7byte，回滚指针，用于配合undo日志,指向这条记录的上一个版本（存储于rollback segment里）
DB_ROW_ID
6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了（deleted_bit）

在这里插入图片描述

undo日志：

undo日志中记录的是数据记录的历史版本，记录也含有更新其的事务ID号。因此undo日志中可能存在某条记录的多个历史版本。

在了解undo日志前，我们先理解版本控制与数据备份的区别，

数据备份：对数据进行二进制层面的复制并保存，目的是数据的安全
版本控制：在并发下实现数据多个版本的管理和控制，目的是并发的安全

为了实现多版本并发控制，MySQL保存多个版本的记录到undo log中，便于在并发操作记录时实现读写安全。

比如说当前两个事务操作一条记录，

A事务进行了更新但没提交，undo log记录了更新前的数据，真实记录的隐式字段中的指针指向undo log中数据
此时B事务来读，是读真实记录还是undo log中的记录呢？肯定是后者
当A事务进行回滚时，则根据隐式字段中指针找到undo log中记录，进行恢复

Read view（读视图）：

前面我们提到，并发事务中，快照读读的可能是undo log中的记录，而undo log中可能有多条记录，到底读那条呢？此时就用到了读视图。

什么是Read View，说白了Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务ID值越大)，通过read view，我们可以知道当前事务能够看到哪个版本的数据

Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果不符合可见性（我猜是事务ID大小比较）则去undo log中取记录进行比较，直到找到可以看见的最新老版本。

记录	含义
m_ids	当前活跃的事务id列表
min_trx_id	当前系统中活跃的读写事务中最小的`事务id`
max_trx_id	表示生成`ReadView`时系统中应该分配给下一个事务的`id`值。
creator_trx_id	表示生成该`ReadView`的事务的`事务id`。

访问判断

判断	含义	访问
数据`trx_id` == `creator_trx_id`	当前事务修改的	可以访问
trx_id < min_trx_id	已经提交的事务修改的	可以访问
trx_id >= max_trx_id	之后产生的事务修改的	不可访问
min_trx_id <= trx_id <= max_trx_id	可能存在，需要在m_ids查找	存在则不可访问

如果当前数据版本不可访问时，沿着版本链找到下一个版本的数据，直到最后。如果还没找到说明查询不到该条记录。

既然MVCC是通过undo日志实现快照读的，那么undo日志会无限增长吗？如果不能怎么清理历史数据呢？

**purge线程：**清除undo log中的老版本记录

为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

在这里插入图片描述

2.3 MVCC实现事务隔离

2.3.1 事务隔离级别的实现

对于使用READ UNCOMMITTED隔离级别的事务来说，直接读取记录的最新版本即可
对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，就需要用到我们上边所说的版本链了，核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。
对于使用SERIALIZABLE隔离级别的事务来说，使用加锁的方式来访问记录

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

2.3.2 MVCC怎么解决脏读

使用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。它总是读取当前的最新版本的undo日志。

如果被访问版本的trx_id属性值小于m_ids列表中最小的事务id，表明生成该版本的事务在生成ReadView前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值大于m_ids列表中最大的事务id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。

因此，事务并不会读到未提交的数据，那么能不能解决不可重复读呢（结果被更新）？答案是不能，因为此事务中每次查询开始时都会生成一个独立的ReadView，多个查询使用的readview不同，读到的结果肯定也就不同了

2.3.3 MVCC怎么解决不可重复读

对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成了。所以它总是能读到最开始读的那个版本，所以就解决了不可重复读的问题。

3. MySQL的锁机制

在事务并发时，还存在幻读，幻读产生原因是事务修改了其他数据，而MVCC只是实现了记录的并发版本控制，所以需要一种新的机制来解决幻读，也就是下面要说的锁机制。

我们知道，MySQL数据在存储中是分为区和页，因此为了实现更高效的锁机制，MySQL的InnD引擎引入了多种锁：行锁，表锁。而BDB还引入了页锁。

锁到底锁住了什么？

实际上，数据库中的锁是锁住索引项实现的！对于innodb而言：

表锁锁住的是整个聚簇索引，如果未建立主键索引或唯一索引时，锁住MySQL默认创建的索引
行锁锁住的是唯一索引或主键索引，当未定义此索引时，默认锁住整个表。

为什么MyISAM不支持行锁

前面提到，InnoDB和MyISAM在底层数据结构上是不同的，InnoDB索引和数据存储在一个文件中，而MyISAM索引存储的是指向真实物理地址的指针。

因此在InnoDB中，可以通过锁住索引项来封锁数据，而MyISAM不行（可能存在多个索引指向通一个物理地址）。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及的数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁，我们需要显式地加上共享锁或排它锁。

3.1 行锁、表锁、页锁

行锁：行级锁是mysql中锁定粒度最细的一种锁。表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。使用行级锁的开销大，加锁慢，会出现死锁。发生锁冲突的概率最低，并发度也最高。
表锁：表级锁是mysql中锁定粒度最大的一种锁，表示对当前操作的整张表加锁。它实现简单，资源消耗较少，被大部分mysql引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）
页锁：页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。BDB 支持页级锁。

3.2 共享锁、排他锁、意向锁

共享锁：共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据（都要加共享锁），但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
排他锁：又称写锁、独占锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
意向锁：表明“某个事务正在某些行持有了锁、或该事务准备去持有锁”，意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。比如事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求

select * from xxx lock in share mode 	# 行锁
select…for update		# 排他锁

3.3 记录锁、间隙锁、临键锁

记录锁

当我们用范围条件而不是相等条件索引数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，这就是记录锁；

例如，SELECT c1 FROM t WHERE c1 = 10 For UPDATE;会对c1=10这条记录加锁，为了防止任何其他事务插入、更新或删除c1值为10的行。

间隙锁

对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”枷锁，这种锁机制就是所谓的间隙锁（gap锁）。

间隙锁存在的主要目的就是为了防止在可重复读（记录锁保证了可重复读）的事务级别下，出现幻读（有新的插入或删除）问题。

例如，如果选择所有大于10的值进行更新，间隙锁将阻止另一个事务插入大于10的新值。

临键锁

Next-Key锁是索引记录上的记录锁和索引记录之前间隙上的间隙锁的组合。

非唯一索引Gap锁实例：

非唯一索引指的是可以有相同的key值，比如在下面插入6,k，原则上允许插入，但并发下不允许

比如，我要查询这个6，我就必须对3的next指针和6的next指针加锁，这样就不会插入/删除数据了。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IOMHpFNA-1632750607087)(…/…/…/images/MySQL并发间隙锁.PNG)]

范围检索gap锁原则分析：

另一种会出现gap锁的情况就是使用索引时，用到范围检索，就会出现gap 锁。

如果where条件查询的目标全部命中数据库表中的数据存在，如select *，则不会加gap锁，只会加记录锁
如果部分命中则会加gap锁，避免由于插入或删除导致出现幻读，会将所有的未命中区间加锁。

select * from gap_tbz where id > 5 and id < 11 for update;
# gap锁区间是左开右闭，也就是(5,11]，

Repeatable Read能解决幻读

很多人看过网上的关于数据库事务级别的介绍，会认为MySQL中Repeatable Reads能解决不可重复读的问题，但是不能解决幻读，只有Serializable才能解决。但其实，这种想法是不对的。

因为MySQL跟标准RR不一样，标准的Repeatable Reads确实存在幻读问题，但InnoDB中的Repeatable Reads是通过next-key lock解决了RR的幻读问题的。

因为我们知道，因为有了next-key lock，所以在需要加行锁的时候，会同时在索引的间隙中加锁，这就使得其他事务无法在这些间隙中插入记录，这就解决了幻读的问题。

3.4 死锁

上文讲过，MyISAM表锁是不存在死锁的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待。

但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，当两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。

死锁检测

在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当InnoDB检测到系统中产生了死锁之后，InnoDB会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。（一般通过两个事务操作的数据量大小判断）