MySQL事务隔离级别：可重复读、读已提交、读未提交。实操

it2026-02-07 0

MySQL的事务隔离级别：

一、可重复读（默认） REPEATABLE-READ；

二、读已提交 READ-COMMITTED；

一、可重复读（默认） REPEATABLE-READ；

准备实操的表和数据：

-- ---------------------------- -- Table structure for account -- ---------------------------- DROP TABLE IF EXISTS `account`; CREATE TABLE `account` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `balance` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of account -- ---------------------------- INSERT INTO `account` VALUES ('1', 'lilei', '100.00'); INSERT INTO `account` VALUES ('2', 'hanmei', '1090.00'); INSERT INTO `account` VALUES ('3', 'lucy', '2.00'); INSERT INTO `account` VALUES ('4', 'wanggang', '99.00');

分别通过两个SQL客户端来实际操作测试可重复读；

1、开启事务A：

-- 开启事务 mysql> start transaction; Query OK, 0 rows affected -- 查询account表所有记录 mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | lilei | 100 | | 2 | hanmei | 1090 | | 3 | lucy | 2 | | 4 | wanggang | 99 | +----+----------+---------+ 4 rows in set

2、开启事务B：

3、事务B操作数据：

-- 更新id=1 的balance 增加50 mysql> update account set balance = balance + 50 where id = 1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 -- 当前事务查询id=1 的balance 已经增加50 mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | lilei | 150 | | 2 | hanmei | 1090 | | 3 | lucy | 2 | | 4 | wanggang | 99 | +----+----------+---------+ 4 rows in set

4、事务A查询验证【可重复读】：

-- 查询account 数据还是第一步的数据，没有变化；支持可重复读 mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | lilei | 100 | | 2 | hanmei | 1090 | | 3 | lucy | 2 | | 4 | wanggang | 99 | +----+----------+---------+ 4 rows in set

5、事务B提交事务，查询数据变化：

-- 提交事务 mysql> commit; Query OK, 0 rows affected -- 查询当前account数据 mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | lilei | 150 | | 2 | hanmei | 1090 | | 3 | lucy | 2 | | 4 | wanggang | 99 | +----+----------+---------+ 4 rows in set

6、事务A查询验证数据【可重复读】：

-- 查询account数据，没有变化；支持可重复读 mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | lilei | 100 | | 2 | hanmei | 1090 | | 3 | lucy | 2 | | 4 | wanggang | 99 | +----+----------+---------+ 4 rows in set

7、事务A提交事务，查询到最新数据：

-- 提交当前事务 mysql> commit; Query OK, 0 rows affected -- 再次查询account；此时的数据为最新的数据。 mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | lilei | 150 | | 2 | hanmei | 1090 | | 3 | lucy | 2 | | 4 | wanggang | 99 | +----+----------+---------+ 4 rows in set

MySQL的可重复读，是通过MVCC多版本控制机制来实现的。

二、读已提交 READ-COMMITTED；

1、开启事务A ，查询account数据；

mysql> select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set mysql> start transaction; mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | lilei | 150 | | 2 | hanmei | 1090 | | 3 | lucy | 2 | | 4 | wanggang | 99 | +----+----------+---------+ 4 rows in set

2、开启事务B ，查询account数据；

3、事务B ，更新account id = 1，balance+50 ；查询数据已经变化

mysql> update account set balance = balance + 50 where id = 1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | lilei | 200 | | 2 | hanmei | 1090 | | 3 | lucy | 2 | | 4 | wanggang | 99 | +----+----------+---------+ 4 rows in set mysql>

4、事务A ，查询account数据，验证数据是否可重复读；

-- 事务B修改操作，当前事务B未提交；没有影响事务A的数据。 mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | lilei | 150 | | 2 | hanmei | 1090 | | 3 | lucy | 2 | | 4 | wanggang | 99 | +----+----------+---------+ 4 rows in set

5、事务B ，事务提交，查询account数据，已经修改完成；

mysql> commit; Query OK, 0 rows affected mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | lilei | 200 | | 2 | hanmei | 1090 | | 3 | lucy | 2 | | 4 | wanggang | 99 | +----+----------+---------+ 4 rows in set

6、事务A ，查询account数据，验证数据是否可重复读；

-- 事务A并没有结束，但是数据已经变化了。证明读已提交这个隔离级别，是不可重复读的。 mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | lilei | 200 | | 2 | hanmei | 1090 | | 3 | lucy | 2 | | 4 | wanggang | 99 | +----+----------+---------+ 4 rows in set

后续增加读未提交、串行化实操案例。

-------------欢迎各位留言交流，如有不正确的地方，请予以指正。【Q:981233589】

最新回复(0)