SQL事务与隔离

àì夳堔傛蜴生んèń 2023-10-13 09:22 91阅读 0赞

## 事务 ##

### 事务的定义 ###

事务是完成一个任务的多条语句,这些语句中,只要有一条语句失败,那么整个事务就会失败,即使之前的语句已经执行完毕也会被撤回

举个例子:

我去银行给王哥转钱,这个转钱呢分两个步骤,第一步先把我的钱拿出来,第二步把钱给王哥,那万一刚把我钱拿出来但是没到王哥账户里面呢银行爆炸了,我钱直接凭空消失,这是不合理的,所以我们使用事务,把两个操作放在一个事务当中,往王哥账户转钱的操作没完成,那么我的钱就会退回

### 事务的性质 ###

事务有四大特性，各取首字母得到 ACID（“酸的”）：

1.  Atomicity 原子性，即整体性，不可拆分行（unbreakable），所有语句必须都执行成功事务才算完成，否则只要有语句执行失败，已执行的语句也会被复原
2.  Consistency 一致性，指的是通过事务我们的数据库将永远保持一致性状态，比如不会出现没有完整订单项目的订单
3.  Isolation 隔离性，指事务间是相互隔离互不影响的，尤其是需要访问相同数据时。具体而言，如果多个事务要修改相同数据，该数据会被锁定，每次只能被一个事务有权修改，其它事务必须等这个事务执行结束后才能进行
4.  Durability 持久性，指的是一旦事务执行完毕，这种修改就是永久的，任何停电或系统死机都不会影响这种数据修改

### 创建事务 ###

START TRANSACTION;
    
    
    
    COMMIT;

在这两行命令之间放我们的业务代码即可

如果我们写着写着突然发现写错了 那可以直接用ROLLBACK代替COMMIT

### **autocommit** ###

我们执行的SELECT、INSERT、UPDATE 或 DELETE 语句其实都被MYSQL自动包装成了一个事务然后自动提交

SHOW VARIABLES LIKE 'autocommit';

![6d50323d9ed94c2e9d074deb3f8b7770.png][] 我们可以看到这个参数是打开的

### 并发问题 ###

#### **Lost Updates 丢失更新** ####

![be8fd4b4929f4eb2bf538aa7ed0698d6.png][]

A操作的更新还没结束,此时B的更新操作读取到的还是未更新的值 所以等B实际更新完成后 就把A的操作覆盖了

**Dirty Reads 脏读**

事务A将某顾客的积分从10分增加为20分，但在提交前就被事务B读取了，事务B按照这个尚未提交的顾客积分确定了折扣数额，可之后事务A被退回了，所以该顾客的积分其实仍然是10分，因此事务B等于是读取了一个数据库中从未提交的数据并以此做决定，这被称作为脏读

**Non-repeating Reads 不可重复读取 （或 Inconsistent Read 不一致读取）**

事务A的语句里需要读取两次某顾客的积分数据，读取第一次时是10分，此时事务B把该积分更新为0分并提交，然后事务A第二次读取积分为0分，这就发生了不可重复读取 或 不一致读取

保持数据一致性，以事务A在开始执行时的数据初始状态为依据来做决定，如果这是我们想要的话，就要增加事务A的隔离等级，让它在执行过程中看不见其它事务的数据更改（即便是提交过的），SQL有个标准隔离等级叫 Repeatable Read 可重复读取，可以保证读取的数据是可重复和一致的，无论过程中其它事务对数据做了何种更改，读取到的都是数据的**初始状态**

**Phantom Reads 幻读**

幻读错误的理解：说幻读是事务A 执行两次 select 操作得到不同的数据集，即 select 1 得到10条记录，select 2 得到11条记录。这其实并不是幻读，这是不可重复读的一种，只会在 R-U R-C 级别下出现，而在 mysql 默认的 RR 隔离级别是不会出现的。

幻读，并不是说事务中多次读取获取的结果集不同，幻读更重要的是某次的 select 操作得到的结果集所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 记录不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，如同产生了幻觉

### 隔离级别 ###

#### Read Uncommitted读取未提交 ####

最低等级的隔离级别解决不了任何问题,就是说这个事务还没提交呢,其他的事务就可以读取它的数据了,或者就直接理解成完全没有隔离

#### Read Committed 读取已提交 ####

只能读取已提交的数据，这防止了Dirty Reads 脏读

#### Repeatable Read 可重复读取 ####

不同的读取会返回相同的结果，即便数据在这期间被更改和提交,以事务开始读取到的数据为准(通过)

#### Serializable 序列化 ####

可以防止以上所有问题，这一级别还能防止幻读，如果有其他事务修改了可能影响结果的数据 我们的事务必须等待它完成，但这很明显会给服务器增加负担，因为管理等待的事务需要消耗额外的储存和CPU资源

