mysql隔离级别及安全性问题演示

nacrt

已于 2022-06-10 22:54:56 修改

阅读量531

点赞数

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

于 2021-03-16 15:56:29 首次发布

本文链接：https://blog.youkuaiyun.com/qq_31975227/article/details/114837558

版权

MySQL 专栏收录该内容

10 篇文章

订阅专栏

本文详细介绍了MySQL中事务的四大特性及其隔离级别的设置方法，并通过具体案例演示了脏读、不可重复读和幻读等问题的发生及如何通过调整隔离级别来避免这些问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、mysql隔离级别

1.事务的特性:

1. 原子性:强调事务的多个操作不可分割.	
2. 一致性:强调事务执行的前后, 数据完整性保持一致.
3. 隔离性:强调事务执行过程中, 不应该受到其他事务的干扰.
4. 持久性:强调事务一旦提交成功, 数据就存储到磁盘内.

2.如果不考虑事务的隔离性, 有可能会引发一些安全性的问题

读的方面:
	脏读：指的是一个事务读到另一个事务未提交的数据.
	不可重复读：指的是一个事务读到了另一个事务已经提交的 update的数据, 
		导致多次查询结果不一致.
	虚读/幻读：指的是一个事务读到了另一个事务已经提交的 insert的数据,
		导致多次查询结果不一致
写的方面：
	丢失更新.

3.事务的隔离级别

设置事务的隔离级别:

read uncommitted: //未提交读
脏读、不可重复读、幻读都可能发生。
read committed: //已提交数据库
避免脏读, 但是不可重复读和虚读是有可能发生的.
repeatable read: //可重复读
避免脏读, 不可重复读. 但是虚读是有可能发生的.
- 值得注意的是，在innoDB下，RR可以避免快照读（ReadView）的幻读问题(MVCC解决)
- 不过在当前读（insert,update,delete,select … for update, select … lock in share mode）(行锁、间隙锁实现)情况下，还有会有幻读的情况
serializable: 串行化的
避免脏读, 不可重复读, 虚读

安全性：read uncommitted < read committed < repeatable read< Serializable
效率：read uncommitted > read committed > repeatable read > Serializable
一般数据库设置: read committed 和 repeatable read
MySQL默认: repeatable read
Oracle默认: read committed

二、安全性的问题的演示

-- 准备条件：
CREATE TABLE `user_account` (
  `id` int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `amount` decimal(10,0) DEFAULT NULL
);
INSERT INTO `user_account`(`name`, `amount`) VALUES ('zs', 1000);
INSERT INTO `user_account`(`name`, `amount`) VALUES ('ls', 1000);

1.演示"脏读"的产生:

开启两个cmd窗口A、B 登录mysql
查看当前的事务的隔离级别:

select @@tx_isolation;	-- 默认为: repeatable-read
-- 若执行报错，可尝试下面命令（新版mysql）
 select @@transaction_isolation;

将A窗口的隔离级别修改为: read uncommitted;

set session transaction isolation level read uncommitted;

再次查看下两个窗口的的隔离级别:

select @@tx_isolation;

在这里插入图片描述
5. 在A, B两个窗口中分别开启事务:

-- A、B窗口分别开启事务
start transaction;
-- A 查看所有数据
select * from user_account;

在这里插入图片描述
6. 在B窗口中完成转账.（注意: 先不要提交事务.）

-- B 执行转账交易
update user_account set amount = amount - 100 where name = 'zs';
update user_account set amount = amount + 100 where name = 'ls';

7.在A窗口中进行查询，读到了B未提交的数据.

-- A 再次查看所有数据
select * from user_account;

在这里插入图片描述

发现转账已经成功, 说明脏读已经发生了.（脏读：一个事物读取到另一个事务未提交的数据.）

2.演示"不可重复读"的产生:

开启两个cmd窗口A, B.
查看当前的事务的隔离级别:

select @@tx_isolation

将A窗口的隔离级别修改为: read committed;

set session transaction isolation level read committed;

再次查看下两个窗口的的隔离级别:

select @@tx_isolation;

前四步
5. 在A, B两个窗口中分别开启事务:

start transaction;

在B窗口中完成转账.（注意: 先不要提交事务.）

-- B 执行转账交易
update user_account set amount = amount - 100 where name = 'zs';
update user_account set amount = amount + 100 where name = 'ls';

在这里插入图片描述
7. 在A窗口中进行查询.

select * from user_account;

在这里插入图片描述

发现A窗口中的数据没有变化, 说明脏读没有发生.（脏读: 一个事物读取到另一个事务未提交的数据.）

在B窗口中提交事务.

-- B commit
commit;
-- A 再次查询
select * from user_account;

在这里插入图片描述

发现A窗口中的数据已经变化了, 说明"不可重复读"已经产生了,
不可重复读: 指的是一个事务读到了另一个事务已经提交的 update的数据,
导致多次查询结果不一致.

3.避免演示"不可重复读"的产生:

开启两个cmd窗口A, B.
查看当前的事务的隔离级别:

select @@tx_isolation;	(默认为: repeatable read)

将A窗口的隔离级别修改为: repeatable read;

set session transaction isolation level repeatable read;

再次查看下两个窗口的的隔离级别:

select @@tx_isolation;

在这里插入图片描述

在A, B两个窗口中分别开启事务:

start transaction;

在B窗口中完成转账.(注意: 先不要提交事务.)

-- A 先查询
select * from user_account;
-- B 执行转账交易
update user_account set amount = amount - 100 where name = 'zs';
update user_account set amount = amount + 100 where name = 'ls';

在这里插入图片描述
7. 在A窗口中进行查询.

-- A 查询
select * from user_account;

在这里插入图片描述

发现A窗口中的数据没有变化, 说明脏读没有发生.（脏读: 一个事物读取到另一个事务未提交的数据.）

在B窗口中提交事务.

-- B 提交事务
commit;

-- A 查询
select * from user_account;

在这里插入图片描述

发现A窗口中的数据没有变化, 说明"不可重复读"没有发生,
（不可重复读: 指的是一个事务读到了另一个事务已经提交的 update的数据,导致多次查询结果不一致.）

在这里插入图片描述

4.演示"幻读"的产生:

开启两个cmd窗口A, B.
查看当前的事务的隔离级别:

select @@tx_isolation
//默认为: repeatable read

将A窗口的隔离级别修改为: repeatable read;

set session transaction isolation level repeatable read;

再次查看下两个窗口的的隔离级别:

select @@tx_isolation;

在A, B两个窗口中分别开启事务:

start transaction;

在B窗口中插入一条数据并提交事务

-- B 插入数据
INSERT INTO `user_account`(id, `name`, `amount`) VALUES (3, 'ww', 1000);
commit;

在A窗口中进行查询.

-- A 查询
-- 快照读不会出现幻读
select * from user_account;
-- 当前读都会出现幻读
select * from user_account for update;

发现A窗口中查到了新增了一条数据, 说明幻读发生.（幻读: 指的是一个事务读到了另一个事务已经提交的insert的数据）