Transactions

This topic describes how transaction support is implemented in UniDAC. So, you should be pretty familiar with transactions to understand how to control them with UniDAC.

The local transactions are managed by the TUniConnection component with StartTransaction, Commit, Rollback, and other methods. Each time you are about to start a transaction, you should check whether it is active. You can do this using the InTransaction property. Call to StartTransaction when the transaction is already active will cause an exception. Here is a short example that demonstrates the general approach for working with local transactions:

if not UniConnection.InTransaction then
     UniConnection.StartTransaction;
   try
     // Do some actions with database. For example:
     UniSQL1.Execute;
     UniSQL2.Execute;
 
     // Commit the current transaction to reflect changes in database if no errors were raised
     UniConnection.Commit;
   except
     // Rollback all changes in database made after StartTransaction if an error was raised
     UniConnection.Rollback;
   end;

After you have activated a transaction, all operations, including dataset opening, will be performed within the context of the current transaction until you commit or rollback it. If no transactions were started, changes performed by each operation are reflected in database right after the operation is completed (so-called AutoCommit mode). When using InterBase provider, please pay attention to the AutoCommit property. The AutoCommit property has the True value by default that leads to automatically execution of CommitRetaining or RollbackRetaining when there is any data modification. By setting the property to False, you will get rid of this behavior, however, you will have to manage the transactions by yourself. The TUniConnection.AutoCommit property has a higher priority than the specific option "AutoCommit" of datasets (TUniQuery, TUniTable). If the TUniConnection.AutoCommit property is set to False, all transactions can be committed only explicitly (despite of the specific option "AutoCommit" value of a dataset). If you want most datasets to automatically commit transactions, and for some of them to control transactions manually, you should set the TUniConnection.AutoCommit property to True, and only for datasets with manual transaction control, set the specific option "AutoCommit" value to False.

The behaviour of each explicitly started transaction can be customized with parameters passed to the overloaded StartTransaction method. You can specify the isolation level for the transaction and whether this transaction will be editable. There is a more detailed description of these parameters in the StartTransaction topic.

UniDAC also supports working with Savepoints. The Savepoint method lets you to define a named savepoint within a transaction. You can use the savepoint name in the RollbackToSavepoint method to rollback changes in the database to the actual state at the point of time the savepoint was made. Call to RollbackToSavepoint keeps the current transaction active.

The CommitRetaining and RollbackRetaining methods are similar to Commit and Rollback, but they keep the current transaction active. It means that you will not need to call StartTransaction to keep working in transaction like you do with the Commit and Rollback methods. Functionality of CommitRetaining and RollbackRetaining is supported by InterBase/Firebird/Yaffil servers. For other servers this functionality is emulated by subsequent call to StartTransaction after Commit or Rollback.

InterBase-like servers support several simultaneous active transactions within a single connection and require a transaction to be active when opening a cursor. You should not take care of this, as UniDAC encapsulates these peculiarities letting you work in a way similar to the way of working with other database servers. If you want to involve abilities of InterBase servers to run parallel transactions, you should place several TUniTransaction components onto the form and setup properties of TCustomUniDataSet descendants such as Transaction and UpdateTransaction with these components. The Transaction and UpdateTransaction properties are used only for the InterBase provider. For other providers these properties are ignored.

UniDAC uses MTS to manage distributed transactions with Oracle and Microsoft SQL Server connections. Distributed transactions are controlled by the TUniTransaction component. You can add connections to a distributed transaction context using the AddConnection method. The MTS distributed transaction coordinator allows mixing connections both to different servers and different server kinds.

begin
  UniConnection1.Connect;
  UniConnection2.Connect;
 
  UniTransaction.AddConnection(UniConnection1);
  UniTransaction.AddConnection(UniConnection2);
 
  UniTransaction.StartTransaction;
 
  UniSQL1.Connection := UniConnection1;
 
  UniSQL2.Connection := UniConnection2;
  try
    UniSQL1.Execute;
    UniSQL2.Execute;
    UniTransaction.Commit;
  except
    UniTransaction.Rollback;
  end;
end;