投稿
  1. 首页
  2. 随笔

MySQL中事务相关问题的分析

华为交换机配置 • 2022-12-17 • 随笔 • 阅读 15

MySQL中事务相关问题的分析,第1张

MySQL中事务相关问题的分析

目录
  • 1. 事务
    • 1.1 定义
    • 1.2 事务的ACID属性
    • 1.3 MySQL中的脏读、不可重复读、幻读
    • 1.4 事务的隔离级别
    • 1.5 事务的 *** 作
  • 2. MySQL如何解决不可重复读和幻读现象
    • 2.1 MVCC如何解决不可重复读问题
    • 2.2 MySQL解决幻读问题
      • 2.2.1 快照读与当前读
      • 2.2.2 MVCC与锁解决幻读问题
  • 3. MySQL中常见的存储引擎
  • 4. 数据库的三大范式
  • 5. MySQL的查询指令
  • 6. MySQL中字段类型CHAR和VARCHAR的区别
  • 7. MySQL中字段类型DATETIME和TIMESTAMP的区别？

1. 事务 1.1 定义

事务是由一组SQL语句组成的逻辑处理单元，事务具有四种属性，简称为事务的ACID属性。

1.2 事务的ACID属性

  • 原子性Atomicity：

    • 一个事务必须是不可分割的最小工作单元，这句话的意思是一个事务要么都成功，要么都失败。

  • 一致性Consistency：

    • 事务必须是使数据库从一个一致性状态转换到另一个一致性状态。通俗的来说就是，事务的结果必须和预期相符。(参考文章：什么是事物的一致性)
    一致性 例子：
小明向小红转账100元，那么最后的结果一定是小明账户减少100元，小红账户增加100元。而不应该是某个人的账户金额不变。

  • 隔离性Isolation：

    • 一个事务相对于另一个事务是隔离的，一个事务所做的修改在最终提交之前，对其他事务是不可见的。一个事务对一个数据进行 *** 作时，其他事务不允许对同一个数据进行 *** 作。

  • 持久性Durability：

    • 事务一旦提交，则其所做的修改就会永久的保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。
1.3 MySQL中的脏读、不可重复读、幻读
  • 脏读：一个事务正在对一个数据进行修改，但是这个时候另一个事务读到了这个修改后的数据，但是原来的事务由于某些原因回滚了，这时第二个事务读到的数据与数据库中的数据不一致（一个事务读取到了另一个事物尚未提交的数据），这就是脏读。
  • 不可重复读：同一个事务前后多次读取某个数据内容，不能读取到相同的结果，（中间有另一个事务 *** 作了该数据），这种情况就是不可重复读。
  • 幻读：如果当前事务读取某一个范围内的记录时，另一个事务又在该范围内插入新记录，导致当前事务再次读取该范围的记录时，两次结果不一样（和产生了幻觉一样），称为幻读。
# 不可重复读和幻读的区别
不可重复读是本身数据内容，幻读是一个范围，但本质上都是数据不一致。
1.4 事务的隔离级别 
# 事务的隔离级别，由低到高为
	读未提交->读已提交->可重复读->可串行化
  • 读未提交（Read Uncommitted）：一个事务可以读取到其他事务未提交的数据，这种隔离级别可能会产生脏读、不可重复读、幻读的问题。
  • 读已提交（Read Committed）：一个事务只能读取到其他事务已经提交的数据。这种隔离级别可以解决脏读的问题，但是依然会产生不可重复读、幻读的问题。
  • 可重复读（Repeatable Read）：MySQ默认的隔离级别，可以解决脏读、不可重复读的问题，但是依然存在幻读的问题。
  • 可串行化（Serializable）：强制所有事务串行执行，脏读、不可重复读、幻读的问题都可以解决，但是效率极低。
# 如何设置事务的隔离级别

-- 查看当前事务的隔离级别
SELECt @@tx_isolation; 

-- 设置当前事务的隔离级别为Read Uncommitted
set session transaction isolation level read uncommitted;

-- 设置当前事务的隔离级别为Read Committed
set session transaction isolation level read committed;

-- 设置当前事务的隔离级别为Repeatable Read
set session transaction isolation level repeatable read;

