【Mysql 探究事务及底层原理】简单理解

最新推荐文章于 2023-07-12 23:04:59 发布

积善之家、必有余庆

最新推荐文章于 2023-07-12 23:04:59 发布

阅读量255

点赞数

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

本文链接：https://blog.csdn.net/qq_44682003/article/details/105164601

版权

#mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

在这里插入图片描述

事务

一、sql语句执行过程分析

在这里插入图片描述

（1）缓存

mysql支持一种 Buffer pool 机制，文件类型数据库：mysql将数据都存储在文件中。对数据的任何操作都意味着要对文件操作，那必须要进行文件IO（磁盘IO）但是磁盘Io非常耗时，
如果将文件中的一部分数据拿到缓存中存储，再去操作数据的时候就可以只操作缓存。
Buffer pool机制：缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO，起到加速访问的作用。速度快
那为啥不把所有数据都放到缓冲池里？
（1）缓存访问快，但容量小，数据库存储了200G数据，缓存容量可能只有64G；
（2）内存访问快，但容量小，买一台笔记本磁盘有2T，内存可能只有16G；
因此，只能把常用”的数据放到“最近”的地方，以“最大限度”的降低磁盘访问。
（3） Buffer pool中修改的数据会定期刷新到磁盘中（这一过程称为：刷脏），但是如果Mysql宕机，数据没来及刷脏，数据丢失

（2）解析器

解析器通过关键字将SQL语句进行解析，并生成对应的解析MySQL解析器将使用MySQL语法规则验证和解析查询。
解析树：判断sql语法是否正确

（3）预处理器

预处理器则根据一些MySQL规则进行进一步检查解析树是否合法，例如检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。

（4）查询优化器

查询优化器会将解析树转化成执行计划。一条查询可以有多种执行方法，最后都是返回相同结果。优化器的作用就是找到这其中最好的执行计划。

最后我们知道：不同的存储引擎数据存储的方式是不同，操作数据时操作数据的方式也是不同。

二、 Mysql异常中断

DBMS（Mysql）保证它对数据库中的数据的修改是永久性的，
即使数据库因为故障出错，也应该能够恢复数据！

（一） redo log（重做日志）

是什么？
由引擎层的InnoDB引擎实现,是物理日志,记录的是物理数据页修改的信息,比如"某个数据页上内容发生了哪些改动"
原理和存储
原理:当一条数据需要更新时,InnoDB会先将更新操作记录到rodo log中,并更新到内存中,这个更新就算是完成了。InnoDB引擎会在mysql空闲时将这些更新操作更新到磁盘中(数据文件)。 (这个就是MySql经常说到的WAL技术,Write-Ahead Logging ,关键点是先写日志,再写磁盘)

存储:redolog是顺序写入指定大小的物理文件中的。是循环写入的,当文件快写满时,会边擦除边刷磁盘,即擦除日志记录(redolog file)并将数据刷到磁盘中。

用处
1.提供crash-safe 能力(崩溃恢复),确保事务的持久性。数据库突然崩溃,有些数据并未刷到数据文件中,当重启MySQL数据库,会从redolog中未刷到磁盘的数据刷到磁盘中。

2.利用WAL技术推迟物理数据页的刷新，从而提升数据库吞吐，有效降低了访问时延。

三、什么是事务

一个事务是由一条或者多条对数据库操作的SQL语句所组成的一个不可分割的单元，只有当事务中的所有操作都正常执行完了，整个事务才会被提交给数据库；如果有部分事务处理失败，那么事务就要回退到最初的状态，因此，事务要么全部执行成功，要么全部失败。

（一）开启事务，事务回滚，执行事务

语句： set autocommit
一旦开启事务就不是按下回车造成永久性修改
语句：rollback
如果发现sql语句中有错误，那么调用rollback 退回
语句：commit
如果发现sql语句没错误执行commit，这时候才会对数据造成永久影响

四、Mysql中undo log日志

是什么
由引擎层的InnoDB引擎实现,是逻辑日志，mysql通过undo log 日志保证了可以rollback。
作用和redo log相反，在sql语句执行的时候会记录一条和你要执行的sql作用相反的
怎么工作
mysql通过undo log 日志保证了可以rollback。
作用和redo log相反，在sql语句执行的时候会记录一条和你要执行的sql作用相反的

