看了一段时间的MySQL方面的书籍,想把看过的东西总结一下分享出来,一方面是希望可以和大家沟通交流,另一方面对自已也是一个总结升华的过程。注意:如果没有特殊说明本系列所有文章都是以Innodb存储引擎来说明。

事物及其ACID属性

事物的ACID

事物是有一组SQL语句组成的逻辑处理单元,事物具有ACID四个属性。

  • 原子性:事物是一个原子操作单位,其对数据的修改要么全部执行,要么全部不执行。
  • 一致性:在事物开始和完成时数据都必须保持一致的状态。可以这样理解。MySQL中的B+树索引缓存,和数据的存储,在事物开始前和结束后都是对应一致的。
  • 隔离性:一个事物处理过程中的中间状态对另外一个事物是不可见的。
  • 持久性:事物完成之后。修改的数据是持久性的,即使出现故障,数据也能持久性保存。

并发事物带来的问题

众所周知,MySQL的并发事物处理大大增加了数据库资源的利用率。但是同时也带来了如下的几个问题:

  • 丢失更新:当多个事物选择同一行进行更改时,由于每个事物都不知道其他事物的存在,就会发生丢失更新的问题。最后更新覆盖了其他事物的更新。
  • 脏读:一个事物正在对一条记录做修改,在这个事物未提交之前,第二个事物也来读取这条记录。如果不加控制,第二个事物读取的数据就是脏数据。
  • 不可重复读:一个事物读取某些数据之后,隔一段时间之后再次读取这些数据,却发现这些数据已经修改或者删除了。
  • 幻读:一个事物按照相同的查询条件重复读取以前检索的数据,却发现获得的数据集不一样,因为有其他事物进行了数据的修改并提交了修改操作。

事物隔离级别

如上看到了并发修改带来的四个问题。“丢失更新”并不能单靠数据库来解决。这个应该是应用程序的事情。必须由应用程序来应用程序通过加锁来解决。而“脏读”、“不可重复读”、“幻读”,都是数据库读一致性的问题,需数据库提供。不同的事物隔离级别,效率不同,带来的并发问题也不一样。

数据库准备

1
2
3
4
5
6
7
8
9
create table translation_test(
id int(11) not null auto_increment primary key comment '自增ID',
name varchar(32) not null default '' comment '姓名',
age int(11) not null default 0 comment '年龄'
) engine=InnoDB default charset=utf8mb4 ;
insert into translation_test(name,age)values('zhangsan',22);
insert into translation_test(name,age)values('lisi',20);
insert into translation_test(name,age)values('wangwu',26);

表中数据如下:

Read UnCommitted:

由于MySQL默认的事物隔离级别是Repeatable Read。所以这里首先将MySQL的事物隔离级别设置为Read UnCommitted

1
SET session transaction isolation level read uncommitted;

Read UnCommitted脏读问题

事务A 事务B
begin; begin;
update translation_test set age = 0 where id = 1;
select * from translation_test where id =1;
select * from translation_test where id =1;
commit; commit;

以上代码的处理逻辑是:

  1. 打开两个MySQL的终端,修改当前session的事务隔离级别为:Read Uncommit。
  2. 在事务A中执行修改ID为1的记录的数据,并执行查询。在事务B中查询ID为1的记录的数据。
  3. 提交两个事务。
    这里事物B查询到了事务A未提交的数据。所以Read UnCommitted 会出现脏读

Read UnCommitted幻读问题

事物A 事物B
begin; begin;
select * from translation_test where age=20;
insert into translation_test(name,age)values(‘gcl’,20);
select * from translation_test where age=20;
select * from translation_test where age=20;
commit;
select * from translation_test where age=20;
commit;

可以看到在事物A中两次执行同一个SQL,但是得到的结果并不一样。这是因为事物B提交了Insert事物。所以Read UnCommitted会出现幻读

Read UnCommitted不可重复读问题

事物A 事物B
begin; begin;
select * from translation_test where age =20 ;
update translation_test set age =21 where id =1;
commit;
select * from translation_test where age =20 ;
commit;

可以看到在事物A中两次执行同一个SQL,但是得到的结果并不一样。这是因为事物B提交了Update事物。所以Read UnCommitted会出现不可重复读问题

说明:
好多人不能区分幻读和不可重复读。其实区分情况很简单,幻读主要是针对Insert的情况。不可重复读主要是针对Update和Delete情况

Read Committed:

设置事务的隔离级别为Read Committed

1
SET session transaction isolation level read committed;

Read Committed脏读问题

事务A 事务B
begin; begin;
update translation_test set age = -1 where id = 1;
select * from translation_test where id =1;
select * from translation_test where id =1;
commit; commit;

这里事物B查询不到事务A未提交的数据。所以Read Committed 不会出现脏读

Read Committed幻读问题

事物A 事物B
begin; begin;
select * from translation_test where age=20;
insert into translation_test(name,age)values(‘gcl’,20);
select * from translation_test where age=20;
select * from translation_test where age=20;
commit;
select * from translation_test where age=20;
commit;

可以看到在事物A中两次执行同一个SQL,但是得到的结果并不一样。这是因为事物B提交了Insert事物。所以Read Committed会出现幻读

Read Committed不可重复读问题

为了更好演示不可重复读的情况。我们将数据修改如下。

事物A | 事物B
—|—
begin; | begin;
select from translation_test where age =20 ;
|
| update translation_test set age =21 where id =1;
| commit;
select
from translation_test where age =20 ;
|
commit;|
可以看到在事物A中两次执行同一个SQL,但是得到的结果并不一样。这是因为事物B提交了Update事务。所以ReadCommit会出现不可重复读问题。

Repeatable Read:

设置事物的隔离级别为Repeatable Read

1
SET session transaction isolation level repeatable read;

Repeatable Read脏读问题

事务A 事务B
begin; begin;
update translation_test set age = -1 where id = 1;
select * from translation_test where id =1;
select * from translation_test where id =1;
commit; commit;

这里事物B查询不到事务A未提交的数据。所以Repeatable Read 不会出现脏读

Repeatable Read幻读问题

事物A 事物B
begin; begin;
select * from translation_test where age=20;
insert into translation_test(name,age)values(‘gcl’,20);
select * from translation_test where age=20;
select * from translation_test where age=20;
commit;
select * from translation_test where age=20;
commit;

可以看到在事物A中两次执行同一个SQL,但是得到的结果并不一样。这是因为事物B提交了Insert事物。所以Repeatable Read会出现幻读

Repeatable Read不可重复读问题

事物A 事物B
begin; begin;
select * from translation_test where age =20 ;
update translation_test set age =21 where id =1;
commit;
select * from translation_test where age =20 ;
commit;

可以看到在事物A中两次执行同一个SQL,但是得到的结果是不一样的。所以Repeatable Read 不会出现不可重复读问题(不可重复读就是为了解决这个问题)。

Serializable:

Serializable事物隔离级别中。所有的事物都串行执行。这种情况下,不会出现并发事物带来的问题。但是效率也是最低的。

事物隔离级别问题总结

隔离级别 脏读(Dirty Read) 不可重复读(NonRepeatable Read) 幻读(Phantom Read)
未提交读(Read uncommitted) 可能 可能 可能
已提交读(Read committed) 不可能 可能 可能
可重复读(Repeatable read) 不可能 不可能 可能
可串行化(Serializable ) 不可能 不可能 不可能