Managing Transactions (FireDAC)
Go Up to Working with Connections (FireDAC)
Describes how to manage DBMS transactions using FireDAC. FireDAC provides the TFDConnection and TFDTransaction components to help you handle database transactions .
Contents
General
By default the FireDAC application works in auto-commit mode, where a transaction is automatically started by FireDAC 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 TFDTxOptions.AutoCommit property. The auto-commit mode is simple to use for the application, but it:
- slows down multiple updates to a database.
- does not allow you to perform more database operations in a single transaction.
- cannot be prolonged in a time.
Alternatively, the application can use the explicit transaction control. For this, use the TFDConnection methods StartTransaction, Commit, Rollback. An alternative to this is the use of the TFDTransaction component.
Note: The use of the TFDTransaction component is optional in FireDAC.
FireDAC provides the Isolation, ReadOnly, Params properties, allowing you to control the transaction mode. They apply to both auto-commit and explicit transactions. Not all modes are supported by the DBMS, for example the read-only mode, because FireDAC uses a less restrictive mode. Note that all settings will be applied only to the next transactions.
The standard code using explicit transaction is as follows:
FDConnection1.StartTransaction;
try
FDQuery1.ExecSQL;
....
FDQuery1.ExecSQL;
FDConnection1.Commit;
except
FDConnection1.Rollback;
raise;
end;
The TFDTransaction component wraps the transaction control functionality into a component. Practically, it offers the same transaction functionality as TFDConnection, but allows you to group commands and datasets by linking them to a specific transaction object. At first, this refers to the multiple active transactions support, that is the Interbase and Firebird server features.
TFDTxOptions.Params can be used to specify DBMS specific transaction attributes. For the moment, only the Firebird / InterBase driver supports such attributes. Each attribute must be specified on a separate line. It corresponds to the isc_tpb_<attribute name> transaction parameters.
Nested Transactions
Although none of the supported database management systems supports the nested transactions, they are emulated by FireDAC using savepoints. This means that the nested StartTransaction call does not start a new transaction and does not raise an exception, but puts a savepoint. If a DBMS does not support savepoints, then the "Capability is not supported" exception is raised.
To disable a nested transaction, set TFDTxOptions.EnableNested to False. Then, the nesting StartTransaction call raises an exception.
The corresponding Commit call releases a savepoint and Rollback rolls back to a savepoint. For example:
// start new transaction
FDConnection1.StartTransaction;
try
FDQuery1.ExecSQL;
....
// set savepoint
FDConnection1.StartTransaction;
try
FDQuery1.ExecSQL;
....
// release savepoint
FDConnection1.Commit;
except
// rollback to savepoint
FDConnection1.Rollback;
raise;
end;
// commit transaction
FDConnection1.Commit;
except
// rollback transaction
FDConnection1.Rollback;
raise;
end;
Note: A nested transaction uses the settings of the topmost transaction.
Continuous Transactions
The CommitRetaining and RollbackRetaining methods are similar to the Commit and Rollback methods, but they do not finish the transaction. Therefore, it remains active after these calls. The Firebird / InterBase servers support this feature on the DBMS core level. For all other database management systems, this feature is emulated using the Commit / Rollback and StartTransaction calls.
Multiple Active Transactions
Firebird and InterBase support multiple active transactions on the DBMS core level. This means that some commands may be performed in one transaction context, others in the second transaction context, and so on. To support this feature, FireDAC provides the TFDTransaction component. Its single instance allows you to handle single transactions at any given moment.
The TFDCustomConnection properties can be used to set up default transaction objects:
- Transaction - the default transaction object for all commands.
- UpdateTransaction - the default transaction object for all update commands, used to post updates from the FireDAC datasets. Note that the UpdateTransaction will not be used, for example, for a UPDATE query explicitly specified for the TFDQuery component.
The TFDCustomQuery 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 best way to set up transaction objects for Firebird / InterBase application is as follows:
UpdateTransaction: TFDTransaction;
ReadTransaction: TFDTransaction;
...
// setup transaction for updating commands: read_committed, rec_version, nowait
UpdateTransaction.Connection := FDConnection1;
FDConnection1.UpdateOptions.LockWait := False;
UpdateTransaction.Options.ReadOnly := False;
UpdateTransaction.Options.Isolation := xiReadCommitted;
...
ReadTransaction.Connection := FDConnection1;
ReadTransaction.Options.ReadOnly := True;
ReadTransaction.Options.Isolation := xiReadCommitted;
...
SelectQuery.Transaction := ReadTransaction;
SelectQuery.UpdateTransaction := UpdateTransaction;
Note: You can use more than one TFDTransaction for other database management systems. Then, all TFDTransaction components share the same transaction.
Transactions and Cursors
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:
| DBMS | Action |
|---|---|
| Microsoft Access | Invalidates a cursor on StartTransaction / Commit / Rollback. |
| Firebird | Invalidates a cursor on Commit / Rollback. |
| Informix | <nothing> |
| InterBase | Invalidates a cursor on Commit / Rollback. |
| IBM DB2 | Invalidates a cursor on Rollback. |
| MySQL | Invalidates a cursor on StartTransaction / Commit / Rollback. |
| Oracle | <nothing> |
| PostgreSQL | Invalidates a cursor on Commit / Rollback. |
| SQLite | Invalidates a cursor on Rollback. |
| SQL Anywhere | <nothing> |
| SQL Server | Invalidates a cursor on StartTransaction / Commit / Rollback. |
| Teradata Database | <nothing> |
When FireDAC discovers a transaction control command that leads to the cursor invalidation, it performs an actions specified by FetchOptions.AutoFetchAll and releases the cursor.
Note: The Firebird / InterBase servers are invalidating the prepared statement on the transaction finish. Therefore, the auto-commit mode may lead to performance degradation on these database management systems.