![7cc8474a3bc34f73a39d6ea5606ddfc2.png][]

关于这些有一个很有意思的总结,就是我们认为读取已提交是锁住了读行为(不能读未提交的数据) 所以解决了脏读问题

可重复提交锁住了行 所以解决了行修改造成的并发问题(丢失更新 不可重复读)

序列化锁住了表 所以解决了插入,删除对整张表的问题(幻读)

更详细的解释:

*  对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
 *  对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
 *  对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 **Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View**。

更低的隔离级别更容易并发，会有更多用户能在相同时间接触到相同数据，但也因此会有更多的并发问题，另一方面因为用以隔离的锁定更少，性能会更高

相反，更高的隔离等级限制了并发并减少了并发问题，但代价是性能和可扩展性的降低，因为我们需要更多的锁定和资源

MySQL的默认等级是 Repeatable Read 可重复读取，它可以防止除幻读外的所有并发问题并且比序列化更快,虽然不能防止幻读 但是可以避免大部分情况的幻读，多数情况下应该保持这个默认等级。

当然,如果对于某个特定的事务，防止幻读至关重要，可以改为 Serializable 序列化

对于某些对数据一致性要求不高的批量报告或者对于数据很少更新的情况，同时又想获得更好性能时，可考虑前两种等级

总的来说，一般保持默认隔离等级，只在特别需要时才做改变

设置隔离级别的方法:

SET [SESSION]/[GLOBAL] TRANSACTION ISOLATION LEVEL SERIALIZABLE

SESSION 就是设置本次会话（链接）之后所有事务的隔离等级，加上 GLOBAL 就是设置之后所有对话的所有事务的隔离等级

LEVEL后面写对应隔离

###  ###

我们来看一道例题

有一张账户余额表，里面有一条账户余额为 100 万的记录。然后有两个并发的事务，事务 A 只负责查询余额，事务 B 则会将我的余额改成 200 万，下面是按照时间顺序执行两个事务的行为：

![70ba7ee3d98614e7725ec4bf620d50f3.png][]

不同隔离等级下的结果是什么呢

未提交读:V1V2V3都是 200万 事务A可以看到未提交前事务B中的改动

已提交读:V1 100万 V2V3 200万 V1的时候看不见未提交的修改

可重复读: V1 V2 100万 V3 200万 从定义角度来说 事务内的多次读结果一致 从原理来说 整个事务使用的都是事务开始一瞬间的快照

序列化(串行化): V1 V2 100万 V3 200万 事务A开始进行读操作的时候 读写锁就锁住了数据 直到事务A结束后 才释放锁 事务B才能开始修改数据

### 死锁 ###

事务里的增删改语句都会锁定相关行（,如果两个同时在进行的事务分别锁定了对方下一步要使用的行，就会发生死锁，死锁不能完全避免但有一些方法能减少其发生的可能性

![395aef00cdcd4fbbb074ff0289327d5d.png][]

事务1执行到第二个修改的时候 发现该行事务2锁定了

事务2执行到第二个修改的时候 发现该行事务1锁定了(只有事务整体执行完才会释放锁)

规避死锁的方法:

1.  注意语句顺序：如果检测到两个事务总是发生死锁，检查它们的代码，这些事务可能是储存过程的一部分，看一下事务里的语句顺序，如果这些事务以相反的顺序更新记录，就很可能出现死锁，为了减少死锁，我们在**更新多条记录时可以遵循相同的顺序**
2.  尽量让你的事务小一些，持续时间短一些，这样就不太容易和其他事务相冲突
3.  如果你的事务要操作非常大的表，运行时间可能会很长，冲突的风险就会很高，看看能不能让这样的事物避开高峰期运行，以避开大量活跃用户

[6d50323d9ed94c2e9d074deb3f8b7770.png]: https://img-blog.csdnimg.cn/6d50323d9ed94c2e9d074deb3f8b7770.png
[be8fd4b4929f4eb2bf538aa7ed0698d6.png]: https://img-blog.csdnimg.cn/be8fd4b4929f4eb2bf538aa7ed0698d6.png
[7cc8474a3bc34f73a39d6ea5606ddfc2.png]: https://img-blog.csdnimg.cn/7cc8474a3bc34f73a39d6ea5606ddfc2.png
[70ba7ee3d98614e7725ec4bf620d50f3.png]: https://img-blog.csdnimg.cn/img_convert/70ba7ee3d98614e7725ec4bf620d50f3.png
[395aef00cdcd4fbbb074ff0289327d5d.png]: https://img-blog.csdnimg.cn/395aef00cdcd4fbbb074ff0289327d5d.png