当事务提交之后，undo log并不能立马被删除,而是会被放到待清理链表中,待判断没有事物用到该版本的信息时才可以清理相应undolog。
作用
保存了事务发生之前的数据的一个版本，用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

1.保证原子性:更新数据前,记录undo log,为保证在更新数据时发生异常导致更新失败,这时可以使用undo log对数据进行回滚(回滚内存中的数据,并会在redo log中记录回滚操作)

2.保证持久性:每更新数据后,记录redo log,为防止服务器突然宕机,导致没有把数据刷到磁盘中,每次重启MySql服务器都会从redo log将脏页(未能及时写到磁盘的数据页)刷到磁盘

五、多用户操作会出现的问题

（一）脏读（Dirty Read）

一个事务读取了另一个事务未提交的数据。
例如当事务A和事务B并发执行时，当事务A更新后，事务B查询读取到A尚
未提交的数据，此时事务A回滚，则事务B读到的数据就是无效的脏数据。
（事务B读取了事务A尚未提交的数据）

（二）不可重复读（NonRepeatable Read）

一个事务的操作导致另一个事务前后两次读取到不同的数据。例如当事务A和事务B并发执行时，当事务B查询读取数据后，事务A更新操作更改事务B查询到的数据，此时事务B再次去读该数据，发现前后两次读的数据不一样。（事务B读取了事务A已提交的数据）

（三）虚读（Phantom Read）/幻读

一个事务的操作导致另一个事务前后两次查询的结果数据量不同。例如当事务A和事务B并发执行时，当事务B查询读取数据后，事务A新增或者删除了一条满足事务B查询条件的记录，此时事务B再去查询，发现查询到前一次不存在的记录，或者前一次查询的一些记录不见了。（事务B读取了事务A新增加的数据或者读不到事务A删除的数据）

六、事务隔离性

事务的隔离性：分级别的不同级别的隔离性解决不同的问题。根据不同场景设置不同的隔离级别。
只要将隔离级别设置正确上述三个问题就轻松解决了。不同的隔离级别解决不同的问题：隔离级别

TRANSACTION_NONE。表示不支持事务
TRANSACTION_READ_UNCOMMITTED。未提交读。
说明在提交前一个事务可以看到另一个事务的变化。这样读”脏”数据，不可重复读和虚读都是被允许的。
TRANSACTION_READ_COMMITTED。已提交读。
说明读取未提交的数据是不允许的。这个级别仍然允许不可重复读和虚读产生。
TRANSACTION_REPEATABLE_READ。可重复读。
说明事务保证能够再次读取相同的数据而不会失败，但虚读仍然会出现。
TRANSACTION_SERIALIZABLE。可序列化/串行化。
是最高的事务级别，它防止读脏数据，不可重复读和虚读。

七、事物的四大特性

（一）事务的原子性（Atomic）

rollback; 原子性的实现原理 ——>Undo log
事务是一个不可分割的整体，事务必须具有原子特性，及当数据修改时，要么全执行，要么全不执行，即不允许事务部分的完成。

（二）事务的一致性（Consistency）

事务的一致性实现原理 = 原子性原理 + 隔离性原理 + 持久性原理
一个事务执行之前和执行之后，数据库数据必须保持一致性状态。
数据库的一致性状态必须由用户来负责，
由并发控制机制实现。就拿网上购物来说，你只有让商品出库，
又让商品进入顾客的购物车才能构成一个
完整的事务！由于并发操作带来的数据不一致性包括读脏数据
（脏读），不可重复读和虚读（幻读）。

（三）事务的隔离性（Isolation）

4个隔离级别隔离性实现原理：mysql锁机制 + MVCC机制
当两个或者多个事务并发执行时，为了保证数据的安全性，
将一个事物内部的操作与其它事务的操作隔离起来，
不被其它正在执行的事务所看到。
例如对任何一对事务T1和T2，
对T1而言，T2要么在T1开始之前已经结束，要么在T1完成之后
再开始执行。隔离性使得每个事务的更新在它被提交之前，
对其它事务都是不可见的。
让我们看看事务处理之父Jim Gray对事务隔离性的定义[1]：
Isolation: Concurrently executing transactions see the stored
information as if they were running serially (one after another).

