Term for non transaction save actions

If you only look at the database everything is fine. You have transactions and if somethings goes wrong everything gets rolled back. That's nice - I like this.

BUT: I want to send mails. Now I am in trouble because I can't rollback.

example:

• transaction starts

• Mail gets send

• Other stuff gets done (inside DB)

• Something goes wrong.

• Rollback.

How to solve this is a different question, not this.

This question how to call this in general. In this example is about sending mails. But the same problem as soon as you do modify something in systems which are outside the transaction boundary.

Is there a name for this problem?

Roughly the same problem arises if you want to import files from a directory. If you delete the file inside the transaction, then the transaction might fail and the file was deleted but never imported. Or you delete the file after the transaction. Then the delete of the file might fail and the file gets imported a second time.

I don't want to reinvent a solution for this. That's why I need the matching term for this problem. Then I can read some papers and learn what's "state of the art" in the year 2018.

Oracle PL/SQL keyword AUTONOMOUS_TRANSACTION will cause a procedure to create another session, do a transaction, commit/rollback just that private transaction, and return flow control back to the parent.

Oh..never send email on uncommitted data.

EDIT: (due to edit of original post)


Roughly the same problem arises if you want to import files from a
directory. If you delete the file inside the transaction, then the
transaction might fail and the file was deleted but never imported. Or
you delete the file after the transaction. Then the delete of the file
might fail and the file gets imported a second time.

This type of problem is called a bug.

The solution is:

• Define each step as its own TRANSACTION

• You'll want to create them in a way that you can rerun (or skip) the step(s) as needed

• Run each step in the appropriate order.

• do not send email prior to COMMIT.

• do not delete a file prior to successful loading of the data

• You'll need to keep track of "where you are at" and if that step has passed/failed.

EMAIL example

You should have a procedure to sendEmail that should be called after commit.

If you want to call the procedure prior to commit, you'll need to add a row to a queue that will rollback with the main transaction. For Oracle, this will be either Advance Queuing or the package APEX_MAIL

By putting it in a separate procedure, you can sendEmail a 2nd time upon [end-user's] request.

Process File

You have an algorithm that contains a few steps where each step can fail. this is actually different than your sendEmail problem.

You need to record what you are processing, where you are at within your algorithm, and if that step have succeeded or failed.

In order to recover from an error at any step, each step of the process needs to be defined as a discrete TRANSACTION.

In Oracle, I would have these procedures (1 procedure per TRANSACTION):

create or replace
package file_processing_package
as
  procedure update_file_processing_status(
                                p_id       IN files_to_process.id%TYPE
                              , p_status   IN process_states.id%TYPE);

  function add_a_file_to_be_processed( p_filename IN files_to_process.file_name%TYPE )
                               return files_to_process.id%TYPE;

  procedure load_data_from_file( p_id in files_to_process.id%TYPE );

  procedure process_already_loaded_data( p_id in files_to_process.id%TYPE );

  procedure delete_file_from_os( p_id in files_to_process.id%TYPE );
end;
/

This is based on the following tables:

CREATE TABLE PROCESS_STATES (
  id   int generate by default on null as identity, -- 12c+
  state_desc  varchar2(25) not null,
  constraint process_states_pk primary key (id),
  constraint process_states_uq1 unique (state_desc)
);

insert into process_states( state_desc ) values ( 'file to be processed' );
insert into process_states( state_desc ) values ( 'file loaded' );
insert into process_states( state_desc ) values ( 'processing' );
insert into process_states( state_desc ) values ( 'processing failed' );
insert into process_states( state_desc ) values ( 'processing succeeded' );
insert into process_states( state_desc ) values ( 'delete failed' );
insert into process_states( state_desc ) values ( 'OK' ); -- delete succeeded
commit;

CREATE TABLE FILES_TO_PROCESS (
  id               int generate by default on null as identity, -- 12c+
  file_name        varchar2(50) not null,
  process_state_id int not null,
  constraint file_to_process_pk  primary key (id),
  constraint file_to_process_uq1 unique (file_name),
  constraint file_to_process_fk1 foreign key (process_state_id)
                            references (process_states.id)
);

The UNIQUE constraint on FILE_NAME prevents the same file being processed twice.

StackExchange Database Administrators Q#205831, answer score: 6