MySQL----事务transaction

最新推荐文章于 2024-06-17 15:04:59 发布

4nc414g0n

最新推荐文章于 2024-06-17 15:04:59 发布

阅读量527

点赞数

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

本文链接：https://blog.csdn.net/qq_41420788/article/details/126215077

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

初识事务

概念：事务就是一组DML语句组成，这些语句在逻辑上存在相关性，这一组DML语句要么全部成功，要么全部失败，是一个整体，MySQL提供一种机制，保证我们达到这样的效果事务还规定不同的客户端看到的数据是不相同的

一个完整的事务，需要满足如下四个属性（ACID）：

原子性（Atomicity）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（ Read uncommitted ）、读提交（ read committed ）、可重复读（ repeatable read ）和串行化（ Serializable ）
持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

事务本质上是为了应用层服务的，事务能够简化我们的编程模型,不需要我们去考虑各种各样的潜在错误和并发问题

注意：Innodb支持事务， MyISAM 不支持

事务提交方式

autocommit ON: mysql 会针对每条sql做自动提交，其中如果用begin / commit, mysql会自动转为手动提交

自动提交

设置为自动提交：

自动提交(mysql默认)：
每条DML语句被当成一个事务，但前提是你要能够保证数据的读一致性，不能保持事务的一致性，也就不能保证数据完整

手动提交

改为手动提交：

手动提交（Oracle默认）：
在你显式提交之前的所有语句都被认为是一个事务（当使用start transaction和 commit语句时则表示发生显式事务），它的好处是，当这个事务中的某一条语句失败时，事务会回滚，也就是都不会写到数据库，这有利于于保持数据库的一致性

事务操作方式及测试

操作

以隔离级别设置读未提交，同时自动提交为例 (详见后面)：

创建测试表：

操作：

开始事务：start transaction 或 begin
创建保存点：savepoint (savename)
回滚到保存点：rollback to (savename)
回滚到最开始：rollback
提交：commit

测试

测试1：未commit，客户端崩溃，MySQL自动会回滚（开启两个终端模拟并发访问的情景）

开始事务，插入数据，未commit：

此时另一个终端也正常显示数据：

异常终止第一个终端：数据自动回滚

测试2：commit后，客户端aborted，MySQL数据不会再受影响，已经持久化

测试3：begin操作会自动更改提交方式，不会受MySQL是否自动提交影响

可以看到在设置为手动提交后结果和测试1一样回滚

测试4：证明单条 SQL 与事务的关系 (均不使用begin 一次设置autocommit=1，一次设置autocommit=0)

autocommit=1：终止终端后仍然持久化

autocommit=0：终止终端后回滚

总结：

只要输入begin或者start transaction，事务便必须要通过commit提交，才会持久化，与是否设置set autocommit无关
事务可以手动回滚，同时，当操作异常，MySQL会自动回滚
对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交。（select有特殊情况，因为 MySQL 有MVCC ）（mvcc见下面）
从上面的例子，我们能看到事务本身的原子性(回滚)，持久性(commit)

注意：

如果没有设置保存点，也可以回滚，只能回滚到事务的开始。直接使用 rollback(前提是事务还没有提交)
如果一个事务被提交了（commit），则不可以回退（rollback）
可以选择回退到哪个保存点
InnoDB 支持事务， MyISAM 不支持事务
开始事务可以使 start transaction 或者 begin

事务隔离级别

初步理解 (同时对于一致性的理解)

理解：

一个事务可能由多条SQL构成，但对于用户表现出来的特性，应该是原子性的
MySQL服务可能会同时被多个客户端进程(线程)以事务的方式进行访问，在多个事务各自执行多个SQL的时候，可能会出现互相影响的情况，例如：多个事务同时访问同一张表，同一行数据
数据库中，为了保证事务执行过程中尽量不受干扰，就有了隔离性
数据库中，允许事务受不同程度的干扰，就有了隔离级别

一致性：

事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态。当数据库只包含事务成功提交的结果时，数据库处于一致性状态。如果系统运行发生中断，某个事务尚未完成而被迫中断，而改未完成的事务对数据库所做的修改已被写入数据库，此时数据库就处于一种不正确（不一致）的状态。因此一致性是通过原子性来保证的
一致性和用户的业务逻辑强相关，一般MySQL提供技术支持，但是一致性还是要用户业务逻辑做支撑，也就是，一致性，是由用户决定的而技术上，通过AID保证C

