（十三）事务处理

---

1、概述

  事务是数据库区别于文件系统的重要特征之一，其用于保证数据库的完整性，事务能使批量的SQL语句要么完全执行，要么完全不执行。

  例如最简单的转账示例：假设用户A要给用户B转账100元，用户A的转账指令已经成功发出，而用户B由于未知的原因接收失败，如果两个命令单独执行，那么用户A的账户少了100元，但是用户B又没收到这100元，显然是不合理的；此时将用户A向用户B转账100元的这个过程当成一个事务处理，那么由于用户B接收的失败，整个转账事务都将失败，用户A不会减少100元，用户B更不会增加100元，这才是合理的情况。

  事务的特性有以下几点：

• 原子性（Atomicity）：事务对数据的修改，要么全部执行，要么全部不执行；
• 一致性（Consistency）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；
• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行；
• 持久性（Durability）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

---

2、控制事务处理

  事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定在数据何时应该回退，何时不应该回退。

显式的开启一个事务

  START TRANSACTION;
  或
  BEGIN;

回滚

  回滚会结束用户的事务，并撤销正在进行的所有未提交的修改，分为直接回滚和部分回滚：

• 直接回滚：会回滚到设置BEGIN之前的状态。
  ROLLBACK;

• 部分回滚：通过设置保存点，回滚到保存点设置前的状态。
  设置一个事务保存点及标识符名称：
  SAVEPOINT identifier;
  回滚到设置保存点之前的状态：
  ROLLBACK TO SAVEPOINT identifier;
  从当前事务的一组保存点中删除指定的保存点：
  RELEASE SAVEPOINT identifier;
  使用COMMIT或ROLLBACK结束事务后，所有保存点会被删除。

提交事务

  使已对数据库进行的所有修改成为永久性的，分为自动提交和手动提交：

• 自动提交：MySQL默认是自动提交的，即执行SQL语句后就会马上执行COMMIT操作。
• 手动提交：如果正在使用的是一个事务安全型的存储引擎，如“InnoDB”，那么当显式的开启事务后，MySQL的自动提交会被禁用，直到使用COMMIT或ROLLBACK结束事务后，MySQL会恢复为自动提交。
  查询当前自动提交功能状态：
  SELECT @@AUTOCOMMIT;
  改变MySQL的自动提交模式：
  SET AUTOCOMMIT=0; 禁用
  SET AUTOCOMMIT=1; 启用
  仅对当前连接生效。

---

3、案例演示

  创建数据表“users”，并查看其存储引擎为“InnoDB”：

  查看此时的自动提交状态，并修改为禁用自动提交：

  禁用自动提交后，查看数据表记录，此时为空，添加两条记录，显示成功，但注意目前并未提交，即这两条记录没有被存储到磁盘中，因此调用另一个MySQL窗口查看该表时，其数据仍然为空：

  此时执行COMMIT语句，记录被写入磁盘，调用其他的MySQL窗口也可以查询到数据：

  若此时执行ROLLBACK语句，则会回滚到添加记录之前，查询数据，显示为空：

注意：
  之所以没有回到创建数据表之前的状态，而仅仅是记录消失，是因为有些SQL语句会隐式的结束一个事务，即使用了COMMIT语句。
  例如：
ALTER FUNCTION、ALTER PROCEDURE、ALTER TABLE
BEGIN
CREATE DATABASE、CREATE FUNCTION、CREATE INDEX、CREATE PROCEDURE、CREATE TABLE
DROP DATABASE、DROP FUNCTION、DROP INDEX、DROP PROCEDURE、DROP TABLE
LOAD MASTER DATA、LOCK TABLES
RENAME TABLE
SET AUTOCOMMIT=1
START TRANSACTION
TRUNCATE TABLE
UNLOCK TABLES

  之后修改为自动提交，添加两条记录后，数据会被直接写入磁盘，调用其他的MySQL窗口也可以查询到数据：

  此时显示的创建一个事务，会默认禁用自动提交，因此添加记录后，在其他的MySQL窗口无法查询到数据：

  在添加用户“Alice”后，创建保存点“sp1”；之后清空数据表记录，查询后显示该表记录为空，回滚到保存点“sp1”的位置，再次查询数据后，显示用户“Alice”也在数据表中；此时执行ROLLBACK语句，会直接回滚到创建事务时的状态，即添加用户“Alice”之前，因此查询数据显示只有4条记录：

注意：
  有些语句不能被回滚，通常包括数据定义语言语句（DDL），例如创建或取消数据库的语句，和创建、取消或更改表或存储的子程序的语句。
  因此在设计事务时，不应包含这类语句。如果在事务的前部调用了一个不能被回滚的语句，则后部的其它语句会发生错误，在这些情况下，通过使用ROLLBACK语句不能回滚事务的全部效果。

---

4、锁定机制

  上述操作都是针对单个连接进行的，但实际会存在多个连接同时操作数据的情况，举例来说，如果用户A和用户B几乎同时登录到订票APP，发现某场次电影的最佳观影位置只剩最后一张票，于是用户都纷纷下单，此时冲突就产生了。对于这种当多个连接对记录进行修改的操作，为保证数据的一致性和完整性，需要用到锁系统：

• 共享锁（读锁）：
  在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会有任何变化；
• 排他锁（写锁）：
  在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或写锁操作。

语法结构
LOCK TABLES
tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES

