MySQL事务-EW帮帮网

后面也会持续更新，学到新东西会在其中补充。

建议按顺序食用，欢迎批评或者交流！

缺什么东西欢迎评论！我都会及时修改的！

感谢各位大佬写的文章让我学到很多东西！只是在各位大佬的基础加了我自己的思路！

(七)MySQL事务篇：ACID原则、事务隔离级别及事务机制原理剖析MySQL的核心是存储数据，是整个业务系统中最重要的 - 掘金

保姆级教程，终于搞懂脏读、幻读和不可重复读了！18张图，模拟演示并发事务的隔离级别问题，在 MySQL 中事务的隔离级别 - 掘金第 01 期 [事务] 事务的起源：事务池和管理器的初始化

第 02 期 [事务] BEGIN 语句会马上启动事务吗？

第 03 期 [事务] 我是一个事务，请给我一个对象

MySQL 核心模块揭秘 | 04 期 | 终于要启动事务了

MySQL innodb_print_ddl_logs参数介绍 - 吴昊博客

mysql查看当前事务id_select 事务id-CSDN博客

MySQL 是怎样运行的：从根儿上理解 MySQL - 小孩子4919 - 掘金小册

实验环境

mysql9.0.4

经典的例子

+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 狗哥   |      11 |
|  2 | 猫爷   |       2 |
+----+--------+---------+

UPDATE account SET balance = balance - 10 WHERE id = 1;
UPDATE account SET balance = balance + 10 WHERE id = 2;

上述两条语句只执行了一条时忽然服务器断电了咋办？

扣了狗哥的钱但是猫爷没有加钱。

Buffer Pool，在对某个页面进行读写访问时，都会先把这个页面加载到Buffer Pool中，之后如果修改了某个页面，也不会立即把修改同步到磁盘，而只是把这个修改了的页面加到Buffer Pool的flush链表中，在之后的某个时间点才会刷新到磁盘。

如果在将修改过的页刷新到磁盘之前系统崩溃了 喵爷还是得不到钱。

因此，引入了下面的四条规则。

环境搭建