隔离级别相关操作

查看全局隔离级别
SELECT @@global.tx_isolation;
查看当前会话隔离级别
SELECT @@session.tx_isolation;或SELECT @@tx_isolation;
设置会话隔离性：
SET [SESSION] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE}
设置全局隔离性
SET [GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED |REPEATABLE READ | SERIALIZABLE}

注意：更改后需重启mysql客户端
(从 5.7.20 版本开始，transaction_isolation 作为 tx_isolation 的别名被引入，而 tx_isolation 在 8.0 版本后被废弃了，应用应该使用 transaction_isolation，而非 tx_isolation)

读未提交【Read Uncommitted】

读未提交：

在该隔离级别，所有的事务都可以看到其他事务没有提交的执行结果（实际生产中不可能使用这种隔离级别的），但是相当于没有任何隔离性，也会有很多并发问题，如脏读，幻读，不可重复读等（上面测试使用的就是读未提交）

设置隔离级别为Read Uncommitted

读提交【Read Committed】

读提交：

该隔离级别是大多数数据库的默认的隔离级别（不是 MySQL 默认的）它满足了隔离的简单定义:一个事务只能看到其他的已经提交的事务所做的改变，这种隔离级别会引起不可重复读，即一个事务执行时，如果多次 select，可能得到不同的结果