注意：
  写锁定通常比读锁定拥有更高的优先权，以确保更新被尽快地处理。这意味着，如果一个线程获得了一个读锁定，则另一个线程会申请一个写锁定，后续的读锁定申请会等待，直到写线程获得锁定并释放锁定。此时可以使用LOW_PRIORITY WRITE锁定来允许其它线程在该线程正在等待写锁定时获得读锁定。前提是当此时没有线程拥有读锁定时，才应该使用LOW_PRIORITY WRITE锁定。

  对于锁系统还需要了解锁的粒度，即锁定时的单位。原则上只需要对待修改的数据精确加锁即可，而不是所有资源都枷锁，以减少系统的开销：

• 表锁，是一种占有系统开销最小的锁策略；
• 行锁，是一种占有系统开销最大的锁策略。

---

5、隔离级别

  在SQL的标准中，定义了四种隔离级别。每一种级别都规定了，在一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的：

1. READ UNCOMMITTED (未提交读) ：可以读取未提交的记录，会出现脏读。
2. READ COMMITTED (提交读) ：事务中只能看到已提交的修改。但不可重复读，会出现幻读。（在InnoDB中，会加行锁，但是不会加间隙锁）该隔离级别是大多数数据库系统的默认隔离级别。
3. REPEATABLE READ (可重复读) ：在InnoDB中是这样的：RR隔离级别保证对读取到的记录加锁 (行锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，因此不存在幻读现象，但是标准的RR只能保证在同一事务中多次读取同样记录的结果是一致的，而无法解决幻读问题。InnoDB的幻读解决是依靠MVCC的实现机制做到的，该隔离级别是MySQL的默认隔离界别。
4. SERIALIZABLE （可串行化）：该隔离级别会在读取的每一行数据上都加上锁，退化为基于锁的并发控制，即LBCC。

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	√	√	√
READ COMMITTED	×	√	√
REPEATABLE READ	×	×	√
SERIALIZABLE	×	×	×

查看隔离级别
全局隔离：SELECT @@global.tx_isolation;
会话隔离：SELECT @@tx_isolation;

修改隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED
| REPEATABLE READ | SERIALIZABLE};

  低级别的隔离可以执行更高级别的并发，性能好，但是会出现脏读、幻读等错误的现象：

更新丢失

  当两个或多个事务选择同一条记录，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生更新丢失的问题，即最后的更新覆盖了之前由其他事务所做的更新。

脏读

  一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；此时，另一个事务也来读取同一条记录，如果不加控制，第二个事务会读取到尚未提交的脏数据，并据此做进一步的处理，这种现象被形象地叫做"脏读"。

左侧为A窗口，右侧为B窗口

  如案例所示，将A窗口隔离级别修改为允许脏读后，显式创建事务，并修改用户名，此时A窗口始终未执行提交指令，意味着数据其实并没有写入磁盘，因此在B窗口首次查询时并没有出现修改后的记录，但是将B窗口的隔离级别也修改至允许脏读后，再次查询发现脏数据被读出来了。

不可重复读

  一个事务在读取某些数据后，再次读取之前读过的数据，却发现这些数据已经被另一个已提交的事务修改过，这种现象被称为“不可重复读”。

左侧为A窗口，右侧为B窗口

  如案例所示，将A窗口隔离级别修改为允许不可重复读后，显式创建事务，并修改用户名，假设在A窗口执行提交指令前，B窗口进行了第一次查询，显示的是原始记录，但在B窗口查询不久后，A窗口执行了提交指令，因此当B窗口再次查询相同的数据时，数据发生了变化，即为不可重复读。

幻读

  一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

左侧为A窗口，右侧为B窗口

  如案例所示，将A窗口隔离级别修改为允许幻读后，显式创建事务，并添加新用户，假设在A窗口执行提交指令前，B窗口进行了第一次查询，显示的是原始记录，但在B窗口查询不久后，A窗口执行了提交指令，因此当B窗口再次执行相同的查询指令时，出现了新的记录，即为幻读。

---

5、死锁

  死锁是指两个或者多个事务在同一资源上相互作用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。

左侧为A窗口，右侧为B窗口

  如案例所示，在A窗口显式创建事务，并修改id为3的用户名称，之后在B窗口显式创建事务并修改id为6的用户名称，此时在A窗口再次修改id为6的用户名称，A窗口会等待B窗口提交后释放锁定，在B窗口修改id为3的用户名称，B窗口也会等待A窗口提交后释放锁定，此时互相等待陷入死锁，这时MySQL会使B窗口强制解除锁定，使A窗口继续执行。

---

6、事务处理的SQL语句汇总

• 显式的开启一个事务
  START TRANSACTION;
  或
  BEGIN;

• 回滚
  直接回滚：
  ROLLBACK;
  部分回滚：
  设置一个事务保存点及标识符名称：SAVEPOINT identifier;
  回滚到设置保存点之前的状态：ROLLBACK TO SAVEPOINT identifier;
  从当前事务的一组保存点中删除指定的保存点：RELEASE SAVEPOINT identifier;

• 提交事务
  COMMIT;
  查询当前自动提交功能状态：
  SELECT @@AUTOCOMMIT;
  改变MySQL的自动提交模式：
  SET AUTOCOMMIT=0; 禁用
  SET AUTOCOMMIT=1; 启用

• 锁定
  LOCK TABLES
  tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
  [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
  UNLOCK TABLES

• 隔离级别
  查看隔离级别：
  全局隔离：SELECT @@global.tx_isolation;
  会话隔离：SELECT @@tx_isolation;
  修改隔离级别：
  SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
  {READ UNCOMMITTED | READ COMMITTED
  | REPEATABLE READ | SERIALIZABLE};

---

版权声明：欢迎转载，欢迎扩散，但转载时请标明作者以及原文出处，谢谢合作！             ↓↓↓