mysql> create table type(id int,card int,primary key (id));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into type values(1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into type values(2,2);
Query OK, 1 row affected (0.00 sec)

ACID

Atomicity原子性

原子性靠 undo log、回滚，保证原子操作。

Consistency一致性

举个栗子：比如原本的总库存是10个，此时库存剩余8个，那也就代表着必须要有2条订单数据才行。

这个时候我再买一个这样的商品，订单数得加1库存得减1。订单数或者库存但凡一个没有变都是没有保留一致性。

如果出现库存减了，但订单没有增加的情况，就代表着事务执行过程中出现了异常，此时MySQL就会利用事务回滚机制，将之前减的库存再加回去，确保数据的一致性。

原子性+隔离性共同保证

Isolation隔离性

举个栗子：比如原本的总库存是10个，此时库存剩余1个，有9条订单数据。

A事务和B事务同时购买这个商品库存会变成负数嘛？

当然不会！

此时A、B两个事务一起执行，同一时刻执行减库存的SQL，因此这里是并发执行的，ACID原则中的隔离性保障了并发事务的顺序执行，一个未完成事务不会影响另外一个未完成事务。

那么就算A事务执行完B事务就被回滚了。

MVCC快照读，或者当前读，保证读到的是符合预期，防止脏读或者幻读。

Durability持久性

持久性是ACID原则中最容易理解的一条，持久性是指一个事务一旦被提交，它会保持永久性，所更改的数据都会被写入到磁盘做持久化处理，就算MySQL宕机也不会影响数据改变，因为宕机后也可以通过日志恢复数据。

redo log 和 bin log。

至死不渝

事务的概念

事务大致上划分成了这么几个状态：

活动的（active）

事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态。
部分提交的（partially committed）

当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在部分提交的状态。
失败的（failed）

当事务处在活动的或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态。
中止的（aborted）

如果事务执行了半截而变为失败的状态，要撤销失败事务对当前数据库造成的影响。把这个撤销的过程称之为回滚。当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了中止的状态。

提交的（committed）

当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说该事务处在了提交的状态。

只有当事务处于提交的或者中止的状态时，一个事务的生命周期才算是结束了。

对于已经提交的事务来说，该事务对数据库所做的修改将永久生效，对于处于中止状态的事务，该事务对数据库所做的所有修改都会被回滚到没执行该事务之前的状态。

CAP定理

一致性C代表更新操作成功后，所有节点在同一时间的数据完全一致。

可用性A代表用户访问数据时，系统是否能在正常响应时间返回预期的结果。

分区容错性P代表分布式系统在遇到某节点或网络分区故障的时候仍然能够对外提供满足一致性或者可用性的服务。

不可能CAP都满足，只能满足比如CA、CP、AP

CP强一致性：

用户体验较差，用户处于一直等待的状态。但是CP可以保证数据是一致的。

AP：

AP表现为订单创建后不等待库存减少直接返回处理结果。

用户这一侧不管库存是否减少，为了保证用户的可用性牺牲数据的一致性。

库存是异步处理的，异步处理后通知订单系统，如果库存没有减少成功，系统就要采用补偿机制，比如重新发起请求，人工补录，校对程序保证数据最终是一致的。

CP常常用到银行这些地方，AP一般用到互联网保证用户的体验。

CA：

单体应用，数据不分区所有数据都在一个数据库。在一个数据库通过一个事务加订单减库存，要提交全部提交要回滚全部回滚。处理效率是很高的，事务的完整性很高的。缺点不能做分区，分区涉及网络进而涉及分区容错性，就得在AP和CP中选择。

CA就是单体架构，舍去了分布式。

AP 银行数据要一致，可以分区

CP 互联网响应数据要快，可以分区

CA 小公司数据要一致，响应要快。没分区

MySQL事务

开启事务

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

两种SQL意义相同

只不过START TRANSACTION语句后边跟随几个修饰符

READ ONLY：标识当前事务是一个只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。（but 可以对临时表（CREATE TMEPORARY TABLE创建的表）由于它们只能在当前会话中可见，因此可以进行增删改查）
READ WRITE：标识当前事务是一个读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
WITH CONSISTENT SNAPSHOT：启动一致性读

只读事务
START TRANSACTION READ ONLY;

START TRANSACTION后边跟随多个修饰符
START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT;    只读事务和一致性读
START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT    读写事务和一致性读

READ ONLY和READ WRITE是用来设置所谓的事务访问模式的，就是以只读还是读写的方式来访问数据库中的数据，一个事务的访问模式不能同时既设置为只读的也设置为读写的，我们不能同时把READ ONLY和READ WRITE放到START TRANSACTION语句后边。另外，如果我们不显式指定事务的访问模式，那么该事务的访问模式就是读写模式。

提交事务

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

手动中止事务

如果我们写了几条语句之后发现某条语句写错了，使用下边这个语句来将数据库恢复到事务执行之前的样子：

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

ROLLBACK语句是我们程序员手动的去回滚事务时才去使用的，如果事务在执行过程中遇到了某些错误而无法继续执行的话，事务自身会自动的回滚。

支持事务的存储引擎

目前只有InnoDB和NDB存储引擎支持

自动提交

我们的每一条SQL都是一个事务，为什么？因为MySQL的自动提交。

关闭自动提交就需要我们自己提交了。

借着这个机会我们再演示一下事务的隔离性

隐式提交

START TRANSACTION或者BEGIN语句开启了一个事务，或者把系统变量autocommit的值设置为OFF时，事务就不会进行自动提交，但是如果我们输入了某些语句之后就会悄悄的提交掉，就像我们输入了COMMIT语句了一样，这种因为某些特殊的语句而导致事务提交的情况称为隐式提交，这些会导致事务隐式提交的语句包括：

定义或修改数据库对象的数据定义语言（Data definition language，缩写为：DDL）。

所谓的数据库对象，指的就是数据库、表、视图、存储过程等等这些东西。当我们使用CREATE、ALTER、DROP等语句去修改这些所谓的数据库对象时，就会隐式的提交前边语句所属于的事务，就像这样：

BEGIN;

SELECT ... # 事务中的一条语句
UPDATE ... # 事务中的一条语句
... # 事务中的其它语句

CREATE TABLE ... # 此语句会隐式的提交前边语句所属于的事务

隐式使用或修改mysql数据库中的表

当我们使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等语句时也会隐式的提交前边语句所属于的事务。

事务控制或关于锁定的语句

在一个事务还没提交或者回滚时就又使用START TRANSACTION或者BEGIN语句开启了另一个事务时，会隐式的提交上一个事务，比如这样：

BEGIN;

SELECT ... # 事务中的一条语句
UPDATE ... # 事务中的一条语句
... # 事务中的其它语句

BEGIN; # 此语句会隐式的提交前边语句所属于的事务

autocommit系统变量的值为OFF，我们手动把它调为ON时，也会隐式的提交前边语句所属的事务。

或者使用LOCK TABLES、UNLOCK TABLES等关于锁定的语句也会隐式的提交前边语句所属的事务。

加载数据的语句

使用LOAD DATA语句来批量往数据库中导入数据时，也会隐式的提交前边语句所属的事务。

关于MySQL复制的一些语句

START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等语句时也会隐式的提交前边语句所属的事务。

其它的一些语句

使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等语句也会隐式的提交前边语句所属的事务。

保存点

CREATE TABLE account (
    id INT NOT NULL AUTO_INCREMENT COMMENT '自增id',
    name VARCHAR(100) COMMENT '客户名称',
    balance INT COMMENT '余额',
    PRIMARY KEY (id)
) Engine=InnoDB CHARSET=utf8;
insert into account values(1,'王',10);
insert into account values(2,'李',20);

保存点（英文：savepoint）的概念，就是在事务对应的数据库语句中打几个点，我们在调用ROLLBACK语句时可以指定会滚到哪个点，而不是回到最初的原点。

SAVEPOINT 保存点名称;

想回滚到某个保存点时，可以使用下边这个语句

ROLLBACK TO 保存点名称;

如果我们想删除某个保存点，可以使用这个语句：

RELEASE SAVEPOINT 保存点名称;

举例：

mysql> set autocommit = off;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | 王   |      10 |
|  2 | 李   |      20 |
+----+------+---------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> update account set balance = balance - 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | 王   |       0 |
|  2 | 李   |      20 |
+----+------+---------+
2 rows in set (0.00 sec)

mysql> savepoint s1; #保存点 s1
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = balance + 1 where id = 2; # 写错了回滚到保存点s1
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | 王   |       0 |
|  2 | 李   |      21 |
+----+------+---------+
2 rows in set (0.00 sec)

mysql> rollback to s1; # 回滚到保存点s1
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | 王   |       0 |
|  2 | 李   |      20 |
+----+------+---------+
2 rows in set (0.00 sec)

脏读、不可重复读、幻读三幻神

我们的角度是对于事务的隔离性来说的。

select @@global.transaction_isolation,@@transaction_isolation;
            全局事务隔离级别                会话事务隔离级别

脏读

脏读的意思是指一个事务读到了其他事务还未提交的数据，也就是当前事务读到的数据，由于还未提交，因此有可能会回滚。

窗口2                                   隔离级别是读未提交
set session transaction isolation level read uncommitted;
start transaction;
select * from type;
窗口1
start transaction;
update type set card = 5 where id = 2;

PS：事务隔离级别读未提交存在脏读的问题。

可以得到会话的隔离级别优先于全局的隔离级别

不可重复读

不可重复读问题是指在一个事务中，事务中前后读取到的数据不一致。

窗口2                                   隔离级别是读已提交
set session transaction isolation level read committed;
start transaction;
select * from type;
窗口1
start transaction;
update type set card = 10 where id = 2;

PS：读已提交可以解决脏读的问题，但存在不可重复读的问题。

要是窗口2先commit再测试一下呢？

脏读可以读到其他事务中未提交的数据，而不可重复读是读取到了其他事务已经提交的数据，但前后两次读取的结果不同。

按正常逻辑事务A是没有对type表进行修改的，因此不管怎样数据都不应该改变！

幻读

幻读是一次事务中前后数据量发生变化用户产生不可预料的问题。

并发新增、删除这种会产生数量变化的操作时，另一个事务前后查询相同数据时的不符合预期。

窗口2                                   隔离级别是可重复读
set session transaction isolation level repeatable read;
start transaction;
select * from type;
窗口1
start transaction;
insert into type values(3,3);

PS：可重复读可以解决脏读、不可重复读的问题，但存在幻读的问题。

例子1：

在一个事务中明明没有查到主键为 3的数据，但主键为 3 的数据就是插入不进去，就像某种幻觉一样。

总结一下就是这样的：

竟然会事务失效？

#我们start transaction;并没有真正的”启动事务“ 事务有读事务、写事务。而我们此时只是给事务打了一个begin标签
start transaction;

sessionA	sessionB
select * from type;
start transaction;	start transaction;
	insert into type values(10,10);
	commit;
select * from type;

为什么？按照正常逻辑来说应该还是7行数据啊！

commit后才创建了一个读事务，且创建了一个视图（MVCC）。sessionB的事务已经提交，因此可以读到sessionB的数据。

start transaction with consistent snapshot;换这个试试？

MVCC

【IT老齐030】这可能是最直白的MySQL MVCC机制讲解啦!_哔哩哔哩_bilibili

在MySQL InnoDB存储引擎下RC（读已提交）、RR（可以重复读）基于MVCC（多版本并发控制）进行并发事务控制。

MVCC是基于“数据版本”对并发事务进行访问。

为什么RR级别和RC级别输出不同呢？

MVCC基本控制下前提是需要基于UNDO_LOG版本链

undo log就是回滚日志事务处理的时候，需要增加回滚日志，假如事务执行失败执行rollback才能进行还原。

执行一条SQL就会产生undo日志，不管提交是否，这里之前理解有问题！

undo_log版本链并不是立即删除，MySQL确保版本链数据不再被引用后再进行删除。

删除undo_log的线程叫做purge线程

ReadView读视图

在快照读的时候才会使用MVCC。

ReadView是快照读SQL执行时MVCC提取数据的依据

快照读就算最普通的select查询SQL语句

当前读指代执行下列语句进行数据读取的方式

insert、update、delete、select for update、select for share。

ReadView是一个数据结构，包含4个字段。

m_ids:当前活跃的事务编号集合，记录的哪些事务还没被提交！！！！！！！！！！！
min_trx_id:最小活跃事务编号
max_trx_id:预分配事务编号，当前最大事务编号+1
creator_trx_id:ReadView创建者的事务编号

RR是第一次快照读生成，RC是每次快照读生成。

先看RC隔离级别！

分析数据提取的过程

RC的第一次ReadView

RC的第二次ReadView

可重复读（RR）：仅在第一次执行快照读时生成，ReadView快照读会复用。

因此解决了不可重复读的情况了！RR级别使用MVCC能避免幻读嘛？

连续多次快照读，ReadView会产生复用，没有幻读问题。

特例

当两次快照读之间存在当前读，ReadView会重新生成，导致产生幻读。

sessionA	sessionB
start transaction;	start transaction; ReadView1
insert into type values(9,9);
commit;
	update type set card = 99 where id = 9; 当前读导致了重新生成快照读
	select * from type; ReadView2

事务执行顺序

事务池和事务对象

一个事务管理池器可以有多个事务池。

创建事务池管理器

初始化事务池

绿色代表有数据了红色代表没有

那么剩余内存什么时候初始化？

开启事务的多种方式

/*  1 */ BEGIN 开启读写事务
/*  2 */ BEGIN WORK 开启读写事务
/*  3 */ START TRANSACTION  开启读写事务       
/*  4 */ START TRANSACTION READ WRITE  开启读写事务
/*  5 */ START TRANSACTION READ ONLY   开启只读事务
/*  6 */ START TRANSACTION WITH CONSISTENT SNAPSHOT     开启新的读写事务并创建一致性读视图
/*  7 */ START TRANSACTION WITH CONSISTENT SNAPSHOT, READ WRITE 开启新的读写事务并创建一致性读视图
/*  8 */ START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY 开启新的只读事务并创建一致性读视图
语句 1 ~ 5 都不会马上启动新事务，只会给执行这些语句的线程打上 OPTION_BEGIN 标记

开启事务都做了什么

正好新年也快到了这里祝大家新年快乐。

事务做的就是辞旧迎新的工作。

会发生什么呢？

辞旧流程：

OPTION_NOT_AUTOCOMMIT 是指当前会话没有开启自动提交事务，

而OPTION_AUTOCOMMIT 表示的是当前会话开启了自动提交事务。

OPTION_AUTOCOMMIT 和 OPTION_NOT_AUTOCOMMIT 是通过系统变量 autocommit 控制的。

迎新流程：

用户事务和内部事务

3.待续

mysql> SHOW VARIABLES LIKE 'innodb_print_ddl_logs';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_print_ddl_logs | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)
SET GLOBAL innodb_print_ddl_logs = ON;

