MySQL事务详解，事务并发冲突，事务隔离级别

4253人阅读 2022/5/8 18:28 总访问：2977513 评论：3 收藏：1 手机

分类: 数据库

[TOC]


## 事务的概念

事务：把所有的操作当中一个整体，要么全部成功，要么全部失败

tn2> 一旦开启了事务所有的操作都是临时的，你可以选择提交或者回滚
提交事务：全部成功
回滚事务：全部失败

##### 事务四大特性（ACID）

tn2> 事务具有原子性，一致性，隔离性，持久性
Atomicity,Consistency,Isolation,Duration
A 原子性：事务必须是一个自动工作的单元，要么全部执行，要么全部不执行。
C 一致性：事务把数据库从一个一致状态带入到另一个一致状态，事务结束的时候，所有的内部数据都是正确的。
I 隔离性：并发多个事务时，一个事务的执行不受其他事务的影响。
D 持久性：事务提交之后，数据是永久性的，不可再回滚，不受关机等事件的影响。

##### 事务能解决什么问题

tn4>有些操作并不是一步就能搞定的。但是它又不允许这些步骤中一些成功一些失败。需要保证他们，要么全部成功，要么全部撤销。比如转账，转账失败不可怕，重新转账一次即可，比如转账超时，账户冻结等，可怕的是数据不一致，A账户减去了1000，而B账户又没有加上的情况。如果一些操作能够允许成功一部分，失败一部分，那么不使用事务也没有关系，效率还快一点。

比如转账的操作简化成3步：我们能保障他要么全部执行成功要么全部执行失败，保证数据的完整性和一致性等。让这些分开的步骤当中一个统一的不开分隔的操作去执行。
```sql
/* 转账操作简化步骤 */
## 1：查询余额是否转账的金额
## 2：A账户减去1000
## 3：B账户增加1000
## .......
```
又或者网上购物下单也是一个整体。我们都需要保证这些数据的一致性。使用的情况非常多，所以事务也是会使用比较频繁的。
```sql
/* 网上购物下单也是一个整体，如果一些成功一些失败就会产生bug,造成数据不一致  */
## 购买一个商品
## 对方订单增加一条
## 库存要减去对应的购买数量
## ........
```

开启了事务，相当于我们多了一个控制它的机会，可以选择或者是回滚还是提交！

## 模拟转账操作
测试表：
```
  create Table Account
  (
    Id int primary key auto_increment,
    MyMoney int,
    AccountNumber varchar(64) 
  )
  
  select *from Account
  
  insert Account values(default,2000,'小红');
  insert Account values(default,2000,'小芳');
  -- 恢复数据
  update Account set MyMoney =2000

```

一般转账的步骤是：

tn2> 1：检查账户A的余额是否充足，也就是余额是否大于等于转账的金额
2：账户A减少转账的金额
3：账户B增加转账添加

#### 模拟转账操作 小红给小芳转账1000

#### 正常的情况
小红减少1000，小芳增加1000
``` sql
/* 模拟转账操作 小红给小芳转账1000 */

-- 1：查询小红的余额
select * from Account where accountNumber = '小红';

-- 2: 将小红余额减去1000
update Account set mymoney = mymoney-1000 where accountNumber = '小红';

-- 3：将小芳余额加上1000
update Account set mymoney = mymoney+1000 where accountNumber = '小芳';
```