（四）事务的持久性（Durability）

持久性的实现原理 redo log
事务完成以后，DBMS保证它对数据库中的数据的修改是永久性的，
即使数据库因为故障出错，也应该能够恢复数据！

八、事务操作

前提：有表和数据，存储引擎为：InnoDB

关闭自动提交事务：

一、可通过select @@autocommit; 查看MySQL是否已经关闭自动提交
0：手动提交事务，1：自动提交事务；若发现未关闭自动提交事务，使用：SET AUTOCOMMIT = 0操作;设置事务提交方式为手动提交事务

开启事务：

BEGIN 或 START TRANSACTION 显式地开启一个事务；（可写可不写）；为什么可写可不写，因为当我们一个事务完成后执行：commit；代表这个事务就结束了。也就是这个事物的结束位置，就是下一个事务开启的位置。

提交事务：

COMMIT; 当组成事务的所有sql语句都执行成功，调用commit提交一个事务;
注意：当commit执行后，对数据的修改是永久的，回滚也没有作用的

事务的回滚：

ROLLBACK; 如果在执行事务的过程当中有一个事务执行失败，回滚一个事务到初始的位置

保存点

语句：SAVEPOINT point1; 设置一个名字为point1的保存点
作用：ROLLBACK TO point1; 事务回滚到保存点point1，而不是回滚到初始状态
注意：ROLLBACK TO point1 并没有结束事务，它只是回滚到保存点sql语句这里；而rollback才是结束事务，回滚到事务最初的状态。

查询事务的隔离级别

语句：select @@tx_isolation;
说明：MySQL默认工作在“可重复读”的隔离级别重启恢复默认

设置事务的隔离级别

语句：set session transaction isolation level READ COMMITTED; 设置事务的隔离级别
如下4种分别对应，我们之前讲过的4种隔离级别：
Read uncommitted 、Read committed 、Repeatable read 、 Serializable 序列化

总结：

（1）设置隔离级别（使用一个新事务之前完成的操作）开启事务： SET AUTOCOMMIT = 0;
（2）事务结束：rollback; 事务执行中有错误 commit;事务中所有的sql全部执行成功。
（3）如果不想退回最初的状态，那么需要设置相应的保存点。
（4）设置的隔离级别、事务的开启在mysql重启之后全部恢复默认。

九、事务的底层原理

（一）事务的底层原理

原子性原理、一致性原理、隔离性原理、持久性原理
事务的隔离性原理：是由mysql的锁机制 + MVCC机制实现

（1）原子性（Atomic）

rollback; 原子性的实现原理 ——>Undo log
事务是一个不可分割的整体，事务必须具有原子特性，及当数据修改时，要么全执行，要么全不执行，即不允许事务部分的完成

（2）事务的一致性（Consistency）

如果其他三个特性能够保证，一致性一定可以保证
事务的一致性实现原理 = 原子性原理 + 隔离性原理 + 持久性原理
一个事务执行之前和执行之后，数据库数据必须保持一致性状态。
数据库的一致性状态必须由用户来负责，
由并发控制机制实现。就拿网上购物来说，你只有让商品出库，
又让商品进入顾客的购物车才能构成一个
完整的事务！由于并发操作带来的数据不一致性包括读脏数据（脏读），不可重复读和虚读（幻读）。

（3）隔离性（Isolation）

4个隔离级别隔离性实现原理：mysql锁机制 + MVCC机制
当两个或者多个事务并发执行时，为了保证数据的安全性，
将一个事物内部的操作与其它事务的操作隔离起来，
不被其它正在执行的事务所看到。
例如对任何一对事务T1和T2，
对T1而言，T2要么在T1开始之前已经结束，要么在T1完成之后
再开始执行。隔离性使得每个事务的更新在它被提交之前，
对其它事务都是不可见的。

（4）持久性（Durability）

持久性的实现原理 redo log
事务完成以后，DBMS保证它对数据库中的数据的修改是永久性的，
即使数据库因为故障出错，也应该能够恢复数据！

（二）Mysql锁机制和MVCC机制

MyIsam

