7

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:

  1. transaction starts
  2. Mail gets send
  3. Other stuff gets done (inside DB)
  4. Something goes wrong.
  5. 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.

  • I think the term you are looking for is "side effect". – hunterjrj May 7 '18 at 13:24
  • @hunterjrj I tried do search for "side effect postgresql" but the first search results did not match. – guettli May 8 '18 at 10:14
1
+50

You are describing a distributed transaction. Note that the term "transaction" has a more general meaning than simply "database transaction".

In a distributed transaction different members may have different ACID properties (e.g. email is not necessarliy guaranteed to be delivered), different approaches to achieving those properties, and different failure scenarios.

To ensure consistency of a distributed transaction an external entity called transaction coordinator (or manager) is typically employed to control commitment of each member (can also be called resource or resource manager). One common method is two-phase commit (2PC).

If you search for "consistency in distributed systems" on the internets you'll find a great wealth of materials on the topic.

|improve this answer|||||
  • Thank you for "consistency in distributed systems". I think this term matches very good. – guettli May 14 '18 at 8:14
6

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.

|improve this answer|||||
  • The name for this problem is "autonomous transaction"? – guettli May 7 '18 at 6:55
  • I think this answer does not match to the question. Thank you very much, that you want to solve the use cases of the question, but this was not what it was about. I searched a matching term to learn more about the general problem. – guettli May 14 '18 at 8:16
3

I think maybe the term you're looking for is dirty read:

A dirty read (aka uncommitted dependency) occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed. [...]

In our example, Transaction 2 changes a row, but does not commit the changes. Transaction 1 then reads the uncommitted data. Now if Transaction 2 rolls back its changes (already read by Transaction 1) or updates different changes to the database, then the view of the data may be wrong in the records of Transaction 1.

|improve this answer|||||
1

What you describe is the wish for a distributed transaction , only you don't have a distributed transaction manager and no rollback possibility. The easiest is to use queue ( external ) or the sql server broker to decouple the loop from the actual send. See for example : http://python-rq.org/

|improve this answer|||||
1

I don't have a specific term for the actual combination of invoking an external process from a database transaction, but I would classify this problem as tightly coupled.

The root problem is that you have tightly coupled the sending of the email with the database transaction.

A solution to this problem will be to loosely couple them.

Technically, you could solve this in many ways, in rough order from ugly to nice:

  • a flag on the rows in the table to indicate whether or not an email has been sent. An external process can poll for flags and send emails.
  • generate and store the emails in a table. These will then commit in the same transaction. An external process reads and sends emails that need to be sent. Monitoring the table could be done with polling, or with a listen/notify construct (see next).
  • use a listen/notify construct (Postgres implementation). The database transaction invokes NOTIFY. A LISTENing process which runs continuously is NOTIFIED when the transaction commits, providing the isolation desired and the loose coupling.
|improve this answer|||||
0

Implicit Commits

I believe is The Term you are looking for. These are statements do/can & will not adhere to Transactions

Oracle

MySQL

SqlServer

database-ddls-and-implicit-commit

and most interestingly This:

sp_send_dbmail (Transact-SQL)

When executing sp_send_dbmail without a transaction context, Database Mail starts and commits an implicit transaction. When executing sp_send_dbmail from within an existing transaction, Database Mail relies on the user to either commit or roll back any changes. It does not start an inner transaction.

The Workflow is just in need of attention,

Instead of

  • Transaction Starts
  • Mail gets sent
    • Other stuff gets done (inside DB)
      • Something goes wrong
  • Rollback.

TRY

  • Send Mail "Starting with Snapshot Stats" (capture starting row count to a variable A or file)
  • Transaction Starts
    • Other stuff gets done (inside DB)
      • Something goes wrong.
  • Rollback.
  • Send Mail "Ending with Snapshot Stats"

(Capture ending row count to a variable B or file)

and if A & B Variables match you know there is an error.

Change the workflow and try using what you already have to your advantage, variable comparison etc.

|improve this answer|||||
-1

This is an issue that wasn't accounted for in the "Requirements Engineering" phase of the project. It shouldn't be looked at as an issue of the database system, because the database is performing as it should do. The mail is sent because it is not (yet) part of the correct business logic.

It is called a Business Logic Flaw or possibly even a Business Logic Issue.

Business Logic

In computer software, business logic or domain logic is the part of the program that encodes the real-world business rules that determine how data can be created, stored, and changed. It is contrasted with the remainder of the software that might be concerned with lower-level details of managing a database or displaying the user interface, system infrastructure, or generally connecting various parts of the program.

Reference: Business Logic (Wikipedia.org)

Business Logic Flaws / Issues

Web applications have become the core mechanism for business processes over the Internet. As more and more businesses are migrating to the Internet model, it has led to various information security issues and vulnerabilities. SQL Injection, Cross Site Scripting, Remote Code Execution to name a few. However apart from the conventional vulnerabilities, there are many forms of business logic vulnerabilities commonly exploited by attackers. These vulnerabilities are routinely overlooked during QA because the process is intended to test what a piece of code is supposed to do and not what it can be made to do. The other problem(s) with business logic flaws is scanners can’t identify them, IDS can’t detect them, and Web application firewalls can’t defend them.

Thus, business logic vulnerabilities are ways of using the legitimate processing flow of an application in a way that results in a negative consequence to the organization. Automating business processes such as customer purchase orders, banking queries, wire transfers or online auctions, for example, requires entities to have access to extremely sensitive information. Improper implementation of these can lead to the business logic flaws.

Reference: Business Logic Vulnerabilities And Some Common Scenarios Of Business Logic Flaws (Cyber Security Community)

"Business logic is the intended behavior of the application," explained Dan Kuykendall, co-CEO and CTO of NT OBJECTives. "It’s the functionality that governs the core of what the application does, for example, which users are allowed to see what, how much users are charged for various items, etc. Business logic attacks are things you can do to exploit the logic and cheat the application…(they) are hard to test for because they require both an understanding of the application and of security. In many cases, QA teams know the business logic, but they aren’t security experts and haven’t been trained on the clever attack techniques."

Reference: Common Business Logic Flaws Compromising Application Security (SecurityWeek.com)

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.