【Mysql】事务、锁、MVCC、redo、undo、binlog

Leoh_LL

已于 2024-06-28 12:19:57 修改

阅读量721

点赞数

分类专栏：计算机数据库文章标签： mysql 数据库 java

于 2022-09-03 20:05:34 首次发布

本文链接：https://blog.csdn.net/qq_37717687/article/details/122565633

版权

计算机数据库专栏收录该内容

13 篇文章 0 订阅

订阅专栏

一、事务

目的

事务将数据库从一种一致性状态转换为另一种一致性状态；

组成

事务可由一条SQL语句组成，也可以由一组复杂的SQL语句组成；
注意一条SQL语句也是一个事务；

特征

在数据库提交事务时，可以确保要么所有修改都已经保存，要么所有修改都不保存；

事务是访问并更新数据库各种数据项的一个程序执行单元。

在 MySQL innodb 下，每一条语句都是事务；可以通过 set autocommit = 0; 设置当前会话手动提交；

事务控制语句

-- 显示开启事务 
START TRANSACTION | BEGIN 
-- 提交事务，并使得已对数据库做的所有修改持久化 
COMMIT 
-- 回滚事务，结束用户的事务，并撤销正在进行的所有未提交的修改 
ROLLBACK 
-- 创建一个保存点，一个事务可以有多个保存点 
SAVEPOINT identifier 
-- 删除一个保存点 
RELEASE SAVEPOINT identifier 
-- 事务回滚到保存点 
ROLLBACK TO [SAVEPOINT] identifier

二、事务ACID特性

原子性（A）

事务操作要么都做（提交），要么都不做（回滚）；事务是访问并更新数据库各种数据项的一个程序执行单元，是不可分割的工作单位；通过undolog来实现回滚操作。undolog记录是事务每步具体操作，当回滚时，回放事务具体操作的逆运算；

隔离性（I）

事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，也就是事务提交前对其他事务都不可见；通过 MVCC 和锁来实现；MVCC 是多版本并发控制，主要解决致性非锁定读，通过记录和获取行版本，而不是使用锁来限制读操作，从而实现高效并发读能。锁用来处理并发 DML 操作；数据库中提供粒度锁的策略，针对表（聚集索引B+树）页（聚集索引B+树叶子节点）、行（叶子节点当中某一段记录行）三种粒度加锁；

持久性（D）

事务提交后，事务DML操作将会持久化（写入redolog磁盘文件哪一个页页偏移值具体据）；即使发生宕机等故障，数据库也能将数据恢复。redolog记录的是物理日志；

一致性（C）

一致性指事务将数据库从一种一致性状态转变为下一种一致性的状态，在事务执行前后，数据库完整性约束没有被破坏。例如：一个表的姓名是唯一键，如果一个事务对姓名进行改，但是在事务提交或事务回滚后，表中的姓名变得不唯一了，这样就破坏了一致性；一致性原子性、隔离性以及持久性共同来维护的。

三、事务并发异常

脏读

事务（A）可以读到另外一个事务（B）中未提交的数据；也就是事务A读到脏数据；在读分离的场景下，可以将slave节点设置为 READ UNCOMMITTED；此时脏读不影响，在slav上查询并不需要特别精准的返回值。
请添加图片描述

不可重复读

事务（A) 可以读到另外一个事务（B）中提交的数据；通常发生在一个事务中两次读到的数据是不一样的情况；不可重复读在隔离级别 READ COMMITTED 存在。一般而言，不可重复读的问题是可以接受的，因为读到已经提交的数据，一般不会带来很大的问题，所以很多厂商（如Oracle、SQL Server）默认隔离级别就是READ COMMITTED；
请添加图片描述

幻读

事务中一次读操作不能支撑接下来的业务逻辑；通常发生在一个事务中一次读判断接下来写操作失败的情况；例如：以name为唯一键的表，一个事务中查询 select * from t where name = 'mark'; 不存在，接下来 insert into t(name) values ('mark'); 出现错误，因为此时另外一个事务也执行了 insert 操作；幻读在隔离级别 REPEATABLE READ 及以下存在；但是可以在REPEATABLE READ 级别下通过读加锁（使用next-key locking）解决；
请添加图片描述

四、事务隔离级别

ISO和ANIS SQL标准制定了四种事务隔离级别的标准，各数据库厂商在正确性和性能之间做了妥协，并没有严格遵循这些标准；MySQL innodb默认支持的隔离级别是 REPEATABLE READ；

READ UNCOMMITTED

读未提交；该级别下读不加锁，写加排他锁，写锁在事务提交或回滚后释放锁；

READ COMMITTED

读已提交；从该级别后支持 MVCC (多版本并发控制)，也就是提供一致性非锁定读；此时读取操作读取历史快照数据；该隔离级别下读取历史版本的最新数据，所以读取的是已提交的数据；

REPEATABLE READ

可重复读；该级别下也支持 MVCC，此时读取操作读取事务开始时的版本数据；

SERIALIZABLE

可串行化；该级别下给读加了共享锁；所以事务都是串行化的执行；此时隔离级别最严苛；

不同隔离级别下并发异常

请添加图片描述

命令

