Jump to content
Yaron

FireBird, TFDConnection and concurrency

Recommended Posts

I am trying to build a system that will handle concurrent DB requests efficiently, there's not a lot of load, but it should be able to handle more than 1 query concurrently.

I am using FireBird v3.0.4 and Delphi 10.3.2 with the TFDConnection component.

I am hosting the code in an ISAPI DLL that runs on IIS 7.5.

 

Right now I'm creating the DB connection using TFDConnection in the DLL's "initialization" section.

 

However, I noticed that even under low-load (2-3 users), I occasionally get an "[FireDAC][Phys][FB]Error reading data from the connection." exception when performing a DB query (not the same DB query, it seems pretty random).

 

I tried to research the error and didn't find anything clear, the best I got was :

https://forums.embarcadero.com/thread.jspa?threadID=245750

Which seems to indicate that I should instance a copy of TFDConnection for every query.

 

Before I make significant code changes to test this, I would welcome any tips on the best approach to handling DB concurrency.

 

Share this post


Link to post
Posted (edited)
3 hours ago, Jacek Laskowski said:

In multithread application you must use separated connection per thread.

Does that mean that for every http request (that requires DB access) I have to create a new instance of TFDConnection and then connect to the DB?

 

This is the first time I'm writing an ISAPI dll with DB access and the threading model is not exactly clear to me.

My original (perhaps flawed) assumption was that IIS will load additional DLLs in separate threads to handle concurrent http requests.

 

I would like to avoid the 100ms penalty for connecting to the DB on each request, any tips?

Edited by Yaron

Share this post


Link to post
Posted (edited)
procedure CreatePooledConnection;
var
  oParams: TStrings;
begin
  oParams := TStringList.Create;
  try
   oParams.Add('Server='+FServerName);
   oParams.Add('Database='+FDatenbank);
   oParams.Add('User_Name='+FUsername);
   oParams.Add('Password='+FPassword);
   oParams.Add('Port='+FPort.ToString);
   oParams.Add('Pooled=True');
   FDManager.AddConnectionDef(CONN_RAM_Pooled, 'MySQL', oParams);
   FDManager.Active:=True;
  finally
   oParams.Free;
  end;

We use a "PooledConnection" for this! And then in the Thread you only use this Pooled Connection for connecting to the Database, but you must create a new TFDConnection in every thread.

 

 AConnection:=TFDConnection.Create(nil);
 try
  AConnection.ConnectionDefName:=CONN_RAM_Pooled;
  try
   AConnection.Connected:=true;
.....

 

Edited by ConstantGardener
  • Like 2

Share this post


Link to post

No, he doesn't need to create TFDConnection, he can use TFDQuery alone and get the connection from the pool in it:


 

FDQuery := TFDQuery.Create(nil);
FDQuery.ConnectionName := RegisteredConnectionDefinitionString;

 

  • Like 3

Share this post


Link to post
Posted (edited)

Thank you!

Took me a bit to understand the mechanics, but with your help I managed to rewrite the code with minimal changes and so far it's working.

 

P.S.

"FDManager.Active :=True;" no longer works in Delphi 10.3, it simply doesn't exist anymore, but "FDManager.Open" seems to be the replacement.

Edited by Yaron
  • Like 1

Share this post


Link to post

Btw, you're in DLL so

- Don't forget to set IsMultiThread to True manually

- Don't do anything serious in DLLMain (and inside DLL project's begin-end block). DLL loading stage is pretty special, not much things are allowed here

  • Like 1

Share this post


Link to post

Looks like I'm encountering an IIS issue when using this scheme.

In the ISAPI's DLL's finalization code I call "FDManager.Close" and in IIS, when stopping the application pool associated with the DLL it can take ~60 seconds before I can restart the pool, any ideas?

 

This is the error I get:

Quote

 

---------------------------
Cannot Start Application Pool
---------------------------
There was an error while performing this operation.

 

Details:

The service cannot accept control messages at this time. (Exception from HRESULT: 0x80070425)

 

 

 

When checking my logs, It doesn't seem to exit cleanly (the debug message that the "DB Closed" does not show up in the log), but there doesn't seem to be an exception, here's the code I use:

  Try FDManager.Close; Except
    on E : Exception do {$IFDEF TRACEDEBUG}AddDebugEntry('Exception disconnecting from DB : '+E.Message){$ENDIF};
  End;
  {$IFDEF TRACEDEBUG}AddDebugEntry('DB Closed');{$ENDIF}

 

When I run the same code locally as an EXE that handles the HTTP requests, there are no issues, any ideas?

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×