MySQL 事务隔离测试

╰半橙微兮° 2023-01-07 04:53 125阅读 0赞

# 事务（transcation） #

MySql 中只有使用 InnoDB 引擎的数据库才支持事务，事物处理可以用来完整数据路的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。事务满足 4 个条件：

*  原子性（Atomicity）事务中的 多个 SQL 语句，要么全部执行，要么全部不执行，这些 SQL 语句中只要有一条执行失败，数据库会被回滚到事务开始前的状态，也就是说这个事务没被执行过。
 *  一致性（Consistency）保证事务只能把数据库从一个正确的状态“变成”另一个正确的状态，当事务执行 的 SQL 语句不满足数据库定义的约束条件时，事务会被回滚。
 *  隔离性（Isolation）数据库允许多个并发事务同时对数据进行读写，隔离可以防止多个事务并发执行时由于交叉执行而导致的数据不一致。事务隔离有四个级别：读未提交、读提交、可重复读和串行化。
 *  持久性（Serializable）事务处理结束后，对数据的修改是永久的，即便系统故障也不会丢失。

# 事务的提交 #

在 MySQL 命令行的默认设置下，事务都是自动提交的，即 SQL 语句执行完后会马上执行 commit 操作。因此要显示地开启一个事务必须使用命令 `begin` 或 `start transcation` ，或者 执行命令 `set autocommit=0` ,用来禁止使用当前会话的自动提交。

# 事务并发出现的异常现象 #

当有多个事务同时读写同一张表时，会出现一些读取异常现象：

**脏读**：B 事务访问数据库并修改了表中的一行记录 record1，但还没有提交到数据库。此时恰好 A 事务也访问数据库读取了 B 修改的后的 record1 ，那么 A 读到的 record1 就是“脏数据”。用这样的数据执行后面的逻辑，大概率也会出错。

**不可重复读**：事务内部多次读同一个数据时，读出来的数据值不一样。比如：表 `table-1` 中的 `record-1` 中的字段 `f-1` 原本的值是20，A 事务内的 SQL 语句分别在 `t1`、`t3`时刻读取 `f-1`。按理说两次读取的结果都应该是 20 。但在 `t2` 时刻，B 事务将也访问数据库而且还把 `f-1` 的值改成了 30，最后还给提交了。这样一来，A 事务在 t1 时刻读取的结果是 20，在 t3 时刻读取的结果是 30，这就是“不可重复读”。

**幻读**：幻读和不可重复读很相似，A 事务根据删选条件读取了几条记录，但为提交，此时 B 事务在表里插入了几条满足 A 事务删选条件的记录，并提交。A 事务中第二次再根据筛选条件查找时就会发现多出了几条记录，出现了不一致的现象，这就是“幻读”。

不可重复读 和 幻读都是当前事务读取到了其他事务中已经提交的数据，但他们的针对点有不同：

*  不可重复读：update 语法。
 *  幻读：insert 和 delete 语法。

# 事务的隔离级别 #

事务并发访问数据库会出现脏读、不可重复读、幻读这些异常现象，隔离级别就是为了组织这些异常现象的出现。**事务并发提高了数据库的被访问效率，隔离级别努力保证着并发过程中读写数据的正确性**。

**读未提交(read-uncommit)**：最低的隔离级别，允许事务读取其他事务尚未提交的数据变更，可能会导致脏读，不可重复读，或者幻读。

**读提交(read-commit)**：允许事务读取其他事务已经提交的数据变更。不会出现脏读，但可能会出现 不可重复读 和 幻读。

**可重复读(repeatable-read)**：当前事务在执行时，不允许看到其他事务已经提交的数据修改。可以防止脏读，不可重复读，至于幻读，查到的资料说是不能防止，但实际测试的结果是：可重复读也可以防止幻读。这是因为测试时，MySQL 使用的 InnoDB 引擎，[因为多版本并发控制的原因，可重复度是能够防止幻读的][Link 1]。

**可串行化(serializable)** ：这是最高的隔离级别，它要求在选定对象上的读锁和写锁报纸知道事务结束后才能释放，这从根本上阻止了事务并发，所以，它能防止 脏读、不可重复读 和幻读。

# 测试 #

## 1. 查看当前隔离级别 ##

MySQL 默认是隔离级别是 可重复读。

show variables like 'transaction_isolation';

+-----------------------+-----------------+
    | Variable_name         | Value           |
    +-----------------------+-----------------+
    | transaction_isolation | REPEATABLE-READ |
    +-----------------------+-----------------+

或者

select @@tx_isolation;

+-----------------+
    | @@tx_isolation  |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+

## 2. 设置当前隔离级别 ##

