MySQL之1--MySQL事物隔离级别

事物及其ACID属性

事物的ACID

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

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

并发事物带来的问题

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

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

事物隔离级别

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

数据库准备

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;

以上代码的处理逻辑是：

打开两个MySQL的终端，修改当前session的事务隔离级别为：Read Uncommit。
在事务A中执行修改ID为1的记录的数据，并执行查询。在事务B中查询ID为1的记录的数据。
提交两个事务。
这里事物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不可重复读问题

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 ）	不可能	不可能	不可能

Category：Technology

Labels： MySQL