Here is some meta-code:

procedure Processing1;
begin
  // Here we do single operations like single INSERT
end;

procedure Processing2;
begin
  // Here we do single operations like single SELECT
end;

procedure Processing3;
begin
  // Here we do multiple DB operations
end;

procedure Processing4;
begin
  // Here we do not touch the database at all
end;

procedure EntryPoint(ProcessingType: Integer);
begin
  // It could be even a loop
  StartTransaction;
  try
    case ProcessingType of
      1: Processing1;
      2: Processing2;
      3: Processing3;
      4: Processing4;
    end;
    CommitTransaction;
  except
    RollbackTransaction;
  end;
end;

Could I wrap every method call in a transaction or should I use a transaction only when needed? What is the overhead of an universal approach?

CodePudding user response：

In Oracle:

Beginning of a Transaction

A transaction begins when the first executable SQL statement is encountered.

An executable SQL statement is a SQL statement that generates calls to a database instance, including DML and DDL statements and the SET TRANSACTION statement.

When a transaction begins, Oracle Database assigns the transaction to an available undo data segment to record the undo entries for the new transaction. A transaction ID is not allocated until an undo segment and transaction table slot are allocated, which occurs during the first DML statement. A transaction ID is unique to a transaction and represents the undo segment number, slot, and sequence number.

[...]

End of a Transaction

A transaction can end under different circumstances.

A transaction ends when any of the following actions occurs:

• A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.

  In a commit, a user explicitly or implicitly requested that the changes in the transaction be made permanent. Changes made by the transaction are permanent and visible to other users only after a transaction commits. The transaction shown in "Sample Transaction: Account Debit and Credit" ends with a commit.

• A user runs a DDL command such as CREATE, DROP, RENAME, or ALTER.

  The database issues an implicit COMMIT statement before and after every DDL statement. If the current transaction contains DML statements, then Oracle Database first commits the transaction and then runs and commits the DDL statement as a new, single-statement transaction.

• A user exits normally from most Oracle Database utilities and tools, causing the current transaction to be implicitly committed. The commit behavior when a user disconnects is application-dependent and configurable.

• A client process terminates abnormally, causing the transaction to be implicitly rolled back using metadata stored in the transaction table and the undo segment.

As to your questions:

Could I wrap every method call in a transaction or should I use a transaction only when needed?

You can do either.

What is the overhead of an universal approach?

For each transaction then at the end of the transaction you MUST either COMMIT or ROLLBACK the transaction (either explicitly via the appropriate statement or implicitly via a DDL statement or terminating the connection). If you ROLLBACK then the transaction effectively never occurred and if you COMMIT then the data will be written from the undo/redo logs to the database files.

If you have multiple transactions each with individual statements then:

• You will have multiple small entries into the undo/redo logs (and the meta-data associated with the log entry will take up a greater proportion of the each log entry).
• You will have multiple COMMITs so there will be more instances of I/O on the database files.
• You cannot ROLLBACK the first statement after an error with the second statement as you will have COMMITted the first statement; if the statements are a chain of related activities then this could leave your business process in an invalid state if part of the chain is committed and the remainder is rolled-back.
• If the statements are unrelated activities that must be preserved then you may want to COMMIT after each one.

If you have a single transaction with multiple statements then:

• You will have one larger entry into the undo/redo log (and the meta-data associated with the log entry will take up a smaller proportion of the each log entry).
• You will have one COMMIT so there will be one instance of I/O on the database files.
• You can chain statements together and ROLLBACK the earlier statements after an error with a later statement; if the statements are a chain of related activities then this will allow you to retain the atomicity of the business process.

In general, you should group the statements into a transactions such that each transaction that is representative of a business process so that you can ROLLBACK the entire transaction and leave the business in a valid state. What a business process is and how many statements comprise it are something that is individual to each business process and you need to evaluate that to get the right atomicity for your business.

CodePudding user response：

It is important to keep atomicity. If Processing2 depends on the result of Processing1, please execute Processing1 and Processing2 in the same transaction. If Processing3 also depends on Processing2, please execute Processing1, Processing2, Processing3 in the same transaction. If something goes wrong, you can rollback the transaction. There is try-catch. If the data Processing1, Processing2, Processing3, Processing4 do not correlate with each other, you can execute several transactions.

I think you get the basic idea. It is also important not to keep transactions open for a long time. This approach is applicable not only to Oracle.