可以看到：此时右边的终端还在当前事务中，并未commit，造成了，同一个事务内，同样的读取，在不同的时间段(依旧还在事务操作中！)，读取到了不同的值，这种现象就是不可重复读(non reapeatable read）
这里也就有问题了：例子：评奖

有一张表有：id 姓名分数三列属性，数据若干（其中有一个姓名为jack，分数62）
一个终端(左边)进行对分数修改(查错进行处理)，另一个终端(右边)进行评奖：
select * from table where score between 60 and 70; 三等奖
select * from table where score between 70 and 80;二等奖
select * from table where score between 80 and 100;一等奖
第一次选出jack为三等奖，但就在执行第二条筛选语句的时候发现jack试卷改错了10分，马上调整到72，立即commit，通过上面实验来看，右边的终端也会马上获取到jack的新分数，第二条语句再次把jack筛选出来，
jack不可能同时获得二三等奖

注意区分：

当我们多次多次读取的时候:在一个事务内多次读取数据不变(变化)
在事务结束之后，正常的select读取的数据不变(变化)

可重复读【Repeatable Read】

可重复读：

这是 MySQL 默认的隔离级别，它确保同一个事务，在执行中，多次读取操作数据时，会看到同样的数据行。但是会有幻读问题(mysql通过Next-Key锁和mvcc解决了幻读问题)

对应不可重复读：右边需要commit后才能看到添加的数据
事务无论什么时候进行查找，看到的结果都是一致的，这叫做可重复读

注意：

一般的数据库在可重复读情况的时候，无法屏蔽其他事务insert的数据，造成虽然大部分内容是可重复读的，但是insert的数据在可重复读情况被读取出来，导致多次查找时，会多查找出来新的记录，也就是幻读(phantom read)
MySQL在RR级别的时候，解决了幻读问题

串行化【serializable】

串行化：

这是事务的最高隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决了幻读的问题，它在每个读的数据行上面加上共享锁，但是可能会导致超时和锁竞争（这种隔离级别太极端，实际生产基本不使用）

在右边终端commit后才能进行insert

总结

隔离级别越严格，安全性越高，但数据库的并发性能也就越低，往往需要在两者之间找一个平衡点
不可重复读的重点是修改和删除：同样的条件, 你读取过的数据,再次读取出来发现值不一样了幻读的重点在于新增：同样的条件, 第1次和第2次读出来的记录数不一样
mysql 默认的隔离级别是可重复读,一般情况下不要修改
事务也有长短事务这样的概念。事务间互相影响，指的是事务在并行执行的时候，即都没有commit的时候，影响会比较大

解决脏读不可重复读幻读 (读-写)

脏读不可重复读幻读，都是数据库读一致性问题

锁机制 (MARK待补充)

MyISAM 表锁：

表共享读锁（Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
表独占写锁（Table Write Lock）：会阻塞其他用户对同一表的读和写操作

InnoDB行锁算法

Record Lock: 单个记录上的锁；
Gap Lock: 间隙锁，它锁住的是一个范围，不包含记录本身
Next-key Lock ≈ Record Lock + Gap Lock

MVCC

介绍

数据库并发的场景有三种：

读-读：不存在任何问题，也不需要并发控制
读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

多版本并发控制（ MVCC ）是一种用来解决读-写冲突的无锁并发控制
MVCC是通过隐藏列字段 + undo log + Read View 来实现的
一致性非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过多版本控制(MVVC)读取当前数据库中行数据的方式

记录隐藏列字段

关于隐藏列更多参考：眼见为实，看看 MySQL 中的隐藏列！

注：DB_TRX_ID 和 DATA_TRX_ID是一样的，不同版本实现

DB_TRX_ID ：6 byte，最近修改( 修改/插入 )事务ID，记录创建这条记录/最后一次修改该记录的事务ID
DB_ROLL_PTR : 7 byte，回滚指针，指向这条记录的上一个版本（简单理解成，指向历史版本就行，这些数据一般在 undo log 中）
DB_ROW_ID : 6 byte，隐含的自增ID（隐藏主键），如果数据表没有主键， InnoDB 会自动以 DB_ROW_ID 产生一个聚簇索引（当表中的主键或唯一索引不满足一定条件时，innoDB使用的隐式的_rowid是存在一定风险的）
删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag改变 （？？delete mark，soft-delete？？）

有一张表：

他的实际表示的是

id name age DB_TRX_ID DB_ROLL_PTR DB_ROW_ID
1 jack 18 NULL NULL 1

id	name	age	DB_TRX_ID	DB_ROLL_PTR	DB_ROW_ID
1	jack	18	NULL	NULL	1

undo log（MARK了解其他日志）

其他日志：

redo log：持久化的日志进行数据落盘，持久化，cs (崩溃时安全工作) 参考：MySQL · 引擎特性 · InnoDB redo log
bin log ：二进制日志，记录下来大部分所有的用户操作

概念：

undo log是逻辑日志，当事务回滚时或者数据库崩溃时，可以利用undo log来进行回滚，当执行一条语句的时候，undo log会记录一条回到上个状态的语句，简单比如：delete一条记录，undo log会增加一条insert记录，这也就是为什么当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，实现一致性非锁定读
insert操作在事务提交前只对当前事务可见，在insert后，如果事物成功提交，则会直接删除insert产生的undo log(新插入的数据无历史版本) （insert undo类型）
update，delete操作需要维护多版本信息(有历史版本)，当没有事务在访问的时候，可以看作清空版本链的契机(不一定清空) （两个都是update undo类型）

插入: 记这条的主键值，回滚时把这个主键值对应的记录删掉
删除: 这条记录中的内容都记下来，回滚时把这些内容组成的记录插入到表中
更新: 记被更新的列的旧值，回滚时把这些列更新为旧值

undo log相关变量：

innodb_max_undo_log_size : 当超过阈值时，会触发truncate回收动作，truncate后空间缩小到10MB((默认1GB)
innodb_undo_directory: 指定单独存放undo表空间的目录，默认为./
innodb_undo_log_truncate: 设置是否开启在线回收(压缩)
innodb_undo_logs：定义回滚段(rollback segment,每个回滚段中有1024个undo log segment)的个数（默认128个，共可以记录128 * 1024个undo操作)（innodb_rollback_segments也可以用来定义回滚段个数）
innodb_undo_tablespaces：控制undo是否开启独立的表空间的参数（0：undo使用系统表空间，即ibdata1 非0：使用独立的表空间，一般名称为 undo001 undo002，存放地址的配置项为：innodb_undo_directory）

undo log详细参考好文：
MySQL · 引擎特性 · InnoDB undo log
MySQL之undo log

简单模拟MVCC过程

假设有一张表

id name age DB_TRX_ID DB_ROLL_PTR DB_ROW_ID
1 jack 18 NULL NULL 1

现在有一个事务1，对该表中记录进行修改(update)：jack改为mary

事务1,因为要修改，所以要先给该记录加行锁（锁住name=‘jack’的那一行数据，其他事务再想更新，就只能等前一个事务释放锁）
修改前，分配了一个undo slot(槽，参见上面其他文章链接)，初始化后通过 trx_undo_page_report_modify函数（源码trx0rec.cc内）向其中写入记录(简单理解为写时拷贝)
现在修改原始记录中的name，改成 ‘mary’。并且修改原始记录的隐藏字段 DB_TRX_ID 为当前事务1 的ID, 我们默认从 1开始，之后递增, 而原始记录的回滚指针 DATA_ROLL_PTR 列，里面写入undo log中副本数据的地址，从而通过DB_ROLL_PTR可以找到副本记录地址，既表示我的上一个版本就是它
（图中写错了：前一条undo log的结束位置）
commit事务1，释放行锁

又有一个事务2，对表中记录进行修改(update)：将age(18)改成age(28)

事务2,因为也要修改，所以要先给原始记录加行锁
同理，再次写入undo log

commit事务2，释放行锁

这样，就有了一个基于链表记录的历史版本链，所谓回滚，就是用历史数据，覆盖当前数据，上面的一个一个版本，我们可以称之为一个一个的快照

再次理解隔离级别:

通过让不同的事务读取不同的历史版本，就实现了所谓的隔离性，读取到不同程度的历史版本，就体现了不同程度的隔离级别

id	name	age	DB_TRX_ID	DB_ROLL_PTR	DB_ROW_ID
1	jack	18	NULL	NULL	1

MVCC意义

select读取，是读取最新的版本，还是读取历史版本？
分两种：

当前读：读取最新的记录，就是当前读。增删改，都叫做当前读，select也有可能当前读，比如：select lock in share mode(共享锁), select for update
快照读：读取历史版本(一般而言)，就叫做快照读

在多个事务同时删改查的时候，都是当前读，是要加锁的，如果同时有select进行读取，如果也要读取最新版(当前读)，那么也就需要加锁，这就是串行化
如果是快照读，读取历史版本的话，是不受加锁限制的，也就是可以并行执行提高效率，即MVCC的意义所在
是隔离级别决定了select是哪一种读取

Read View

事务从begin->CURD->commit，是有一个阶段的。也就是事务有执行前，执行中，执行后的阶段，不管怎么启动多个事务，总是有先有后的，在多个事务中CURD操作总是会有交集
为了保证事务的“有先有后”，应该让不同的事务读取到它应该读取的内容，这就是所谓的隔离性与隔离级别要解决的问题
注意：先来的事务不能读取到后来事务的修改

Read View 在 MySQL 源码中,是一个类
类私有属性有：

trx_id_t m_low_limit_id：高(注意这里翻译为高而不是低)水位，大于等于这个ID的事务均不可见（ReadView生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1）
rx_id_t m_up_limit_id：低水位：小于这个ID的事务均可见（m_up_limit_id 初始化时为 m_ids 的最小值）
trx_id_t m_creator_trx_id：创建该 Read View 的事务ID
ids_t m_ids：创建视图时的活跃事务id列表
trx_id_t m_low_limit_no：标识该视图不需要小于m_low_limit_no的UNDO LOG，小于该 Number 的 Undo Logs 均可以被 Purge
trx_id_t m_view_limit_no：read views不需要访问 MVCC 的undo log记录的下限number
bool m_closed：标记视图是否被关闭

私有成员函数：

void creator_trx_id (trx_id_t id)：创建该ReadView的事务ID，现有 id 必须为 0
其他略

公有成员函数：

bool changes_visible()：检查 id 的变化是否可见

当我们某个事务执行快照读的时候，对该记录创建一个 Read View 读视图，将它作为条件,来判断当前事务能够看到哪个版本的数据，可能是当前最新的数据，也可能是该行记录的 undo log 里面的某个其他历史版本的数据
在实际读取数据版本链的时候，是能读取到每一个版本对应的事务ID，通过和ReadView的事务ID比较，判断当前快照读，是否应该读到当前版本记录

下面是mysql5.7的源码中changes_visible()函数：（外层肯定是遍历版本链，调用此函数，并传入各版本链的事务DATA_TRX_ID）

参数接收的id为版本链每条记录的DATA_TRX_ID
name表名
id < m_up_limit_id || id == m_creator_trx_id 表示当前的DATA_TRX_ID小于低水位，小于这个ID的事务均可见 (应该读取到当前的版本记录)，当前的DATA_TRX_ID等于m_creator_trx_id，当前事务做的修改自然可以看到
id >= m_low_limit_id：表示当前的DATA_TRX_ID大于等于高水位(目前已出现过的事务ID的最大值+1), 大于等于这个ID的事务均不可见 (不应该读取到当前的版本记录)，else if如果当前无活跃的事务，即表示应该读取到当前的版本记录
第三种情况：先为binary_search函数创建一个指针(指向活跃事务id列表)，return(!std::binary_search(p, p + m_ids.size(), id));如果二分查找到此DATA_TRX_ID在活跃事务id列表m_ids内，说明不应该读取到当前的版本记录，即！

形象表现为图：

注意：

m_ids内的事务ID不一定是连续的，例如5，6，7，8，9，在快照前id为6和7的事务commit了，在快照当前看到的事务(也就是m_ids中)只有5，8，9（事务ID不断在递增，所以，事务ID大小，就能决定先后顺序）
read view不是事务被创建出来就有的，而是当首次进行select快照读，动态形成的

总流程

有一个场景

事务4修改提交形成版本链
当事务2快照读的时候形成read view：m_ids：1,3 ， up_limit_id：1 ， low_limit_id：4 + 1 = 5(ReadView生成时刻，系统尚未分配的下一个事务ID)， creator_trx_id ：2
事务4在事务2执行快照读前，就提交了事务，所以事务2从DATA_TRX_ID=4的记录开始查找：也就是当前记录
id < m_up_limit_id || id == m_creator_trx_id 不满足，下一步 id >= m_low_limit_id 不满足，下一步 m_ids.empty(）不满足，下一步 binary_search没找到，说明，事务4不在当前的活跃事务中，返回真，判断当前事务2能看到该记录的版本

RR 与 RC的区别

RR：repeatable read
RC：read commit

lock in share mode 以加共享锁方式进行读取，对应当前读
RR模式下：

用例1：

事务A操作事务A描述事务B描述事务B操作
begin 开启事务开启事务 begin
select * from table1 快照读(无影响)查询快照读查询 select * from table1
update table1 set age=28 where id=1; 更新age=18
commit 提交事务
select 快照读 ,没有读到 age=28 select * from user
select lock in share mode当前读 , 读到age=28 select * from table1 lock in share mode

用例2：

事务A操作事务A描述事务B描述事务B操作
begin 开启事务开启事务 begin
select * from table1 快照读，查到age=18
update table1 set age=38 where id=1; 更新age=28
commit 提交事务
select 快照读 age=38 select * from table1
select lock in share mode当前读 age=38 select * from table1 lock in share mode

事务中快照读的结果非常依赖该事务首次出现快照读的地方，即某个事务中首次出现快照读，决定该事务后续快照读结果的能力

RR 与 RC的本质区别：

正是Read View生成时机的不同，从而造成RC,RR级别下快照读的结果的不同，在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照及Read View, 将当前系统活跃的其他事务记录起来。此后在调用快照读的时候，还是使用的是同一个Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见
即RR级别下，快照读生成Read View时，Read View会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View创建的事务所做的修改均是可见
而在RC级别下的，事务中，每次快照读都会新生成一个快照和Read View, 这就是我们在RC级别下的事务中可以
看到别的事务提交的更新的原因。
总之在RC隔离级别下，是每个快照读都会生成并获取最新的Read View；而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View。
正是RC每次快照读，都会形成Read View，所以，RC才会有不可重复读问题

事务A操作	事务A描述	事务B描述	事务B操作
begin	开启事务	开启事务	begin
select * from table1	快照读(无影响)查询	快照读查询	select * from table1
update table1 set age=28 where id=1;	更新age=18
commit	提交事务
		select 快照读 ,没有读到 age=28	select * from user
		select lock in share mode当前读 , 读到age=28	select * from table1 lock in share mode

事务A操作	事务A描述	事务B描述	事务B操作
begin	开启事务	开启事务	begin
select * from table1	快照读，查到age=18
update table1 set age=38 where id=1;	更新age=28
commit	提交事务
		select 快照读 age=38	select * from table1
		select lock in share mode当前读 age=38	select * from table1 lock in share mode

解决丢失更新和写入偏差 (写-写) （MARK）

丢失更新：

第一类丢失更新(回滚丢失，Lost update)
第二类丢失更新(覆盖丢失/两次更新问题，Second lost update)

写入偏差
??

解决方案

传统的悲观锁法（不推荐），现在的悲观锁法（推荐优先使用）
旧值条件（前镜像）法，使用版本列法（推荐优先使用），CAS机制就是乐观锁的典型实现
使用校验和法（不推荐）
使用ORA_ROWSCN法（不推荐）