mysql四种事务隔离级别

mysql事务并发问题

ACID什么的就不啰嗦了。mysql多个事务并发的时候,可能会出现如下问题:

1. 更新丢失

  即两个事务同时更新某一条数据,后执行的更新操作会覆盖先执行的更新操作,导致先执行的更新结果丢失。

2. 脏读

  即一个事务会读到另一个事务尚未提交的数据更新,由于该数据更新可能会回滚,所以称之为脏读。

3. 不可重复读

  即一个事务中对同一行数据读取两次,会得到不同的结果。原因是在该事务两次读取之间,其他事务会修改此数据。

4. 幻读

  即一个事务对同一个表执行两次查询,会得到相同的结果行数。即使在该事务两次查询之间,其他事务同时也向此表插入了新的数据。

mysql事务隔离级别

mysql共支持四种事务隔离级别,分别是:

  1. read uncommitted(读取未提交数据):从字面上理解,该事务隔离级别允许读取未提交数据。仅处理更新丢失的问题,但是不处理脏读、不可重复读、幻读的问题。
  2. read committed(读取已提交数据):从字面上理解,该事务隔离级别允许读取已经提交的数据,即未提交的数据不可读取。则该事务隔离级别处理更新丢失、脏读的问题,不处理不可重复读、幻读的问题。
  3. repeatable read(重复读):该事务隔离级别无法从字面上理解了,处理更新丢失、脏读、不可重复读的问题,不处理幻读的问题。
  4. serializable(最高隔离级别):该事务隔离级别为最高事务隔离级别,处理以上所有问题,包括:更新丢失、脏读、不可重复读、幻读。

以上四种事务隔离级别依次递增,事务隔离级别越高,一致性越强可用性越差。

可以看到,所有的事务隔离界别都会处理更新丢失的问题。mysql默认的事务隔离级别为:repeatable read,即默认不处理幻读的问题。

废话不多说,直接上例子(大前提:数据库引擎为innoDB):

1. 首先要注意:mysql自动提交事务要关闭

查看mysql的autoCommit是否开启:

-- 查看mysql是否自动提交事务
show variables like 'autocommit';

如果需要关闭autoCommit,则执行如下语句:

-- 关闭autoCommit
set autocommit = 0;

2. 查看、设定mysql的事务隔离级别:注意只在当前会话session有效。

-- 查看当前数据库事务隔离级别
select @@tx_isolation;

-- 设定当前数据库隔离级别为 read uncommitted
set session transaction isolation level read uncommitted;
---set session transaction isolation level read committed ;
-- set session transaction isolation level repeatable read ;
-- set session transaction isolation level serializable;

3. 创建测试表及测试数据

use test;

create table dt_table1 (
    id int auto_increment primary key,
    value varchar(50) null
);

insert into dt_table1 (value) value ('00000000');
insert into dt_table1 (value) value ('00000000');
insert into dt_table1 (value) value ('00000000');

(1)测试更新丢失

我们先更新一下数据,以便后续测试。

update dt_table1 set value = '00000000' where id = 1;

设定测试环境:创建两个session连接,并设定事务隔离级别 read uncommited

set session transaction isolation level read uncommitted;

打开session连接1,执行如下脚本,通过第三步可以看到数据已经被更新:

start transaction;  -- 第一步
update dt_table1 set value = 'update 1' where id = 1; -- 第二步
select * from dt_table1 where id = 1; -- 第三步

打开session连接2,执行如下脚本,当执行第五步的时候,可以看到该更新操作被阻塞了。

start transaction;  -- 第四步
update dt_table1 set value = 'update 2' where id = 1; -- 第五步

回到session连接1,执行commit,可以看到session连接2中第五步的更新操作也取消了阻塞并执行通过了。

commit; -- 第六步

回到session连接2,执行查询并commit。最终数据被更新为值‘update 2’。

select * from dt_table1 where id = 1; -- 第七步
commit; -- 第八步

通过以上的例子,我们可以看出,即使是mysql的最低事务隔离级别,也解决了更新丢失的问题。即:在session连接1中执行更新操作但尚未提交事务,session连接2中的更新操作会被阻塞,以避免session连接1中的更新丢失。将两个session连接的事务隔离级别设定为其他等级也会得到同样的结果。

