mysql隔离级别

mysql事务隔离级别

1、回顾事务特性

事务的基本要素（ACID）

• 原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

• 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

• 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

• 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

总结：

原子性是事务隔离的基础，隔离性和持久性是手段，最终目的是为了保持数据的一致性

2、素材

Create database AAA;
CREATE TABLE t_user (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    money double
) Engine=InnoDB CHARSET=utf8;
---------------------------------
插入数据
mysql> INSERT INTO t_user VALUES(1, 'liubei',1000);
mysql> INSERT INTO t_user VALUES(2, 'guanyu',1000);

mysql> select * from t_user;

3、开启和关闭事务

默认情况下，单独的一条sql就是一个事务，所谓默认情况指的是你没有手动去开启事务

• 关闭/开启自动提交状态

#########查看自动提交状态###################
select @@autocommit;
######值为0： 关闭自动提交   值为1：开启自动提交
set autocommit = 0|1; 

关闭自动提交后，从下一条sql语句开始则开启新事务，需要使用commit或rollback语句结束该事务

案例说明

现在我们采用默认情况，也就是没有手动去开启事务或者手动提交事务

mysql> update t_user set money = money - 100 where name = 'liubei';  //其实默认执行了commit
mysql> select * from t_user;

mysql> rollback;      //即使是回滚，依然还是更新了
mysql> select * from t_user;

4、隔离级别

MySQL是一个服务器／客户端架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称之为一个会话（Session）。我们可以同时在不同的会话里输入各种语句，这些语句可以作为事务的一部分进行处理。不同的会话可以同时发送请求，也就是说服务器可能同时在处理多个事务，这样子就会导致不同的事务可能同时访问到相同的记录。我们前边说过事务有一个特性称之为隔离性，理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据。但是这样子的话对性能影响太大，所以设计数据库的大叔提出了各种隔离级别，来最大限度的提升系统并发处理事务的能力，但是这也是以牺牲一定的隔离性来达到的

查看当前数据库的隔离级别

mysql> SELECT @@tx_isolation    ####mysql默认是可重复读

修改当前数据库的隔离级别

mysql> set global transaction isolation level read uncommitted;   ####这是设置的读未提交级别

注意：设置后要重启客户端

4-1、读未提交READ UNCOMMITTED

读未提交：READ UNCOMMITTED

如果一个事务读到了另一个未提交事务修改过的数据，那么这种隔离级别就称之为未提交读（英文名：READ UNCOMMITTED），事务A对数据做的修改，即使没有提交，对于事务B来说也是可见的，这种问题叫脏读，这是隔离程度较低的一种隔离级别，在实际运用中会引起很多问题，因此一般不常用，它不能解决脏读，可重复读，幻读的问题

案例：

1、开启两个客户端，连接数据库，并设置数据库的隔离级别为读未提交，设置后要重启客户端

mysql> set global transaction isolation level read uncommitted; 

2、重启2个客户端

设置每个客户端的自动提交为false

客户端A
mysql>show variables like 'autocommit'\G
mysql>set autocommit=off;
-----------------------------------
客户端B
mysql>show variables like 'autocommit'\G
mysql>set autocommit=off;

此步骤不用重启客户端

3、操作步骤如下

步骤	Session A	Session B	备注
1	begin	begin	各自开启事务
2	update t_user set money = money-100 where id =2;	select * from t_user;	A没有提交，B：900
3	rollback	select * from t_user;	A回滚，B查出来的是id为2的钱又为1000

经过上面的实验可以得出结论，事务A更新了一条记录，但是没有提交，此时事务B查出来关羽的钱已经减掉了100。当A回滚时，B再查发现关羽钱又为1000，造成脏读现象。未提交读是最低的隔离级别

4-2、读已提交READ COMMITTED

如果一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那么这种隔离级别就称之为已提交读（英文名：READ COMMITTED），在此隔离级别下，不会出现脏读的问题。事务A对数据做的修改，提交之后会对事务B可见。它能解决脏读问题，不能解决可重复读和幻读问题

操作如下

步骤	Session A	Session B	备注
1	set global transaction isolation level READ COMMITTED；	set global transaction isolation level READ COMMITTED；	设置隔离级别为读已提交，其实不用两个会话都设置
2	重启客户端	重启客户端
3	set autocommit=off;	set autocommit=off;	关闭默认提交功能
4	begin;	begin;	开启各自事务
5	update t_user set money = money-100 where id =2;	select * from t_user;	A没有提交，B：id为2还是查询结果 1000
6	commit；	select * from t_user	select * from t_user;

