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

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：

-- 开启事务
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

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数据；

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

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】