MySQL 的事务四个隔离级别演示

摘要:MySQL 的事务四个隔离级别演示,事务,隔离级别,未提交读,读已提交,可重复读,串行化,脏读,读已提交,幻读。

什么是事务隔离级别:

mysql 的四个特性,原子性、一致性、隔离性、持久性的隔离性篇。通俗的说事务 A 和 事务 B 具有隔离性,而这种隔离性又存在四个隔离级别,每一种级别都规定了一个事务中所做的修改,那些在事务内和事物间是可见的,那些是不可见的,较低的隔离级别可以执行更高的并发,系统的开销也更低。

每个隔离级别会对事务造成不同的影响,比如脏读、不可重复读、幻读。但是隔离级别越高并发度越低。


隔离性的四个级别:

- read uncommitted 未提交读

* 事务 A 和事务 B,事务 A 未提交的数据,事务 B 可以读取到。

* 这里读取到的数据称为“脏数据”或者“Drity Read”。

* 这种级别是最低级别,这种级别一般是在理论上存在的,数据库的默认级别一般都比这个级别高。

- read committed 读已提交(不可重复读)

* 事务 A 和事务 B,事务 A 提交的数据,事务 B 才可以读取到,比如对方事务提交之后的数据,我当前事务才可以读取到。

* 这种隔离级别高于上面的未提交读。

* 这种隔离级别可以避免脏数据。

* 这种隔离级别会导致:“不可重复读取”,比如读取对方提交事务前和后的数据不一致。

* oracle 数据库默认的隔离级别。

- repeatable read 可重复读(mysql 默认的隔离级别)

* 事务 A 和事务 B,事务 A 提交的数据,事务 B 读取不到。

* 事务 B 是可重复读取数据的,换句话说:对方提交之后的数据我还是读取不到。

* 这种隔离级别高于读已提交。

* 这种隔离级别可以避免“不可重复读取”,达到可重复读。

* mysql 默认隔离级别。

* 虽然可以达到“可重复读的效果”但是会导致“幻象读”,比如一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的数据行。

- serializable 串行化

* 事务 A 和事务 B,事务 A 在操作数据表的时候,事务 B 只能排队等待。

* 这种事务隔离级别一般很少使用,吞吐量太低,用户体验度不好。

* 这种隔离级别可以避免“幻象读”,每一次都是读取的真实的数据。


准备工作:

1、查看 mysql 隔离级别

# 查看当前会话的隔离级别
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

# 查看全局的隔离级别
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

2、设置 mysql 隔离级别

# 设置当前会话的隔离级别
mysql> set session transaction isolation level repeatable read; 
Query OK, 0 rows affected (0.01 sec)

# 设置全局的隔离界别
mysql> set global transaction isolation level repeatable read;                    
Query OK, 0 rows affected (0.00 sec)

3、例子:我们有一个 transaction 的数据库,里面有一张 product 商品数据表,然后打开两个终端 A B 来测试这四种隔离级别。

mysql> use transaction;
Database changed
mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     20 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)


未提交读 read uncommitted 演示:

# 终端 A 设置当前会话隔离级别为 read uncommitted
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
# 终端 B 设置当前会话隔离级别为 read uncommitted
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
# 查询终端 A 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# 查询 product 表苹果价钱 = 20
mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     20 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)
# 终端 B 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# 修改苹果价钱 20 - 5 = 15
mysql> update product set amount = amount - 5 where id = 1; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from product;                              
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     15 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)
# 再次查看终端 A 苹果价钱 = 20,终端 B 没有将事务提交,但是终端 A 可以读取到终端 B DML操作之后的结果
# 如果终端 B 将事务回滚,那么终端 A 读取到的数据就是脏数据
mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     15 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)
# 终端 B 事务回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

# 苹果价钱没有修改
mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     20 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)
# 再次查看终端 A 苹果价钱还是20,那么刚才读取到的 15 就是脏数据
mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     20 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)

在 read uncommitted 级别,事物中的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也被称为脏读 (Dirty Read)。这个级别会导致很多问题,从性能上说 read uncommitted 不会比其他的级别好太多,但是缺乏其他级别的很多好处,除非真的有非常必要的理由,在实际应用中有一般很少用到。


读已提交 read committed 演示:

