mysql事务的acid和隔离级别

我是一只代码狗

已于 2024-03-04 16:49:08 修改

阅读量127

点赞数

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

于 2023-11-21 21:14:52 首次发布

原文链接：https://mp.csdn.net/mp_blog/creation/editor/134539473

版权

mysql 专栏收录该内容

59 篇文章 0 订阅

订阅专栏

本文详细解释了MySQL8.0中的事务特性，包括隔离性、原子性、一致性、持久性，以及不同隔离级别的应用场景，如读取未提交、读取已提交、可重复读和可串行化，以及可能出现的脏读、幻读问题。

摘要由CSDN通过智能技术生成

mysql 8.0 查询默认隔离级别

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

默认的隔离级别是可重复读

事务的4大特性 Acid

隔离性，持久性，一致性，原子性

CREATE TABLE `biao` (
`id` int NOT NULL AUTO_INCREMENT,
`num` int DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `biao` (`id`, `num`, `name`) VALUES (1, 100, '张三');

隔离性

隔离性：当前事务操作不会被其他事务所干扰

来看下场景

BEGIN

SELECT * from biao;

UPDATE biao set num=70 WHERE id=1;

在A事务进行修改num为70，但是还没有提交

在B事务进行修改num为50，还没有提交

BEGIN

SELECT * from biao;

UPDATE biao set num=50 WHERE id=1;

这时候就会触发加锁，这就是事务的隔离性

原子性

原子性：要么都成功，要么都失败

BEGIN;

SELECT * from biao;

UPDATE biao set num=70 WHERE id=1;

INSERT INTO `biao` ( `num`, `name`) VALUES ( 2000000000000000000, 33333);

ROLLBACK;

当修改成功,添加失败，那么这时候触发了回滚，这就是事务的原子性

一致性

一致性：读取和修改都是一样的，数据不会出现错误

A事务把num修改减去20

BEGIN;

SELECT * from biao;

UPDATE biao set num=num-20 WHERE id=1;

COMMIT;

B事务修改减去30

BEGIN;

SELECT * from biao;

UPDATE biao set num=num-30 WHERE id=1;

COMMIT;

那么这时候还剩下50，数据是正常的，这就是一致性

持久性

持久性：当事务提交之后，数据会存储到磁盘上，不会丢失

通过下面的命令可以查询mysql存储的位置

show VARIABLES like 'datadir'

脏读

就是可以读取到别的事务还没有提交的数据

脏写

两个事务同时对一条sql进行修改，后面的sql覆盖了前面的sql，前面的sql修改的结果丢失,这就是脏写

幻读

读取到别的事务突然多了一条，或者少了一条数据，受影响的是行数的变化

不可重复读

在一个事务内，每次读取到的数据都是不一样的，不符合事务的隔离性

事务的隔离级别

读取未提交，读取已提交，可重复读，可串行化

A事务和B事务要同时开启事务

读取未提交

就是可以读取到别的事务还没有提交的数据，会产生脏读，幻读，不可重复读

A事务设置隔离级别为读取未提交并修改num为70

set session transaction isolation level read uncommitted;

BEGIN;

SELECT * from biao;

UPDATE biao set num=70 WHERE id=1;

B事务也设置隔离级别为读取未提交，读取表的数据

set session transaction isolation level read uncommitted;

BEGIN;

SELECT * from biao;

可以发现在b事务读取的时候，A事务还没有提交事务，这个就是脏读，影响的是数据的变化

当A事务进行了添加操作还没有提交事务

B事务读取表数据，发先表出现多了一行

A事务触发回滚操作，B事务这时候读取了一条数据，那么这就是幻读，读取的是受影响的行数

读取已提交

读取别的事务已经提交的数据，不会产生脏读，但是会产生幻读和不可重复读

A事务设置事务的隔离级别，先开启事务，在修改

set session transaction isolation level read committed;

BEGIN;

SELECT * from biao;

UPDATE biao set num=70 WHERE id=1;

COMMIT;

B事务设置事务的隔离级别，先开启事务，在查看

set session transaction isolation level read committed;
BEGIN;
SELECT * from biao;

当A事务还没提交之前，B事务查询的还是之前的num=100

当A事务提交之后，B事务查询的就是num=70

这时候就不会产生脏读

如果A事务出现新增，并且提交事务，B事务再次查询就会产生幻读，多出来一行数据

可重复读

每次读取的数据都是一样的，mysql默认的事务隔离级别，不会产生脏读，不可重复读

但是对于幻读问题并不能完全解决

A事务新增一条数据，并提交事务

set session transaction isolation level repeatable read;

BEGIN;

SELECT * from biao;

INSERT INTO `biao` (`num`, `name`) VALUES ( 200, '李四');

COMMIT;

B事务查询，还是只有1条数据，每次读取的都是一样的，这就是可重复读