-- 设置隔离级别 
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
-- 或者采用下面的方式设置隔离级别 
SET @@tx_isolation = 'REPEATABLE READ'; 
SET @@global.tx_isolation = 'REPEATABLE READ'; 
-- 查看全局隔离级别 
SELECT @@global.tx_isolation; 
-- 查看当前会话隔离级别 
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation; 

-- 手动给读加 S 锁 
SELECT ... LOCK IN SHARE MODE; 
-- 手动给读加 X 锁 
SELECT ... FOR UPDATE; 
-- 查看当前锁信息 
SELECT * FROM information_schema.innodb_locks;

五、锁

锁机制用于管理对共享资源的并发访问；用来实现事务的隔离级别；

锁类型

共享锁和排他锁都是行级锁；MySQL当中事务采用的是粒度锁；针对表（B+树）、页（B+树叶子节点）、行（B+树叶子节点当中某一段记录行）三种粒度加锁；

意向共享锁和意向排他锁都是表级别的锁；
请添加图片描述

共享锁（S）

事务读操作加的锁；对某一行加锁；

在 SERIALIZABLE 隔离级别下，默认帮读操作加共享锁；
在 REPEATABLE READ 隔离级别下，需手动加共享锁，可解决幻读问题；
在 READ COMMITTED 隔离级别下，没必要加共享锁，采用的是 MVCC；
在 READ UNCOMMITTED 隔离级别下，既没有加锁也没有使用 MVCC；

排他锁（X）

事务删除或更新加的锁；对某一行加锁；

在4种隔离级别下，都添加了排他锁，事务提交或事务回滚后释放锁；

意向共享锁（IS）

对一张表中某几行加的共享锁；

意向排他锁（IX）

对一张表中某几行加的排他锁；

锁的兼容性

请添加图片描述
由于innodb支持的是行级别的锁，意向锁并不会阻塞除了全表扫描以外的任何请求；

意向锁之间是互相兼容的；

IX 对共享锁和排他锁都不兼容；

IS 只对排他锁不兼容；

当想为某一行添加 S 锁，先自动为所在的页和表添加意向锁 IS，再为该行添加 S 锁；

当想为某一行添加 X 锁，先自动为所在的页和表添加意向锁 IX，再为该行添加 X 锁；

锁算法

Record Lock

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

Gap Lock

间隙锁，锁定一个范围，但不包含记录本身；全开区间；REPEATABLE READ级别及以上支持间隙锁；

如果 REPEATABLE READ 修改 innodb_locks_unsafe_for_binlog = 0 ，那么隔离级别相当于退化为 READ COMMITTED；

-- 查看是否支持间隙锁，默认支持，也就是 innodb_locks_unsafe_for_binlog = 0; 
SELECT @@innodb_locks_unsafe_for_binlog;

Next-Key Lock

记录锁+间隙锁，锁定一个范围，并且锁住记录本身；左开右闭区间；

Insert Intention Lock

插入意向锁，insert操作的时候产生；在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。

假设有一个记录索引包含键值4和7，两个不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

锁兼容

请添加图片描述
横向：表示已经持有的锁；纵向：表示正在请求的锁；

AUTO-INC Lock

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

锁的对象

行级锁是针对表的索引加锁；索引包括聚集索引和辅助索引；

表级锁是针对页或表进行加锁；

考虑 InnoDB 在 read committed 和 repeatable read 级别下锁的情况；

示例一

select * from t where id = 5 for update; lock in share mode 
-- id 为主键 Read committed 隔离级别 
-- 在主键 id = 5 行上加 X 锁 

-- id 是唯一索引 Read committed 隔离级别 
-- 在唯一索引id=5行上加X锁，在主键索引上对应行加X锁 

-- id 是非唯一索引 Read committed 隔离级别 
-- 在非唯一索引上所有id=5行加上X锁，对应的主键索引列加上X锁 

-- id 不是索引 Read committed 隔离级别 
-- 在聚集索引上扫描，所有行上加X锁，此处有个优化，不满足的行在加锁后，判断不满足即可释放锁 

-- id 为主键 repeatable read 隔离级别 
-- 在主键id=5行上加X锁 

-- id 是唯一索引 repeatable read 隔离级别 
-- 在唯一索引id=5行上加X锁，在主键索引上对应列加X锁 

-- id 是非唯一索引 repeatable read 隔离级别 
-- 在非唯一索引上查找id=5行，找到则加上X锁和GAP锁，然后对应的聚集索引加上X锁; 没有找到则 加上GAP锁 

-- id 不是索引 repeatable read 隔离级别 
-- 在聚集索引上扫描，所有行加上X锁和GAP锁

示例二

-- 在 RR 下 
-- 不加任何锁 
select .. from t; 
-- 扫描到任何索引行上加S锁（next-key lock） 在聚集索引上加X锁 
select...from t lock in share mode; 
-- 扫描到任何索引行上加X锁（next-key lock） 在聚集索引上加X锁 
select..from t for update; 
-- 扫描到任何索引行上加X锁（next-key lock） 在聚集索引上加X锁 
update..where condition 
delete from..where condition 
-- 如果是间隙插入，先添加 insert intention lock, 后在该行上添加X锁; 
-- 如果是递增插入，添加 auto-inc lock 或者 轻量级的互斥锁； 
insert into ...