Firebird 2.5 commit/rollback (using autonomous transaction) in loop

Question

I have to process records in a stored procedure's loop, for example:

create or alter procedure process_waiting_records
as
  declare   v_id                type of column my_table.id;
begin
  for
    select
      t.id
    from
      my_table t
    where
      (t.status = 'WAITING_TO_PROCESS')
    order by
      t.created_at
    into
      :v_id
  do
  begin
    execute procedure process_one_record(:v_id);
  end
end ^

The problem is when the execution of process_one_record() fails (generates any kind of exception), the whole set of modification will be rolled back from the calling code.

The goal would be to process all possible records, I don't really care at this point if some of the records can not be processed, those failed records will be logged in a log table anyway (using autonomous transaction).

I was thinking about to call the process_one_record() stored procedure also in an autonomous transaction block with when any do (dummy code) clause. However, I think that is not going to work, because that failed transaction will not be rolled back, but committed (referring to this topic: Firebird 2.5 exception handling within autonomous transaction).

Could some one please point me to the right direction how to solve this problem?

Mark Rotteveel · Accepted Answer · 2021-01-01T11:04:39.910

You don't need anonymous transactions for this. When an exception is thrown out of a stored procedure, the effects of that stored procedure is automatically undone. If a stored procedure contains SUSPEND, only the effects up to the last SUSPEND is undone (think of it as a savepoint). For any other forms of rollback, a transaction will need to be explicitly rolled back.

See also Savepoints and PSQL in the Firebird 2.5 Language Reference.

Transaction control statements are not allowed in PSQL, as that would break the atomicity of the statement that calls the procedure. However, Firebird does support the raising and handling of exceptions in PSQL, so that actions performed in stored procedures and triggers can be selectively undone without the entire procedure failing.

Internally, automatic savepoints are used to:

undo all actions in the BEGIN...END block where an exception occurs

undo all actions performed by the procedure or trigger or, in for a selectable procedure, all actions performed since the last SUSPEND, when execution terminates prematurely because of an uncaught error or exception

Each PSQL exception handling block is also bounded by automatic system savepoints.

NOTE: A BEGIN...END block does not itself create an automatic savepoint. A savepoint is created only in blocks that contain the WHEN statement for handling exceptions.

In this case, as the effects of a single process_one_record needs to be undone, but not the whole handling in process_waiting_records, you need to allow the exception to be thrown out of process_one_record, but catch it for that single procedure invocation.

In short, you need to do something like:

for select
  ...
do
begin
  execute procedure process_one_record(:v_id);
  when any do
  begin
    -- do nothing, last call to `process_one_record` was undone
  end
end

so if I understand you correctly with this approach all changes made by the failed process_one_record() will be rolled back automatically (no SUSPEND involved at all) but the previously successful process_one_record() calls still can be commited azt the end of the process_waiting_records? — tcxbalage, Feb 08 '20 at 11:07
@tcxbalage Yes, only the work in a failed call to `process_one_record` is undone, the rest of the work is preserved and can be committed. Be aware that you could actually also catch the exception inside `process_one_record`, the catching of an exception inside a block is what triggers the rollback of the work done in that block, but letting the exception come out of the stored procedure and handling it in the caller is - I think - cleaner. — Mark Rotteveel, Feb 08 '20 at 11:10
I competely missed that "Savepoints and PSQL" lang. ref. all what I was looking for is the exception handling. should have done my research more thoroughly. thanks for your help! — tcxbalage, Feb 08 '20 at 11:20

Firebird 2.5 commit/rollback (using autonomous transaction) in loop

1 Answers1