-- 设置当前事务的隔离级别为Serializable
set session transaction isolation level serializable;
1.5 事务的 *** 作 
-- 开启事务
begin; 或 start transaction;
-- 回滚事务
rollback;
-- 提交事务
commit;

-- 设置自动提交是否打开
set autocommit = on;	-- 自动提交打开
set autocommit = off;	-- 自动提交关闭

-- 显示当前自动提交的状态
show variables like 'autocommit';

隐式提交：

当我们使用start transaction或者begin语句开启一个事务，或者把系统变量autocommit设置为off时，事务就不会自动提交，但是如果我们输入了某些语句就会悄咪咪的提交掉，就像我们输入了commit一样，这种因为某些特殊的语句而导致事务的自动提交的情况称为隐式提交，这些会导致事务隐式提交的语句包括：

  • 定义或修改数据库对象的数据定义语言（DDL）。所谓的数据库对象，指的就是数据库、表、视图、存储过程等等这些东西。当我们使用create、drop、alter等语句去修改这些所谓的数据库对象时，就会隐式提交前边事务所属于的事务。
  • 当我们在一个事务还没提交或者回滚时就又使用begin 或 start transaction开启了另一个事务时，会隐式的提交上一个事务。或者当前的autocommit系统变量的值为off，我们手动把它调为on时，也会隐式提交前边语句所属的事务。或者使用LOCK TABLES，UNLOCK TABLES等关于锁定的语句也会隐式提交前边语句所属的事务。
2. MySQL如何解决不可重复读和幻读现象

MVCC多版本并发控制是MySQL中基于乐观锁的理论实现隔离级别的方式，用于实现读已提交和可重复的的隔离级别（其实可重复读隔离级别的实现还有锁的参与）。

2.1 MVCC如何解决不可重复读问题

MySQL解决不可重复读问题是由MVCC来解决的。

mvcc是利用在每条数据后面加了隐藏的两列（创建版本号和删除版本号），每个事务在开始的时候都会有一个递增的版本号。

插入数据：

insert into user(id, name, age) values(1, "张三", 10);
将插入数据的创建版本号设置为当前事务的版本号，删除版本号设置为NULL
idnameagecreate_versiondelete_version1张三101NULL

更新数据：

update user set age = 11 where id = 1;

更新 *** 作采用delete+add *** 作实现：
首先将当前数据标志为删除（在删除版本号上添加上当前事务的版本号），之后新增一条数据（修改之后的数据），将该数据的创建版本号设置为当前事务的版本号，删除版本号设置为NULL；
idnameagecreate_versiondelete_version1张三10121张三112NULL

删除数据：

delete from user where id = 1;
删除 *** 作是将数据的删除版本号设为当前事务的版本号
idnameagecreate_versiondelete_version1张三10121张三1123

查询 *** 作：

select * from user where id = 1;
查询 *** 作为了避免读到被其他事务修改的数据（不可重复读），读到的数据行必须满足以下条件：
1. 查询到的数据的创建版本号必须小于当前查询事务的版本号
2. 查询到的数据的删除版本号必须大于当前事务的版本号（或者数据的删除版本号为NULL）
即当前事务的版本号必须满足：
	create_version <= current_version <= delete_version （或者删除版本号为null）
	数据的创建版本号	 当前事务的版本号	   数据的删除版本号
2.2 MySQL解决幻读问题 2.2.1 快照读与当前读 
# 先简单介绍一下快照读和当前读
读取历史数据的方式称为快照读，读取最新数据（事务已提交的数据）的方式称为当前读。
快照读：
	在MySQL中，select ··· from ··· where ···· 语句采用的都是快照读
当前读：
	select ···· lock in share mode   -- 加共享锁
    select ···· for update  		 -- 加排它锁
    update
    insert
    delete
当前读读到的是最新的数据，并且对读取的记录加锁，阻塞其他事务同时改动相同记录，避免出现安全问题。
例如，假设要update一条记录，但是另一个事务已经delete这条数据并且commit了，如果不加锁就会产生冲突。所以update的时候肯定要是当前读，得到最新的信息并且锁定相应的记录。

  • 快照读

    当执行select *** 作时，InnoDB会默认执行快照读，会记录下这次select的结果，之后select的时候就会返回这次快照的数据，即使其他事务提交了也不会影响当前select的数据，这样就实现了可重复读。

    快照的生成是在第一次select执行的时候，假如A事务开启但是还没有执行select *** 作，这时候B事务开启并插入了一条数据并且插入后B事务
