MySQL不仅是CRUD

最新推荐文章于 2024-07-21 16:18:44 发布

w7486

最新推荐文章于 2024-07-21 16:18:44 发布

阅读量226

点赞数 2

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

本文链接：https://blog.csdn.net/w7486/article/details/119799021

版权

面试专栏收录该内容

8 篇文章 1 订阅

订阅专栏

本文详细解读MySQL事务的特性、隔离级别、事务处理方法，介绍事务隔离中的MVCC概念，以及Mysql的锁机制（包括共享锁、排他锁、意向锁等），深入剖析MVCC中的隐式字段和快照读，最后讲解Redo/Undo日志在事务持久性和一致性中的关键作用。

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

一.事务:

事务是访问并更新数据库各种数据项的一个程序执行单元。
事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成;

一组sql语句组成的数据逻辑处理单元，要么全部执行成功，要么全部执行失败。举一个最经典的例子就是银行取钱，不能你取钱失败，但账户扣钱成功，也不能你取钱成功，但账户余额不足扣钱失败。则两个操作要么全成功，要么就全失败，保证数据的一致性。

1.MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

对于一个MYSQL数据库（InnoDB），事务的开启与提交模式无非下面这两种情况：

1>若参数autocommit=0，事务则在用户本次对数据进行操作时自动开启，在用户执行commit命令时提交，用户本次对数据库开始进行操作到用户执行commit命令之间的一系列操作为一个完整的事务周期。若不执行commit命令，系统则默认事务回滚。总而言之，当前情况下事务的状态是自动开启手动提交。

2>若参数autocommit=1（系统默认值），事务的开启与提交又分为两种状态：

①手动开启手动提交：当用户执行start transaction命令时（事务初始化），一个事务开启，当用户执行commit命令时当前事务提交。从用户执行start transaction命令到用户执行commit命令之间的一系列操作为一个完整的事务周期。若不执行commit命令，系统则默认事务回滚。

②自动开启自动提交：如果用户在当前情况下（参数autocommit=1）未执行start transaction命令而对数据库进行了操作，系统则默认用户对数据库的每一个操作为一个孤立的事务，也就是说用户每进行一次操作系都会即时提交或者即时回滚。这种情况下用户的每一个操作都是一个完整的事务周期。

2.事务的特性：

在Mysql中事务的四大特性主要包含：原子性（Atomicity）、一致性（Consistent）、隔离性（Isalotion）、持久性(Durable)，简称为ACID。

原子性:指事务的原子性操作，即对数据的修改要么全部执行成功要么全部执行失败。是基于日志的Undo机制。
一致性:指执行事务前后要状态要一致，可以理解成数据的一致性。
隔离性:隔离性侧重指事务之间相互隔离，不受影响，这个与事务设置的隔离级别有密切的关系。
持久性:指在一个事务提交后，这个事务的状态会被持久化到数据库中。是基于日志的Redo机制。
原子性、隔离性、持久性都是为了保障一致性而存在的，一致性也是最终的目的。

3.事务隔离级别：

在Mysql中事务的隔离级别分为四大等级，读未提交（READ UNCOMMITTED）、读提交（READ COMMITTED）、可重复读（REPEATABLE READ）、串行化（SERIALIZABLE）。MySql默认的隔离级别是可重复读。

读未提交:可以读取另一个事务没有提交的数据，可能会产生脏读问题。脏读问题:当一个事务A正在对数据进行更改，但并没有提交到数据库中，而这时另一个事务B,访问了该数据，那么它读到的就是一个脏读数据。

读已提交:读取最新数据。解决了脏读问题，但还会存在不可重复读的问题。不可重复读:当事务A多次访问一个数据，而在这期间事务B对该数据进行了更改，就会导致事务A多次读取同一数据时，数据不一致。

可重复读:读取事务开启前的数据。解决了脏读和不可重复读，但可能会产生幻读。幻读:当事务A想插入一条id=100的学生信息，在插入前它应该查询id=100信息是否已经存在。select过后发现不存在。这时候事务A就知道自己可以插入。但这时事务B插入了一条id=100的信息，并提交了事务，当事务A插入的时候就会报主键冲突。事务A再select一下，发现id=100数据行已经存在，这就是幻读。

