delphi - 在多线程应用程序中使用 Firedac

Question

我目前正在开发一个多线程服务器应用程序，我计划使用 Firedac 进行数据访问。从此处提供的文档:http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Multithreading_(FireDAC) ，似乎不应同时从多个线程访问相同的 TFDConnection 和/或 TFDQuery (相反，应在每个线程的基础上创建这些对象)。

因此，上一个链接中显示的示例将 TFDConnection 和 TFDQuery 集中在 TThread 对象中。但是，就我而言，我无法控制线程创建(由服务器环境管理)。因此，我将 TFDConnection 和 TFDQuery 对象的生命周期限制为过程的生命周期，该过程可能会从多个线程调用:

procedure TAPMFiredacTemplate.executeSQL(sql:string);
  var
  oConn: TFDConnection;
  oQuery: TFDQuery;
  begin
  oConn := TFDConnection.Create(nil);
  oQuery := TFDQuery.Create(nil);
  try
    oConn.ConnectionDefName := self.ConnectionDefinitionName;
    oConn.Connected := True;
    oQuery.Connection := oConn;
    oQuery.Open(sql);
    while not oQuery.Eof do
    begin
      // process query
      oQuery.Next;
    end;

  finally
    if assigned(oQuery) then
    begin
      oQuery.Close;
      oQuery.Free;
    end;
    if assigned (oConn) then
    begin
      oConn.Connected := False;
      oConn.Free;
    end;

  end;

这种方法有效吗？系统地创建 TFDQuery 对象是否会影响性能？

注意:为了提高性能，我计划使用私有(private)池连接定义(由 TFDConnection 使用)。因此，根据我的理解，即使我释放 TFDConnection，物理连接也不会被破坏，而是返回到池中:

oParams := TStringList.Create;
oParams.Add('Database=localhost:c:\apm\databases\mydb.FDB');
oParams.Add('User_Name=xxxxx');
oParams.Add('Password=xxxxxx');
oParams.Add('Pooled=True');
FDManager.AddConnectionDef('Firebird_Pooled','FB',oParams);
FDManager.Active := True;

Answer 1

这是线程上下文执行的有效方法，但它在数据库连接建立和每个客户端请求的查询准备方面会产生性能损失(假设您正在使用某些 Indy 服务器)。

要解决第一个问题，请使用 connection pooling (您可以关注the example)。

要解决后一个问题，也可以有一个解决方案。如果您的服务器支持保持事件类型的连接，请创建查询对象和 prepare the query当客户端连接时并在断开连接时销毁它。您可以通过扩展上下文类将这个准备好的对象传递给服务器请求处理方法。

例如，对于TIdTCPServer，它可能是:

type
  { server context in the meaning of a "task" class }
  TMyContext = class(TIdServerContext)
  private
    FQuery: TFDQuery;
  public
    constructor Create(AConnection: TIdTCPConnection; AYarn: TIdYarn; AList: TThreadList = nil); override;
    destructor Destroy; override;
    property Query: TFDQuery read FQuery;
  end;

  TForm1 = class(TForm)
    IdTCPServer1: TIdTCPServer;
    FDConnection1: TFDConnection;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure IdTCPServer1Connect(AContext: TIdContext);
    procedure IdTCPServer1Disconnect(AContext: TIdContext);
    procedure IdTCPServer1Execute(AContext: TIdContext);
    procedure IdTCPServer1Exception(AContext: TIdContext; AException: Exception);
  end;

implementation

constructor TMyContext.Create(AConnection: TIdTCPConnection; AYarn: TIdYarn; AList: TThreadList = nil);
begin
  inherited;
  FQuery := TFDQuery.Create(nil);
end;

destructor TMyContext.Destroy;
begin
  FQuery.Free;
  inherited;
end;

procedure TForm1.FormCreate(Sender: TObject);
var
  Params: TStrings;
begin
  Params := TStringList.Create;
  try
    Params.Add('Database=localhost:C:\MyDatabase.fdb');
    Params.Add('User_Name=xxxxx');
    Params.Add('Password=xxxxx');
    Params.Add('Pooled=True');
    { add the definition to the global connection manager singleton }
    FDManager.AddConnectionDef('FirebirdPooled', 'FB', Params);
  finally
    Params.Free;
  end;

  { use the added definition and establish the connection to the DB }
  FDConnection1.ConnectionDefName := 'FirebirdPooled';
  FDConnection1.Connected := True;

  { setup the context class, add a port binding and start the TCP server }
  IdTCPServer1.ContextClass := TMyContext;
  IdTCPServer1.Bindings.Add.Port := 6000;
  IdTCPServer1.Active := True;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  { stop the TCP server and destroy all pooled physical connections }
  IdTCPServer1.Active := False;
  FDManager.CloseConnectionDef('FirebirdPooled');
end;

procedure TForm1.IdTCPServer1Connect(AContext: TIdContext);
begin
  { client just connected, assign to the context query object the pooled
    connection and a command text }
  TMyContext(AContext).Query.Connection := FDConnection1;
  TMyContext(AContext).Query.SQL.Text := 'SELECT * FROM MyTable WHERE ID=:ID';
  { preparing the query will acquire one physical connection from the pool
    as this method internally opens the connection }
  TMyContext(AContext).Query.Prepare;
end;

procedure TForm1.IdTCPServer1Disconnect(AContext: TIdContext);
begin
  { client just disconnected, return the physical connection to the pool }
  TMyContext(AContext).Query.Connection.Close;
end;

procedure TForm1.IdTCPServer1Execute(AContext: TIdContext);
var
  ID: Integer;
  Query: TFDQuery;
begin
  { read an integer from socket }
  ID := AContext.Connection.IOHandler.ReadInteger;
  { just a reference helper }
  Query := TMyContext(AContext).Query;

  { fill the parameter and refresh the prepared query object's dataset }
  Query.Params[0].AsInteger := ID;
  Query.Refresh;

  while not Query.Eof do
  begin
    { process the dataset somehow }
    Query.Next;
  end;

  { do not close the dataset, keep it prepared for the next possible request }
end;

procedure TForm1.IdTCPServer1Exception(AContext: TIdContext; AException: Exception);
begin
  if AException is EFDException then
  begin
    { something bad happened with the DB, this is a base FireDAC exception
      class but you can be more specific of course }
  end;
end;