存储函数是否会回滚整个事务?文档中是否存在错误?

huangapple go评论65阅读模式
英文:

Does stored function roll back the entire transaction?, is there an error in the documentation?

问题

https://dev.mysql.com/doc/refman/8.0/en/commit.html

如果在事务中的SELECT语句调用了一个存储过程,并且存储过程中的语句失败了,那个语句会回滚。如果随后为该事务执行ROLLBACK,整个事务都会回滚。

你能给我一个文档中的示例吗?

根据我的理解,如果存储过程失败,整个事务都会失败,不仅仅是SELECT a_stored_function();

我感到困惑,文档中是否存在错误?

英文:

https://dev.mysql.com/doc/refman/8.0/en/commit.html

> 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.

Could you give me an example of that part of the documentation?

From my understanding if the stored function fails the whole transaction will fail, not just the SELECT a_stored_function();

I am confused, is there an error in the documentation?

答案1

得分: 2

Here is the translated code portion:

创建表 t (
  id serial 主键,
  word 文本
);

分隔符 ;;

创建函数 f(p_v 文本) 返回整数
不使用 SQL
开始
  声明 v 字符(3);

  设置 v = p_v;

  返回 0;
结束;;

分隔符 ;

开始事务;

插入到 t (word)  ('bird');

选择 f('too long');

提交;

选择 *  t;

Regarding the explanation:

这个故意在函数中引发错误,试图将一个包含8个字符的字符串放入一个长度为varchar(3)的本地变量中。它太长了,因此在使用该参数调用函数时会引发错误:

错误 1406 (22001): 列 'v' 的数据过长,位于第1行

这是否会回滚在同一事务中执行的 INSERT 操作?证据在于我们在提交事务后执行 SELECT 时的结果:

结果:

从 t 选择 *;
+----+------+
| id | word |
+----+------+
|  1 | bird |
+----+------+

INSERT 操作没有被回滚。只有调用带有错误的函数的单个语句被回滚了(在一个事务中“回滚”一个 SELECT 没有可观察到的效果)。

附言:作为程序员,这种快速的概念验证测试应该是您自己能够完成的事情。我认为上面来自RiggsFolly的评论是在强调这一点。

英文:
create table t (
  id serial primary key,
  word text
);

delimiter ;;

create function f(p_v text) returns int
no sql
begin
  declare v varchar(3);

  set v = p_v;

  return 0;
end;;

delimiter ;

start transaction;

insert into t (word) values ('bird');

select f('too long');

commit;

select * from t;

This deliberately causes an error in the function, by trying to stuff an 8-character string into a local variable that is varchar(3). It's too long, so it causes an error when we call the function with that argument:

ERROR 1406 (22001): Data too long for column 'v' at row 1

Does this roll back the INSERT done during the same transaction? The proof would be when we SELECT after committing the transaction.

Result:

select * from t;
+----+------+
| id | word |
+----+------+
|  1 | bird |
+----+------+

The INSERT was not rolled back. Only the single statement that called the function with the error was rolled back ("rolling back" a SELECT has no observable effect).

P.S.: This kind of quick proof of concept test is something you should be able to do yourself as a programmer. I think that's what the comments from RiggsFolly above were getting at.

huangapple
  • 本文由 发表于 2023年5月31日 22:39:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/76374683.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定