9

Mysql 四种事务隔离级别

 4 years ago
source link: http://www.cnblogs.com/yuanfy008/p/14285344.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

一、前提

时过一年重新拾起博文记录，希望后面都能坚持下来。 接着之前MySql的学习，先记录下这篇。

以下都是基于mysql8 innodb存储引擎进行分析的。

二、事务的ACID特性

  1. A(Atomicity) 原子性

指整个数据库事务是不可分割的单位，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。只有使事务中所有的数据库操作都执行成功，才算整个事务执行成功。否则事务中任何一个SQL语句执行失败，那么这个事务就是执行失败的， 已执行成功的SQL语句也必须撤销，数据库状态应该退回到执行事务前的状态。

  1. C(consistency) 一致性

一致性事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

例如：在表中有个字段为姓名为唯一约束，即在表中姓名不能重复。如果一个事务对姓名字段进行了修改，但是在事务提交或事务操作发生回滚后，表中的姓名变得非唯一了，这就破坏了事务的一致性要求，即事务将数据从一种状态变为了一种不一致的状态。

  1. I(isolation) 隔离性

事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见。通常可以使用锁来实现。

  1. D(durability) 持久性

事务一旦提交，其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。

需要注意的是：只能从事务本身的角度来保证结果的永久性，例如：在事务提交后，所有变化都是永久的。即使当数据因为崩溃而需要恢复时，也能保证恢复后提交的数据都不会丢失。但如果不是数据库本身发生故障，而是一些外部的原因导致数据库发生问题，则有可能是提交的数据丢失(RAID卡损坏)。

因此持久性保证事务系统的高可靠性，而不是高可用性。对于高可用性的实现，事务本身并不能保证，需要一些系统共同配合完成。

三、事务的4种隔离级别

  • Read Uncommitted - 未提交读

在该隔离级别下的事务会读取到其未提交事务的数据，此种现象也称之为 脏读 。

步骤 事务1 事务2 1

设置隔离级别

mysql> set @@session.transaction_isolation 
= 'READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)
2

开启事务1

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

mysql> select * from t;
Empty set (0.00 sec)
3

无需管隔离级别，只开启事务2并插入记录

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

mysql> insert into t select 1, '1';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
4

此时能查到事务2中未提交事务中的数据，这就是脏读。

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

注意 ：设置隔离级别之前，记得查看当前隔离级别的key, 有可能版本不一样对应的key不一样。（另外表可以自己随意选择） 

1 mysql> show variables like '%isolation%';
2 +-----------------------+------------------+
3 | Variable_name         | Value            |
4 +-----------------------+------------------+
5 | transaction_isolation | READ-UNCOMMITTED |
6 +-----------------------+------------------+
7 1 row in set (0.00 sec)

在实际的业务场景中应该都不允许脏读出现，既然这么评价不好为什么还会出现呢？ 但其实这个隔离级别下的数据库并发性能是最好的。

  • Read Committed - 提交读

一个事务可以读取另一个已提交的事务，多次读取会造成不一样的结果。这种现象也被称为 不可重复读 。举例说明：

步骤 事务1 事务2 1 先设置隔离级别 
mysql> set @@session.transaction_isolation 
= 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)
2 开启事务1，查询t表记录为空 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
Empty set (0.00 sec)
3

无需管隔离级别，只开启事务2并插入记录

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

mysql> insert into t select 2, '2';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
4

继续查询表t依然显示为空

(没有读取到事务2未提交的数据，所以不存在脏读问题)

mysql> select * from t;
Empty set (0.00 sec)
5

提交事务2

mysql> commit;
6

继续查询表t，此时能查询事务2已提交的数据记录（出现前后查询不一致）

mysql> select * from t;
+------+------+
| id   | name |
+------+------+
|    2 | 2    |
+------+------+

总结：在隔离级别中解决了脏读问题，但存在不可重复读的问题（事务中会读取其他已提交事务中的数据）

  • Repeatable Read - 可重复读

该隔离级别是MySQL默认的隔离级别，在同一个事务里select的结果是事务开始时间时间点的状态，解决了不可重复读问题。

但其中会出现幻读现象：基于可重复读的基础上查询结果是一样的，但是当对某些行进行更新或者插入时却会受到影响操作不了，就形成了幻读。例如：

步骤 事务1 事务2 1

设置隔离级别

mysql> set @@session.transaction_isolation 
= 'REPEATABLE-READ';

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
2

开启事务1，并查询

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

mysql> select * from t;
+------+------+
| id   | name |
+------+------+
|    3 | 3    |
+------+------+
3

无需管隔离级别，只开启事务2并插入记录

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

mysql> insert into t select 4, '4';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
4 继续查询表t依然只有一条记录id=3

(没有读取到事务2未提交的数据，所以不存在脏读问题)

mysql> select * from t;
+------+------+
| id   | name |
+------+------+
|    3 | 3    |
+------+------+
5

将第二个窗口中的事务提交。

mysql> commit;
6 继续查询表t依然只有一条记录id=3

(没有读取到事务2已提交的数据，所以不存在不可重复读问题)