经过上面的实验可以得出结论：在该业务场景中，事务B只能读到事务A已经提交的事务修改过的数据， 事务 B 多次读取同一数据，事务 A 在事务B多次读取的过程中，对数据作了更新并提交，导致事务B再次读取同一数据时，结果 不一致，这就导致不可重复读，但是解决了脏读问题

不可重复读：事务 B 多次读取同一数据，事务 A 在事务B多次读取的过程中，对数据作了更新并提交，导致事务B再次读取同一数据时，结果 不一致

4-3、可重复读REPEATABLE READ

在该业务场景中，事务B只能读到事务A已经提交的事务修改过的数据，但是第一次读过某条记录后，即使其他事务修改了该记录的值并且提交，该事务之后再读该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据。那么这种隔离级别就称之为可重复读（英文名：REPEATABLE READ） ，可重复读解决了脏读和不可重复读的问题，但是不能解决幻读问题

1、修改隔离级别

由于mysql数据库默认的隔离级别是可重复读，故不用设置，重启mysql的docker容器即可，然后再登陆到mysql即可

步骤	Session A	Session B	备注
1	set autocommit=0;	set autocommit=0;	关闭默认提交功能
2	begin;	begin;	开启各自事务
3	update t_user set name = ‘xx’ where id =1;	select * from t_user;	A没有提交，B：id为1还是查询结果 liubei
4	commit；	select * from t_user	A提交，B：id为1还是查询结果 liubei;

4-4、幻读

事务B按照一定条件进行数据读取， 期间事务A插入了相同搜索条件的新数据，事务A查询的时候，还是看不到B插入的新数据，但是其实数据库是有的，即使A事务提交了，B也发现不了，这个时候B也插入一条相同的数据，就会报错。这种情况称为幻读

案例： 隔离级别为 可重复读

步骤	Session A	Session B	备注
1	set autocommit=0;	set autocommit=0;	关闭默认提交功能
2	begin;	begin;	开启各自事务
3	insert into t_user values(3,‘wangwu’,1000);	select * from t_user;	A没有提交，B只能看到2条
4	commit；	select * from t_user	A提交，B还是只能看到2条
5		insert into t_user values(3,‘wangwu’,1000);	B插入相同的数据，报错

总结：

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

如何解决幻读问题？

可以采用间隙锁

步骤	Session A	Session B	备注
1	set autocommit=0;	set autocommit=0;	关闭默认提交功能
2	begin;	begin;	开启各自事务
3	select * from t_user where id>0 for update	select * from t_user;	A没有提交，B只能看到2条
4	insert into t_user values(3,‘wangwu’,1000);	select * from t_user	A提交，B还是只能看到2条
5		insert into t_user values(3,‘wangwu’,1000);	报错，等待

4-5、串行化SERIALIZABLE

以上3种隔离级别都允许对同一条记录进行读-读、读-写、写-读的并发操作，如果我们不允许读-写、写-读的并发操作，可以使用SERIALIZABLE隔离级别，即事务之间的执行是串行的，当一个事务在操作的时候，另外的事务就只能等，必须等到该事务提交或者回滚，其它的事务才能继续操作，serializable隔离级别的多个事务不可以同时对同一张表修改！

串行化，能解决脏读，可重复读，幻读问题，但是效率非常慢，因为事务执行是串行执行的。

案例：重启docker，开启2个客户端登陆到mysql ，

步骤	Session A	Session B	备注
1	set session transaction isolation level SERIALIZABLE；	set session transaction isolation level SERIALIZABLE；	设置隔离级别为串行化
2	set autocommit=0;	set autocommit=0;	关闭默认提交功能
3	begin;	begin;	开启各自事务
5	insert into t_user values(4,‘xx’,1000);;	select * from t_user;	A没有提交， B卡死
6	commit；	select * from t_user	B才有反应

由于A事务一直没有提交

在A事务提交之后，B事务才能查到结果

ser | B才有反应 |

[外链图片转存中…(img-lFLTFbXR-1662473127951)]

[外链图片转存中…(img-NIkEFYmb-1662473127952)]

由于A事务一直没有提交

[外链图片转存中…(img-zSOQwbEP-1662473127953)]

在A事务提交之后，B事务才能查到结果