3.5.1. TFDTransaction Component

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 2. TFDTransaction component main properties
PropertyPurpose

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.