MySQL事务及隔离级别

港控/mmm° 2023-02-17 11:10 9阅读 0赞

# 1、事务及其特性 #

**数据库事务**（简称:事务）是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。事务的使用是数据库管理系统区别文件系统的重要特征之一。

事务拥有**四个重要的特性**：`原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）`，人们习惯称之为 ACID 特性。下面我逐一对其进行解释。

*  原子性（Atomicity）  
    事务开始后所有操作，**要么全部做完，要么全部不做**，不可能停滞在中间环节。**实现事务的原子性，要支持回滚操作**，事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。例如，如果一个事务需要新增 100 条记录，但是在新增了 10 条记录之后就失败了，那么数据库将回滚对这 10 条新增的记录。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
 *  一致性（Consistency）  
    指事务将数据库从一种状态转变为另一种一致的的状态。事务开始前和结束后，数据库的完整性约束没有被破坏。例如工号带有唯一属性，如果经过一个修改工号的事务后，工号变的非唯一了，则表明一致性遭到了破坏。对一致性的理解请参看[如何理解事务的一致性][Link 1].
 *  隔离性（Isolation）  
    要求每个读写事务的对象对其他事务的操作对象能互相分离，即该事务提交前对其他事务不可见。 也可以理解为**多个事务并发访问**时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。这指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。例如一个用户在更新自己的个人信息的同时，是不能看到系统管理员也在更新该用户的个人信息（此时更新事务还未提交）。事务的**隔离级别**有4级。  
    **注**：MySQL 通过锁机制来保证事务的隔离性。
 *  持久性（Durability）  
    事务一旦提交，则其结果就是永久性的。即使发生宕机的故障，数据库也能将数据恢复，也就是说事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。这只是从事务本身的角度来保证，排除 RDBMS（关系型数据库管理系统，例如 Oracle、MySQL 等）本身发生的故障。

--------------------

我们以Msql数据库的操作为例，再进一步解释一下**数据库事务**：  
首先我们用以下命令查看该Mysql会话的事务隔离级别，关于事务隔离级别及其作用，我们在后面的章节中会进行详细介绍，这里只要简单知道数据库可以设置不同的事务隔离级别，不同的隔离级别会对事务的操作产生不同的效果即可。使用以下命令可以查询当前Mysql会话的事务隔离级别，可以看到，**Mysql默认的事务隔离级**别是`REPEATABLE-READ`。

mysql> select @@tx_isolation;
    +-----------------+
    | @@tx_isolation  |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+

为了用实例来解释事务，我们创建了如下的bank数据表，并插入一条数据，

mysql> describe bank;
    +---------+---------------+------+-----+---------+----------------+
    | Field   | Type          | Null | Key | Default | Extra          |
    +---------+---------------+------+-----+---------+----------------+
    | id      | int(11)       | NO   | PRI | NULL    | auto_increment |
    | name    | varchar(40)   | NO   |     | NULL    |                |
    | balance | decimal(10,2) | YES  |     | NULL    |                |
    +---------+---------------+------+-----+---------+----------------+
    mysql> select * from bank;
    +----+------+---------+
    | id | name | balance |
    +----+------+---------+
    |  3 | fufu | 2000.00 |
    +----+------+---------+

使用`start transaction`命令开启数据库事务，

mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)

更新id为3的行的balance值为3000.00，