mysql> SHOW VARIABLES LIKE 'log_error_verbosity';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| log_error_verbosity | 2     | 这个值要保存到3要不然不会产生log日志
+---------------------+-------+
1 row in set (0.00 sec)

开启日志参数，InnoDB会把DDL打印到错误日志中。

DDL包含用于定义和修改数据库结构的SQL语句。这些语句主要用于创建、修改和删除数据库对象，如表、索引和视图。

分配事务对象

show engine innodb status;

事务状态（trx->state）。出厂设置的事务对象，事务状态是 TRX_STATE_NOT_STARTED，表示事务还没有开始。

还有一个属性

trx->lock.autoinc_locks：分配一块内存空间，用于存放 autoinc 锁结构。事务执行过程中需要为 auto_increment 字段生成自增值时使用。

加入事务链表

select * from information_schema.innodb_trx;
能看到当前正在执行的事务有哪些，这些事务来源于两个链表。

SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX  WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
获取当前事务ID

读事务

事务启动于执行第一条 SQL 语句时，如果第一条 SQL 语句是 select，InnoDB 会以读事务的身份启动新事务。

读事务的 ID 会被设置为 0 而实际上事务的 ID是一大串数字。

只读事务

可以改变用户临时表的数据。

临时表支持事务的ACI特性没有持久性因为不存在磁盘上。

