AnyDAC
ContentsIndexHome
PreviousUpNext
Multi Threading

Describes how to use AnyDAC in multi threading environment.

Group
Links
General

AnyDAC is thread-safe, when the following conditions are meat:

  • A connection object and all associated with it objects (like TADQuery, TADTransaction, etc) in each moment of time must be used by a single thread.
  • ADManager must be activated before threads will start by setting ADManager.Active to True.

Means, after a thread opened a query, and until its processing is not finished, application cannot use this query and connection objects in an other thread. Or, after a thread started a transaction, and until it is not finished, application cannot use this transaction and connection objects in an other thread. 

Practically this means an application must serialize access to a connection across all threads, and that is not a convenient technique. Breaking these rules may lead to misbehavior, AV's and errors like 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 database. In this case, no additional serialization is required. For example the following code performs a DB tasks in threads: 

 

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

procedure TDBThread.Execute;
var
  oConn: TADConnection;
  oPrc: TADQuery;
begin
  FreeOnTerminate := False;
  oConn := TADConnection.Create(nil);
  oConn.ConnectionDefName := 'Oracle_Pooled'; // see next section
  oPrc := TADStoredProc.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
  ADManager.Active := True;
  ...
  oThread1 := TDBThread.Create(False);
  oThread2 := TDBThread.Create(False);
  ...
  oThread1.WaitFor;
  oThread1.Free;
  oThread2.WaitFor;
  oThread2.Free;
end;

 

Note, for above case, where application runs a single SQL query in background, consider to use asynchronous query execution mode

Note, multi-threaded application may close connections opened in the background threads in a TADManager.BeforeShutdown event handler to avoid possible dead lock. 

 

Connection Pooling

One of the expensive database interaction operations is the connection establishment. In multi threading application, where each thread starts, establish connection, performs some short task and releases connection, the repetitive connection establishments may lead to performance degradation across all system. To avoid that the application may use the connection pooling. 

The connection pooling may be enabled only for persistent or private connection definition by setting Pooled=True. For example, persistent definition: 

 

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

 

Or 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');
  ADManager.AddConnectionDef('Oracle_Pooled', 'Ora', oParams);
  .....................
  ADConnection1.ConnectionDefName := 'Oracle_Pooled';
  ADConnection1.Connected := True;

 

None additional parameters may be specified in TADConnection.Params property, as all pooled connections must share the same connection parameters. 

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

 

ADManager.CloseConnectionDef('Oracle_Pooled');

 

or close the AnyDAC driver manager by calling: 

 

ADManager.Close;

 

Additional connection definition parameters may be specified to setup a pool:

Parameter 
Description 
Example 
POOL_CleanupTimeout 
The timeout (msecs) when AnyDAC will remove connections unused more than POOL_ExpireTimeout time. 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. Default value is 90000 msecs (90 secs). 
600000 
POOL_MaximumItems 
The maximum number of connection in the pool. When application will require more connections, then exception will be riased. Default value is 50. 
100 

For example see the AnyDAC\Samples\Comp Layer\TADConnection\Pooling demo.

What do you think about this topic? Send feedback!