Firebird 2.5 exception handling within autonomous transaction

Question

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

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?

Try rewrite code without catch, wrap insert with if(not exists(select 1 from my_table t where t.my_field = :input_param)) then... with firebird 2.5 like with bees, you don't know for 100% how performance will be affected.. if removed catch clause, Error will halt executing code — Strauteka, Jan 20 '20 at 08:11
can those transactions be waiting for some "concurrent transaction" they believe they have, instead of instant failing with "deadlock" error? There was somethign about REC_VERSION va NO_REC_VERSION options AFAIR. — Arioch 'The, Jan 20 '20 at 09:23
`insert may fail, but that is not a problem` - you may try to prove it modifying the code and doing explicit check before attempting insert. However is this a whole code or just a simplified example with large parts of PSQL omitted? — Arioch 'The, Jan 20 '20 at 09:26
then it might be something in the code you omitted that was actually freezing the transactions, not the `insert` you think about — Arioch 'The, Jan 20 '20 at 13:20
update: as some of you mentioned the source of the problem was somewhere else at the end of a "insert trigger > update trigger > stored proc > stored proc" chain, there was an unindexed SUM() query for the actually inserted records, hence the constant slow down. thanks for every one the help! — tcxbalage, Jan 22 '20 at 20:03

Mark Rotteveel · Accepted Answer · 2020-01-20T08:18:59.000

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.

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.

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 exception handling within autonomous transaction

1 Answers1

Linked