MySQL数据库事务处理

桃扇骨 2022-09-29 04:29 228阅读 0赞

> 事务处理用于有效记录某机构感兴趣的业务活动（称为事务）的数据处理（例如销售、供货的定购或货币传输）。通常，联机事务处理 (OLTP) 系统执行大量的相对较小的事务。——百度百科

事务处理是将多个操作或者命令一起执行，所有命令全部成功执行才意味着该事务的成功，任何一个命令失败都意味着该事务的失败。

以银行转账为例（**100块都不给**），  
A要给B 转账100元，  
A转账的指令已经成功发出，而B 由于未知的原因接收失败，  
如果两个命令单独执行，那么A账户少了100块，但是B又没收到100块，显而易见是不合理的；  
如果将A向B 转账100元当成一个事务处理，那么由于B 接收的失败，整个转账事务都将失败，A不会少100，B更不会增加100，这个时候“100块都不给”才是合理的情况。  
![事务处理][SouthEast]

**因此，事务处理是不是一荣俱荣，而是一毁全毁。**

接下来介绍MySQL数据库中如何进行事务处理以及锁定。

## **0 数据库事务处理相关命令** ##

<table> 
 <thead> 
  <tr> 
   <th>进行的操作</th> 
   <th>命令</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>查看存储引擎</td> 
   <td><code>SHOW CREATE TABLE 表名；</code></td> 
  </tr> 
  <tr> 
   <td>更改引擎</td> 
   <td><code>ALTER TABLE 表名 ENGINE=新引擎名；</code></td> 
  </tr> 
  <tr> 
   <td>回滚</td> 
   <td><code>ROLLBACK;</code></td> 
  </tr> 
  <tr> 
   <td>声明事务开始</td> 
   <td><code>BEIGIN;</code></td> 
  </tr> 
  <tr> 
   <td>事务提交</td> 
   <td><code>COMMIT;</code></td> 
  </tr> 
  <tr> 
   <td>查询自动提交功能状态</td> 
   <td><code>SELECT @@AUTOCOMMIT;</code></td> 
  </tr> 
  <tr> 
   <td>设置自动提交功能</td> 
   <td><code>SET AUTOCOMMIT=0或1;</code></td> 
  </tr> 
  <tr> 
   <td>设置分离水平</td> 
   <td><code>SET SESSION TRANSACTION ISOLATION LEVEL 分离水平;</code></td> 
  </tr> 
 </tbody> 
</table>

## **1 存储引擎** ##

MySQL数据库的存储引擎是可以选择改变和替换的（可替换存储引擎构架，Pluggable Storage Engine Architecture）。MySQL主要有**8种**存储引擎：

<table> 
 <thead> 
  <tr> 
   <th>存储引擎</th> 
   <th>特征</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MyISAM</td> 
   <td>高速引擎，不支持事务处理</td> 
  </tr> 
  <tr> 
   <td>InnoDB</td> 
   <td>支持行锁定以及事务处理，速度比MyISAM稍慢</td> 
  </tr> 
  <tr> 
   <td>ISAM</td> 
   <td>MyISAM的前身</td> 
  </tr> 
  <tr> 
   <td>MERGE</td> 
   <td>将多个MyISAM类型的表作为一个表来处理的引擎</td> 
  </tr> 
  <tr> 
   <td>MEMORY,HEAP</td> 
   <td>只在内存上保存数据</td> 
  </tr> 
  <tr> 
   <td>Falcon</td> 
   <td>一种新的存储引擎，支持事务处理</td> 
  </tr> 
  <tr> 
   <td>ARCHIVE</td> 
   <td>将数据压缩后保存（只能支持INSERT/SELECT操作</td> 
  </tr> 
  <tr> 
   <td>CSV</td> 
   <td>以CSV形式保存数据（应用于跨平台数据交换）</td> 
  </tr> 
 </tbody> 
</table>

**MySQL的存储引擎种类和特征**

**（1）查看存储引擎 `SHOW CREATE TABLE 表名;`**  
查看某表使用的存储引擎，语法代码如下：

`SHOW CREATE TABLE 表名;`

如，要查看表customer的存储引擎，可以输入代码：

`SHOW CREATE TABLE customer;`

![查看存储引擎][SouthEast 1]  
如图表所示`ENGINE=`后面显示的就是存储引擎。

**（2）更改存储引擎`ALTER TABLE 表名 ENGINE=新引擎名；`**

若要更改存储引擎，可以使用代码：

>ALTER TABLE 表名 ENGINE=新引擎名；

eg:将表customer的存储引擎修改为MyISAM，输入指令：

ALTER TABLE customer ENGINE=MyISAM；

![更改存储引擎][SouthEast 2]

## **2 事务处理** ##

