Go Up to Working with Connections (FireDAC)
This topic describes how to use FireDAC in a multithreaded environment.
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.
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:
or close the FireDAC driver manager by calling:
Additional connection definition parameters can be specified to set up a pool:
|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|