MySQL事务的隔离级别实战

秒速五厘米 2023-02-17 11:15 3阅读 0赞

# 事务的隔离级别 #

SQL 标准定义的四种隔离级别被 ANSI（美国国家标准学会）和 ISO/IEC（国际标准）采用，每种级别对事务的处理能力会有不同程度的影响。

我们分别对四种隔离级别从并发程度由高到低进行描述，并用代码进行演示，数据库环境为 MySQL 5.7。

--------------------

## READ UNCOMMITTED（读未提交） ##

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

1.  准备两个终端，在此命名为 mysql 终端 1 和 mysql 终端 2，再准备一张测试表 test，写入一条测试数据并调整隔离级别为 `READ UNCOMMITTED`，任意一个终端执行即可。

SET @@session.transaction_isolation = 'READ-UNCOMMITTED';
    create database test;
    use test;
    create table test(id int primary key);
    insert into test(id) values(1);

1.  登录 mysql 终端 1，开启一个事务，将 ID 为 1 的记录更新为 2。

begin;
    update test set id = 2 where id = 1;
    select * from test; -- 此时看到一条ID为2的记录

1.  登录 mysql 终端 2，开启一个事务后查看表中的数据。

use test;
    begin;
    select * from test; -- 此时看到一条 ID 为 2 的记录

最后一步读取到了 mysql 终端 1 中未提交的事务（没有 commit 提交动作），即产生了脏读，大部分业务场景都不允许脏读出现，但是此隔离级别下数据库的并发是最好的。

--------------------

## READ COMMITTED（读提交） ##

一个事务可以读取另一个已提交的事务，多次读取会造成不一样的结果，此现象称为不可重复读问题，Oracle 和 SQL Server 的默认隔离级别。

1.  准备两个终端，在此命名为 mysql 终端 1 和 mysql 终端 2，再准备一张测试表 test，写入一条测试数据并调整隔离级别为 `READ COMMITTED`，任意一个终端执行即可。

SET @@session.transaction_isolation = 'READ-COMMITTED';
    create database test;
    use test;
    create table test(id int primary key);
    insert into test(id) values(1);

1.  登录 mysql 终端 1，开启一个事务，将 ID 为 1 的记录更新为 2，并确认记录数变更过来。

begin;
    update test set id = 2 where id = 1;
    select * from test; -- 此时看到一条记录为 2

1.  登录 mysql 终端 2，开启一个事务后，查看表中的数据。

use test;
    begin;
    select * from test; -- 此时看一条 ID 为 1 的记录

1.  登录 mysql 终端 1，提交事务。

commit;

1.  切换到 mysql 终端 2。

select * from test; -- 此时看到一条 ID 为 2 的记录

mysql 终端 2 在开启了一个事务之后，在第一次读取 test 表（此时 mysql 终端 1 的事务还未提交）时 ID 为 1，在第二次读取 test 表（此时 mysql 终端 1 的事务已经提交）时 ID 已经变为 2，说明在此隔离级别下已经读取到已提交的事务。

--------------------

## REPEATABLE READ（可重复读） ##

该隔离级别是 MySQL 默认的隔离级别，在同一个事务里，select 的结果是事务开始时时间点的状态，因此，同样的 select 操作读到的结果会是一致的，但是，会有幻读现象。MySQL 的 InnoDB 引擎可以通过 next-key locks 机制（参考下文"行锁的算法"一节）来避免幻读。

1.  准备两个终端，在此命名为 mysql 终端 1 和 mysql 终端 2，准备一张测试表 test 并调整隔离级别为 REPEATABLE READ，任意一个终端执行即可。

SET @@session.transaction_isolation = 'REPEATABLE-READ';
    create database test;
    use test;
    create table test(id int primary key,name varchar(20));

1.  登录 mysql 终端 1，开启一个事务。

begin;
    select * from test; -- 无记录

1.  登录 mysql 终端 2，开启一个事务。

begin;
    select * from test; -- 无记录

1.  切换到 mysql 终端 1，增加一条记录并提交。

insert into test(id,name) values(1,'a');
    commit;

1.  切换到 msyql 终端 2。

select * from test; --此时查询还是无记录

通过这一步可以证明，在该隔离级别下已经读取不到别的已提交的事务，如果想看到 mysql 终端 1 提交的事务，在 mysql 终端 2 将当前事务提交后再次查询就可以读取到 mysql 终端 1 提交的事务。我们接着实验，看看在该隔离级别下是否会存在别的问题。

1.  此时接着在 mysql 终端 2 插入一条数据。

insert into test(id,name) values(1,'b'); -- 此时报主键冲突的错误

--------------------

## SERIALIZABLE（序列化） ##

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

1.  准备两个终端，在此命名为 mysql 终端 1 和 mysql 终端 2，分别登入 mysql，准备一张测试表 test 并调整隔离级别为 SERIALIZABLE，任意一个终端执行即可。

SET @@session.transaction_isolation = 'SERIALIZABLE';
    create database test;
    use test;
    create table test(id int primary key);

1.  登录 mysql 终端 1，开启一个事务，并写入一条数据。

begin;
    insert into test(id) values(1);

1.  登录 mysql 终端 2，开启一个事务。

begin;
    select * from test; -- 此时会一直卡住

1.  立马切换到 mysql 终端 1,提交事务。

commit;

一旦事务提交，msyql 终端 2 会立马返回 ID 为 1 的记录，否则会一直卡住，直到超时，其中超时参数是由 innodb\_lock\_wait\_timeout 控制。由于每条 select 语句都会加锁，所以该隔离级别的数据库并发能力最弱，但是有些资料表明该结论也不一定对，如果感兴趣，您可以自行做个压力测试。

表 1 总结了各个隔离级别下产生的一些问题。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk5OTMyNw_size_16_color_FFFFFF_t_70]

# 参考 #

[https://www.ibm.com/developerworks/cn/opensource/os-mysql-transaction-isolation-levels-and-locks/index.html][https_www.ibm.com_developerworks_cn_opensource_os-mysql-transaction-isolation-levels-and-locks_index.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk5OTMyNw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200613165941870.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk5OTMyNw==,size_16,color_FFFFFF,t_70
[https_www.ibm.com_developerworks_cn_opensource_os-mysql-transaction-isolation-levels-and-locks_index.html]: https://www.ibm.com/developerworks/cn/opensource/os-mysql-transaction-isolation-levels-and-locks/index.html