MySQL篇—事务和隔离级别介绍

☘️博主介绍☘️：

✨又是一天没白过，我是奈斯，DBA一名✨

✌✌️擅长Oracle、MySQL、SQLserver、Linux，也在积极的扩展IT方向的其他知识面✌✌️

❣️❣️❣️大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注❣️❣️❣️

    今天，作为新一年的第一篇文章，与大家分享关于MySQL事务和隔离级别的知识。在数据库管理系统中，事务是确保数据完整性和一致性的关键机制。通过事务，我们可以将多个数据库操作组合成一个逻辑单元，要么全部执行，要么全部不执行，从而确保数据的正确性和一致性。

    而隔离级别则是控制多个事务并发执行时如何相互影响的关键因素。不同的隔离级别提供了不同的数据可见性和并发性能，需要根据具体的业务需求和性能要求来选择合适的隔离级别。

    一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性、持久性）属性。

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

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

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

持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。小结：原子性是事务隔离的基础，隔离性和持久性是手段，最终目的是为了保持数据的一致性

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

---

          

目录

案例：设置4种隔离级别，分析对mysql的影响。建议设置为read-committed事务级别

级别一：读未提交read-uncommitted：一个事务可以读到另一个事务未提交的结果为脏数据

级别二：读已提交read-committed：只有在事务提交后，其结果才会被其他事务看见

级别三：可重复读repeatable-read：无论事务对数据是否进行操作，事务是否提交，对于同一份数据的读取结果总是相同的。只有退出会话（事务）才能同步数据

级别四：串行化serializable：，隔离级别最高，牺牲了系统的并发性。就是锁表（不是行锁），事务修改表时并没有提交，禁止其他所有事务连接当前表

---

          

通过现象反映隔离级别效果：

01更新丢失（lost update）：当系统允许两个事务同时更新同一数据是，发生更新丢失。例：事务A将数值改为1并提交，事务B将数值改为2并提交。这时值变为2。不算问题，正常情况

02脏读（dirty read）：当一个事务读取另一个事务尚未提交的修改时，产生脏读。

03不可重复读（non-repeatable read）：同一查询语句在同一事务中多次进行，如果这个过程中其他事务提交了所做的修改或删除，会发生每次返回不同的结果集，此时发生非重复读，那么就是意味着同一事务执行完全相同的select语句时可能看到不一样的结果。导致这种情况的原因可能有：

             (1)有一个交叉的事务有新的commit，导致了数据的改变

             (2)一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit多个commit提交时，只读一次出现结果不一致

04幻读（phantom read）：出现幻读的情况是由于并发事务引起的。在同一个事务内对于相同的查询条件，在两次查询之间新增或删除了符合条件的数据，导致第二次查询结果与第一次查询结果不一致的情况。解决幻读的办法：

            (1)调整事务隔离级别为串行化

            (2)通过间隙锁和next-key locks，而间隙锁和next-key locks只有在隔离级别为可重复读或以上才有（二八定律，RR级别: 20%的事务存在幻读;80%的事务不存在幻读的风险）。在可重复读级别下查询加上for update后如果有数据返回就是行锁，没有数据就加上间隙锁和next-lock key锁住一个范围不允许其他事务DML只能自己才能DML。

例：事务A改了未提交，事务B改其他，A再查。  A把所有‘100’改为‘200’，B把所有‘50’改为‘100’。A查询100，发现还有数据，产生幻读。 RR 级别下存在幻读的可能，但也是可以使用对记录手动加 X锁（RR模式下的X锁会同时进行间隙锁和next-key locks来防止幻读） 的方法消除幻读。SERIALIZABLE 正是对所有事务都加 X锁 才杜绝了 幻读

           

四种隔离级别（MySQL默认Repeated Read，建议改为read committed）：

（1）read uncommitted：最低的隔离级别，一个事务可以读到另一个事务未提交的结果为脏数据。

（2）read committed (DEFAULT)：只有在事务提交后，其更新结果才会被其他事务看见，解决了更新丢失、脏读。Oracle、db2、sql server默认的隔离级别

（3）Repeated Read(重复读)：MySQL默认隔离级别。在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交，它确保同一事务的多个实例在并发读取数据时，看到同样的数据行，只有退出会话（同事务）才能同步数据。解决了更新丢失、脏读、不可重复读。RR 级别下存在幻读的可能，解决幻读的办法：

             (1)调整事务隔离级别为串行化

             (2)通过间隙锁和next-key locks，而间隙锁和next-key locks只有在隔离级别为可重复读或以上才有（二八定律，RR级别: 20%的事务存在幻读;80%的事务不存在幻读的风险）。在可重复读级别下查询加上for update后如果有数据返回就是行锁，没有数据就加上间隙锁和next-lock key锁住一个范围不允许其他事务DML只能自己才能DML

（4）Serializable(串行化)：事务串行化执行，隔离级别最高，牺牲了系统的并发性。可以解决并发事务的所有问题。就是X锁表（不是行锁），一个事务修改表时并没有提交，禁止其他所有事务连接当前表。SERIALIZABLE 正是对所有事务都加 X锁 才杜绝了 幻读，但很多场景下我们的业务 sql 并不会存在 幻读 的风险。SERIALIZABLE 的一刀切虽然事务绝对安全，但性能会有很多不必要的损失。故可以在 RR 下根据业务需求决定是否加锁，存在幻读风险我们加锁，不存在就不加锁，事务安全与性能兼备，这也是 RR 作为 mysql默认隔是个事务离级别的原因，所以需要正确的理解 幻读。

                   

