mysql事务和阶段性回滚点

手册地址 https://dev.mysql.com/doc/refman/8.0/en/savepoint.html

默认的, 如果不修改 mysql 配置文件, 会启用 自动提交功能auto commit, 即隐式提交你的每一条 SQL 语句;

有些语句，在执行之前，会隐式地发出commit。这些语句包括：

1. DDL语句, 比如 create database/drop database, create/drop/alter table/routine
2. 隐式地使用或者修改mysql数据库的语句，比如 ALTER USER, CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE, SET PASSWORD
3. 事务控制和锁语句，比如BEGIN,START TRANSACTION,  LOCK/UNLOCK TABLES, SET autocommit = 1(原来不是1)
4. LOAD数据语句， LOAD DATA INFILE
5. 管理语句，比如 ANALYZE TABLE, CACHE INDEX, CHECK TABLE等等
6. 复制控制语句，比如 START/STOP/RESET SLAVE, CHANGE MASTER TO

示例：

mysql> set autocommit=1;

mysql> BEGIN;
mysql> CREATE TABLE T2(ID INT);
mysql> INSERT INTO T2 VALUES(100);
mysql> ROLLBACK;

mysql> SELECT * FROM T2;
+------+
| ID   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)
————————————————

事务的基础操作:

开启事务(进入事务模式,不会自动提交) START TRANSACTION; 或者简写 BEGIN;

提交事务 COMMIT;

回滚事务 ROLLBACK;

关闭提交之后, 无需开启事务, 等价于"已经在事务模式下", 需要手动COMMIT或ROLLBACK才能使SQL发生作用;

当前TCP session级别, 关闭自动提交 SET autocommit=0;

全局, 关闭自动提交 SET GLOBAL autocommit=0;

重要提示:DDL语句无法被回滚, 上面说了"有些语句会提供commit提交"，比如 create database/drop database, create/drop/alter table/routine。

在需要COMMIT/ROLLBACK的场景下, 可以阶段性的回滚和提交, 即 "打点计时器" 一样, 留下锚点(保存点, save point);

1. 开启事务后,在事务内声明存档点 SAVEPOINT $name

   批处理也是事务, 比如: (1)BEGIN;(2)巴拉巴拉多条SQL;;(3)COMMIT;或ROLLBACK;

2. 提交事务之前,释放存档点 RELEASE SAVEPOINT $name

   只会移除这一个存档点,不影响其他存档点, 也不会自动触发commit或rollback;
   
   如果释放的存档点不存在,则报错 ERROR 1305 (42000): SAVEPOINT identifier does not exist

3. 回滚至某个存档点 ROLLBACK TO SAVEPOINT $name

   如果回滚的存档点不存在,则报错 ERROR 1305 (42000): SAVEPOINT identifier does not exist

4. 执行COMMIT或ROLLBACK后, 自动删除 事务内的所有存档点;

5. 开启事务之前, 可以设置隔离级别:

   SET TRANSACTION ISOLATION LEVEL $level;

四种级别情况,

第一种情况：

READ COMMITTED

作用是：

指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数据。该选项是 SQL Server的默认值。

第二种情况：

READ UNCOMMITTED

作用是：

执行脏读或0级隔离锁定，这表示不发出共享锁，也不接受排它锁。当设置该选项时，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。

第三种情况：

REPEATABLE READ

作用是：

锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。

第四种情况：

SERIALIZABLE

作用是：

在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK相同。

SAVEPOINT语法如下：
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

注意: 如果存档点的名称(标识符identifier)跟上方重复了, 则删除旧的存档点, 设置新的存档点, 相当于"游戏存档"被擦写覆盖了。

示例:

mysql> CREATE TABLE T2(ID INT);

mysql> BEGIN;
mysql> INSERT INTO T2 VALUES(100);
mysql> SAVEPOINT sp1;
mysql> INSERT INTO T2 VALUES(200);
mysql> ROLLBACK TO SAVEPOINT sp1;
mysql> RELEASE SAVEPOINT sp1;
mysql> COMMIT;

mysql> SELECT * FROM T2;
+------+
| ID   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)
————————————————

存档点可以层叠嵌套, 很像"操作系统在切换进程上下文的时候,压栈/弹栈", 具体表现在:

1. 当 "调用invoke"存储过程/函数(store function) 或 激活(activated)触发器(trigger) 时，将隐式创建"更高层 级别的"存档点。
2. 低级别上的存档点将不可用(保留工作现场)，因此不会与新级别上的存档点冲突(比如存档点的标识符名称)。
3. 当函数或触发器执行结束时，它创建的任何存档点都将被释放，并还原上一个存档点级别(还原工作现场)。

值得一提的是, PHP的Yii2框架, 居然支持"多次开启事务", 其实mysql是不支持"多次开启事务"的, 而是"存档点的层叠嵌套"; 更有意思的是, 这也导致框架的使用者采坑: (1)多次"开启事务", (2)"提交事务"居然失败了?

$m = new AR();
$transaction = $m->getDb()->beginTransaction(); // 返回一个对象,挂载在db下
// 巴拉巴拉一顿CRUD操作猛如虎..
$transaction = $m->getDb()->beginTransaction(); // 该对象与前者是同一个实例(单例)
// 又是一顿CRUD操作猛如虎...
$m->getDb()->getTransaction()->commit();
// 或者 $transaction->commit();
exit;
 
// 等等,为什么MySQL没有找到刚才的"一顿操作"??!!
// 因为 第二次beginTransaction() 创建了新的savepoint层级,
// 第一次调用commit()函数, 只是 RELEASE SAVEPOINT $name
// 所以需要再多调用一次 commit() 函数...直到层级为0的时候,才会真正发送 commit;语句给MYSQL服务器;

详见代码 vendor/yiisoft/yii2/db/Transaction.php

public function begin($isolationLevel = null)
{
    $this->db->open();

    if ($this->_level === 0) {
        if ($isolationLevel !== null) {
            // 设置隔离级别SET TRANSACTION ISOLATION LEVEL $level;
            $this->db->getSchema()->setTransactionIsolationLevel($isolationLevel);
        }
        $this->db->pdo->beginTransaction();
        $this->_level = 1;
        return;
    }

    $schema = $this->db->getSchema();
    if ($schema->supportsSavepoint()) { // 如果是InnoDB存储引擎
        Yii::trace('Set savepoint ' . $this->_level, __METHOD__);
        $schema->createSavepoint('LEVEL' . $this->_level);
    } else {
        Yii::info('Transaction not started: nested transaction not supported', __METHOD__);
    }
    $this->_level++;
}

public function commit()
{
    if (!$this->getIsActive()) { // 没有开启事务则报错
        throw new Exception('Failed to commit transaction: transaction was inactive.');
    }

    $this->_level--;
    if ($this->_level === 0) {
        $this->db->pdo->commit();
        return;
    }

    $schema = $this->db->getSchema();
    if ($schema->supportsSavepoint()) { // InnoDb存储引擎
        Yii::trace('Release savepoint ' . $this->_level, __METHOD__);
        $schema->releaseSavepoint('LEVEL' . $this->_level);
    } else {
        Yii::info('Transaction not committed: nested transaction not supported', __METHOD__);
    }
}

YII2 的 rollback机制 也混合了savepoint, 要多次调用rollback函数才会真正rollback,很危险.