By default the AnyDAC application is working in auto-commit mode, where a transaction is automatically started by AnyDAC when it is required, and is committed on the successful command execution or rolled back on a failure. The auto-commit is controlled by the TADTxOptions.AutoCommit property. The auto-commit mode is simple to use for the application, but it:

• slowdowns multiple updates to a database;
• do not allow to perform few database operation in a single transaction;
• cannot be prolonged in a time.

Alternatively the application may use the explicit transaction control. For that use the TADConnection methods StartTransaction, Commit, Rollback. Or to use the TADTransaction component. Note, the TADTransaction component usage is optional in AnyDAC. 

AnyDAC offers the Isolation, ReadOnly, Params properties allowing to control the transaction mode. They apply as to auto-commit as to explicit transactions. Not all modes may be supported by DBMS, for example read-only mode, then AnyDAC will use less restrictive mode. Note, that all settings will be applied to the next transactions only. 

The standard code using explicit transaction looks like: 

ADConnection1.StartTransaction;
try
  ADQuery1.ExecSQL;
  ....
  ADQuery1.ExecSQL;
  ADConnection1.Commit;
except
  ADConnection1.Rollback;
  raise;
end;

The TADTransaction component wraps the transaction control functionality into a component. Practically it offers the same transaction functionality as TADConnection, but allows to group commands and datasets by linking them to a specific transaction object. At first that refers to the multiple active transactions support, that is Firebird / Interbase servers feature. 

The TADTxOptions.Params may be used to specify DBMS specific transaction attributes. At moment only Firebird / Interbase driver supports such attributes. Each attribute must be specified on a separate line. It will correspond to isc_tpb_<attribute name> transaction parameters. For additional information read:

• (on English) Transactions in InterBase/Firebird: how to use them in FIBPlus, (c) DevRace Software Development
• (on Russian) Транзакции в InterBase и Firebird, (c) Кузьменко Дмитрий, www.ibase.ru.

Although none of the supported DBMS's supports the nested transactions, they are emulated by AnyDAC using the savepoints. Means, the nested StartTransaction call will not start a new transaction and will not raise an exception, but will put a savepoint. If a DBMS does not support savepoints, then "Capability is not supported" exception will be raised. 

To disable nested transaction set TADTxOptions.EnableNested to False. Then nesting StartTransaction call will raise an exception. 

Corresponding Commit call releases a savepoint and Rollback rolls back to a savepoint. For example: 

// start new transaction
ADConnection1.StartTransaction;
try
  ADQuery1.ExecSQL;
  ....

  // set savepoint
  ADConnection1.StartTransaction;
  try
    ADQuery1.ExecSQL;
    ....

    // release savepoint
    ADConnection1.Commit;
  except
    // rollback to savepoint
    ADConnection1.Rollback;
    raise;
  end;

  // commit transaction
  ADConnection1.Commit;

except
  // rollback transaction
  ADConnection1.Rollback;
  raise;
end;

Note, that nested transaction will use the settings of the most top transaction. 

The CommitRetaining and RollbackRetaining methods are similar to the Commit and Rollback methods, but they do not finish the transaction. So, it remains active after these calls. The Firebird / Interbase servers are supporting this feature on the DBMS core level. For all other DBMS's this feature is emulated using the Commit / Rollback and StartTransaction calls. 

The Firebird / Interbase server supports multiple active transactions on the DBMS core level. That means, that some commands may be performed in one transaction context, others in the second transaction context, etc. To support this feature AnyDAC offers TADTransaction component. It single instance allows to handle single transaction in each moment of the time. 

The TADCustomConnection properties may be used to setup default transaction objects:

• Transaction - the default transaction object for all commands;
• UpdateTransaction - the default transaction for all update commands, used to post updates from the AnyDAC datasets. Note, the UpdateTransaction will not be used, for example, for a UPDATE query explicitly specified for TADQuery component.

The TADCustomQuery and other components have similar properties:

• Transaction - the explicit transaction object to use to execute the SQL query;
• UpdateTransaction - the explicit transaction object to use to post updates from the dataset.

In general, the good practice to setup transaction objects for Firebird / Interbase application: 

UpdateTransaction: TADTransaction;
ReadTransaction: TADTransaction;
...
// setup transaction for updating commands: read_committed, rec_version, nowait
UpdateTransaction.Connection := ADConnection1;
ADConnection1.UpdateOptions.LockWait := False;
UpdateTransaction.Options.ReadOnly := False;
UpdateTransaction.Options.Isolation := xiReadCommitted;
...
ReadTransaction.Connection := ADConnection1;
ReadTransaction.Options.ReadOnly := True;
ReadTransaction.Options.Isolation := xiReadCommitted;
...
SelectQuery.Transaction := ReadTransaction;
SelectQuery.UpdateTransaction := UpdateTransaction;

Note, you can use few TADTransaction's for other DBMS's. Then all TADTransaction's will share the same transaction. 

A DBMS associates an open cursor with the transaction context, where it was opened. When the transaction finishes, the DBMS may invalidate the active cursors. The exact behavior depends on the DBMS:

When AnyDAC discovers a transaction control command that will lead to the cursor invalidation, AnyDAC performs an actions specified by the FetchOptions.AutoFetchAll and releases the cursor. 

Note, the Firebird / Interbase servers are invalidating the prepared statement on the transaction finish. So, the auto-commit mode may lead to performance degradation on these DBMS's.

TADCustomConnection, TADCustomTransaction, Executing Command