mysql 四大事务隔离级别及实现方式

     今天聊聊老生长谈的mysql事务隔离级别,相信大家应该听到这个名词不会陌生。我们都知道事务具有ACID特性(原子性,一致性,持久性,隔离性),今天聊的主题肯定是跟隔离性有关的,以下都基于mysql innodb引擎

名词概念说明

  • 脏读


    读取到其他事务还没有提交的数据

    例:事务A在执行过程中,事务B插入or修改一条数据此刻尚未提交。A此刻查询出了B插入的记录,若B事务回滚。则A查询出了不正确的记录,这就是脏读现象


  • 不可重复读

    同一事务内查询,由于别的事务将数据修改or删除,导致执行两次查询结果可能不一样。

    例:事务A执行查询年龄小于28岁的员工,用户id 为 1 ,3,5,10.此刻事务B修改了id为3的年龄为30岁,A再次执行查询少了id为3的记录。两次查询结果集不一样,这就是不可重复读现象

  • 幻读
    通常针对插入操作,与不可重复读的区别是不可重复读,是由于数据变更导致数据变少。幻读是因为后一次insert操作导致数据增加,先前没有读取到。

    例:事务A将数据库性别为男的年龄都update成28,此刻A事务没提交事务B插入了一个性别男30岁的记录。A事务查询记录发现还有一条没有修改,就会感觉产生了幻觉一样。

    事务隔离级别

  • 读未提交(Read Uncommited)
  • 读已提交(Read Commited)
  • 可重复读(Repeatable Read)
  • 串行化(Serializable)

上述隔离级别隔离强度逐渐增强,性能逐渐下降。可重复读为mysql默认隔离级别。以下表格列出相应隔离级别是否会出现上述问题

隔离级别脏读不可重复读幻读
读未提交(Read Uncommited)可能可能可能
读已提交(Read Commited)不可能可能可能
可重复读(Repeatable Read)不可能不可能其他引擎可能(innodb 利用间隙锁)
串行化(Serializable)不可能不可能不可能

纸上得来终觉浅,实操一把。

测试表结构

CREATE TABLE `user_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` char(4) COLLATE utf8mb4_bin NOT NULL,
  `sex` char(1) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- 初始化数据


insert  into `user_info`(`id`,`name`,`sex`,`age`) values (1,'One','男',29),(2,'貂蝉','女',27),(3,'王昭君','女',18),(4,'吕布','男',30);

-- 查看mysql版本 我的是5.7.33
SELECT VERSION();

-- 查看当前数据库默认隔离级别
SHOW VARIABLES LIKE '%transaction_isolation%'

-- 设置手动提交事务
SET  autocommit = 0; 
SHOW VARIABLES LIKE 'autocommit';

读未提交测试 

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SHOW VARIABLES LIKE '%transaction_isolation%'

--  设置之后,只对新session生效。需要重新连接mysql。

-- 设置手动提交事务
SET  autocommit = 0; 
SHOW VARIABLES LIKE 'autocommit';
-- 会话1 执行修改id1的年龄,并未手动commit
UPDATE user_info SET age=30 WHERE id =1;

-- 在会话2 执行查询,已经查询到了修改后的数据
SELECT * FROM user_info  WHERE id =1;

读已提交测试

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SHOW VARIABLES LIKE '%transaction_isolation%'

--  设置之后,只对新session生效。需要重新连接mysql。

-- 设置手动提交事务
SET  autocommit = 0; 
SHOW VARIABLES LIKE 'autocommit';

此刻表当前数据为

-- 会话1修改年龄,未commit
UPDATE user_info SET age=30 WHERE id =1;

SELECT * FROM user_info

-- 会话2是查询不到更新记录的

SELECT * FROM user_info

执行commit后

 可重复读测试

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SHOW VARIABLES LIKE '%transaction_isolation%'

--  设置之后,只对新session生效。需要重新连接mysql。

-- 设置手动提交事务
SET  autocommit = 0; 
SHOW VARIABLES LIKE 'autocommit';

当前数据

-- 会话1 开启事务读取数据

START TRANSACTION; 

SELECT * FROM user_info;

-- 会话2 修改数据
START TRANSACTION; 
UPDATE user_info SET age=29 WHERE id =1;
COMMIT;

-- 会话1 执行查询,数据跟第一次查询一致

SELECT * FROM user_info;

实现方式

  • 读未提交(Read Uncommited)


性能最好,没有任何加锁操作,没有隔离效果


  • 读已提交(Read Commited)

    mvcc readview 版本链

  • 可重复读(Repeatable Read)

    InnoDB的MVCC(多版本并发控制)解决了可重复读问题,每一行实际都可能存在多个版本记录,数据记录有一个trx_id  代表事务id。可重复读是在事务开始的时候生成一个当前事务全局性的快照,而读提交则是每次执行语句的时候都重新生成一次快照。当前读解决幻读是借助于行锁跟间隙锁结合,这个锁称为Next-Key锁。

  • 串行化(Serializable)

    单线程执行,读的时候加共享锁允许并发读。写的时候使用排它锁,其他事务不能并发写也不能并发读。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值