当操作数据时，MyIsam引擎下锁住的是整张表。所以称之为表锁。

InnoDB

InnoDB的锁相比于MyIsam有些特殊：它既支持行锁也支持表锁。原因就是InnoDB的行锁是根据索引项的记录添加的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁
表锁：数据所在表把整张表锁住，就是表锁。行锁：操作的数据肯定是在表中的某一行或者某几行，那么如果我们只锁住这某几行数据就是行锁。

行锁：读锁（共享锁）和写锁（排他锁）
表锁：读锁和写锁
读读不互斥读写互斥、写写互斥
select 对数据不造成修改的操作，前提是你的隔离级别设置成序列化的级别 ———读操作读锁
update、insert、delete 对数据造成改变的操作 —— (写操作) 写锁

那我们就来看看锁是如何工作的同时，证明InnoDB既支持行锁，也支持表锁

（1）行锁案例分析

一、先创建一张表，插入数据（id为主键）
在这里插入图片描述
二、我们打开两个窗口：A，B（模拟多用户）同时操作表中数据
操作如下

A:窗口：

set autocommit=0;

B:窗口：

set autocommit=0;

A:窗口：

select * from test_innodb_lock1 where id = 1 for update;
加上for update就强制把他改成写操作， for update 想让这条sql获取到写锁但是我又不希望数据修改

B:窗口：

select * from test_innodb_lock1 where id = 2 for update;

结果：A，B两个sql语句都顺利执行
结论1：虽然A，B表sql语句都触发行锁，但一个是锁的id = 1的那一行，一个锁的是id = 2的那一行，并不冲突
结论2：如果我们A，B两个用户同时操作表中相同id，那触发行锁，结果就是：有且只有一位用户可以执行成功
只有当执行成功的这个用户commit事务，那么其它用户才可以进入

（2）表锁案例分析

我们这次对属性：name进行操作，还是用上面这个表
操作如下：

A:窗口：

set autocommit=0;

B:窗口：

set autocommit=0;

A:窗口：

select * from test_innodb_lock1 where name = ‘Tom’ for update;

B:窗口：

select * from test_innodb_lock1 where name = ‘Jack’ for update;

 结果：A执行成功，B失败
 结论:表锁是针对整张表数据，只要多用户同时操作触发表锁，有且只有一位用户可以执行成功
 只有该用户事务完成退出了，其它用户才可以执行!

（3）行锁，表锁，死锁，间隙锁

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

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

MyIsam中 表锁不会出现死锁 ，InnoDB中的行锁会出现死锁  原因：就是myIsam不支持事务。
那么什么是死锁呢：
所谓死锁，是指两个或者两个以上线程在执行过程中，因争夺资源而产生互相等待的现象，
若无外力作用，他们都将无法推进下去，此时，称系统处于死锁。

（3-1）死锁案例分析

我们两个窗口（模拟多用户，其中：id都为主键）

A窗口：
B窗口：
A窗口：

select * from table1 where id = 1 for update;     1锁

B窗口：

select * from table2 where id = 2 for update;     2锁

A窗口：

select * from table2 where id = 2 for update;     A：2锁   A在等B释放2锁

B窗口：

select * from table1 where id = 1 for update;     B： 1锁   	  B在等A释放1锁

结果：我们可以看到 A在等B放2锁；B在等A放1锁；两个都在等，若没有外力，A，B就会这样一直僵持下去。

（3-2）间隙锁案例分析

在索引存在的情况下，在添加行锁的基础上会添加间隙锁
一、我们先创建表（id为主键）
在这里插入图片描述
二、我们开两个窗口A，B模拟多用户操作

A:窗口：

select * from test_innodb_lock1 where id > 1 for update;

B:窗口：

insert into test_innodb_lock1 values(3);

结果：
A:

B:

正常分析：A用户执行sql，及就是对id = 4 所在行加锁，那另一用户B同时对表添加数据 id = 3，按理来说不会发生冲突
所以：这就是我们所说的间隙锁，我们表中只有1和4，1和4之间是有间隙的，间隙我们可以理解为：有数字存在
有间隙就有间隙锁的存在，当然间隙锁是建立在行锁的基础上的!

（4）MVCC机制