read committed和Repeated Read在处理并发事务时的区别：

1）"Read Committed" 隔离级别：

每个读操作只能看到已经提交的事务所做的更改，而不能看到其他未提交的事务所做的更改。

事务在读取数据时会对每一行数据加共享锁，直到读操作完成才会释放锁定。

2）"Repeatable Read" 隔离级别：

在事务开始后，所有的查询都只能看到在该事务开始之前已经提交的数据，不会看到其他事务所做的更改。

事务在读取数据时会对整个表加共享锁，直到事务结束才会释放锁定。

总结：

      "Read Committed" 隔离级别只保证读取已提交的数据，可以避免脏读，但可能出现不可重复读和幻读。

     "Repeatable Read" 隔离级别通过在事务期间锁定读取的数据，可以避免不可重复读，但仍可能出现幻读。

      根据具体的业务需求和并发环境来选择合适的隔离级别，如果需要更高的数据一致性和读取的稳定性，可以选择 "Repeatable Read" 隔离级别。如果对一致性要求相对较低，需要更好的并发性能，可以选择 "Read Committed" 隔离级别。

                      

隔离级别相关参数：

mysql> show variables like '%tx_isolation%';     ---默认REPEATABLE-READ对数据不安全。建议修改为READ-COMMITTED

                   

设置隔离级别：

mysql> set global tx isolation='READ-COMMITTED|READ-UNCOMMITTED|REPEATABLE-READ|SERIALIZABLE';
注：在会话和全局级别修改参数，都不会永久修改参数。永久修改参数只能将参数添加到my.cnf文件，然后重启生效。添加参数：transaction-isolation=READ-COMMITTED

                     

案例：设置4种隔离级别，分析对mysql的影响。建议设置为read-committed事务级别

级别一：读未提交read-uncommitted：一个事务可以读到另一个事务未提交的结果为脏数据

mysql> set global tx_isolation='READ-UNCOMMITTED';     ---影响所有会话，但重启失效

mysql> show variables like '%tx_isolation%';  

mysql> set global autocommit=0;       ---关闭自动提交功能。这里只是为了测试，所以关闭了自动提交功能，默认开启

           

会话一：

mysql> create table tb(id int,name varchar(20));
mysql> insert into tb values (1,'itpux1');
mysql> select * from tb;

          

会话二：

mysql> select * from tb;         ---其他会话查到未提交事务的数据

                   

级别二：读已提交read-committed：只有在事务提交后，其结果才会被其他事务看见

mysql> set global tx_isolation='READ-COMMITTED';    ---影响所有会话，但重启失效

mysql> show variables like '%tx_isolation%';  

mysql> set global autocommit=0;       ---关闭自动提交功能。这里只是为了测试，所以关闭了自动提交功能，默认开启

          

会话一：

mysql> create table tb(id int,name varchar(20));
mysql> insert into tb values (1,'itpux1');
mysql> select * from tb;

         

会话二：

mysql> select * from tb; 

        

会话一：

mysql> commit;

         

会话二：

mysql> select * from tb;         ----事务提交后，其他事务才能读取数据

                     

级别三：可重复读repeatable-read：无论事务对数据是否进行操作，事务是否提交，对于同一份数据的读取结果总是相同的。只有退出会话（事务）才能同步数据

mysql> set global tx_isolation='REPEATABLE-READ';    ---影响所有会话，但重启失效

mysql> show variables like '%tx_isolation%';  

mysql> set global autocommit=0;       ---关闭自动提交功能。这里只是为了测试，所以关闭了自动提交功能，默认开启

           

会话一：

mysql> select * from tb;

mysql> update tb set id=1000 where name='itpux1';
mysql> commit;

              

会话二（会话一打开时确保会话二一同打开）：

mysql> select * from tb; 

[root@mysql2 ~]# mysql -u root -p    ---退出会话，相当于结束一个事务。然后重新登录一个会话（同事务）
mysql> select * from tb;        ---查询到其他事务提交的事务

            

级别四：串行化serializable：，隔离级别最高，牺牲了系统的并发性。就是锁表（不是行锁），事务修改表时并没有提交，禁止其他所有事务连接当前表

mysql> set global tx_isolation='SERIALIZABLE';    ---影响所有会话，但重启失效

mysql> show variables like '%tx_isolation%';  

mysql> set global autocommit=0;       ---关闭自动提交功能。这里只是为了测试，所以关闭了自动提交功能，默认开启

         

会话一：

mysql> select * from tb;

mysql> update tb set id=9999 where name='itpux1';

          

会话二：

mysql> select * from tb;          ----查询没有响应。事务级别为串行化，事务没结束之前，对操作的对象进行锁表

           

会话一：

mysql> commit;          ---结束事务（提交事务）

          

会话二：

mysql> select * from tb;   ---事务完成后，其他事务才能读取数据

总结：串行化serializable事务隔离级别，只有读读之间可以并发；读写/写读/写写都要阻塞进行锁表。也就是no MVCC（多版本并发控制）

            

    好啦今天的内容结束了，希望这边文章可以让大家对事务和隔离级别有所了解。