之前讲到，事务处理是一毁全毁，因此事务中任意一个任务或指令失败，整个事务都将失败。那是怎么实现的呢？方法是时间中多个任务全部成功，则任务成功结束，并且会进行提交（COMMIT），如果任何一件任务失败，则强制回滚（ROLLBACK）到初始状态。

> 事务处理涉及到三个最重要的命令：BEGIN,ROLLBACK,COMMIT，分别表示声明事务开始，回滚和确认提交。

**（1）回滚演示（ROLLBACK）**  
首先将表格customer 的存储引擎设置为InnoDB，  
确认表格数据；`SELECT * FROM customer;`  
事务开始；`BEGIN;`  
删除表格数据；`DELETE FROM customer;`  
再次查看表格数据；`SELECT * FROM customer;`  
回滚到初始状态；`ROLLBACK;`  
再次查看表格数据；`SELECT * FROM customer;`

![回滚][SouthEast 3]  
可以看到，当执行`ROLLBACK;`之后，删除的记录又恢复到了`BEGIN`之前的状态，如果将ROLLBACK 换成COMMIT，那么事务将会提交，删除的记录就不能恢复了。

**（2）自动提交**

> 当搜索引擎为MyISAM时，因为不支持事务处理，因此命令一旦执行，就一定会提交，这种默认的提交方式被称为**自动提交**。

而当搜索引擎设置为InnoDB时，可以设置自动提交功能是否开启，当自动提交功能为ON时，命令执行就会提交（COMMIT）,而自动提交设置为OFF 时，必须执行COMMIT才提交，可以使用ROLLBACK进行回滚。

查询当前自动提交功能状态：

>SELECT @@AUTOCOMMIT;

设置自动提交功能：

>SET AUTOCOMMIT=0或1;

如图，将自动提交设置为OFF，插入一条记录，然后使用回滚ROLLBACK，再次查看记录，会发现不见了。  
![自动提交][SouthEast 4]

**（3）部分回滚 SAVEPOINT**

直接ROLLBACK会回滚到BEGIN开始之前的地方，而通过SAVEPOINT可以保存一个点，通过ROLLBACK TO SAVEPOINT就可以回滚到保存点了，也就实现了“想去哪就去了哪”。  
![部分回滚][SouthEast 5]

部分回滚主要有两个步骤：  
①保存点

>SAVEPOINT 保存点名;

②回滚到保存点

>ROLLBACK TO SAVEPOINT 保存点名;

**eg:**  
![mysql部分回滚][mysql]

**（4）不能事务处理的命令（直接提交）**  
大部分命令都可以通过事务处理（BEGIN -ROLLBACK- COMMIT）进行操作，但是：

*  DROP DATABASE;
 *  DROP TABLE;
 *  DROP;
 *  ALTER TABLE

不能通过事务处理，会直接COMMIT;

## 3 锁定与事务处理 ##

前面讲到的ROLLBACK 等操作指令都是基于一个用户进行的。但是事务类型往往不只一个用户，多个用户同时操作，如被人广泛诟病的“12306”火车票购票系统，全国各地的售票窗口以及互联网购票注册账户，成千上万的用户同时使用，因此事务处理必须能够处理多个用户同时操作的情况，这就需要锁定。

举个例子，如果某班火车只剩最后一张票，A和B 同时登陆网站购票，得到的反馈是还剩一张，于是A，B 都赶紧下单，处理这种冲突事件，就需要对该事务进行**锁定（LOCK）**，接触锁定被称为**解锁（Unlock）**。

![锁定][SouthEast 6]

**3.1 锁定**

（1）锁定的分类

> 锁定分为共享锁定（Shared Lock）和排他锁定（Exclusive Lock）：
> 
>  *  共享锁定是将对象数据变为**只读**形式，不能进行更新，所以也成为读取锁定；
>  *  排他锁定是当执行INSERT/UPDATE/DELETE的时候，其它事务**不能读取**该数据，因此也成为写入锁定。

（2）锁定的粒度

> 锁定对象的大小是锁定的粒度，有三种粒度：
> 
>  *  记录
>  *  表
>  *  数据库

**3.2 事务处理的分离水平**

需要使用锁定来有效解决事务冲突的情况，但是锁定也会使性能下降（因为别人无法访问），因此频繁锁定不一定合理，数据库中，使用**分离水平**来表示事务处理之间的影响程度。

事务处理的分离水平对应的数据整合情况：

<table> 
 <thead> 
  <tr> 
   <th>分离水平</th> 
   <th>非提交读取</th> 
   <th>不可重复读取</th> 
   <th>幻象读取</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>READ UNCOMMITED</td> 
   <td>√</td> 
   <td>√</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td>READ COMMITED</td> 
   <td>×</td> 
   <td>√</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td>REPEATABLE READ</td> 
   <td>×</td> 
   <td>×</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td>SERIALIZABLE</td> 
   <td>×</td> 
   <td>×</td> 
   <td>×</td> 
  </tr> 
 </tbody> 
