MySQL事务和锁原理与优化
2024-04-02 15:17:58 6 举报AI智能生成
Mysql 查询的原理和优化
作者其他创作
大纲/内容
ACID特性
原子性
事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行
执行图
持久性
指的是一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，后续的操作或故障不
应该对其有任何影响，不会丢失。
应该对其有任何影响，不会丢失。
执行流程图
为什么使用两阶段提交
两阶段提交主要解决 binlog 和 redo log 的数据一致性的问题
执行图
持久性的保证
redo log在系统Crash重启之类的情况时，可以修复数据，从而保障事务的持久性【根据XID】
如果碰到既有 prepare、又有 commit 的 redo log，就直接提交.
如果 redo log 处于 prepare，就拿着 XID 去 binlog 找对应的事务, 判断事务对应的 binlog 是不是
完整的.
是，则把事务提交
否，则事务回滚
完整的.
是，则把事务提交
否，则事务回滚
通过原子性可以保证逻辑上的持久性.
通过存储引擎的数据刷盘可以保证物理上的持久性.
隔离性
指的是一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他的并
发事务是隔离的
发事务是隔离的
不考虑隔离性会引发的问题:
脏读 : 一个事务读取到了另一个事务修改但未提交的数据。
不可重复读: 一个事务中多次读取同一行记录的结果不一致，后面读取的跟前面读取的结果不一 致。
幻读 : 一个事务中多次按相同条件查询，结果不一致。后续查询的结果和面前查询结果不同，多了 或少了几行记录
InnoDB 支持的隔离性
读未提交
读提交
可重复读
可串行化
一致性
指的是事务开始之前和事务结束之后，数据库的完整性限制未被破坏。一致性包括两方面的内
容，分别是约束一致性和数据一致性。
关系
事务的持久化是为了应对系统崩溃造成的数据丢失.
只有保证了事务的一致性，才能保证执行结果的正确性
在非并发状态下，事务间天然保证隔离性，因此只需要保证事务的原子性即可保证一致性.
在并发状态下，需要严格保证事务的原子性、隔离性。
总体图
事务控制的演进
排队
完全顺序执行所有事务的数据库操作，不需要加锁，全局排队
排它锁
务之间涉及到相同的数据项时，会使用排他锁，或叫互斥 锁，
先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁。
先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁。
读写锁
读和写操作:读读、写写、读写、写读。
MVCC
概念【MVCC最大的好处是读不加锁，读写不冲突】
多版本控制，是指在数据库中为了实现高并发的 数据访问，对数据进行多版本处理，
并通过事务的可见性来保证事务能看到自己应该看到的数据版本
并通过事务的可见性来保证事务能看到自己应该看到的数据版本
Undo log 多版本链
Read View读视图机制
Read View是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view
简单理解就是对数据在每个时刻的状态拍成照片记录下来。
那么之后获取某时刻的数据时就 还是原来的照片上的数据，是不会变的
那么之后获取某时刻的数据时就 还是原来的照片上的数据，是不会变的
m_ids : 用来表示MySQL中哪些事务正在执行,但是没有提交.
min_trx_id : 就是m_ids里最小的值.
max_trx_id : 下一个要生成的事务id值,也就是最大事务id
creator_trx_id : 就是你这个事务的id
min_trx_id : 就是m_ids里最小的值.
max_trx_id : 下一个要生成的事务id值,也就是最大事务id
creator_trx_id : 就是你这个事务的id
1-2
3-4
5
6
Read View的更新方式:
Read Committed级别
1
2
3
Repeatab Read级别
1
Read View总结
Read View判断记录的某个版本是否可见的方式
trx_id = creator_trx_id
如果被访问版本的trx_id,与readview中的creator_trx_id值相同,表明当前事务在访问自己修改 过的记录,该版本可以被当前事务访问.
trx_id < min_trx_id
如果被访问版本的trx_id,小于readview中的min_trx_id值,表明生成该版本的事务在当前事务 生成readview前已经提交,该版本可以被当前事务访问.
trx_id >= max_trx_id
如果被访问版本的trx_id,大于或等于readview中的max_trx_id值,表明生成该版本的事务在当 前事务生成readview后才开启,该版本不可以被当前事务访问.
trx_id > min_trx_id && trx_id < max_trx_id
如果被访问版本的trx_id,值在readview的min_trx_id和max_trx_id之间，就需要判断trx_id属 性值是不是在m_ids列表中?
【在】说明创建readview时生成该版本的事务还是活跃的,该版本不可以被访问
【不在】说明创建readview时生成该版本的事务已经被提交,该版本可以被访问
生成readview时机
RC隔离级别:每次读取数据前，都生成一个readview.
RR隔离级别:在第一次读取数据前，生成一个readview,之后read view不再更新.
MVCC具体实现
实现机制
undo log多版本链+ReadView机制
流程
6字节的 DATA_TRX_ID :标记了最新更新这条行记录的transaction id，每处理一个事务，其值自 动设置为当前事务ID ( DATA_TRX_ID只有在事务提交之后才会更新 )
7字节的 DATA_ROLL_PTR :一个rollback指针，指向当前这一行数据的上一个版本，找之前版本的 数据就是通过这个指针，通过这个指针将数据的多个版本连接在一起构成一个undo log版本链
6字节的 DB_ROW_ID :隐含的自增ID，如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生 一个聚簇索引。这是一个用来唯一标识每一行的字段
DELETE BIT 位:用于标识当前记录是否被删除，这里的不是真正的删除数据，而是标志出来的删 除。真正意义的删除是在commit的时候
具体说明
查询 SELECT
1.InnoDB只查找版本早于当前事务版本的数据行(也就是数据行的版本必须小于等于事务的版本)， 这确保当前事务读取的行都是事务之前已经存在的，或者是由当前事务创建或修改的行
2.行的删除操作的版本一定是未定义的或者大于当前事务的版本号，确定了当前事务开始之前，行 没有被删除
删除 DELETE
修改 的值为当前的执行删除操作的事务的ID，然后设置Delete BIT = true
增加:INSERT
设置新记录的DATA_TRX_ID为当前事务ID，其他的采用默认的。
修改:UPDATE
用排它锁锁定该行(因为是写操作)
记录redo log:将更新之后的数据记录到redo log中,以便日后使用.
记录undo log:将更新之前的数据记录到undo log中.
MVCC读操作分类
快照读
含义
快照读是指读取数据时不是读取最新版本的数据，而是基于历史版本读取的一个快照信息【无需加锁】
解决问题
解决了因加锁导致的修改数据时无法对数据读取问题.
解决了因加锁导致读取数据时无法对数据进行修改的问题.
当前读
含义
当前读是读取的数据库最新的数据，当前读和快照读不同，因为要读取最新的数据而且要保证事务 的隔离性，
所以当前读是需要对数据进行加锁的
所以当前读是需要对数据进行加锁的
加锁SQL
update delete insert select .....lock in share mode, select fro update 为当前读
事务隔离级别
隔离级别的类型
事务隔离级别和锁
【MySQL默认隔离级别:可重复读】
【Oracle、SQLServer默认隔离级别:读已提交】
【MySQL默认隔离级别:可重复读】
【Oracle、SQLServer默认隔离级别:读已提交】
相当于事务并发控制的整体解决方案
锁是数据库实现并发控制的基础，事务隔离性是采用锁来实现，对相应操作加不同的锁，就可以防 止其他事务同时对数据进行读写操作
对用户来讲，首先选择使用隔离级别，当选用的隔离级别不能解决并发问题或需求时，才有必要在 开发中手动的设置锁
查看SQL
show variables like 'tx_isolation';
0 条评论
下一页