多版本并发控制（MVCC,Multiversion Currency Control）。
所谓的mvcc机制就是尽量的不去使用这些锁，一旦这些锁加上之后事务与事务之间就变成了
完全的串行执行，在隔离级别中串行化其实就是这样做的。
MVCC的实现，是通过保存数据在某一个时间点的快照作为每个
事务操作数据的并且记录为一个版本，每一个事务只操作自己版本下的数据。

十、案例分析不同隔离级别的原理

在这里插入图片描述

前提：创建两个表A（ac_a），B(ac_b);（id都为主键）
说明：id = 1 表示人名币类型 id = 2 表示美元
那我们由下表可以知道，A账户有50人民币；B账户有100人民币和300美元

（一）未提交读

对于未提交读，我只举例：脏读；因为未提交读，它隔离性太差，三个读都可以实现，我们不做研究！
先设置mysql隔离级别为：未提交读（A，B都要设置），然后开启事务

（1）脏读

实现方式：B给A转账，A查看账户余额；然后B执行rollback，A再次查看账户余额

B给A转50人民币：update ac_b set money = 50  where id = 1;
A多了50人民币：update ac_a set money = 100 where id = 1;
A查看余额：select * from ac_a;    余额为100；（这个100就是脏数据）
B执行rollback:rollback;
A再次查看余额：select * from ac_a; 余额为50

结论：脏读就是A读取了B未提交事务

（二）已提交读

先设置隔离级别：

set session transaction isolation level Read committed;

（1）脏读

实现方式：B给A转账，A查看账户余额

B给A转50人民币：update ac_b set money = 50  where id = 1;
A多了50人民币：update ac_a set money = 100 where id = 1;
A查看余额：select * from ac_a;    余额还是：50；

我们可以看到B给A转账50（B事务未提交）；A查看账户余额还是50
底层怎么实现的：

在RC级别中，读取数据不加锁，但是数据的写入，删除，修改等是需要加锁的（Record Locks）,脏读问题就是利用MVCC解决的，及就是在：RC
级别下，**只要你没commit，就操作的是自己版本下的数据。**就拿上面的转账来说：B在自己数据版本下看A为100，但如果在A数据版本下看还是50.

（三）可重复读

上面脏读给大家演示了，这里就演示不可重复读

（1）不可重复读

实现方式：假设双11商品打折，商场晚上12点商品价格会刷新，那客户11点59买东西，那12点前东西按双11价格，12点后价格按正常价格，这显然让人不能接受，如何解决。

上面A表看成结账员扫商品，B表看成商场（里面有商品，价格）

A,B表隔离级别都设置：set session transaction isolation level Repeatable read;
12点前商品价格 ,结账员扫描商品id = 1 ：50元
12点商品价格更新（把id = 1商品价格变成100元），然后commit；
12点更新id = 1商品价格后，再看结账员得到的价格:id = 1 商品还是50元

这就解决了不可重复读问题
底层怎么实现的
数据的读取是不加锁的，但数据的写入，删除，修改等都是加锁的（Record Locks）和Mvcc机制
MVCC：即使commit操作，也是自己版本下的数据

（三）串行化

（1）幻读

实现方式：先执行B对A账户查看，A给自己存款100美元，B也给A转账100美元，B再查看A账户
分析：幻读来说 B对A账户看，A只有50人名币，A给自己存款100美元，B也给A转账100美元，我们知道B转账失败，因为id 为主键，这就是B幻读了！如何解决？

先设置隔离级别：set session transaction isolation level Serializable;
B查看A账户：
A给自己存100美元和B给A存100美元sql都无法执行
底层原理
读加读锁，写加排他锁，读写互斥（加的锁就是：Next-key Locks）,并发性非常差

积善之家、必有余庆

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【Mysql 探究事务及底层原理】简单理解

事务一、sql语句执行过程分析（1）缓存（2）解析器（3）预处理器（4）查询优化器二、 Mysql异常中断（没来及刷脏，数据丢失）（一） redo log（重做日志）三、什么是事务开启事务，事务回滚，执行事务四、Mysql中undo log日志一、sql语句执行过程分析（1）缓存mysql支持一种 Buffer pool 机制，文件类型数据库：mysql将数据都存储在文件中。对数...
复制链接

扫一扫