Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Thread safety of Firedac


This question is answered.


Permlink Replies: 6 - Last Post: Feb 10, 2015 5:00 AM Last Post By: koen Barbier
koen Barbier

Posts: 8
Registered: 6/12/00
Thread safety of Firedac  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 5, 2015 11:44 PM
Hi,

We want to use firedac in a multithreaded environment, but we are not sure how to interprete the multithreading documentation in http://docwiki.embarcadero.com/RADStudio/XE6/en/Multithreading_%28FireDAC%29 :

Is it thread safe to use a TFDConnection over multiple threads as long as no threads use the connection simultaneously, or not? A similar question for a TFDQuery for example.

To be specific, I am wondering if following cases are thread safe:
1) a thread creates a connection and uses its connection, but does not close&remove this connection. Another thread serves to find these dangling connection, and closes&removes the connection (when the first thread is no longer using this connection)
2) Same as 1), but also a TFDQuery remained open, and this Query is closes/freed from another thread, when it is no longer used
3) a thread creates a connection and TFDQuery, and after this connection/TFDQuery is no longer used by this thread, another thread starts using this TFDQuery and TFDConnection.

(these situations might not be implemented for real, but we are wondering how thread safe firedac is)

Thanks in advance!
Clement Doss

Posts: 133
Registered: 9/19/00
Re: Thread safety of Firedac  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 6, 2015 7:56 AM   in response to: koen Barbier in response to: koen Barbier
koen Barbier wrote:
Hi,

We want to use firedac in a multithreaded environment, but we are not sure how to interprete the multithreading documentation in http://docwiki.embarcadero.com/RADStudio/XE6/en/Multithreading_%28FireDAC%29 :

Hi,

This is what I did.
I'm using FDManager. This component will manage your thread safe environment.
Each thread must have it's own FDConnection that will use FDManager to get a connection.

From the example, in the main application you should have this code:
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');  // This is *very* important
  FDManager.AddConnectionDef('Oracle_Pooled', 'Ora', oParams); 


Now FDManager can serve your application threads.

Each thread should use the following pattern to be thread safe:

type
  TDBThread = class(TThread)
  protected
    procedure Execute; override;
  end;
 
procedure TDBThread.Execute;
var
  oConn: TFDConnection;  // only be available in this thread
  oPrc: TFDQuery; // only be available in this thread
begin
  FreeOnTerminate := False;
  oConn := TFDConnection.Create(nil);
  oConn.ConnectionDefName := 'Oracle_Pooled'; 
  oPrc := TFDStoredProc.Create(nil);
  oPrc.Connection := oConn;
  try
    oConn.Connected := True; // Will get the a connection from FDManager
    oPrc.StoredProcName := 'MY_LONG_RUNNING_PROC';
    oPrc.ExecProc;
  finally
    oPrc.Free;
    oConn.Free; // Will return the connection to the FDManager for another thread to use it
  end;
end;


HTH,
Clément
koen Barbier

Posts: 8
Registered: 6/12/00
Re: Thread safety of Firedac  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 6, 2015 8:07 AM   in response to: Clement Doss in response to: Clement Doss
Hi,

First of all thanks for your response. I am aware of the solution you provide, but the documentation states that this solution is a 'simplification of the requirements'. I am however interested if the cases I provide are also thread safe, or if they do not meet the required conditions. With existing applications, it might not be easy to just start using the provided solution. We have build a framework that could automatically create and release connection objects per thread, but only if the first (or maybe the first 2) of this cases are thread safe. The last case is more out of curiousity.

regards,

Koen

Clement Doss wrote:
koen Barbier wrote:
Hi,

We want to use firedac in a multithreaded environment, but we are not sure how to interprete the multithreading documentation in http://docwiki.embarcadero.com/RADStudio/XE6/en/Multithreading_%28FireDAC%29 :

Hi,

This is what I did.
I'm using FDManager. This component will manage your thread safe environment.
Each thread must have it's own FDConnection that will use FDManager to get a connection.

From the example, in the main application you should have this code:
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');  // This is *very* important
  FDManager.AddConnectionDef('Oracle_Pooled', 'Ora', oParams); 


Now FDManager can serve your application threads.

Each thread should use the following pattern to be thread safe:

type
  TDBThread = class(TThread)
  protected
    procedure Execute; override;
  end;
 
procedure TDBThread.Execute;
var
  oConn: TFDConnection;  // only be available in this thread
  oPrc: TFDQuery; // only be available in this thread
begin
  FreeOnTerminate := False;
  oConn := TFDConnection.Create(nil);
  oConn.ConnectionDefName := 'Oracle_Pooled'; 
  oPrc := TFDStoredProc.Create(nil);
  oPrc.Connection := oConn;
  try
    oConn.Connected := True; // Will get the a connection from FDManager
    oPrc.StoredProcName := 'MY_LONG_RUNNING_PROC';
    oPrc.ExecProc;
  finally
    oPrc.Free;
    oConn.Free; // Will return the connection to the FDManager for another thread to use it
  end;
end;


HTH,
Clément
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Thread safety of Firedac
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 9, 2015 1:22 AM   in response to: koen Barbier in response to: koen Barbier
Is it thread safe to use a TFDConnection over multiple threads as long as no threads use the connection simultaneously, or not?

Yes, this is thread safe.

A similar question for a TFDQuery for example.

Yes, this is thread safe too.

1) a thread creates a connection and uses its connection, but does not close&remove this connection. Another thread serves to find these dangling connection, and closes&removes the connection (when the first thread is no longer using this connection)

Safe.

2) Same as 1), but also a TFDQuery remained open, and this Query is closes/freed from another thread, when it is no longer used

Thread safe only if associated TFDConnection is not used by other threads at this moment.

3) a thread creates a connection and TFDQuery, and after this connection/TFDQuery is no longer used by this thread, another thread starts using this TFDQuery and TFDConnection.

Safe.

In general at each moment of time each TFDConnection and all datasets associated with it
must be used only by single thread.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
koen Barbier

Posts: 8
Registered: 6/12/00
Re: Thread safety of Firedac  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 9, 2015 8:08 AM   in response to: koen Barbier in response to: koen Barbier
Hi Dmitry,

Thanks for your answers. This confirms what I thought, but I wanted to be sure.

regards,

Koen
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Thread safety of Firedac
Helpful
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 9, 2015 9:57 AM   in response to: koen Barbier in response to: koen Barbier
koen Barbier wrote:
Hi Dmitry,

Thanks for your answers. This confirms what I thought, but I wanted to be sure.

regards,

Koen

One thing I didn't see asked or Dmitry mention is you can not have TDataSource
or LiveBindings against your background threaded queries. If you are background
threading a query that displays the results you should disconnect the LB or
DataSource, open and fetch all the data then re establish the connection.

Those two will be trying to move the cursor on you or querying the buffer for
display while the buffer is very volatile being moved around in a different thread.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
koen Barbier

Posts: 8
Registered: 6/12/00
Re: Thread safety of Firedac  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 10, 2015 5:00 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Thanks for the extra info, Jeff, I will keep that in mind!

regards,

Koen

Jeff Overcash (TeamB) wrote:
koen Barbier wrote:
Hi Dmitry,

Thanks for your answers. This confirms what I thought, but I wanted to be sure.

regards,

Koen

One thing I didn't see asked or Dmitry mention is you can not have TDataSource
or LiveBindings against your background threaded queries. If you are background
threading a query that displays the results you should disconnect the LB or
DataSource, open and fetch all the data then re establish the connection.

Those two will be trying to move the cursor on you or querying the buffer for
display while the buffer is very volatile being moved around in a different thread.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02