【MySQL】数据库事务处理---MySQL

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

 

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

以银行转账为例（100块都不给）， 
A要给B 转账100元， 
A转账的指令已经成功发出，而B 由于未知的原因接收失败， 
如果两个命令单独执行，那么A账户少了100块，但是B又没收到100块，显而易见是不合理的； 

如果将A向B 转账100元当成一个事务处理，那么由于B 接收的失败，整个转账事务都将失败，A不会少100，B更不会增加100，这个时候“100块都不给”才是合理的情况。

 

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

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

1.数据库事务处理相关命令

 

2.存储引擎

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

MySQL的存储引擎种类和特征

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

 `SHOW CREATE TABLE 表名;`

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

 `SHOW CREATE TABLE customer;`

如图表所示ENGINE=后面显示的就是存储引擎。

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

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

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

• 1

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

ALTER TABLE customer ENGINE=MyISAM；

 

3. 事务处理

 

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

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

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

 

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

（2）自动提交

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

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

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

>SELECT @@AUTOCOMMIT;

• 1

设置自动提交功能：

>SET AUTOCOMMIT=0或1;

• 1

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

 

（3）部分回滚 SAVEPOINT

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

 

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

>SAVEPOINT 保存点名;

• 1

②回滚到保存点

>ROLLBACK TO SAVEPOINT 保存点名;

• 1

eg: 

 

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

• DROP DATABASE;
• DROP TABLE;
• DROP;
• ALTER TABLE

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

4. 锁定与事务处理

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

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

 

3.1 锁定

（1）锁定的分类

锁定分为共享锁定（Shared Lock）和排他锁定（Exclusive Lock）：

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

（2）锁定的粒度

锁定对象的大小是锁定的粒度，有三种粒度：

• 记录
• 表
• 数据库

3.2 事务处理的分离水平

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

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

分离水平	非提交读取	不可重复读取	幻象读取
READ UNCOMMITED	√	√	√
READ COMMITED	×	√	√
REPEATABLE READ	×	×	√
SERIALIZABLE	×	×	×

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

>SET SESSION TRANSACTION ISOLATION LEVEL 分离水平;

• 1

 

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

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

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

eg.

 

两个窗口的执行顺序如图红色序号所示: 
①首先再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： 

 

 

 

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

（3）幻象读取

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

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

 

 

 

①A事务开始； 
②B查询； 
③A插入一条记录并提交； 
④B再次查询； 
可以看到B连续执行两次的查询，前后结果不一致。

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

 

①A事务开始； 
②B查询； 
③A插入一条数据；（此时A会一直等待，直到B提交） 
④B提交； 
⑤A提交； 
⑥B再次查询；

 

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

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

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

如A，B两个用户都对表customer中记录’g001’和’g002’实施了排他锁定，由于两个事务互相等待对方释放锁定，所以形成了死锁。 

 

A和B执行命令顺序红色字体所示： 
①A事务开始，并对’g001’进行了更新； 
②B事务开始，并对’g002’进行了更新； 
③A对’g002’进行更新；（A等待B提交释放锁定） 
④B对’g001’进行更新；（B等待A提交释放锁定，A、B互相等待陷入死锁）

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

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