设置为读未提交，其余级别【read committed】、【repeatable read】

set session transaction isolation level read uncommitted

`set session ...` 只在 当前 session 中生效。当前 session：MySQL 的当前连接。

set global transaction isolation level read uncommitted;

`set global...` 在所有 session 中生效，但需要注意两点：

1.  当前用户需要具有 super 权限，才能设置成功；
2.  设置完后要重新登录，才能生效。

## 读未提交 ##

原本的记录：

select username, email, phone from users where username = 'ttttt';

Result:

+----------+------------+----------------+
    | username | email      | phone          |
    +----------+------------+----------------+
    | ttttt    | 123@qq.com | 86 13211112222 |
    +----------+------------+----------------+

<table> 
 <thead> 
  <tr> 
   <th align="center">S1</th> 
   <th align="center">S2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">begin;</td> 
   <td align="center">begin;</td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center">update users set email = ‘234@qq.com’ where username=‘ttttt’;</td> 
  </tr> 
  <tr> 
   <td align="center">select username, email, phone from users where username = ‘ttttt’;</td> 
   <td align="center"></td> 
  </tr> 
 </tbody> 
</table>

S1 的查询结果：

+----------+------------+----------------+
    | username | email      | phone          |
    +----------+------------+----------------+
    | ttttt    | 234@qq.com | 86 13211112222 |
    +----------+------------+----------------+

S1 读到了 S2 尚未 commit 的修改。读未提交隔离级别既然不能防止脏读，那就更防不住 不可重复读 和 幻读了。因为，别的事物修改后未提交的数据当前事务都能读到，等别的事务提交后，当前事务就更能读到了。

## 读提交 ##

原本记录：

select username, email, phone from users where username = 'ttttt';

Result:

<table> 
 <thead> 
  <tr> 
   <th align="center">time</th> 
   <th align="center">S1</th> 
   <th align="center">S2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">t0</td> 
   <td align="center">biegin;</td> 
   <td align="center">begin;</td> 
  </tr> 
  <tr> 
   <td align="center">t1</td> 
   <td align="center"></td> 
   <td align="center">update users set email = ‘234@qq.com’ where username=‘ttttt’;</td> 
  </tr> 
  <tr> 
   <td align="center">t2</td> 
   <td align="center">select username, email, phone from users where username = ‘ttttt’;</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">t3</td> 
   <td align="center"></td> 
   <td align="center">commit;</td> 
  </tr> 
  <tr> 
   <td align="center">T4</td> 
   <td align="center">select username, email, phone from users where username = ‘ttttt’;</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center">commit;</td> 
   <td align="center"></td> 
  </tr> 
 </tbody> 
</table>

t2 时刻 S1 查询结果：

t4 时刻 S1 查询结果：

**读提交隔离级别能防止脏读，但不能防止不可重复读。**

## 可重复读 ##

原本记录：

select username, email, phone from users where username = 'ttttt';

result :

<table> 
 <thead> 
  <tr> 
   <th align="center">time</th> 
   <th align="center">S1</th> 
   <th align="center">S2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">t0</td> 
   <td align="center">begin;</td> 
   <td align="center">begin;</td> 
  </tr> 
  <tr> 
   <td align="center">t1</td> 
   <td align="center"></td> 
   <td align="center">update users set email = ‘567@qq.com’ where username=‘ttttt’;</td> 
  </tr> 
  <tr> 
   <td align="center">t2</td> 
   <td align="center">select username, email, phone from users where username = ‘ttttt’;</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">t3</td> 
   <td align="center"></td> 
   <td align="center">insert users (username,email,phone) values(‘uuuuu’,‘234@qq.com’,‘86 18198765432’);</td> 
  </tr> 
  <tr> 
   <td align="center">t4</td> 
   <td align="center"></td> 
   <td align="center">commit;</td> 
  </tr> 
  <tr> 
   <td align="center">t5</td> 
   <td align="center">select username, email,phone from users where email = ‘234@qq.com’;</td> 
   <td align="center"></td> 
  </tr> 
 </tbody> 
</table>

t2 时刻 S1 的查询结果：

**可以看出，可重复读隔离级别能够防止脏读。**

t4 时刻 S1 的查询结果：

S2 先修改了 email 字段，在 插入了一条心数据，最后提交。t4 时刻 S1 用 email 筛选记录，从结果可以看出，S1 读不到 S2 对 email 字段的修改，也读不到 S2 新插入的字段。

**在实际的测试中， 可重复读隔离级别能够防止 不可重复读 和 幻读。**

[Link 1]: https://blog.csdn.net/yy_diego/article/details/112829346