Describes how to use AnyDAC in multi threading environment.
AnyDAC is thread-safe, when the following conditions are meat:
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.
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!
|