MySQL事务(4种事务隔离级别、脏写、脏读、不可重复读、幻读、当前读、快照读、MVCC、事务指标监控)

小松聊PHP进阶

已于 2024-04-11 16:25:59 修改

阅读量1k

点赞数 21

分类专栏： MySQL 文章标签： mysql 数据库 sql dba 服务器

于 2024-03-09 09:51:06 首次发布

本文链接：https://blog.csdn.net/weixin_42100387/article/details/136325141

版权

MySQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

声明测试表，供文章案例使用

CREATE TABLE `cs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `num` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

事务的分类

显示事务：
- read write：读写事务，默认模式，表示当前事务可以读写数据。
- read only：只读事务，很少用，表示当前事务不能修改数据。
- with consistent snapshot：一致性快照，在数据库事务中确保事务在执行过程中能看到一个事务开始时的一致数据库状态，避免被其他并发操作影响。
隐式事务：不需要显示声明事务相关语句，autocommit是开启状态（默认值），每条DML和DDL的SQL语句都是一个独立的事务。

MySQL事务的4个特性：

原子性（Atomicity）：当前事务中的执行结果，要么全部执行成功，要么全部执行失败。
一致性（Consistency）：事务执行前后，数据库从一个合法（指符合业务预期）状态转换成另一个一合法状态。
隔离性（Isolation）：多个事务可以并发执行，各个事务之间的操作互相隔离互不干扰。
持久性（Durability）：事务提交，会持久化到磁盘中。

自动提交

自动提交（auto commit），指的是SQL语句执行完毕后自动将数据持久化到磁盘（刷盘）中。
显式的声明事务，或者声明set autocommit = 0;都可以关闭自动提交。

savepoint

俗称保存点，是用于实现部分事务回滚的一种机制，需要确定从哪里开始回滚，就需要savepoint的标识来定位。
回滚范围：从保存点开始到事务最后一条SQL，都会被回滚。
适用场景：用于复杂的业务逻辑中，给出灵活可控的后悔药，降低事务回滚影响范围。
注意：rollback to之后，不代表事务流程走完，还需要再次commit提交其它未回滚的事务。
用法：
savepoint 保存点名：创建一个 Savepoint，并为其指定一个名称。
rollbackto savepoint 保存点名;：将事务回滚到指定的 Savepoint。
release savepoint 保存点名;：释放指定的 Savepoint。
示例：

start transaction;
insert into cs(num) values(1);
savepoint insert_1;
insert into cs(num) values(2);
savepoint insert_2;
insert into cs(num) values(3);
savepoint insert_3;
insert into cs(num) values(4);
savepoint insert_4;
rollback to insert_2;
commit;
发现1,2数据被插入。

事务的隐式提交

在上一个事务没提交或回滚时，运行下一个事务，则上一个事务自动提交。

start transaction;
insert into cs(num) values(1);
insert into cs(num) values(2);
start transaction;
insert into cs(num) values(3);
insert into cs(num) values(4);
commit;
成功插入1,2,3,4。

4种隔离级别

读未提交（Read Uncommitted）：最低级别的隔离，事务中的修改即使未提交也能被其他事务看到，可能导致脏读、不可重复读和幻读问题。
读已提交（Read Committed）：保证一个事务提交后对其他事务可见，避免了脏读，但可能会导致不可重复读和幻读问题。
可重复读（Repeatable Read）：保证在同一事务内多次读取数据时，数据保持一致，避免了不可重复读问题，但仍可能出现幻读。
串行化（Serializable）：最高级别的隔离，通过对读取的数据添加共享锁或排他锁来确保事务之间的隔离性，避免了脏读、不可重复读和幻读问题，但可能会影响并发性能。

表格从上到下，越来越高可用，但是性能越来越低。

隔离级别	是否解决脏读	是否解决不可重复读	是否解决幻读	是否加锁
读未提交	否	否	否	否
读已提交	是	否	否	否
可重复读	是	是	否	否
串行化	是	是	是	是

查看或设置MySQL隔离级别

查看：select @@transaction_isolation; 或者 show variables like 'transaction_isolation;'
设置：set session transaction_isolation = 'read-uncommitted/read-committed/repeatable-read/serializable';
注意隔离级别是回话级别的，所以无法set glboal。

MySQL会发生什么读？

因为mysql默认隔离级别是可重复读（Repeatable Read），所以只会发生幻读情况，脏读和可重复度不会发生，除非改事务隔离级别。

脏写（不允许发生）

简介：一个事务修改某些数据时，另一个事务在未提交的情况下也修改了这些数据，引起的导致数据的不一致性。
危害：造成数据在并发情况下严重不一致。
演示：试不出来，脏写这么严重的bug，是不允许发生的情况。

脏读（读未提交隔离级别会发生）

简介：一个事务尚未commit（提交，刷盘，持久化），却读取了事务修改后的值，引起数据读取不准确的情况。
危害：事务还未提交就被读取了，该事务成功提交还好，要是回滚了，会造成读取数据不一致的问题。
演示：因为脏读是读未提交（Read Uncommitted）才会发生的情况，所以要降低MySQL的隔离级别。

步骤	会话A	会话B	备注
1	set session transaction_isolation = ‘read-committed’;	set session transaction_isolation = ‘read-committed’;	设置事务的隔离级别为读未提交
2	select @@transaction_isolation;	select @@transaction_isolation;	检查隔离级别是否设置成功
3	select num from cs where id = 20; #20	start transaction; update cs set num = 20 where id = 40;	会话A num的初始值为20
4	select num from cs where id = 20; #40	/	会话B并未commit，此时会话A中num的值为40，发生脏读现象
5	/	rollback	结束本次事务
6	select num from cs where id = 20; #20	/	num恢复为20

不可重复读（读未提交、读已提交隔离级别会发生）

简介：在事务A中读取某些数据，然后在事务B中修改这些数据，此时事务A读取这些数据还未发生变化，但是事务B提交后，并在事务A在未结束事务的前提下，那些数据发生了变化，不可重复读不是禁止读动作，而是重复读数据不一致。
一句话概括，在同一个事务中，受其它事务提交的影响，读取同一数据两次得到的结果不一致的现象。
危害：破坏了事务内数据的准确性，例如事务内的SQL有自增自减的逻辑，如果事务内的初始值受其他事物提交从而发生变化，那么这是个巨大的问题。
演示：因为不可重复读是读已提交（Read Committed）才会发生的情况，所以要降低MySQL的隔离级别。

步骤	会话A	会话B	备注
1	set session transaction_isolation = ‘read-uncommitted’;	set session transaction_isolation = ‘read-uncommitted’;	设置事务隔离级别为读已经提交
2	select @@transaction_isolation;	select @@transaction_isolation;	检查隔离级别是否设置成功
3	start transaction;	start transaction;	双方开启事务
4	select num from cs where id = 20; #20	select num from cs where id = 20; #20	两个会话中num的值为20
5	update cs set num = 40 where id = 20;	select num from cs where id = 20; #20	会话A将数据更新为40，此时会话B查询的值仍为20
6	commit	select num from cs where id = 40; #40	会话A提交事务，会话B仍在事务中，但是得到的值变成了40，发生了不可重复读
7	/	commit	结束事务

幻读（读未提交、读已提交、可重复读隔离级别会发生）

简介：同一个事务里前后查询两次相同范围的数据，后一次查询查询到了前一次看不到的东西，就好像出现了"幻影"一样。（注意，如果把会话B的insert改为delete导致的数据减少，不算幻读，算不可重复读）。
危害：没有充分的做好数据隔离，数据一致性存在问题。
演示：mysql 的默认隔离级别为REPEATABLE-READ，所以大概率不用调整隔离级别。

步骤	会话A	会话B	备注
1	select @@transaction_isolation;	select @@transaction_isolation;	检查隔离级别是否是REPEATABLE-READ
2	start transaction;	start transaction;	双方开启事务
3	select * from cs;	select * from cs;	两个事务查看，都只有id为20的一条数据
4	insert into cs (id,num) values(21,21);	select * from cs;	会话B查询，仍旧只有id为20的一条数据
5	commit	/	会话A提交事务
6	/	select * from cs;	即使会话A提交了事务，会话B查询仍旧无法搜索到会话A插入的数据，起始这一步已经幻读了，但是mysql不表明是幻读，所以到第7步测试
7	/	insert into cs (id,num) values(21,21);	因为会话B select查不到id为21的数据，所以插入id相同的数据，但是报错1062 - Duplicate entry ‘21’ for key ‘PRIMARY’
8	/	rollback;	回滚以结束事务流程

如何解决幻读？

或者使用串行化的隔离级别。在串行化隔离级别下，也会隐式的添加行（X）锁。
添加间隙锁，可以避免幻读。
mysql 的默认隔离级别为REPEATABLE-READ，又称为RR，通过MVCC的机制，如果对数据进行快照读，正因为读取的不一定第最新的数据，所以可以防止幻读（注意不是解决幻读），如果是当前读（最近数据），那么仍旧会发生幻读现象。

当前读

当前读读的就是数据最新的记录，需要保证当前读的数据不能被修改，修改了就不是最新的记录了（脏写），因此需要加锁，select for update、select lock in share mode以及DML（insert、update、delete）获取的数据都是当前读的数据。

快照读

快照读顾名思义，读取的就是由MVCC Read View控制的undo log的数据，不加锁，所以是读取是非阻塞的。不加锁的select都属于快照读。如果当前事务的隔离级别是串行化，那么快照读也变成了当前读。
举个例子：常用的navicat，查看一个表，事务提交前的insert或update语句，表格内仍旧显示的原数据，则用的快照读。

MVCC

MVCC（Multi-Version Concurrency Control）是 MySQL 中一种实现事务隔离的机制，用于处理数据库事务并发访问时可能出现的读写冲突。事务的四种隔离级别，就是通过MVCC机制提供的底层支撑。
MVCC三板斧：隐藏字段、Undo log（存放历史版本）、Read view（版本控制）

MVCC解决的是读已提交和可重复读级别的并发控制。
因为读未提交，就算事务未提交，可以直接读取最新的数据（脏读），相当于当前读，那就不分快照读和当前读了。
串行化的隔离级别，强制事务串行执行，也不存在快照读和当前读的区分，因为读取的都是事务执行过后的最新数据。

事务各项指标监控

查看 InnoDB 存储引擎中当前活动的事务信息。

SELECT * FROM information_schema.innodb_trx;

trx_id                         事务的唯一标识符。
trx_state                      事务的状态，如 RUNNING、LOCK WAIT、ROLLING BACK 等。
trx_started                    事务启动的时间。
trx_requested_lock_id          请求的锁的标识符。
trx_wait_started               等待锁的开始时间。
trx_weight                     事务的权重，用于死锁检测。
trx_mysql_thread_id            MySQL 线程 ID。
trx_query                      与事务相关的 SQL 查询语句。
trx_operation_state            事务内部操作的状态。
trx_tables_in_use              事务使用的表的数量。
trx_tables_locked              事务锁定的表的数量。
trx_lock_structs               事务内部使用的锁结构数量。
trx_lock_memory_bytes          用于事务锁定的内存字节数。
trx_rows_locked                事务锁定的行数。
trx_rows_modified              事务修改的行数。
trx_concurrency_tickets        用于事务并发控制的票数。
trx_isolation_level            事务的隔离级别。
trx_unique_checks              是否启用了唯一性检查。
trx_foreign_key_checks         是否启用了外键约束检查。
trx_last_foreign_key_error     最后一个外键错误信息。
trx_adaptive_hash_latched      是否适应性哈希被锁定。
trx_adaptive_hash_timeout      适应性哈希锁定超时次数。
trx_is_foreign_key_with_check  是否用于外键约束检查。
trx_is_foreign_key             是否用于外键约束。

小松聊PHP进阶

关注

21
点赞
踩
25

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL事务(4种事务隔离级别、脏写、脏读、不可重复读、幻读、当前读、快照读、MVCC、事务指标监控)

当前读读的就是数据最新的记录，需要保证当前读的数据不能被修改，修改了就不是最新的记录了（脏写），因此需要加锁，select for update、select lock in share mode以及DML（insert、update、delete）获取的数据都是当前读的数据。串行化的隔离级别，强制事务串行执行，也不存在快照读和当前读的区分，因为读取的都是事务执行过后的最新数据。因为读未提交，就算事务未提交，可以直接读取最新的数据（脏读），相当于当前读，那就不分快照读和当前读了。显式的声明事务，或者声明。
复制链接

扫一扫