总结摘要
MySQL Transaction For Stored Procedure And Stored Function
结论
存储过程（Stored Procedure）与存储函数（Stored Function）在事务控制方面有较大差异。
对于存储过程，如果自动提交是开启的，并且你没有在存储过程中显式地控制事务，那么每条独立的 SQL 语句执行完毕后都会立即提交。
存储过程允许显式的事务操作，包括显式地开始、提交或回滚事务。
存储函数内的 SQL 操作被视为原子的，这意味着存储函数内的所有 SQL 语句要么全部成功执行，要么在发生错误时全部回滚。
存储函数会被视为一个单一的执行单元，即使自动提交是开启的，函数内部的所有操作也会被视为一个事务。
不能在存储函数内部使用显式的 COMMIT 或 ROLLBACK 语句，因为这些操作会破坏函数的原子性。
[HY000][1422] Explicit or implicit commit is not allowed in stored function or trigger.
背景知识
| 特性 | 存储函数 | 存储过程 |
|---|
| 返回值 | 必须返回单一值，且类型明确。 | 无返回值，但可通过 <font style="color:rgb(64, 64, 64);">OUT</font>参数返回多个值，或通过结果集返回数据。 |
| 事务控制 | 不能使用显式事务语句，依赖 <font style="color:rgb(64, 64, 64);">autocommit</font> 或外层事务。 | 支持显式事务控制，可自主管理提交或回滚。 |
| 调用方式 | 可在 SQL 语句中直接调用（如 <font style="color:rgb(64, 64, 64);">SELECT func()</font>）。 | 必须通过 <font style="color:rgb(64, 64, 64);">CALL</font>语句 |
函数中不允许使用 COMMIT、ROLLBACK 或 START TRANSACTION 等显式事务控制语句。如果尝试使用，会触发语法错误。
存储过程支持显式事务控制，具有更灵活的事务管理能力。
If a SELECT statement within a transaction calls a stored function, and a statement within the stored function fails, that statement rolls back. If ROLLBACK is executed for the transaction subsequently, the entire transaction rolls back.
如果事务中的 SELECT 语句调用了一个存储函数，并且存储函数中的语句失败，则该语句会回滚。如果随后对事务执行 ROLLBACK ，则整个事务会回滚。
https://dev.mysql.com/doc/refman/8.0/en/commit.html
验证
数据库状态
1
2
3
4
5
6
7
8
| -- 8.0.41
SELECT version();
-- 1，自动提交模式
SELECT @@autocommit;
-- 查询当前数据库连接的事务 ID
SELECT trx_id FROM information_schema.innodb_trx WHERE trx_mysql_thread_id = CONNECTION_ID();
|
初始化数据
1
2
3
4
5
6
7
8
9
10
| DROP TABLE IF EXISTS `test_code`;
-- 建表并添加主键约束（确保唯一性冲突可触发错误）
CREATE TABLE `test_code` (
`id` int PRIMARY KEY, -- 添加主键
`code` int DEFAULT NULL,
`note` varchar(100) DEFAULT NULL
);
TRUNCATE TABLE `test_code`;
|
隐式提交
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
| -- SET GLOBAL log_bin_trust_function_creators = 1;
DROP PROCEDURE IF EXISTS sp_autocommit;
DELIMITER ;;
CREATE PROCEDURE sp_autocommit()
BEGIN
INSERT INTO test_code(id, code, note) VALUES (1, 100, '步骤1');
INSERT INTO test_code(id, code, note) VALUES (1, 100, '重复步骤1'); -- 主键冲突
END ;;
DELIMITER ;
TRUNCATE TABLE `test_code`;
-- [1062] [23000]: Duplicate entry '1' for key 'test_code.PRIMARY'
-- 事务自动回滚。
-- 第一条插入已提交，第二条失败。
call sp_autocommit();
-- 1 100 步骤1
SELECT * FROM test_code;
DROP FUNCTION IF EXISTS func_autocommit;
DELIMITER ;;
CREATE FUNCTION func_autocommit() RETURNS INT
BEGIN
INSERT INTO test_code(id, code, note) VALUES (2, 200, '步骤2');
INSERT INTO test_code(id, code, note) VALUES (2, 200, '重复步骤2'); -- 主键冲突
RETURN 1;
END ;;
DELIMITER ;
TRUNCATE TABLE `test_code`;
-- 执行函数
-- [1062] [23000]: Duplicate entry '2' for key 'test_code.PRIMARY'
-- 事务自动回滚。
-- 无任何数据写入成功。
SELECT func_autocommit();
-- 无数据
SELECT * FROM test_code;
|
主动事务——存过
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| TRUNCATE TABLE `test_code`;
BEGIN;
-- 初始化 1 条数据
INSERT INTO test_code(id, code, note) VALUES (0, 0, '开启事务');
-- 存在 1 条初始化数据
SELECT * FROM test_code;
-- 存在事务 3511
SELECT trx_id FROM information_schema.innodb_trx WHERE trx_mysql_thread_id = CONNECTION_ID();
-- [1062] [23000]: Duplicate entry '1' for key 'test_code.PRIMARY'
call sp_autocommit();
-- 存过中的第一条插入已提交，第二条失败。
-- 仅 2 条数据，初始化数据与存过中的第一条数据。
SELECT * FROM test_code;
-- 存在事务 3511，事务 id 保存不变。
SELECT trx_id FROM information_schema.innodb_trx WHERE trx_mysql_thread_id = CONNECTION_ID();
-- 补充，已验证此处同样支持 COMMIT 命令。提交事务将持久化当前的 2 条数据。
ROLLBACK;
-- 事务已回滚
SELECT trx_id FROM information_schema.innodb_trx WHERE trx_mysql_thread_id = CONNECTION_ID();
-- 无任何数据，成功回滚。
SELECT * FROM test_code;
|
主动事务——函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| TRUNCATE TABLE `test_code`;
BEGIN;
-- 初始化 1 条数据
INSERT INTO test_code(id, code, note) VALUES (0, 0, '开启事务');
-- 存在 1 条初始化数据
SELECT * FROM test_code;
-- 存在事务 3554
SELECT trx_id FROM information_schema.innodb_trx WHERE trx_mysql_thread_id = CONNECTION_ID();
-- [1062] [23000]: Duplicate entry '2' for key 'test_code.PRIMARY'
SELECT func_autocommit();
-- 函数中的两条数据全部插入失败。
-- 仅存在 1 条初始化数据。
SELECT * FROM test_code;
-- 存在事务 3554，事务 id 保存不变。
SELECT trx_id FROM information_schema.innodb_trx WHERE trx_mysql_thread_id = CONNECTION_ID();
-- 补充，已验证此处同样支持 ROLLBACK 命令。回滚当前的 1 条数据。
COMMIT;
-- 事务已提交
SELECT trx_id FROM information_schema.innodb_trx WHERE trx_mysql_thread_id = CONNECTION_ID();
-- 仅存在 1 条初始化数据。
SELECT * FROM test_code;
|
END