How to set transaction LOCK TIMEOUT for Firebird (InterBase) using FireDAC?

Question

The TFDConnection seems to have no setting for the LOCK TIMEOUT when using

TFDConnection.UpdateOptions.LockMode := lmPessimistic;
TFDConnection.UpdateOptions.LockWait := true;

A TFDTransaction also does not seem to have properties for it.

The Firebird SET TRANSACTION documentation suggests that you can set LOCK TIMEOUT.

Is there something I'm overlooking for setting the timeout property, or do I have to resort to executing a SET TRANSACTION LOCK TIMEOUT after the opening the connection?
If I dig around in the FireDAC.Phys.IB*.pas files I see references to isc_tpb_lock_timeout but no way to use them?

Jan Doggen · Accepted Answer · 2017-12-12T13:29:33.547

This can be defined through the IBAdvanced parameter (see Connect to Interbase (FireDAC)):

The ';' separated list of additional parameters. You can find the full list of the supported parameters in the FireDAC.Phys.IBWrapper unit (see the DPBInfos constant array). You can find the description of each Code listed there in the Interbase manuals.

This is the section in FireDAC.Phys.IBWrapper that defines them^*:

const
  TRPBInfo_Items = 24;
  TRPBInfos: array[1 .. TRPBInfo_Items] of TIBPBInfo = (
    (Name: 'consistency';      ParamType: prNoneNoZ; Code: isc_tpb_consistency),
    (Name: 'concurrency';      ParamType: prNoneNoZ; Code: isc_tpb_concurrency),
    (Name: 'shared';           ParamType: prNoneNoZ; Code: isc_tpb_shared),
    (Name: 'protected';        ParamType: prNoneNoZ; Code: isc_tpb_protected),
    (Name: 'exclusive';        ParamType: prNoneNoZ; Code: isc_tpb_exclusive),
    (Name: 'wait';             ParamType: prNoneNoZ; Code: isc_tpb_wait),
    (Name: 'nowait';           ParamType: prNoneNoZ; Code: isc_tpb_nowait),
    (Name: 'read';             ParamType: prNoneNoZ; Code: isc_tpb_read),
    (Name: 'write';            ParamType: prNoneNoZ; Code: isc_tpb_write),
    (Name: 'lock_read';        ParamType: prStrg;    Code: isc_tpb_lock_read),
    (Name: 'lock_write';       ParamType: prStrg;    Code: isc_tpb_lock_write),
    (Name: 'verb_time';        ParamType: prNoneNoZ; Code: isc_tpb_verb_time),
    (Name: 'commit_time';      ParamType: prNoneNoZ; Code: isc_tpb_commit_time),
    (Name: 'ignore_limbo';     ParamType: prNoneNoZ; Code: isc_tpb_ignore_limbo),
    (Name: 'read_committed';   ParamType: prNoneNoZ; Code: isc_tpb_read_committed),
    (Name: 'autocommit';       ParamType: prNoneNoZ; Code: isc_tpb_autocommit),
    (Name: 'rec_version';      ParamType: prNoneNoZ; Code: isc_tpb_rec_version),
    (Name: 'no_rec_version';   ParamType: prNoneNoZ; Code: isc_tpb_no_rec_version),
    (Name: 'restart_requests'; ParamType: prNoneNoZ; Code: isc_tpb_restart_requests),
    (Name: 'no_auto_undo';     ParamType: prNoneNoZ; Code: isc_tpb_no_auto_undo),
    // IB75
    (Name: 'no_savepoint';     ParamType: prNoneNoZ; Code: isc_tpb_no_savepoint),
    // FB20
    (Name: 'lock_timeout';     ParamType: prCard;    Code: isc_tpb_lock_timeout),
    // IB2017
    (Name: 'exclusivity';      ParamType: prNoneNoZ; Code: isc_tpb_exclusive),
    (Name: 'wait_time';        ParamType: prCard;    Code: isc_tpb_wait_time)
   );

So, for e.g. a 5 second timeout:

TFDConnection.Params.Add('IBAdvanced=lock_timeout=5');

^{* Note that the documentation states that they are in DPBInfos, but they are in TRPBInfos.}

How to set transaction LOCK TIMEOUT for Firebird (InterBase) using FireDAC?

1 Answers1