MySQL事务隔离级别

最新推荐文章于 2024-04-08 18:03:22 发布

火柴头9527

最新推荐文章于 2024-04-08 18:03:22 发布

阅读量277

点赞数

分类专栏：分布式事务文章标签：事务隔离级别

本文链接：https://blog.csdn.net/jinjiankang/article/details/113588825

版权

分布式事务专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1、事务隔离级别

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
Read uncommitted	可能	可能	可能
Read committed	不可能	可能	可能
Repeatable read	不可能	不可能	可能
Serializable	不可能	不可能	不可能

MySQL 5.7 默认的事务隔离级别：

mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

2、通过例子体验事务隔离级别

CREATE TABLE `account` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` VARCHAR(20) NULL COMMENT '姓名',
  `balance` INT NULL COMMENT '余额',
  PRIMARY KEY (`id`) USING BTREE
)
COMMENT='测试事务隔离级别'
COLLATE='utf8_bin'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

INSERT INTO `account` (`id`, `name`, `balance`) VALUES 
(1, 'lilei', 450),
(2, 'hanmei', 16000),
(3, 'lucy', 2400);

2.1 Read uncommitted

（1）打开一个客户端A，并设置当前事务模式为read uncommitted

-- 客户端A
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     450 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
+----+--------+---------+
3 rows in set (0.01 sec)

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：

-- 客户端B
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     400 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
+----+--------+---------+
3 rows in set (0.00 sec)

（3）这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据：

-- 客户端A
mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     400 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
+----+--------+---------+
3 rows in set (0.00 sec)

（4）一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询到的数据，即余额为400，就是脏数据：

-- 客户端B
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     450 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
+----+--------+---------+
3 rows in set (0.00 sec)

（5）客户端A可能执行如下逻辑：判断余额是否大于等于订单金额，假设订单金额为300，显然余额400大于300。于是扣款400 - 300 = 100，执行更新语句update account set balance = 100 where id =1，这是错误的！！！

2.2 Read committed

（1）打开一个客户端A，并设置当前事务模式为read committed，查询表account的所有记录：

-- 客户端A
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     450 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
+----+--------+---------+
3 rows in set (0.00 sec)

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：

-- 客户端B
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

（3）这时，客户端B的事务还没提交，客户端A不能查询到B已经更新的数据，解决了脏读问题：

-- 客户端A
mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     450 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
+----+--------+---------+
3 rows in set (0.00 sec)

（4）客户端B的事务提交

-- 客户端B
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

（5）客户端A执行与上一步相同的查询，结果与上一步不一致，即产生了不可重复读的问题

-- 客户端A
mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     400 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
+----+--------+---------+
3 rows in set (0.00 sec)

2.3 repeatable read

（1）打开一个客户端A，并设置当前事务模式为repeatable read，查询表account的所有记录

-- 客户端A
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     400 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
+----+--------+---------+
3 rows in set (0.00 sec)

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account并提交

-- 客户端B
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     350 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
+----+--------+---------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

（3）在客户端A查询表account的所有记录，与步骤（1）查询结果一致，没有出现不可重复读的问题

-- 客户端A
mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     400 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
+----+--------+---------+
3 rows in set (0.00 sec)

（4）在客户端A，接着执行update balance = balance - 50 where id = 1，balance没有变成400-50=350，lilei的balance值用的是步骤（2）中的350来算的，所以是300，数据的一致性倒是没有被破坏。可重复读的隔离级别下使用了MVCC机制

-- 客户端A
mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     300 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
+----+--------+---------+
3 rows in set (0.00 sec)

（5）重新打开客户端B，插入一条新数据后提交

-- 客户端B
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(4, 'lily', 700);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

（6）在客户端A查询表account的所有记录，没有查出新增数据，所以没有出现幻读

-- 客户端A
mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     300 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
+----+--------+---------+
3 rows in set (0.00 sec)

（7）在客户端A提交后，再次查询表account，可见lily那条记录

-- 客户端A
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | lilei  |     300 |
|  2 | hanmei |   16000 |
|  3 | lucy   |    2400 |
|  4 | lily   |     700 |
+----+--------+---------+
4 rows in set (0.00 sec)

“2.3 repeatable read”这个小节的例子不是很好，会给人错觉，错以为“repeatable read”隔离级别下不会出现“幻读”呢。

2.4 repeatable read示例二

初始数据：

mysql> select * from students;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
1 row in set (0.00 sec)

时刻	事务A	事务B
1	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2	BEGIN;	BEGIN;
3		mysql> SELECT * FROM students WHERE id = 99; Empty set (0.00 sec)
4	INSERT INTO students (id, name) VALUES (99, ‘Bob’);
5	COMMIT;
6		mysql> SELECT * FROM students WHERE id = 99; Empty set (0.00 sec)
7		mysql> UPDATE students SET name = ‘Alice’ WHERE id = 99; Rows matched: 1 Changed: 1
8		mysql> SELECT * FROM students WHERE id = 99; 能查询到：id=99，name=Alice
9		COMMIT;

“神奇”的地方出现在时刻6和时刻7，查询时不存在，但更新时却成功了！由此可见，在设计“防重表”时，千万不要这样做：

select * from 防重表 where 。。。
如果结果集为空，则插入防重表

最终结果：

mysql> SELECT * FROM students;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
| 99 | Alice |
+----+-------+
2 rows in set (0.00 sec)

3、如何避免幻读

MySQL如何实现避免幻读？在快照读读情况下，通过MVCC来避免幻读。在当前读读情况下，通过next-key来避免幻。

3.1 什么是MVCC
MVCC全称是multi version concurrent control，即多版本并发控制。MySQL把每个操作都定义成一个事务，每开启一个事务，系统的事务版本号自动递增。每行记录都有两个隐藏列：创建版本号和删除版本号。
select：事务每次只能读到创建版本号小于等于此次系统版本号的记录，同时行的删除版本号不存在或者大于当前事务的版本号。
update：插入一条新记录，并把当前系统版本号作为行记录的版本号，同时保存当前系统版本号到原有的行作为删除版本号。
delete：把当前系统版本号作为行记录的删除版本号。
insert：把当前系统版本号作为行记录的版本号。

3.2 什么是next-key锁
行锁+GAP间隙锁

3.3 什么是快照读和当前读
快照读：简单的select操作，属于快照读，不加锁。

select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

参考资料

https://www.cnblogs.com/wyaokai/p/10921323.html
https://tech.meituan.com/2014/08/20/innodb-lock.html
https://www.liaoxuefeng.com/wiki/1177760294764384/1245268672511968
https://blog.pythian.com/understanding-mysql-isolation-levels-repeatable-read/
https://www.jianshu.com/p/42e60848b3a6

火柴头9527

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL事务隔离级别

隔离级别脏读（Dirty Read）不可重复读（NonRepeatable Read）幻读（Phantom Read）Read uncommitted可能可能可能Read committed不可能可能可能Repeatable read不可能不可能可能Serializable不可能不可能不可能“Read uncommitted”和“Read committed”到底该如何翻译，MySQL 5.7 默认的事务隔离级别：mysql>...
复制链接

扫一扫