mysql> select * from t;
+------+------+
| id   | name |
+------+------+
|    3 | 3    |
+------+------+
7

接着插入一条4的记录，但提示插入不了，提示主键冲突问题。

然而查询却没有这条id=4记录。 这就是 幻读现象 。 
mysql> insert into t select 4, '4';
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  3 | 3    |
+----+------+
1 row in set (0.00 sec)
8 另一种幻读现象：接着上面操作，不插入记录只更新记录，将name 更新成 '5'后，发现有两行受影响 
mysql> update t set name = '5';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  3 | 5    |
|  4 | 5    |
+----+------+

总结：在隔离级别中解决了脏读问题、不可重复读的问题，但会存在幻读问题。

但Innodb引擎提供了间隙锁: innodb-next-key-locks , 解决了幻读问题。基于上面结果演示下：

步骤 事务1 事务2 9

查询时加上间隙锁

mysql> begin;
mysql> select * from t where id  > 0 for update;
+----+------+
| id | name |
+----+------+
|  3 | 5    |
|  4 | 5    |
+----+------+
10

插入记录为6的数据就会发现插入这条记录获取锁超时，自动异常

insert into t select 6, '6';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这样成功的避免了幻读问题，阻止了其他事务可能影响到我当前事务所涉及到的数据范围。

  • Serializable - 可串行化

在该隔离级别下事务都是串行顺序执行的，MySQL 数据库的 InnoDB 引擎会给读操作隐式加一把读共享锁，从而避免了脏读、不可重读复读和幻读问题。

四 总结

1、脏读：在一个事务中会读取到其未提交事务的数据，此种现象也称之为脏读

2、不可重复读：一个事务可以读取另一个已提交的事务，多次读取会造成不一样的结果。这种现象也被称为不可重复读

3、幻读：基于可重复读的基础上查询结果是一样的，但是当对某些行进行更新或者插入时却会受到影响操作不了，就形成了幻读。

隔离级别

脏读

不可重复读

幻读

读未提交(uncommitted read)

可能出现

可能出现

可能出现

读提交(committed read)

不会出现

可能出现

可能出现

可重复读(Repeatable Read)

不会出现

不会出现

可能出现（加上间隙锁就不会）

可串行化(Serializable)

不会出现

不会出现

不会出现

五 参考文献

《MySql 技术内幕（Innodb）第二版》

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html


report

Recommend

  • 127
    • suree.org 6 years ago
    • Cache

    数据库事务隔离级别与加锁机制

    数据库事务隔离级别与加锁机制

  • 59
    • database.51cto.com 6 years ago
    • Cache

    面试问烂的MySQL四种隔离级别，看完吊打面试官！

    什么是事务 事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有...

  • 74
    • www.tuicool.com 5 years ago
    • Cache

    MySQL 事务隔离级别和锁

    事务及其特性 IBM Compose for MySQL IBM Cloud 上提供的 Compose for M...

  • 12
    • ehlxr.top 4 years ago
    • Cache

    MySQL InnoDB 事务隔离级别总结

    SQL 事务隔离级别说明 SQL 标准定义了 4 类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。 Read Uncom...

  • 6
    • segmentfault.com 3 years ago
    • Cache

    MySQL事务的隔离级别与并发问题

    MySQL版本：8.0.27一、事务并发执行面临的问题1. 脏读（Dirty Read）如果事务A读到了未提交的事务B修改过的数据，就意味着发生了脏读现象。2. 不可重复读（Non-Repeatable Read）如果事务B...

  • 12
    • segmentfault.com 3 years ago
    • Cache

    理解mysql的事务隔离级别

    理解mysql的事务隔离级别发布于 今天 10:49 考研分数落定，今年无缘，着手准备春招。说到今年考研，分数可谓水涨船高，去年本校专硕270就能进，今年一舍友考了270，...

  • 10
    • wnanbei.github.io 3 years ago
    • Cache

    MySQL 事务与隔离级别

    MySQL M...

  • 3
    • seineo.github.io 2 years ago
    • Cache

    事物的四种隔离级别 - seineo's blog

    事务的四种隔离级别我们将以如下伪SQL代码为例，说明事务的四种隔离级别。-- 事务1 begin; insert into table1 (val); update table2 set a = a + 1 where id = 1; commit; -- 事务2 begin; select count(*) f...

  • 11
    • www.cnblogs.com 2 years ago
    • Cache

    MySQL之事务隔离级别和MVCC - 大队长11

    事务隔离级别 事务并发可能出现的问题 脏写 事务之间对增删改互相影响 脏读 事务之间读取其他未提交事务的数据 不可重复读 一个事务在多次执行一个select读到的数据前后不相同。因为被别的未提交事...

  • 10
    • www.cnblogs.com 2 years ago
    • Cache

    「MySQL高级篇」MySQL之MVCC实现原理&&事务隔离级别的实现 - Melo~

    大家好，我是melo，一名大三后台练习生，死去的MVCC突然开始拷打我🤣🤣🤣！ MVCC，非常顺口的一个词，翻译起来却不是特别顺口：多版本并发控制。 其中多版本是指什么呢？一条记录的多个版本。 并发控制？如何...

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK