Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Firebird Applications in Delphi → Working with Transactions
Firebird Home Firebird Home Prev: Connection parametersFirebird Documentation IndexUp: Developing Firebird Applications in DelphiNext: Datasets

Working with Transactions

Table of Contents

TFDTransaction Component

The Firebird client allows any operations to be made only in the context of a transaction so, if you manage to access data without explicitly calling TFDTransaction.StartTransaction, it means that it was called automatically somewhere deep in FireDac. It is highly recommended to avoid this practice. For applications to work correctly with databases, it is advisable to manage transactions manually, which means starting and committing them or rolling them back with explicit calls.

The TFDTransaction component is used to manage transactions explicitly.

TFDTransaction Component

Table of Contents

Multiple Transactions

TFDTransaction has three methods for managing a transaction explicitly: StartTransaction, Commit and Rollback. The following table summarises the properties available to configure this component.

Table 3.2. TFDTransaction component main properties

Property Purpose
Connection Reference to the FDConnection component
Options.AutoCommit Controls the automatic start and end of a transaction, emulating Firebird's own transaction management. The default value is True. See note (1) below for more details about behaviour if the Autocommit option is True.
Options.AutoStart Controls the automatic start of a transaction. The default value is True.
Options.AutoStop Controls the automatic end of a transaction. The default value is True.
Options.DisconnectAction The action that will be performed when the connection is closed while the transaction is active. The default value is xdCommit—the transaction will be committed. See note (2) below for details of the other options.
Options.EnableNested Controls nested transactions. The default value is True. Firebird does not support nested transactions as such but FireDac can emulate them using savepoints. For more details, see note(3) below.
Options.Isolation Specifies the transaction isolation level. It is the most important transaction property. The default value is xiReadCommitted. The other values that Firebird supports are xiSnapshot and xiUnspecified; also xiSerializable, to some degree. For more details about the available isolation levels, see note (4) below.
Options.Params Firebird-specific transaction attributes that can be applied to refine the transaction parameters, overriding attributes applied by the standard implementation of the selected isolation level. For the attributes that can be set and the “legal” combinations, see note (5) below.
Options.ReadOnly Indicates whether it is a read-only transaction. The default value is False. Setting it to True disables any write activity. Long-running read-only transactions in READ COMMITTED isolation are recommended for activities that do not change anything in the database because they use fewer resources and do not interfere with garbage collection.

Note 1: AutoCommit=True

If the value of AutoCommit is set to True, FireDAC behaves as follows:

  • Starts a transaction (if required) before each SQL command and ends the transaction after the SQL command completes execution
  • If the command is successfully executed, the transaction will be ended by COMMIT. Otherwise, it will be ended by ROLLBACK.
  • If the application calls the StartTransaction method, automatic transaction management will be disabled until that transaction is ended by Commit or Rollback.

Note 2: DisconnectAction

The following values are possible:

  • xdNone—nothing will be done. The DBMS will perform its default action.
  • xdCommit—the transaction will be committed
  • xdRollback—the transaction will be rolled back

Note that, in some other data access components, the default value for the DisconnectAction property is xdRollback and will need to be set manually with Firebird to match the FDTransaction setting.

Note 3: EnableNested

If StartTransaction is called from within an active transaction, FireDac will emulate a nested transaction by creating a savepoint. Unless you are very confident in the effect of enabling nested transactions, set EnableNested to False. With this setting, calling StartTransaction inside the transaction will raise an exception.

Note 4: Isolation

FireBird has three isolation levels: READ COMMITTED, SNAPSHOT (“concurrency”) and SNAPSHOT TABLE STABILITY (“consistency”, rarely used). FireDac supports some but not all configurations for READ COMMITTED and SNAPSHOT. It uses the third level partially to emulate the SERIALIZABLE isolation that Firebird does not support.

  • xiReadCommitted—the READ COMMITTED isolation level. FireDac starts ReadCommitted transactions in Firebird with the following parameters: read/write, rec_version, nowait
  • xiSnapshot—the SNAPSHOT (concurrency) isolation level. FireDac starts Snapshot transactions in Firebird with the following parameters: read/write, wait
  • xiUnspecified—Firebird's default isolation level (SNAPSHOT) with the following parameters: read/write, wait
  • xiSerializable—the SERIALIZABLE isolation level. Firebird does not support serializable isolation, but FireDac emulates it by starting a SNAPSHOT TABLE STABILITY (“consistency”) transaction with the following parameters: read/write, wait.

Other parameters, not supported by Firebird at all, are:

  • xiDirtyRead—if this is selected (not a good idea!) READ COMMITTED will be used instead
  • xiRepeatableRead—if this is selected, SNAPSHOT will be used instead

Note 5: Firebird-specific Transaction Attributes

Attributes that can be customised in Options.Params are:

  • read write, the default read mode for all of the options.isolation selections—see note (4) above. Set write off if you want read-only mode. Alternatively, you can set Options.ReadOnly to True to achieve the same thing. There is no such thing as a “write-only” transaction.
  • read_committed, concurrency and consistency are isolation levels.
  • wait and nowait are conflict resolution settings, determining whether the transaction is to wait for a conflict to resolve
  • rec_version and no rec_version provide an option that is applicable only to READ COMMITTED transactions. The default rec_version lets this transaction read the latest committed version of a record and overwrite it if the transaction ID of the latest committed version is newer (higher) than the ID of this transaction. The no rec_version setting will block this transaction from reading the latest committed version if an update is pending from any other transaction.

Multiple Transactions

Unlike many other DBMSs, Firebird allows as many TFDTransaction objects as you need to associate with the same connection. In our application, we will use one common read transaction for all primary and secondary modules and one read/write transaction for each dataset.

We do not want to rely on starting and ending transactions automatically: we want to have full control. That is why Options.AutoCommit=False, Options.AutoStart=False and Options.AutoStop=False are set in all of our transactions.

Prev: Connection parametersFirebird Documentation IndexUp: Developing Firebird Applications in DelphiNext: Datasets
Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Firebird Applications in Delphi → Working with Transactions