MySQL 的事务和隔离级别

偏执的太偏执、 2024-03-29 15:53 36阅读 0赞

事务是一组原子性的SQL查询，事务内的SQL语句，要么全部执行成功，要么全部执行失败。本节重点介绍事务的ACID和隔离级别。

### 1. ACID ###

提到事务，大家肯定都不陌生，和数据库打交道，我们都会用到事务。银行转账是解释事务的一个经典例子。银行数据库通常会有两张表：支票表和储蓄表。现在要从用户 A 的支票账户转账 100 元人民币到储蓄账户，一般是下面三个步骤：

1.  检查支票账户的余额高于 100 元；
2.  从支票账户余额减去 100 元；
3.  在储蓄账户余额增加 100 元；

相应的 SQL 语句如下：

start transaction
    select balance from checking where customer_name = 'A'
    update checking set balance = balance - 100.00 where customer_name = 'A'
    update savings set balance = balance + 100.00 where customer_name = 'A'
    commit;

这三个步骤需要封装成一个事务，任何一个步骤失败，都必须回滚所有的步骤。简单的说，事务就是保证一组数据库操作，要么全部执行成功，要么全部执行失败。

一个优秀的事务处理机制，需要具备 ACID 特性，即原子性（atomicity）、一致性（consistency）、隔离性（isolation）、持久性（durability）。

*  **原子性（atomicity）**：一个事务被视为一个完整的最小工作单元，事务中的数据库操作，要么全部执行成功，要么全部执行失败回滚，不能只成功执行了其中的一部分数据库操作；
 *  **一致性（consistency）**：数据库总是从一个一致性的状态转换到另一个一致性的状态。在银行转账的例子中，即使执行到第四条 SQL 语句时失败，用户的支票账户也不会损失 100 元人民币，因为执行失败时，事务进行了回滚，所做的修改并没有保存到数据库中；
 *  **隔离性（isolation）**：通常来说，一个事物所做的修改在提交以前，对其他事务是不可见的。在银行转账的例子中，当执行完第三条 SQL 语句时，此时另外一个程序在汇总支票账户，它所查询到的用户A的支票账户，并没有减去 100 元人民币；
 *  **持久性（durability）**：事务提交成功，所做的修改就会永久保存到数据库中，即使系统崩溃，修改的数据也不会丢失。

**原子性（atomicity）**：一个事务被视为一个完整的最小工作单元，事务中的数据库操作，要么全部执行成功，要么全部执行失败回滚，不能只成功执行了其中的一部分数据库操作；

**一致性（consistency）**：数据库总是从一个一致性的状态转换到另一个一致性的状态。在银行转账的例子中，即使执行到第四条 SQL 语句时失败，用户的支票账户也不会损失 100 元人民币，因为执行失败时，事务进行了回滚，所做的修改并没有保存到数据库中；

**隔离性（isolation）**：通常来说，一个事物所做的修改在提交以前，对其他事务是不可见的。在银行转账的例子中，当执行完第三条 SQL 语句时，此时另外一个程序在汇总支票账户，它所查询到的用户A的支票账户，并没有减去 100 元人民币；

**持久性（durability）**：事务提交成功，所做的修改就会永久保存到数据库中，即使系统崩溃，修改的数据也不会丢失。

在 MySQL 中，**事务是在存储引擎层实现的**。MySQL 是**支持多种存储引擎的数据库，但并不是所有的存储引擎都支持事务**，比如 MyISAM 就不支持事务。

事务增加了数据库的安全性，同时也需要数据库做很多额外的工作。相比没有实现 ACID 的数据库，实现了 ACID 的数据库需要更强的 CPU、内存、以及磁盘空间。

### 2. 隔离级别 ###

在 SQL 标准中，包含了四种隔离级别，即未提交读（read uncommitted）、提交读（read committed）、可重复读（repeatable read）、可串行化（serializable）。

*  **未提交读（read uncommitted）**：一个事务还未提交，它所做的变更能被别的事务看到。事务可以读取未提交的数据，被称为脏读（dirty read），这种隔离级别在实际应用中一般很少使用；
 *  **提交读（read committed）**：一个事务提交之后，它所做的变更才能被别的事务看到。大多数数据库的默认隔离级别是提交读（read committed），比如 Oracle；
 *  **可重复读（repeatable read）**：一个事务在执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。在可重复读隔离级别下，未提交变更对其他事务也是不可见的。该级别保证了在同一个事务中，多次读取同样记录的结果是一致的。MySQL 的默认事务隔离级别是可重复读（repeatable read）；
 *  **可串行化（serializable）**：serializable 是最高的隔离级别。对同一行数据，读写都会进行加锁。当出现锁冲突时，后面访问的事务必须等前一个事务完成，才能继续执行。实际应用场景很少用到这种隔离级别，只有在非常需要确保数据一致性，而且可以接受没有并发的情况，才会使用这种隔离级别。

**未提交读（read uncommitted）**：一个事务还未提交，它所做的变更能被别的事务看到。事务可以读取未提交的数据，被称为脏读（dirty read），这种隔离级别在实际应用中一般很少使用；

**提交读（read committed）**：一个事务提交之后，它所做的变更才能被别的事务看到。大多数数据库的默认隔离级别是提交读（read committed），比如 Oracle；

**可重复读（repeatable read）**：一个事务在执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。在可重复读隔离级别下，未提交变更对其他事务也是不可见的。该级别保证了在同一个事务中，多次读取同样记录的结果是一致的。MySQL 的默认事务隔离级别是可重复读（repeatable read）；

**可串行化（serializable）**：serializable 是最高的隔离级别。对同一行数据，读写都会进行加锁。当出现锁冲突时，后面访问的事务必须等前一个事务完成，才能继续执行。实际应用场景很少用到这种隔离级别，只有在非常需要确保数据一致性，而且可以接受没有并发的情况，才会使用这种隔离级别。

下表为 ANSI SQL 隔离级别：

<table> 
 <thead> 
  <tr> 
   <th>隔离级别</th> 
   <th>脏读可能性</th> 
   <th>不可重复度可能性</th> 
   <th>幻读可能性</th> 
   <th>加锁读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>未提交读（read uncommitted）</td> 
   <td>yes</td> 
   <td>yes</td> 
   <td>yes</td> 
   <td>no</td> 
  </tr> 
  <tr> 
   <td>提交读（read committed）</td> 
   <td>no</td> 
   <td>yes</td> 
   <td>yes</td> 
   <td>no</td> 
  </tr> 
  <tr> 
   <td>可重复读（repeatable read）</td> 
   <td>no</td> 
   <td>no</td> 
   <td>yes</td> 
   <td>no</td> 
  </tr> 
  <tr> 
   <td>可串行化（serializable）</td> 
   <td>no</td> 
   <td>no</td> 
   <td>no</td> 
   <td>yes</td> 
  </tr> 
 </tbody> 
</table>

### 3. 小结 ###

本小节主要介绍了事务的 ACID 和隔离级别。

ACID特性：原子性（atomicity）、一致性（consistency）、隔离性（isolation）、持久性（durability）

隔离级别：未提交读（read uncommitted）、提交读（read committed）、可重复读（repeatable read）、可串行化（serializable）