#### 测试非正常情况
在2，3步骤之间随便弄一点错误出来即可就会出现，小红减去了钱，而小芳还没有添加上的情况。
```sql
/* 模拟转账操作 小红给小芳转账1000 */

-- 1：查询小红的余额
select * from Account where accountNumber = '小红';

-- 2: 将小红余额减去1000
update Account set mymoney = mymoney-1000 where accountNumber = '小红';

-- 随便让一句报错的
int i = 10/0

-- 3：将小芳余额加上1000
update Account set mymoney = mymoney+1000 where accountNumber = '小芳';
```
结果如下：可以看到明显数据不正常
![](https://img.tnblog.net/arcimg/aojiancc2/fbb237b610dc4069905f50dcc8961186.png)

**所以我们应该把这些操作都放到一个事务当中去执行**
其实上面的操作本身是有用事务的，mysql是默认有事务的，默认是一条语句一个事务，执行完成后自动提交，不需要手动提交。oracle默认事务是需要手动添加的，不然一边的数据添加了没有添加，另外一边是不会看到变化的，当然这些默认情况是和版本有关系的。


## 简单事务的操作

### 通常的使用步骤
涉及到的步骤就是，开启事务，提交事务，或者回滚事务

tn3> 1：开启事务  begin 或者start       transaction      [事务名称]
2：提交事务  commit      [transaction   事务名称]
3：回滚事务  rollback    [transaction   事务名称]

sqlserver中事务语法也类似可以简写成trans，比如

tn3> 1：开启事务  begin       trans/transaction      事务名称
2：提交事务  commit      trans/transaction      事务名称
3：回滚事务  rollback    trans/transaction   事务名称


### 事务的控制方式又分为自动与手动
其实上面的转账操作本身是有用事务的，mysql是默认有事务的，默认是一条语句一个事务，执行完成后自动提交，不需要手动提交。oracle默认事务是需要手动添加的，不然一边的数据添加了没有添加，另外一边是不会看到变化的，当然这些默认情况是和版本有关系的。

#### 查询事务的提交方式：
```
	select @@autocommit;
```
tn3> 结果有两种，如果为1就是自动提交，如果为0就是手动提交。这里查询出来是1默认就是自动提交。

#### 设置事务的提交方式：
```
set @@autocommit = 0; -- 设置成手动提交
set @@autocommit = 1; -- 设置成自动提交
```
这里说一下，上面的方式其实是一种临时的方式，换一个连接窗口就恢复了，如果要所有设置都生效也就是全局设置，可以使用如下的方式：
```
SET autocommit = 0|1|ON|OFF;
```
配合的查询是：
```
SHOW VARIABLES LIKE 'autocommit';
```
其实也就是全局变量(global)，会话变量（session）的区别。

#### 使用手动提交的方式进行事务控制
先设置成手动提交
```
set @@autocommit = 0; -- 设置成手动提交
```
**如果设置成手动提交，我们的单条sql语句操作也可以进行回滚**
因为没有执行commit之前，操作都是临时的我们随时可以进行数据的回滚。

比如我们执行删除账户表：来模拟不小心删除数据忘记接条件了
```
delete from Account 
```
我们只需要执行一下rollbak数据就会回来了。而且我们执行删除后在其他连接下看到的数据是没有被删除的，除非我们调用commit数据才会被真正的提交。测试的时候可以多开几个连接来测试，比如两个navicat下的查询窗口加一个控制台下的查询。

**然后我们就可以把上面模拟转账的来进行事务控制**

模拟正常情况下，我们直接commit提交即可，转账会成功：
```
/* 模拟转账操作 小红给小芳转账1000 */

-- 1：查询小红的余额
select * from Account where accountNumber = '小红';

-- 2: 将小红余额减去1000
update Account set mymoney = mymoney-1000 where accountNumber = '小红';

-- 3：将小芳余额加上1000
update Account set mymoney = mymoney+1000 where accountNumber = '小芳';

commit;
```

模拟异常情况下，我们直接rollbak回滚即可(这里我们需要手动执行rollback进行测试)，转账会失败：
```
/* 模拟转账操作 小红给小芳转账1000 */

-- 1：查询小红的余额
select * from Account where accountNumber = '小红';

-- 2: 将小红余额减去1000
update Account set mymoney = mymoney-1000 where accountNumber = '小红';

-- 随便让一句报错的
int i = 10/0

-- 3：将小芳余额加上1000
update Account set mymoney = mymoney+1000 where accountNumber = '小芳';

rollback;
```
tn4> 正常情况下提交事务，异常情况下回滚事务，这样我们就可以保证数据的正确性和完整性了。当然这里只是模拟演示手动提交模式下的事务控制方法，真实情况下我们需要配合存储过程，函数等去把各种情况的验证写完整。


#### 使用自动提交的方式进行事务控制

设置成自动提交(本身就是事务的默认行为)
```
set @@autocommit = 1; -- 设置成自动提交(本身就是事务的默认行为)
```
tn4> 这种方式下，单条sql语句执行完成后会自动提交事务，所以我们需要使用
begin/start transaction手动开始事务

转账的简单示例：使用start transaction这些也非常简单，自己控制事务即可
```

/* 模拟转账操作 小红给小芳转账1000 */

-- 开始事务
start transaction;

-- 1：查询小红的余额
select * from Account where accountNumber = '小红';

-- 2: 将小红余额减去1000
update Account set mymoney = mymoney-1000 where accountNumber = '小红';

-- 随便让一句报错的
 int i = 10/0

-- 3：将小芳余额加上1000
update Account set mymoney = mymoney+1000 where accountNumber = '小芳';

-- 回滚事务 
rollback;

-- 提交事务
commit;
```


## 事务的并发问题

事务的常见并发问题分为：脏读，不可重复读，幻读。这小节只是面试这些问题，下个小节有详细的步骤演示与解决办法。

![](https://img.tnblog.net/arcimg/aojiancc2/9c7f512d00844fc8909337c2f8d733a9.png)

### 脏读

脏读模拟：

tn2>第1步：事务A随便查询一下表先看看当前的数据
第2步：事务B去修改一条数据，并且不提交数据
第3步：事务A在去查询一下数据，会发现数据已经被修改了。读取到另外一个事务没有提交的数据这个就是脏读。

脏读的模拟其实很简单，一个事务去查询另外一个没有提交的事务就行了。


### 不可重复读

不可重复读的意思就是在一个事务中，读取多次会出现数据不一致的情况。
比如事务在第1步读取了数据，然后在第三步又读取一次，数据就变化了。因为这个期间有其他事务更改了这个数据并提交了。

如果要解决这个问题就要把他变成"可重复读"，也就是一个事务中读取n次数据都要一样。虽然有时候即使一个事务中出现多次读取结果不一样也无所谓。



### 幻读

幻读模拟：

tn2>第1步：事务A查询id=1的数据提示不存在
第2步：事务B去添加一条id=1的数据
第3步：因为第1步查询没有id=1的数据所以尝试添加id=1的数据，结果添加不了提示重复id
第4步：在查询一次以确定是不是数据真的不存在于是在查询id=1的，结果还是没有。这就是幻读！明明有但是查询不出来，添加又添加不了的尴尬情况。就像问一个女生有没有男朋友她说没有，但是她又已经有男朋友了。

幻读就是数据插入的时候无法插入，提示已经存在了。但读取又无法读取的情况是一种错觉。明明插入时主键冲突，却又读不出来，就造成的幻觉。
幻读是在解决了不可重复读后引发出来的问题，所以A事务的第1步和第3步查询结果要一致，不然问题还留在不可重复读这个层面上。

**哈哈，来贴一下画得比较粗糙的图**
![](https://img.tnblog.net/arcimg/aojiancc2/e888c801deb3459ba7cbbf6ad05be024.png)

## 数据库隔离级别数据

![](https://img.tnblog.net/arcimg/aojiancc2/798ecc8c5e554a1d8227507bd202c68c.png)

tn3>Read Uncommitted（读取未提交内容）：基本没啥用 什么都不能限制
Read Committed(读取提交内容):大部分数据库的默认级别，只能看到已提交的改变（避免脏读）
Repeatable Read(可重复读)：MySql的默认级别，同一事务并发时 保证读取数据行一样（避免脏读与重复读）
Serializable(串行化)：最高级别 但是性能差，锁竞争（避免脏读、重复读、幻读）。就像玩游戏一样，一个怪只能同时和一队玩家战斗，其他玩家的队伍都得等待，比如梦幻西游的五行斗法，所以会经常堵车

隔离级别越高，效率越慢。
所以在选择事务隔离级别的时候要权限数据安全性和效率。如果业务逻辑允许脏读，不可重复的这些，我们完全可以把数据库隔离级别设置得很低，让效率更高。

tn3>其实通过这些单词也好理解意思的。比如Read Uncommitted就是能够读取未提交的嘛，Read Committed是能够读取提交的嘛，反正就是可以读取不管是否是提交的，就会造成事务的两种并发情况。

### 查询事务的隔离级别
```sql
select @@transaction_isolation
-- 其他方法
show VARIABLES like 'trans%'
select @@session.transaction_isolation
select @@global.transaction_isolation
```

### 设置事务的隔离级别
```sql
set session transaction isolation level read uncommitted
set session transaction isolation level repeatable read
```
### 演示脏读Read Uncommitted
也就是可以读取另外一个事务没有提交过的数据。复现步骤其实上面也说过。
如下图：
第1步先设置一下，事务的隔离级别为允许脏读。此时会出现脏读，不可重复读，幻读。
```
/* 设置事务的隔离级别为read uncommitted。此时会出现脏读，不可重复读，幻读
set session transaction isolation level read uncommitted */
```
![](https://img.tnblog.net/arcimg/aojiancc2/06debde2ea4f4883b5949606b7fd8af5.png)

### 解决脏读，演示不可重复读

设置事务的隔离级别为read committed。 此时不会出现脏读，但是会出现不可重复读与幻读。
```
/* 设置事务的隔离级别为read commited。这种情况效率次之，不会出现脏读，但是会出现不可重复读与幻读 */
set session transaction isolation level read committed 
```

![](https://img.tnblog.net/arcimg/aojiancc2/9a71f1548de34755b51c45f5e005c21a.png)

### 解决不可重复读
设置事务隔离级别为REPEATABLE-READ 。 此时事务不会出现脏读，也不会出现不可重复读，但是会出现幻读。
```
/* 设置事务隔离级别为REPEATABLE-READ 。 此时事务不会出现脏读，也不会出现不可重复读，但是会出现幻读 */
set session transaction isolation level REPEATABLE READ 
```

![](https://img.tnblog.net/arcimg/aojiancc2/4888e46ea8ee421d95ad1f8fca54e1e9.png)

### 演示幻读
![](https://img.tnblog.net/arcimg/aojiancc2/00fe2998cfdb4be9802817e64e9b42e3.png)

### 解决幻读
设置事务隔离级别为SERIALIZABLE 。 此时事务不会出现脏读,不可重复读，幻读
```
/* 设置事务隔离级别为SERIALIZABLE 。 此时事务不会出现脏读,不可重复读，幻读*/
set session transaction isolation level SERIALIZABLE
```
![](https://img.tnblog.net/arcimg/aojiancc2/711bcc15b91d4e75bea6af88ec0b13d0.png)

欢迎加群讨论技术，1群：677373950(满了，加不了)，2群：656732739

评价

MySQL事务详解，事务并发冲突，事务隔离级别

{{titleitem}} {{titleitem}}

{{titleitem}} {{titleitem}}