Multithreading (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Working with Connections (FireDAC)


This topic describes how to use FireDAC in a multithreaded environment.

General

FireDAC is thread-safe if the following conditions are met:

  • A connection object and all objects associated with it (such as TFDQuery, TFDTransaction, and so on) are used by a single thread at each moment.
  • FDManager is activated before threads start, by setting FDManager.Active to True.

This means that after a thread opens a query and until its processing is finished, the application cannot use this query and the connection objects in another thread. Similarly, after a thread starts a transaction and until the transaction is finished, the application cannot use this transaction and the connection objects in another thread.

This practically means that an application must serialize access to a connection across all threads, which is not a convenient technique. Breaking these rules may lead to misbehavior, AV errors, and other errors, such as the SQL Server error "Connection is busy with results for another command".

The standard simplification is to create and use for each thread a dedicated connection object working with the database. In this case, no additional serialization is required. For example, the following code performs DB tasks in threads:

type
  TDBThread = class(TThread)
  protected
    procedure Execute; override;
  end;

procedure TDBThread.Execute;
var
  oConn: TFDConnection;
  oPrc: TFDQuery;
begin
  FreeOnTerminate := False;
  oConn := TFDConnection.Create(nil);
  oConn.ConnectionDefName := 'Oracle_Pooled'; // see next section
  oPrc := TFDStoredProc.Create(nil);
  oPrc.Connection := oConn;
  try
    oConn.Connected := True;
    oPrc.StoredProcName := 'MY_LONG_RUNNING_PROC';
    oPrc.ExecProc;
  finally
    oPrc.Free;
    oConn.Free;
  end;
end;

// main application code
var
  oThread1, oThread2: TDBThread;
begin
  FDManager.Active := True;
  ...
  oThread1 := TDBThread.Create(False);
  oThread2 := TDBThread.Create(False);
  ...
  oThread1.WaitFor;
  oThread1.Free;
  oThread2.WaitFor;
  oThread2.Free;
end;

Note: For the above case, where the application runs a single SQL query in the background, use the asynchronous query execution mode.

Note: A multithreaded application may close the connections opened in the background threads in a TFDManager.BeforeShutdown event handler to avoid possible dead lock.

Connection Pooling

One of the expensive database interaction operations is the connection establishment. In a multithreaded application, where each thread starts, establishes a connection, performs a certain short task and releases the connection, the repetitive connection establishments may lead to performance degradation across the whole system. To avoid this, the application can use the connection pooling.

The connection pooling can be enabled only for a persistent or private connection definition by setting Pooled=True. For a persistent definition:

[Oracle_Pooled]
DriverID=Ora
Database=ORA_920_APP
User_Name=ADDemo
Password=a
Pooled=True

or for a private definition setup:

var
  oParams: TStrings;
begin
  oParams := TStringList.Create;
  oParams.Add('Database=ORA_920_APP');
  oParams.Add('User_Name=ADDemo');
  oParams.Add('Password=a');
  oParams.Add('Pooled=True');
  FDManager.AddConnectionDef('Oracle_Pooled', 'Ora', oParams);
  .....................
  FDConnection1.ConnectionDefName := 'Oracle_Pooled';
  FDConnection1.Connected := True;

No additional parameters can be specified in the TFDConnection.Params property, because all pooled connections must share the same connection parameters.

Setting TFDConnection.Connected to True acquires a physical connection from the pool. Setting TFDConnection.Connected to False releases the physical connection to the pool, but keeps the connection opened. To close and destroy all pooled physical connections, the application can call the TFDManager.CloseConnectionDef method:

FDManager.CloseConnectionDef('Oracle_Pooled');

or close the FireDAC driver manager by calling:

FDManager.Close;


Additional connection definition parameters can be specified to set up a pool:

Parameter Parameter Example
POOL_CleanupTimeout The time (msecs) until FireDAC removes the connections that have not been used for longer than the POOL_ExpireTimeout time. The default value is 30000 msecs (30 secs). 3600000
POOL_ExpireTimeout The time (msecs) after which the inactive connection may be deleted from the pool and destroyed. The default value is 90000 msecs (90 secs). 600000
POOL_MaximumItems The maximum number of connections in the pool. When the application requires more connections, then an exception is raised. The default value is 50. 100

See Also

Samples