mysql> update bank set balance = 3000 where id = 3;
    Query OK, 1 row affected (0.09 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from bank;
    +----+------+---------+
    | id | name | balance |
    +----+------+---------+
    |  3 | fufu | 3000.00 |
    +----+------+---------+
    1 row in set (0.00 sec)

此时我们可以看到，select语句查询到的id为3的行的balance值已经修改为3000.00，接下来我们再尝试插入一条新数据，

mysql> insert into bank (name, balance) values ('melo', 1000);
    Query OK, 1 row affected (0.06 sec)
    
    mysql> select * from bank;
    +----+------+---------+
    | id | name | balance |
    +----+------+---------+
    |  3 | fufu | 3000.00 |
    |  4 | melo | 1000.00 |
    +----+------+---------+
    2 rows in set (0.00 sec)

由于以上的`update`和`insert`操作都是在`start transaction`命令开启事务之后，所以直到事务结束，这些操作都属于同一事务，假设我们在`insert`操作时产生了错误，可以根据事务的定义得知，这些属于同一事务的所有操作要么都执行要么都不执行，我们可以验证一下，使用`rollback`命令，模拟事务失败回滚，

mysql> rollback;
    Query OK, 0 rows affected (0.01 sec)

此时我们在查询数据库中的所有数据，发现数据恢复到了update命令执行前的状态，id为3的行的balance值等于2000没有变化。

mysql> select * from bank;
    +----+------+---------+
    | id | name | balance |
    +----+------+---------+
    |  3 | fufu | 2000.00 |
    +----+------+---------+
    1 row in set (0.00 sec)

到此，我们阐述了数据库事务的定义并用简单的Mysql操作说明了事务的操作方式，我们可以总结出数据库事务的生命周期如下：  
![在这里插入图片描述][20200613161248629.png]  
可以看出事务的边界包括：

*  事务的开始边界
 *  事务的正常结束边界（COMMIT），提交事务，永久保存被事务更新后的数据库状态。
 *  事务的异常结束边界（ROLLBACK）：撤销事务，使数据库退回到执行事务前的初始状态。

现在我们回过头来思考一下上述示例，示例中的所有操作都是在一个Mysql会话中进行的，也就是没有其他用户在同时连接数据库进行操作，在这种没有**并发会话**的使用场景中，无论事务是正常结束还是异常结束，对于该单独用户读取数据不会造成任何影响，因为他的所有操作都是串行的。但是在实际应用场景中，数据库每时每刻都服务于很多会话，假设用户A的事务A开始后更新了数据库数据，此时用户B开始读取该数据，用户B将会读取到了新的值。但是如果紧接着事务A在下一条SQL语句操作时产生了错误，将事务A回滚了，那么用户B读取到的数据就是错误的无效数据了。这只是数据库事务在并发环境下会产生的一个简单的问题，所以接下来详细阐述并发事务会产生的问题。

--------------------

# 2、并发事务会导致的问题 #

这节我们主要说明并发事务时可能会出现的问题，我们用时间点和事务操作表格的方式来举例。

### 2.1 丢失更新 ###

#### 2.1.1 第一类丢失更新 ####

定义：A事务撤销时，把已经提交的B事务的更新数据覆盖了。

<table> 
 <thead> 
  <tr> 
   <th>时间点</th> 
   <th>事务A</th> 
   <th>事务B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>T1</td> 
   <td><strong>开始事务</strong></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T2</td> 
   <td></td> 
   <td><strong>开始事务</strong></td> 
  </tr> 
  <tr> 
   <td>T3</td> 
   <td>查询账户余额为1000元</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T4</td> 
   <td></td> 
   <td>查询账户余额为1000元</td> 
  </tr> 
  <tr> 
   <td>T5</td> 
   <td></td> 
   <td>存入100元把余额改为1100元</td> 
  </tr> 
  <tr> 
   <td>T6</td> 
   <td></td> 
   <td><strong>提交事务</strong></td> 
  </tr> 
  <tr> 
   <td>T7</td> 
   <td>取出100元把余额改为900元</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T8</td> 
   <td><strong>撤销事务</strong></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T9</td> 
   <td>余额恢复为1000元（丢失更新）</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

以上的示例演示了第一类丢失更新问题，事务B虽然成功了，但是它所做的更新没有被永久存储，这种并发问题是由于完全没有隔离事务造成的。当两个事务更新相同的数据时，如果一个事务被提交，另一个事务却撤销，那么会连同第一个事务所做的更新也被撤销了。（这是绝对避免出现的事情） 事务A的开始时间和结束时间包含事务B的开始和结束时间,事务A回滚事务的同时,把B的已经提交的事务也回滚的,这是避免的,这就是第一类丢失更新.

#### 2.1.2 第二类丢失更新 ####

定义：A事务提交时，把已经提交的B事务的更新数据覆盖了。

<table> 
 <thead> 
  <tr> 
   <th>时间点</th> 
   <th>事务A</th> 
   <th>事务B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>T1</td> 
   <td></td> 
   <td><strong>开始事务</strong></td> 
  </tr> 
  <tr> 
   <td>T2</td> 
   <td><strong>开始事务</strong></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T3</td> 
   <td></td> 
   <td>查询账户余额为1000元</td> 
  </tr> 
  <tr> 
   <td>T4</td> 
   <td>查询账户余额为1000元</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T5</td> 
   <td></td> 
   <td>取出100元把余额改为900元</td> 
  </tr> 
  <tr> 
   <td>T6</td> 
   <td></td> 
   <td><strong>提交事务</strong></td> 
  </tr> 
  <tr> 
   <td>T7</td> 
   <td>存入100元把余额改为1100</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T8</td> 
   <td><strong>提交事务</strong></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T9</td> 
   <td>余额恢复为1100元（丢失更新）</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

第二类丢失更新和第一类的区别实际上是对数据的影响是由A事务的撤销还是提交造成的，它和不可重复读(下面介绍)本质上是同一类并发问题，通常把它看做是不可重复读的一个特例。两个或多个事务查询同一数据。然后都基于自己的查询结果更新数据，这时会造成最后一个提交的更新事务，将覆盖其它已经提交的更新事务。

### 2.2 脏读 ###

定义：读到未提交更新的数据

<table> 
 <thead> 
  <tr> 
   <th>时间点</th> 
   <th>事务A</th> 
   <th>事务B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>T1</td> 
   <td></td> 
   <td><strong>开始事务</strong></td> 
  </tr> 
  <tr> 
   <td>T2</td> 
   <td><strong>开始事务</strong></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T3</td> 
   <td></td> 
   <td>查询账户余额为1000元</td> 
  </tr> 
  <tr> 
   <td>T4</td> 
   <td></td> 
   <td>取出500元把余额改为500元</td> 
  </tr> 
  <tr> 
   <td>T5</td> 
   <td>查询账户余额为500元（脏读）</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T6</td> 
   <td></td> 
   <td><strong>撤销事务</strong>，余额恢复为1000元</td> 
  </tr> 
  <tr> 
   <td>T7</td> 
   <td>存入100元把余额改为600元</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T8</td> 
   <td><strong>提交事务</strong></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

A事务查询到了B事务未提交的更新数据，A事务依据这个查询结果继续执行相关操作。但是接着B事务撤销了所做的更新，这会导致A事务操作的是脏数据，以上的示例中T5时刻产生了脏读，最终导致A事务提交时账户余额的不正确，可能有人会有疑问，B事务还没有提交或撤销，T5时刻A事务为什么能读到已经改变的数据，这里要说的是，数据表中的数据是实时改变的，事务只是控制数据的最终状态，也就是说如果没有正确的隔离级别，在更新操作语句结束后，即使事务未完成，其他事务就已经可以读取到改变的数据值了。

**现在为止:所有的数据库都避免脏读操**，可以用两个Mysql会话试验一下以上的操作，在默认的隔离级别下（`REPEATABLE-READ`），A事务在T5时刻读取到的余额为1000元，不会是500元。

### 2.3 不可重复读 ###

定义：读到已经提交更新的数据，但一个事务范围内两个相同的查询却返回了不同数据。

<table> 
 <thead> 
  <tr> 
   <th>时间点</th> 
   <th>事务A</th> 
   <th>事务B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>T1</td> 
   <td></td> 
   <td><strong>开始事务</strong></td> 
  </tr> 
  <tr> 
   <td>T2</td> 
   <td><strong>开始事务</strong></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T3</td> 
   <td></td> 
   <td>查询账户余额为1000元</td> 
  </tr> 
  <tr> 
   <td>T4</td> 
   <td>查询账户余额为1000元</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T5</td> 
   <td></td> 
   <td>取出100元把余额改为900元</td> 
  </tr> 
  <tr> 
   <td>T6</td> 
   <td></td> 
   <td><strong>提交事务</strong></td> 
  </tr> 
  <tr> 
   <td>T7</td> 
   <td>查询账户余额为900元（与T4读取的一不一致，不可重复读）</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

### 2.4 幻读 ###

定义：读到已提交插入数据，幻读与不可重复读类似，幻读是查询到了另一个事务已提交的新插入数据，而不可重复读是查询到了另一个事务已提交的更新数据。

<table> 
 <thead> 
  <tr> 
   <th>时间点</th> 
   <th>事务A</th> 
   <th>事务B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>T1</td> 
   <td></td> 
   <td><strong>开始事务</strong></td> 
  </tr> 
  <tr> 
   <td>T2</td> 
   <td><strong>开始事务</strong></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T3</td> 
   <td>统计用户Z总存款数为1000元</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T4</td> 
   <td></td> 
   <td>新增Z的一个存款账号，存款100元</td> 
  </tr> 
  <tr> 
   <td>T5</td> 
   <td></td> 
   <td><strong>提交事务</strong></td> 
  </tr> 
  <tr> 
   <td>T6</td> 
   <td>再次统计用户Z总存款数为1100元（与T3读取的不一致，幻读）</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

A事务第一次查询时，没有问题，第二次查询时查到了B事务已提交的新插入数据，这导致两次查询结果不同。

**不可重复读和幻读的区别**：简单来说，不可重复读是由于数据修改引起的，幻读是由数据插入或者删除引起的。

不可重复读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。

一种更易理解的说法是：在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。

所谓幻读，是指事务A读取与搜索条件相匹配的若干行。事务B以插入或删除行等方式来修改事务A的结果集，然后再提交。

幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样.一般解决幻读的方法是增加范围锁RangeS，锁定检锁范围为只读，这样就避免了幻读。

--------------------

# 3、事务的隔离级别 #

实战请参看：[MySQL事务的隔离级别实战][MySQL]

以上就是数据库并发事务导致的五大问题，总结来说其中两类是更新问题，三类是读问题，数据库是如何避免这种并发事务问题的呢？答案就是通过不同的事务隔离级别，在不同的隔离级别下，并发事务读取数据的结果是不一样的，比如在脏读小节里介绍的，如果是在`REPEATABLE-READ`隔离级别下，A事务在T5时刻读取是读取不到B事务未提交的数据的。我们需要根据业务的要求，设置不同的隔离级别，在效率和数据安全性中找到平衡点。

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

### 3.1 SERIALIZABLE（串行化、序列化 ###

当数据库系统使用SERIALIZABLE隔离级别时，一个事务在执行过程中完全看不到其他事务对数据库所做的更新。当两个事务同时操作数据库中相同数据时，如果第一个事务已经在访问该数据，第二个事务只能停下来等待，必须等到第一个事务结束后才能恢复运行。因此这两个事务实际上是串行化方式运行。

### 3.2 REPEATABLE READ（可重复读） ###

当数据库系统使用REPEATABLE READ隔离级别时，一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，但是不能看到其他事务对已有记录的更新。

### 3.3 READ COMMITTED（读提交） ###

当数据库系统使用READ COMMITTED隔离级别时，一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，而且还能看到其他事务已经提交的对已有记录的更新。

### 3.4 READ UNCOMMITTED（读未提交） ###

当数据库系统使用READ UNCOMMITTED隔离级别时，一个事务在执行过程中可以看到其他事务没有提交的新插入的记录，而且还能看到其他事务没有提交的对已有记录的更新。  
以上的四种隔离级别按从高到底排序，你可能会说，选择SERIALIZABLE，因为它最安全！没错，它是最安全，但它也是最慢的！四种隔离级别的安全性与性能成反比！最安全的性能最差，最不安全的性能最好！

--------------------

# 4、隔离级别与并发问题 #

通过以上的四种隔离级别的定义，我们已经可以分析出，每个隔离级别可以避免哪些并发问题了，总结一下如下表：

<table> 
 <thead> 
  <tr> 
   <th>隔离级别</th> 
   <th>第一类丢失更新</th> 
   <th>第二类丢失更新</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SERIALIZABLE （串行化）</td> 
   <td>不允许出现</td> 
   <td>不允许出现</td> 
   <td>不允许出现</td> 
   <td>不允许出现</td> 
   <td>不允许出现</td> 
  </tr> 
  <tr> 
   <td>REPEATABLE READ（可重复读）</td> 
   <td>不允许出现</td> 
   <td>不允许出现</td> 
   <td>不允许出现</td> 
   <td>不允许出现</td> 
   <td>可以出现</td> 
  </tr> 
  <tr> 
   <td>READ COMMITTED （读已提交</td> 
   <td>不允许出现</td> 
   <td>可以出现</td> 
   <td>不允许出现</td> 
   <td>可以出现</td> 
   <td>可以出现</td> 
  </tr> 
  <tr> 
   <td>READ UNCOMMITTED（读未提交）</td> 
   <td>不允许出现</td> 
   <td>可以出现</td> 
   <td>可以出现</td> 
   <td>可以出现</td> 
   <td>可以出现</td> 
  </tr> 
 </tbody> 
</table>

我们通过隔离级别的定义很容易自己分析出这张表，比如可重复读隔离级别的定义是一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，但是不能看到其他事务对已有记录的更新。所以，在这种隔离级别下，在脏读示例的T5时刻和不可重复读的T7时刻，事务A都是无论事务B是否提交，事务A都是无法读取到事务B对已有记录的更新的，所以不会产生脏读和不可重复读，而又由于这种隔离级别下可以看到其他事务已经提交的新插入记录，自然是无法避免幻读的产生。另外，值得注意的是所有隔离级别都可以避免第一类丢失更新的问题。  
大多数关系数据库默认使用Read committed的隔离级别，**MySQL InnoDB**默认使用`Read repeatable`的隔离级别，这和Mysql replication 机制使用Statement日志格式有关。各数据库隔离级别的实现也是有差别的，例如Oracle支持Read committed 和Serializable两种隔离级别，另外可以通过使用读快照在Read committed级别上禁止不可重复读问题；MySQL默认采用RR隔离级别，SQL标准是要求RR解决不可重复读的问题，但是因为MySQL采用了gap lock，所以实际上MySQL的RR隔离级别也解决了幻读的问题，也就是Mysql InnoDB在Read repeatable级别上使用next-key locking 策略来避免幻读现象的产生。

--------------------

# 参考 #

[https://juejin.im/post/5b90cbf4e51d450e84776d27][https_juejin.im_post_5b90cbf4e51d450e84776d27]

[Link 1]: https://blog.csdn.net/weixin_43999327/article/details/106733550
[20200613161248629.png]: https://img-blog.csdnimg.cn/20200613161248629.png
[MySQL]: https://blog.csdn.net/weixin_43999327/article/details/106734624
[https_juejin.im_post_5b90cbf4e51d450e84776d27]: https://juejin.im/post/5b90cbf4e51d450e84776d27