当前位置：首页 > article >正文

MYSQL 事物隔离级别的区别与现象

article 2024/10/8 18:35:14

事物的ACID属性本章不再赘述，本章主要描述事物的隔离级别及隔离级别导致的现象，日常工作中该如何选择MYSQL的隔离级别。

MYSQL事物的隔离级别及各隔离级别存在的问题如下：

隔离级别/问题	脏读	不可重复读	幻读
读未提交(Read-Uncommitted)	✅	✅	✅
读已提交(Read-Committed)	❌	✅	✅
可重复读(Repeatable-Read)	❌	❌	✅
序列化(Serializable)	❌	❌	❌

查看数据库隔离级别SQL，默认即为：可重复读

下面以实际案例列举各隔离级别出现的问题，创建如下表并插入数据：

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;


INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lilei', '1000');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('hanmei', '1000');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lucy', '1000');

1，读未提交(Read-Uncommitted)

1.1，navicate打开两个查询连接，且把两个连接的事物隔离级别设置为读未提交，模拟两个事物，如图：

1.2，事物一开启事物并更新数据 id = 1的数据，但不提交：

此时id = 1 的数据未改变：

1.3，事物二开启事物并查询 id = 1的数据：

1.4，结论

1，事物一更新数据的事物未提交，但事物二已经读取了新数据，如果事物一的更新回滚，则事物二出现【脏读】；

2，事物一未提交事物，插入一条新数据。事物一的插入数据事物未提交，但事物二已可读取到新插入的数据，如果事物一的插入事物回滚，则事物二出现【幻读】。

3，事物一提交或不提交事物，再次更新id=1的数据，将1500该为2000。事物二同一事物中再次查询，结果为2000，此时事物二出现【不可重复读】；

2，读已提交(Read-Committed)

2.1，navicate打开两个查询连接，且把两个连接的事物隔离级别设置为读已提交，模拟两个事物，如图：

2.2，事物二开启事物并查询 id = 2 的数据，查询后不提交查询事物，如下图：

2.3，事物一开启事物，更新 id = 2的数据，不提交事物，如下图：

未提交事物，所以此时id=2的数据并未改变：

2.4，事物二在同一事物中查询id=2的数据，

查询结果同第一次查询一样，即【读已提交】已解决【读未提交】的问题。

2.5，事物一提交，id=2的数据变化，如图：

2.6，事物二在同一事物中查询，如图：

2.7，结论

1，事物一增/删/改数据的事物未提交，不论事物一操作多少次，事物二都不会查询到新数据，事物二只会查询到事物一提交事物后的数据。即事物二不会出现【脏读】；

2，事物二在同一查询事物中，事物一对同一数据进行多次更新并提交事物，事物二查询结果可能不一样，即事物二出现【不可重复读】；

3，假如事物二查询表的数据量count(*)=5，事物一新增一行并提交事物，事物二再次查询count(*)=6，事物一开启新事物：删除一条数据并提交事物，事物二再次查询count(*)=5.即事物二在同一查询事物中出现【幻读】。

3，可重复读(Repeatable-Read)

3.1，navicate打开两个查询连接，且把两个连接的事物隔离级别设置为读已提交，模拟两个事物，如图：

3.2，事物二开启事物并查询 id = 3 的数据，查询后不提交查询事物，如下图：

3.3，事物一开启事物，更新 id = 3的数据，set balance = 4000，不提交事物。事物二同一事物中查询id=3，查询结果balance=1000，表明【可重复读】和【读已提交】一样，解决了【读未提交】的脏读问题。

事物一提交事物，id=3的数据变化，如图：

3.4，事物二在同一事物中查询id=3的数据，

查询结果仍然是1000，即同一事物中，查询同一条数据结果一致。且在事物二的同一事物中，不论事物一对id=4的数据做多少次更改，事物二的查询结果都一致，解决了【读已提交】的问题。

3.5，结论

1，事物一增/删/改数据的事物未提交，不论事物一操作多少次，事物二都不会查询到新数据，事物二只会查询到事物一提交事物后的数据。即事物二不会出现【脏读】；

2，事物二在同一查询事物中，事物一对同一数据进行多次更新并提交事物，事物二查询结果可能一样，即事物二不会出现【不可重复读】；

3，假如事物二查询表的数据量：select count(*) from account; count(*)=5，事物一新增一行并提交事物，事物二再次查询数据量： select count(*) from account for update; count(*)=6两次的查询语句不通，第二次加了读锁，即当前读，事物二在同一查询事物中出现【幻读】。