如果只读事务执行的第一条 SQL 语句就是插入记录到用户临时表的 insert，事务启动过程中会分配事务 ID。

-- 开始只读事务之前创建一个用户临时表
CREATE TEMPORARY TABLE tmp_table (
      name VARCHAR(10) NOT NULL, 
      time date  NOT NULL
  );

读写事务/内部事务

如果事务执行的第一条 SQL 语句是 insert，update，delete这个事务就会以读写事务的身份启动。

内部事务都是读写事务，所以放到 rw_trx_list 了，不会放到 mysql_trx_list。

update、delete执行顺序

读事务(二阶段)

读事务启动时，没有分配事务 ID 和回滚段，事务对象也没有加入到 trx_sys->rw_trx_list 链表。

以读事务身份启动的事务，不仅能正常执行改变（插入、更新、删除）表中数据的操作，还支持 MVCC、回滚。

读事务可以通过update、delete操作变为读写事务。

MySQL 核心模块揭秘 | 05 期 | 读事务和只读事务的变形记

待续这里有点看不懂回滚段啥的去学一下。

总结

所有结论都需要反复测试！如果有错误欢迎指正！一起努力！

如果喜欢的话，请点个赞吧就算鼓励我一下！

MySQL事务

实验环境

经典的例子

环境搭建

ACID

Atomicity原子性

Consistency一致性

Isolation隔离性

Durability持久性

事务的概念

CAP定理

MySQL事务

开启事务

提交事务

手动中止事务

支持事务的存储引擎

自动提交

隐式提交

保存点

脏读、不可重复读、幻读三幻神

竟然会事务失效？

MVCC

ReadView读视图

特例

事务执行顺序

事务池和事务对象

创建事务池管理器

初始化事务池

开启事务的多种方式

开启事务都做了什么

用户事务和内部事务

分配事务对象

加入事务链表

读事务

只读事务

读写事务/内部事务

update、delete执行顺序

读事务(二阶段)

总结

网站公告

今日签到

热门文章

最新发布