FireBird, TFDConnection and concurrency

[Yaron 53]

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.

 

[Jacek Laskowski 57]

In multithread application you must use separated connection per thread.

[Yaron 53]

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 October 3, 2019 by Yaron

[ConstantGardener 31]

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 October 4, 2019 by ConstantGardener

[Jacek Laskowski 57]

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;

 

[Yaron 53]

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 October 4, 2019 by Yaron

[Fr0sT.Brutal 900]

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

[Yaron 53]

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?

 

[ConstantGardener 31]

maybe this ...

 

https://wiert.me/2019/10/03/how-to-properly-cleanup-shutdown-a-delphi-isapi-which-uses-threads-mathias-pannier-programmiert/

 

[Yaron 53]

You beat me to it, I just found this:
https://mathiaspannier.wordpress.com/2016/07/17/how-to-properly-cleanupshutdown-a-delphi-isapi-which-uses-threads/

 

Which is essentially the same answer, can't close the connection in the finalization section when dealing with ISAPI.

 

[Michel Bernabela 0]

Hi Aaron,

 

I am facing the same issue right now in regards to connecting to a fireDAC datasnap database using and ISAPI module. My current architecture works fine with a VCL datasnap server GUI windows datasnap server. But turning this server into ISAPI dll, i am not connecting to the database.

Seeing your post and you have experience solving this issue, I hope you are able to help me. Can you share with me an small simple code how to connect to a datasnap firedec server from within an ISAPI dll module?

 

Awaiting your reply...

 

Regards, Michel