</table>

设置分离水平可以使用命令：

>SET SESSION TRANSACTION ISOLATION LEVEL 分离水平;

为了模拟多个用户对数据库进行访问和操作，我们打开两个命令窗口接入MySQL。  
**（1）非提交读取**

> 非提交读取指的是别的事务能够读取到还没有提交的更新数据，只发生在分离水平为READ UNCOMMITED的情况下。

因为对事务处理的读取没有任何限制，所以一般不推荐使用。

eg：  
![非提交读取][SouthEast 7]

两个窗口的执行顺序如图红色序号所示:  
①首先再A窗口对Id为g001的记录的nam进行修改;  
②然后B进行访问，发现已经能够读取新的nam;  
③A执行ROLLBACK,回滚到初始状态（nam恢复原来的记录）；  
④B再次查询，又得到OLD记录（旧记录）；  
⑤A再次UPDATE，并COMMIT;  
⑥B再次SELECT,得到新纪录（new）。  
可以看到，当A还没提交，B就可以看到更新的数据，这个时候很可能出现问题，比如A后来执行ROLLBACK，B看到的数据实际上是错误的数据。

**（2）不可重复读取**

> 不可重复读取是指在某事务处理过程中对数据进行读取，由于该事务**更新**操作导致多次读取数据时发生了改变。

不可重复读取发生在READ COMMITED 一下的分离水平。

eg：  
![不可重复读取][SouthEast 8]  
命令顺序依然如图红色数字所示：  
①A更新id为g001的nam；  
②B查询，结果是OLD数据；  
③A提交更新；  
④B再次查询，得到更新后的NEW数据。  
*B先后两次查询，结果不一致。*

**（3）幻象读取**

> 幻象读取指的是，在某事物处理数据过程中对数据多次读取，由于该事务的**插入/删除**操作而导致在多次读取过程中读取到不存在或者消失的数据。

下图是幻象读取发生的例子：

![幻象读取发生][SouthEast 9]  
①A事务开始；  
②B查询；  
③A插入一条记录并提交；  
④B再次查询；  
*可以看到B连续执行两次的查询，前后结果不一致。*

当设置分离水平为**SERIALIZABLE**时，可以消除幻象读取。

![消除幻象读取][SouthEast 10]  
①A事务开始；  
②B查询；  
③A插入一条数据；***（此时A会一直等待，直到B提交）***  
④B提交；  
⑤A提交；  
⑥B再次查询；

特别需要注意的是，由于B正在读取数据，当A 执行插入命令时，无法立马得到结果，而是一直等待，直到B提交。当B提交时，A的插入命令自动完成。

**3.3 死锁 Dead Lock**（狭路相逢勇者胜）

> 死锁指的是两个事务互相对待对方释放锁定，则永远也不可能接触锁定的状态。

如A，B两个用户都对表customer中记录’g001’和’g002’实施了排他锁定，由于两个事务互相等待对方释放锁定，所以形成了死锁。  
![死锁][SouthEast 11]  
A和B执行命令顺序红色字体所示：  
①A事务开始，并对’g001’进行了更新；  
②B事务开始，并对’g002’进行了更新；  
③A对’g002’进行更新；***（A等待B提交释放锁定）***  
④B对’g001’进行更新；***（B等待A提交释放锁定，A、B互相等待陷入死锁）***

> 此时,MySQL数据库让B强制解除锁定，A继续执行；

⑤A提交；  
⑥B回滚并查询，得到A事务更新的数据；

[SouthEast]: /images/20220708/3145eead7d414fa191be992d8881b6d4.png
[SouthEast 1]: /images/20220708/842e9898bda24ce4817eae042181d388.png
[SouthEast 2]: /images/20220708/2e5bb953a2594900bd407a4ab64abcd9.png
[SouthEast 3]: /images/20220708/fac4807ce4c9403cb2894a40d4484df9.png
[SouthEast 4]: /images/20220708/74a7337dbfaf470b9bf9f55c73e1eee6.png
[SouthEast 5]: /images/20220708/78052b0fcd09427c9ac3009bf07cb97c.png
[mysql]: /images/20220708/e649aeb62e414ec8ad242dd953da177a.png
[SouthEast 6]: /images/20220708/15b078c32ded4ad9beef29a8ad96fd79.png
[SouthEast 7]: /images/20220708/65122823ac694904977f5daac6958584.png
[SouthEast 8]: /images/20220708/3aceb796a5bc4c26b39d7e86f9a135f8.png
[SouthEast 9]: /images/20220708/c2e2b989994046199ab31bcc38f5fee2.png
[SouthEast 10]: /images/20220708/7ed43fa41bff42e79f7b24c25e505ee1.png
[SouthEast 11]: /images/20220708/8dc5142663ca44cab721ef256e95c6c9.png