Using Transactions

Understanding Transactions

A transaction is one or several operations considered as a single unit of work which is completed entirely or have no effect at all ("all-or-nothing"). If a failure occurs at one point in the transaction, all of the updates can be rolled back to their pre-transaction state. A transaction must conform to the ACID properties - atomicity, consistency, isolation, and durability-in order to guarantee data consistency.

If a transaction involves multiple tables in the same database, then explicit transactions in PL/SQL often perform better. You can use COMMIT and ROLLBACK statements in your SQL to fix and discard respectively the previous commands in your current PL/SQL block. For more information, see Oracle PL/SQL documentation.

Otherwise, a transaction with plain SQL can be implemented via TOraTransaction TOraSession classes. For example, you can use TOraSession: start transaction on TOraSession, execute several SQL statements via TOraDataSet, and commit/rollback all operations when it is necessary. See the sample from the Local Transaction topic.

This article describes the way to manipulate transactions from your application (without envolving PL/SQL transactions) - this is the most common case of working with transactions. Concerning your task, you can choose the type of transaction to implement - local or distributed. A transaction considered to be a local transaction when it is a single-phase transaction and is handled by the database directly. A distributed transaction is a transaction that affects several resources, it is coordinated by a transaction monitor and uses fail-safe mechanisms (such as two-phase commit) for transaction resolution.

Note: transaction will be global if either TransactionId or TransactionName property is set or if GlobalCoordinator property is gcMTS.

Local Transactions

To start local transaction with TOraTransaction component, set DefaultSession property of the component to a session on which transaction will be performed. Set IsolationLevel property optionally. Then call StartTransaction method of the TOraTransaction component. To manage transaction use Commit, Rollback, Savepoint, RollbackToSavepoint methods.

[Delphi]

var
  OraSession: TOraSession;
  OraTransaction: TOraTransaction;
begin
  OraSession := TOraSession.Create(nil);
  OraTransaction := TOraTransaction.Create(nil);
  try
    OraSession.ConnectString := 'login/password@SID';
    OraSession.Connect;
    OraTransaction.AddSession(OraSession);
    OraTransaction.IsolationLevel := ilReadCommitted;
    OraTransaction.StartTransaction;
    try
      OraSession.ExecSQL('INSERT INTO Dept(DeptNo, DName) Values(50, ''DEVELOPMENT'')');
      OraSession.ExecSQL('INSERT INTO Dept(DeptNo, DName) Values(60, ''PRODUCTION'')');
      OraTransaction.Commit;
      ShowMessage('Both records are written to database.');
    except
      OraTransaction.Rollback;
      ShowMessage('Neither record was written to database.');
    end;
  finally
    OraTransaction.Free;
    OraSession.Free;
  end;
end;

[C++ Builder]

  TOraSession *OraSession = new TOraSession(NULL);
  TOraTransaction *OraTransaction = new TOraTransaction(NULL);
  try
  {
	OraSession->ConnectString = "SCOTT/TIGER@ORCL1020";
	OraSession->Connect();
	OraTransaction->AddSession(OraSession);
	OraTransaction->IsolationLevel = ilReadCommitted;
	OraTransaction->StartTransaction();
	try
	{
	  OraSession->ExecSQL("INSERT INTO Dept(DeptNo, DName) Values(50, 'DEVELOPMENT')");
	  OraSession->ExecSQL("INSERT INTO Dept(DeptNo, DName) Values(60, 'PRODUCTION')");
	  ShowMessage("Both records are written to database.");
	}
	catch(const Exception& e)
	{
	  OraTransaction->Rollback();
	  ShowMessage("Neither record was written to database.");
	}
  }
  __finally
  {
	OraSession->Disconnect();
	OraTransaction->Free();
	OraSession->Free();
  }