自主事务中的Firebird 2.5异常处理 [英] Firebird 2.5 exception handling within autonomous transaction

问题描述

我的Firebird存储过程之一性能下降，我不知道为什么.我在提到的SP中找到了以下代码:

I'm experiencing performance drop in one of our Firebird stored procedures and I have no clue why. I have found the following code in the mentioned SP:

declare v_dummy integer;
...
in autonomous transaction do
begin
  -- insert may fail, but that is not a problem because it means the record is already there
  insert into my_table(my_field) values (:input_param);
when ANY do
  v_dummy = 1;
end

我在RDB$TRANSACTIONS表中看到状态为3的几十条记录，在MON $ TRANSACTIONS表中没有相关的记录.

I see few dozens of records in RDB$TRANSACTIONS table with STATE 3, no relevant records in MON$TRANSACTIONS table.

问题是，如果插入失败，那么将自动回滚自主事务，或者何时做"会阻止回滚，并且将有一个已打开的事务?我可以删除异常处理，这样它会自动回滚而不会引发异常并阻止其余代码吗?

The question is, if the insert fails will the autonomous transaction be rolled back or does the "when ANY do" prevent the rollback and there will be an opened transaction? Can I just remove the exception handling, so it will be rolled back automatically without raising an exception and blocking the rest of the code?

推荐答案

在自主事务块内使用when any do将会不回滚事务，相反，它将在该块结束后提交，因为异常不会逃脱该块.

Using a when any do inside an autonomous transaction block will not rollback the transaction, instead it will commit once the block ends because the exception does not escape the block.

但是，这可能是预期的结果:在Firebird中进行事务(相对)比回滚便宜.实际上，如果在没有任何更改的情况下事务回滚，Firebird仍将回滚转换为提交.

However, this is probably the desired result: committing transactions in Firebird is (relatively) cheaper than rolling back. In fact, if a transaction rolls back when nothing was changed, Firebird will convert a rollback into a commit anyway.

我不认为这是造成性能问题的原因，但是如果没有可重复的示例，就很难对此进行推理了.

I don't think this is the cause of your performance problem, but without reproducible example, it is hard to reason about this.

顺便说一句，状态3的事务将回滚，并且回滚的事务已结束. MON$TRANSACTIONS仅显示活动事务，因此回滚事务将不会显示在该虚拟表中.

As an aside, transactions with state 3 are rolled back, and rolled back transactions have ended. MON$TRANSACTIONS only shows active transactions, so rolled back transactions will not be shown in that virtual table.

这篇关于自主事务中的Firebird 2.5异常处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！