提交，这时A事务中执行select数据是可以查到B事务查询的数据的，但是之后如果还有其他事务插入事务并且提交，在A事务中select是查询不到其他事务插入的数据的，因为A已经保存了第一次select的结果，之后再执行多少次都是这个结果，直到A事务提交。

  • 当前读

2.2.2 MVCC与锁解决幻读问题

在快照读的情况下

在快照读的情况下，MySQL采用MVCC解决幻读问题。

在当前读的情况下

# 当前读的情况：
select ····· where 主键 ···  
selecct ···· where ····· lock in share mode
selecct ···· where ····· for update
update
insert
delete

在当前读的情况下，MySQL通过X锁或next-key来避免其他事务修改：

  • 当where条件为主键时，通过对主键索引加record locks（索引加锁/行锁）处理幻读

  • 当where条件为非主键时，通过next-key锁来处理。next-key是record locks 和gap locks的结合，每次锁住的不只是需要使用的数据，还会锁住这个数据附近的数据（自己和前后之间都会锁住）。

    - 临建锁产生的条件
1. 隔离级别为RR
2. 当前读
3. 查询条件能够走到索引

学习链接：关于当前读临键锁处理幻读的情况

3. MySQL中常见的存储引擎

  • MySQL5.5之前使用的是MYISAM引擎，5.5以上版本使用的是InnoDB引擎

  • 二者区别：

    区别项InnoDBMYISAM事务支持不支持锁粒度行锁，适合高并发表锁，不适合高并发是否默认默认非默认是否支持外键支持不支持适用场景读写均衡，写多读少场景，需要事务读多写少场景，不需要事务是否支持全文索引不支持（可以借助插件或者使用ElasticSearch）支持
4. 数据库的三大范式
  • 第一范式
    • 确保数据库表中的每个字段都是不可分解的原子项。保证原子性约束。
  • 第二范式
    • 主键列与非主键列是完全函数依赖关系。确保唯一性约束。
  • 第三范式
    • 非主键列之间没有传递函数依赖关系。即任何字段不能由其他字段衍生出来，是对字段的冗余性约束。

文章链接

5. MySQL的查询指令 
select
    字段列表	
from
    表名列表
where
    条件列表		-- 初步过滤，不能有聚合函数
group by
    分组字段		-- 分组
having
    分组之后的条件	  -- 再过滤，可以使用聚合函数
order by	
    排序			  -- 排序
limit
    分页限定		 -- 分页
6. MySQL中字段类型CHAR和VARCHAR的区别 对比项char(16)varchar(16)长度特点长度固定，存储字符长度可变，存储字符长度不足情况插入的长度小于定义的长度时，则用空格填充小于定义长度时，按实际插入长度存储性能存取速度比varchar快得多存取速度比char慢得多使用场景适合存储很短的，固定长度的字符串，如手机号，MD5值等适合用在长度不固定场景，如收货地址，邮箱地址等 7. MySQL中字段类型DATETIME和TIMESTAMP的区别？ 类型占据字节范围时区问题datetime8字节1000-01-01 00:00:00 到 9999-12-31 23:59:59存储与时区无关，不会发生改变timestamp4字节1970-01-01 00:00:01 到 2038-01-19 11:14:07存储与时区有关，随数据库的时区而发生改变 
文章学习于：
https://blog.csdn.net/cy973071263/article/details/104490345
https://blog.csdn.net/nhlbengbeng/article/details/84951613
https://blog.csdn.net/sanyuesan0000/article/details/90235335
https://csp1999.blog.csdn.net/article/details/113801545			-- 有关于日志的小结
https://blog.csdn.net/huangyaa729/article/details/89924358
https://www.cnblogs.com/zhoujinyi/p/3435982.html

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/zaji/5691541.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
华为交换机配置 华为交换机配置 一级用户组
0 0
上一篇 2022-12-17
下一篇 2022-12-17

发表评论

登录后才能评论

评论列表（0条）

保存