# 终端 A 设置当前会话隔离级别为 read committed
mysql> set session transaction isolation level read committed;   
Query OK, 0 rows affected (0.00 sec)
# 终端 B 设置当前会话隔离级别为 read committed
mysql> set session transaction isolation level read committed;   
Query OK, 0 rows affected (0.00 sec)
# 终端 A 开启事务查询苹果价格
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     20 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)
# 终端 B 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# 苹果价格 20 - 5 = 15
mysql> update product set amount = amount - 5 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     15 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)
# 这时候 终端 B 的事务还没有提交,再次查询终端 A 发现数据并没有变,解决了刚才脏读的问题
mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     20 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)
# 终端 B 提交事务,事务执行成功
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

# 苹果价格成功改为 15 元,此时已经成功改写数据磁盘中的结果
mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     15 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)
# 终端 A 查看苹果价钱变成了 15 元,在一个事务内两次读到的数据产生变化,因此称为不可重复读
mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     15 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)

read committed 这种级别很好的解决了脏读的问题,但是又产生了不可重复读的问题。一种更易理解的说法是:在一个事务内,多次读同一个数据。在这个事务还没有结束时,另一个事务也访问该同一数据并修改数据。那么,在第一个事务的两次读数据之间。由于另一个事务的修改,那么第一个事务两次读到的数据可能不一样,这样就发生了在一个事务内两次读到的数据是不一样的,因此称为不可重复读,即原始读取不可重复。多数的数据库系统默认的隔离级别都是 read committed。


可重复读 repeatable read 演示:

# 终端 A 设置当前会话隔离级别为 repeatable read
mysql> set session transaction isolation level repeatable read; 
Query OK, 0 rows affected (0.00 sec)

# 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# 苹果价钱 = 20 元
mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     20 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)
# 终端 B 设置当前会话隔离级别为 repeatable read
mysql> set session transaction isolation level repeatable read;                
Query OK, 0 rows affected (0.00 sec)

# 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# 苹果价钱 = 20 元
mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     20 |     1 |           0 |
+----+--------+--------+-------+-------------+

# 价钱减去 5 元
mysql> update product set amount = amount - 5 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     15 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)

# 关闭事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 终端 A 中的苹果价钱 = 20 元,与第一次查询结果一致,没有出现不可重复读问题
mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     20 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)

# 将价苹果价钱减去 5 元
mysql> update product set amount = amount - 5 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 价钱没有从 20 - 5 = 15,反而成了 10 元,这是因为终端 A 中操作的值是从终端 B 中最后的 15 - 5 = 10 算出来的。
# 但是数据的一致性倒是没有被破坏。
# 可重复读的隔离级别下使用了 MVCC 机制,select 操作不会更新版本号,是快照读(历史版本),insert、update、delete 会更新版本号,是当前读(当前版本)。
mysql> select * from product;                              
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     10 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)
# 重新打开终端 B 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# 插入一条香蕉
mysql> insert into product (`name`, `amount`) values ('香蕉', '30');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     15 |     1 |           0 |
|  2 | 香蕉   |     30 |     1 |           0 |
+----+--------+--------+-------+-------------+
2 rows in set (0.00 sec)

# 关闭事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 终端 A 中的还是只有一条苹果数据,没有出现幻读
mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     10 |     1 |           0 |
+----+--------+--------+-------+-------------+
1 row in set (0.00 sec)

所谓幻读,指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行(Phantom Row)。


串行化 serializable 演示:

# 终端 A 设置当前会话隔离级别为 serializable
mysql> set session transaction isolation level serializable;                 
Query OK, 0 rows affected (0.00 sec)

# 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from product;
+----+--------+--------+-------+-------------+
| id | name   | amount | state | create_time |
+----+--------+--------+-------+-------------+
|  1 | 苹果   |     10 |     1 |           0 |
|  2 | 香蕉   |     30 |     1 |           0 |
+----+--------+--------+-------+-------------+
2 rows in set (0.00 sec)
# 终端 B 设置当前会话隔离级别为 serializable
mysql> set session transaction isolation level serializable;     
Query OK, 0 rows affected (0.00 sec)

# 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# 插入一条数据,表被锁了,插入失败
mysql> insert into product (`name`, `amount`) values ('香蕉', '30');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这是最高的隔离级别,避免了前面说的幻读的问题,serializable 会再读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用的问题,实际应用中很少用这个隔离级别,只有在非常需要确保数据一致性而且可以接受没有并发的情况下,才会考虑该级别。

结束语:感谢您对本网站文章的浏览,欢迎您的分享和转载,但转载请说明文章出处。
Top