如何解决幻读问题:在select语句后手动加意向共享锁select....lock in share mode。加了锁之后会阻塞其他事务对该行进行添加数据，但是自己可以对该行进行添加。

串行化:串行化的执行流程相当于把事务的执行过程变为顺序执行.

4.隔离操作基本命令:

查看当前会话隔离级别        select @@tx_isolation;
查看系统当前隔离级别        select @@global.tx_isolation;
设置当前会话隔离级别        set session transaction isolatin level repeatable read;
设置系统当前隔离级别        set global transaction isolation level repeatable read;
命令行，开始事务时        set autocommit=off 或者 start transaction

这四大等级从上到下，隔离的效果是逐渐增强，但是性能却是越来越差。因为涉及到了加锁问题。

在四个隔离级别中加锁肯定是要消耗性能的，而读未提交读不加锁，写加锁，所以对于它来说也就是没有隔离的效果，所以它的性能也是最好的。
对于读提交和可重复读，他们俩的实现是兼顾解决数据问题，然后又要有一定的并发性，所以在实现上锁机制会比串行化优化很多，提高并发性，所以性能也会比较好。可重复读，写加锁，读不加锁，MVCC,读取事务开始前数据；读已提交，写加锁，读不加锁,MVCC,读取最新数据。
对于串行化加的是一把大锁，读的时候加共享锁，不能写，写的时候，家的是排它锁，阻塞其它事务的写入和读取，若是其它的事务长时间不能写入就会直接报超时，所以它的性能也是最差的，对于它来就没有什么并发性可言。

二.Mysql的锁机制

1.有哪几种锁

共享锁/读(select)锁（Shared Locks）行锁，对某一行加锁

排他锁/写(update,delete)锁（Exclusive Locks）行锁，对某一行进行加锁

意向共享锁表锁，对表中某几行进行加共享锁

意向排他锁表锁，对表中某几行进行加排他锁

2.锁的兼容性:

意向锁之间相互兼容，排他锁也就是写锁与所有锁冲突，共享锁兼容意向共享锁

3.锁的实现

举例:一个表中有id为1， 3，7， 9这四条数据。

Record Lock

记录锁，单个行记录上的锁;

记录锁用来实现共享锁和排他锁是给B+树的索引加锁

Gap Lock

间隙锁，锁定一个范围，但不包含记录本身:全开区间;REPEATABLE READ级别及以上支持间隙锁;
如果 REPGATABLE READ修改innodb_locks_unsafe_for_binlog = 0,那么隔离级相当于退化为READ COMMITTED

间隙锁可以解决幻读问题，当我们查询where id > =3的数据时加间隙锁就相当与在(3, 7)(7, 9)(9,无穷大)加了锁

Next-Key Lock

记显锁+间隙锁，锁定一个范围并且锁住记录本身，左开右闭;只有在rr级别下才可以加

lnsert lntention Lock

插入意向锁，insert操作的时候产生;在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。
假设有一个记录索引包含键值4和7，两个不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

[3,7)[7, 9)[9,无穷大)

AUTO-INC Lock

自增锁，是一种特殊的表级锁，发生在AUTO_INCREMENT约束下的插入操作;采用的一种特殊的表锁机制;完成对自增长值插入的SQL语句后立即释放;在大数据量的插入会影响插入性能，因为另一个事务中的插入会被阻塞;从MySQL 5.1.22开始提供一种轻量级互斥量的自增长实现机制，该机制提高了自增长值插入的性能;

当我们插入时会采用哪种锁?若是自增插入就采用自增锁,当插入时就会把表锁起来，插入完成会立马释放，大量数据插入时性能很差；当我们在上述例子中插入id=8的数据时就会采用插入意向锁，当插入成功时会在插入行加上排他锁，但自增锁不会。

三.MVCC

1.什么是MVCC

