搞懂MySQL事务隔离级别

接触数据库时间也不短了,只是想起自己对数据库的事务隔离级别这块还是迷迷糊糊,似懂非懂的。现在把这块整理一下供大家参考。

首先创建一个表 account,然后往表中插入两条数据:

create table account (
    id int(11) PRIMARY KEY,
    account float
) ENGINE=INNODB;

INSERT into account (id,account) VALUES(1,1000),(2,1000);

为了说明问题,打开两个控制台分别进行登录来模拟两个用户(用户 A 和用户 B),并设置当前 MySQL 会话的事务隔离级别。

一. Read uncommitted(未提交读)

用户 A 的操作如下:

set session transaction isolation level read uncommitted;
start transaction;
select * from account;

结果如下:

+------+---------+
| id   | account |
+------+---------+
|    1 |    1000 |
|    2 |    1000 |
+------+---------+

用户 B 的操作如下:

set session transaction isolation level read uncommitted;
start transaction;
update account set account=account+200 where id=1;

随后 A 用户再次查询数据,结果如下:

+------+---------+
| id   | account |
+------+---------+
|    1 |    1200 |
|    2 |    1000 |
+------+---------+
那么这么做有什么问题吗?

那就是我们在一个事务中可以随随便便读取到其他事务未提交的数据,实际上只有B用户执行了事务 commit 后才会更新到数据库,如果B回滚了事务操作,那么A读取到的数据就是脏数据,这种现象叫脏读

结论

我们将事务隔离级别设置为 read uncommitted,即便是事务没有 commit,但是我们仍然能读到未提交的数据,这是所有隔离级别中最低的一种机制,任何情况都无法保证

二. Read committed(提交读)--- 大多数数据库默认的隔离级别

同样的办法,我们将用户 B 所在的会话当前事务隔离级别设置为 read commited。

mysql> set session transaction isolation level read committed;

在用户 A 所在的会话中执行下面操作:

mysql> update account set account=account-200 where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
+------+---------+
| id   | account |
+------+---------+
|    1 |     800 |
|    2 |    1000 |
+------+---------+

将 id=1 的用户 account 减 200。然后查询,发现 id=1 的用户 account 变为 800。

在 B 用户所在的会话中查询:

mysql> select * from account;
+------+---------+
| id   | account |
+------+---------+
|    1 |    1000 |
|    2 |    1000 |
+------+---------+

我们会发现数据并没有变,还是 1000。

接着在会话 A 中我们将事务提交:

mysql> commit;

在会话 B 中查询结果如下:

mysql> select * from account;
+------+---------+
| id   | account |
+------+---------+
|    1 |     800 |
|    2 |    1000 |
+------+---------+

那么这么做有什么问题吗?

那就是我们在会话 B 同一个事务中,读取到两次不同的结果,这种现象叫不可重复读

结论:

当我们将当前会话的隔离级别设置为 read committed 的时候,当前会话只能读取到其他事务提交的数据,未提交的数据读不到。可避免脏读的发生,但是可能会造成不可重复读

三. Repeatable read(可重复读)---MySQL 默认的隔离级别

同样的办法,将用户 B 所在的会话当前事务隔离级别设置为 repeatable read,具体操作如下:

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

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

mysql> select * from account;
+------+---------+
| id   | account |
+------+---------+
|    1 |    1000 |
|    2 |    1000 |
+------+---------+

在 A 用户所在会话中为表 account 添加一条数据:

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

mysql> insert into account(id,account) value(3,1000);
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from account;
+------+---------+
| id   | account |
+------+---------+
|    1 |    1000 |
|    2 |    1000 |
|    3 |    1000 |
+------+---------+
3 rows in set (0.00 sec)

回到 B 用户所在的会话,我们查询结果:

mysql> select * from account;
+------+---------+
| id   | account |
+------+---------+
|    1 |    1000 |
|    2 |    1000 |
+------+---------+
2 rows in set (0.00 sec)

用户 B 两次查询到了相同的数据,这时,用户 B 想插入一条新数据 id=3,value=1000,操作如下:

mysql> insert into account(id,account) value(3,1000);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

什么?竟然插不进去?
用户 B 当然不服啊,明明查询到的数据只有两条啊,为什么不能插入?试想一下,在实际中用户 A 和用户 B 肯定是相互隔离的,彼此不知道操作什么。用户 B 碰到这种现象,肯定会炸毛的啊,明明不存在的数据,插入却说主键冲突。这种现象叫幻读

这里给出一种比较白话的理解:

幻读,并不是说两次读取获取的结果集不同,幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些:select 某记录是否存在,不存在,准备插入此记录,但执行 insert 时发现此记录已存在,无法插入,此时就发生了幻读。

结论:

当我们将当前会话的隔离级别设置为 repeatable read 的时候,多次读取同一范围的数据会返回第一次查询的快照,即使其他事务对该数据做了更新修改。事务在执行期间看到的数据前后必须是一致的。

但如果这个事务在读取某个范围内的记录时,其他事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻读。总的来说,可避免脏读、不可重复读的发生,但是可能会出现幻读。

四. serializable(串行化)

同样,我们将用户 B 所在的会话的事务隔离级别设置为 serializable 并开启事务。结果如下:

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

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

mysql> select * from account;
+------+---------+
| id   | account |
+------+---------+
|    1 |    1000 |
|    2 |    1000 |
+------+---------+

那这个时候在用户 A 所在的会话中写数据呢?

mysql> insert into account(id,account) value(3,1000);

会发现用户 A 所在的会话陷入等待,如果超时(这个时间可以进行配置),会出现 Lock wait timeout 提示:

mysql> insert into account(id,account) value(3,1000);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

如果在等待期间我们用户 B 所在的会话事务提交,那么用户 A 所在的事务的写操作将提示操作成功。

结论:

当我们将当前会话的隔离级别设置为 serializable 的时候,其他会话对该表的写操作将被挂起。
这是因为“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行。事务 100% 隔离,可避免脏读、不可重复读、幻读的发生

这是隔离级别中最严格的,这样做牺牲了系统的并发性,会对性能造成影响。所以在实际的选用上,我们要根据当前具体的情况选用合适的。

各隔离级别所拥有的问题

经过上文的举例,我们已经了解了各个事务隔离级别的表象与问题,那么,我们来总结一下。
首先,我们明白了一个道理,事务的隔离级别越高,隔离性越强,所拥有的问题越少,并发能力越弱,所以,我们可以用如下表格进行总结。

隔离级别脏读不可重复读幻读
未提交读(read uncommitted)可能可能可能
提交读(read committed)不可能可能可能
可重读(repeatable read)不可能不可能可能
串行化(serializable)不可能不可能不可能
0 人推荐

声明:本文原创发布于加藤非博客,转载请注明出处:加藤非博客 jiatengfei.com 。如有侵权,请联系本站删除。

加藤非博客
请先登录再发表评论
  • 最新评论

  • 总共0条评论