TOraTransaction Component

TOraTransaction component can be used to manage either local or distributed (global) 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.

Global transactions can be performed on one or more sessions connected to the same or to the different databases. These sessions can be established from different applications and computers. On each of these sessions a separate branch of transaction is performed. Global transaction can be coordinated either by internal mechanism of TOraTransaction or Microsoft Transaction Manager DTC. This behavior can be tuned by GlobalCoordinator property. In case of using internal mechanism you should specify TransactionId and BranchQualifier for each session to identify global transaction. Global transaction can be managed using Commit, Rollback, Savepoint, RollbackToSavepoint, Detach and Resume methods of TOraTransaction. If an OraSession uses global transaction, it must have non-empty InternalName property. For more information about global transaction please refer to Oracle documentation.

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

Here is a sample code that starts and commits global transaction:

var
Id: TBytes;
begin
  OraSession1.InternalName := 'SampleName1';
  OraSession2.InternalName := 'SampleName2';
 
  OraSession1.Connect;
  OraSession2.Connect;

  SetLength(Id, 2);
  id[0] := 7; id[1] := 3;
  OraTransaction.TransactionId := Id;

  SetLength(Id, 1);
  id[0] := 1;
  OraTransaction.AddSession(OraSession1, Id);
  id[0] := 2;
  OraTransaction.AddSession(OraSession2, Id);

  OraTransaction.StartTransaction;

  OraSQL1.Session := OraSession1;
  OraSQL2.Session := OraSession2;
  OraSQL1.Execute;
  OraSQL2.Execute;

  OraTransaction.Commit;
end;

The following example demonstrates a global transaction with two branches created from different applications. After performing update these applications detach their transaction branches. Then third application (transaction manager) resumes all branches of the transaction and performs two-phase commit.

// Appication 1
var
  Id: TBytes;
begin
  OraSession.InternalName := 'SampleName1';
  OraSession.Connect;

  SetLength(Id, 2);
  id[0] := 7; id[1] := 3;
  OraTransaction.TransactionId := Id;

  SetLength(Id, 1);
  id[0] := 1;
  OraTransaction.AddSession(OraSession, Id);

  OraTransaction.StartTransaction;

  OraSQL.Execute;

  OraTransaction.Detach;
end;

// Appication 2
var
  Id: TBytes;
begin
  OraSession.InternalName := 'SampleName2';
  OraSession.Connect;

  SetLength(Id, 2);
  id[0] := 7; id[1] := 3;
  OraTransaction.TransactionId := Id;

  SetLength(Id, 1);
  id[0] := 2;
  OraTransaction.AddSession(OraSession, Id);

  OraTransaction.StartTransaction;

  OraSQL.Execute;

  OraTransaction.Detach;
end;

// Application 3 (transaction manager that commits transaction)
var
  Id: TBytes;
begin
  OraSession1.Connect;
  OraSession2.Connect;

  SetLength(Id, 2);
  id[0] := 7; id[1] := 3;
  OraTransaction.TransactionId := Id;

  SetLength(Id, 1);
  id[0] := 1;
  OraTransaction.AddSession(OraSession1, Id);
  id[0] := 2;
  OraTransaction.AddSession(OraSession2, Id);

  OraTransaction.Resume;

  OraTransaction.Commit;
end;

The next example demonstrates using distributed transaction coordinated by MTS DTC:

begin
  OraSession1.InternalName := 'SampleName1';
  OraSession2.InternalName := 'SampleName2';
 
  OraSession1.Connect;
  OraSession2.Connect;

  OraTransaction.GlobalCoordinator := gcMTS;

  OraTransaction.AddSession(OraSession1);
  OraTransaction.AddSession(OraSession2);

  OraTransaction.StartTransaction;

  OraSQL1.Session := OraSession1;
  OraSQL2.Session := OraSession2;
  OraSQL1.Execute;
  OraSQL2.Execute;

  OraTransaction.Commit;
end;