多版本并发控制;用来实现一致性的非锁定读;非锁定读是指不需要等待访问的行上X锁的释放;
在read committed和repeatable read下，innodb使用MVCC;然后对于快照数据的定义不同;在reacommitted隔离级别下，对于快照数据总是读取被锁定行的最新一份快照数据;而在repeatable read 隔离级别下，对于快照数据总是读取事务开始时的行数据版本;

实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。

2.隐式字段

每行记录除了我们自定义的字段外，还有数据库隐式定义的 DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID 等字段

DB_TRX_ID

6 byte，最近修改(修改/插入)事务 ID：记录创建这条记录/最后一次修改该记录的事务 ID

DB_ROLL_PTR

7 byte，回滚指针，指向这条记录的上一个版本（存储于 rollback segment 里）

DB_ROW_ID

6 byte，隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以DB_ROW_ID产生一个聚簇索引实际还有一个删除 flag 隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除 flag 变了

DB_ROW_ID 是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID 是当前操作该记录的事务 ID ,而 DB_ROLL_PTR 是一个回滚指针，用于配合 undo日志，指向上一个旧版本.

3.什么是当前读和快照读

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

当前读

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

快照读

像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即 MVCC ,可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

说白了 MVCC 就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现

思考:为什么读取快照数据不需要上锁?
因为没有事务需要对历史的数据进行修改操作;

四.日志的`Redo/Undo`机制

1.undo日志

undo日志用于存放数据修改被修改前的值，假设修改 tba 表中 id=2的行数据，把Name=’B’ 修改为Name = ‘B2’ ，那么undo日志就会用来存放Name=’B’的记录，如果这个修改出现异常，可以使用undo日志来实现回滚操作，保证事务的一致性。

undo log 主要分为两种：

insert undo log：代表事务在 insert 新记录时产生的 undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
update undo log：事务在进行 update 或 delete 时产生的 undo log ; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除

purge

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

同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log 的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该 undo log 的节点可能是会 purge 线程清除掉，向图中的第一条 insert undo log，其实在事务提交之后可能就被删除丢失了。

2.redo日志

Redo log是InnoDB存储引擎所特有的一种日志，其他存储引擎没有这个日志功能。Redo log用来记录某数据块被修改后的值，可以用来恢复未写入 data file 的已成功事务更新的数据；

当数据库对数据做修改的时候，需要把数据页从磁盘读到buffer pool中，然后在buffer pool中进行修改，那么这个时候buffer pool中的数据页就与磁盘上的数据页内容不一致，称buffer pool的数据页为dirty page 脏数据，如果这个时候发生非正常的DB服务重启，那么这些数据还没在内存，并没有同步到磁盘文件中（注意，同步到磁盘文件是个随机IO），也就是会发生数据丢失，如果这个时候，能够在有一个文件，当buffer pool 中的data page变更结束后，把相应修改记录记录到这个文件（注意，记录日志是顺序IO），那么当DB服务发生crash的情况，恢复DB的时候，也可以根据这个文件的记录内容，重新应用到磁盘文件，数据保持一致。

这个文件就是redo log ，用于记录数据修改后的记录，顺序记录。它可以带来这些好处：

当buffer pool中的dirty page 还没有刷新到磁盘的时候，发生crash，启动服务后，可通过redo log 找到需要重新刷新到磁盘文件的记录；
buffer pool中的数据直接flush到disk file，是一个随机IO，效率较差，而把buffer pool中的数据记录到redo log，是一个顺序IO，可以提高事务提交的速度；

假设修改 tba 表中 id=2的行数据，把Name=’B’ 修改为Name = ‘B2’ ，那么redo日志就会用来存放Name=’B2’的记录，如果这个修改在flush 到磁盘文件时出现异常，可以使用redo log实现重做操作，保证事务的持久性。

举例:假如某个时刻数据库崩溃，在崩溃之前有事务A和事务B在执行，事务A已经提交，而事务B还未提交。当数据库重启进行 crash-recovery 时，就会通过Redo log将已经提交事务A改写到数据文件，而还没有提交的事务B就通过Undo log进行roll back。