(2)测试脏读

我们先更新一下数据,以便后续测试。

update dt_table1 set value = '00000000' where id = 1;

设定测试环境:创建两个session连接,并设定事务隔离级别 read uncommited。在此事务隔离级别下,会出现脏读的问题。

set session transaction isolation level read uncommitted;

打开session连接1,执行如下脚本。

start transaction;  -- 第一步
update dt_table1 set value = 'read uncommitted' where id = 1; -- 第二步

打开session连接2,执行如下脚本。可以看到读取到了session连接1中已经更新但尚未提交的数据(查询值为‘read uncommited’)。

select * from dt_table1 where id = 1; -- 第三步

回到session连接1,执行rollback。

rollback; -- 第四步

回到session连接2,再次执行查询,可以看到查询值已经成功回滚(查询值为:‘00000000’)

select * from dt_table1 where id = 1; -- 第五步

通过以上的例子,我们可以看出,将mysql的事务隔离级别设定为read uncommited,会出现脏读的问题。即:session连接2中在第三步会读取到session连接1中已经更新但尚未提交的数据,由于session链接1可能会发生事务回滚,那么session连接2中读取到的数据也就无效了,即读取脏数据,即脏读。

如果设定事务隔离级别为read commited或以上,则不会出现此问题。上例中第三步读取到的值会为:'00000000'。即不会脏读。

(3)尝试不可重复读

我们先更新一下数据,以便后续测试。

update dt_table1 set value = '00000000' where id = 1;

设定测试环境:创建两个session连接,并设定事务隔离级别 read commited。在此事务隔离级别下,会出现不可重复读情况。

set session transaction isolation level read committed;

打开session连接1,执行如下脚本。可以看到查询值为‘00000000’

start transaction; -- 第一步
select * from dt_table1 where id = 1; -- 第二步

打开session连接2,执行如下脚本。

start transaction; -- 第三步
update dt_table1 set value = 'read committed' where id = 1; -- 第四步
commit; -- 第五步

回到session连接1,执行如下脚本。可以看到查询值为‘read commited’

select * from dt_table1 where id = 1; -- 第六步
commit; -- 第七步

通过以上的例子,我们可以看出,将mysql的事务隔离级别设定为read commited,会出现不可重复读问题,即在session连接1中的一个事务中,先后两次读取到的值不一样。原因是在该事务两次读取之间,session连接2的事务修改并提交了此数据。

如果设定事务隔离级别为repeatable read或以上,则不会出现此问题。上例中第六步读取到的数据值依然为‘00000000’

(4)尝试幻读

设定测试环境:创建两个session连接,并设定事务隔离级别 repeatable read。在此事务隔离级别下,会出现幻读的情况。

set session transaction isolation level repeatable read ;

我们先更新一下数据,以便后续测试

update dt_table1 set value = '00000000' where 1=1;

打开session连接1,执行如下脚本。我们记录下第二步查询结果行数,假如为n行。

start transaction; -- 第一步
select * from dt_table1; -- 第二步

打开session连接2,执行如下脚本。

start transaction; -- 第三步
insert into dt_table1 (value) value ('phantom read new'); -- 第四步
commit; -- 第五步

回到session连接1,执行如下脚本,可以看到第六步查询结果行数依然为n,但是执行第七步更新操作的时候,更新影响行数却是n+1。执行第九步查询的时候结果行数也是n+1

select * from dt_table1; -- 第六步
update dt_table1 set value = 'phantom read' where 1=1; -- 第七步
commit; -- 第八步
select * from dt_table1; -- 第九步

通过以上的例子,我们可以看出幻读问题的存在,即:第六步查询结果明明是n行,但是第七步跟新操作影响行数却是n+1,难道是我的幻觉?这就是幻读。

如果设定事务隔离级别为serializable,则不会出现此问题。第四步执行输入插入操作时会被阻塞,第七步更新操作影响结果行数和第六步查询结果行数也会保持一致,且执行到第九步的时候,可以看到第四步新插入的数据也并没有被第七步更新掉。