Multithreading with connection pool "Duplicate database name 'XXX'"

Question

I’m developing a service to multithread an old source code that uses BDE and DBX, so that each thread has its own connection I developed a Singleton with connection pool to which it uses the method Getcurrentthreadid to locate in the pool if the connection already exists and if it does not exist I create

---

     if not Assigned(m_PoolConexoes)
        then m_PoolConexoes := TObjectList.Create(True);

      for i := 0 to Pred(m_PoolConexoes.Count) do
      begin
        if TSingletonConexao(m_PoolConexoes[i]).FThreadID = GetCurrentThreadId then
        begin
          Result := TSingletonConexao(m_PoolConexoes[i]);
          Break;
        end;
      end;

      if not Assigned(Result) then
      begin
        Result := TSingletonConexao.Create();
        m_PoolConexoes.Add( Result );
        TMisc.LogEventViewer(9999,'Quantidade de Conexoes Ativas: '+ IntToStr(m_PoolConexoes.Count), 9999);
      end;

When finishing the thread on Onterminate i clear the connection by the method below:

class procedure TSingletonConexao.FreeInstancia;
var
  i: Integer;
begin

  TMisc.LogEventViewer(9996,'Iniciando o FreeInstancia de conexão da thread : '+ IntToStr(GetCurrentThreadId), 9996);
  if Assigned(m_PoolConexoes) then
  begin
    for i := 0 to Pred(m_PoolConexoes.Count) do
    begin
      if TSingletonConexao(m_PoolConexoes[i]).FThreadID = GetCurrentThreadId then
      begin
        m_PoolConexoes.Remove( m_PoolConexoes[i] ); // Ao remover da lista, o obj é destruído...
        Break;
      end;
    end;
  end;
  TMisc.LogEventViewer(9996,'Quantidade de Conexoes Ativas: '+ IntToStr(m_PoolConexoes.Count), 9996);
end;

Destroy:

destructor TSingletonConexao.Destroy;
begin
  SafeFree( FQuery );

  if SafeAssigned( FConexaoDBX )
    then FConexaoDBX.Close;
  SafeFree( FConexaoDBX );

  if SafeAssigned( FConexaoBDE )
    then FConexaoBDE.Close;
  SafeFree( FConexaoBDE );

  TMisc.LogEventViewer(9996,'Encerrando uma Conexão com o ThreadID: '+ IntToStr(FThreadID), 9996);
  inherited Destroy;
end;

The problem is that as I am in loop, for some reason unknown by me, the connection is not being destroyed and error occurs "Duplicate database name 'XXX'". Could someone give me a light on how to resolve this? It seems that the connection is holding even having been destroyed (strange).

Obs: To create the connections, I do the following, when logging into the system there is already an open connection, so I create the object and pass the attributes of the open connection by changing only the Name and the Databasename, by placing the name of the original connection concatenated with Getcurrentthreadid

  FThreadID := GetCurrentThreadId();
  with dmConexaoDireta.dbAplicacao do
  begin
    FConexaoBDE := TDatabase.Create(nil);
    FConexaoBDE.DatabaseName := DatabaseName +'_'+ IntToStr(FThreadID);
    FConexaoBDE.DriverName := DriverName;
    FConexaoBDE.AliasName := AliasName;
    FConexaoBDE.Exclusive := Exclusive;
    FConexaoBDE.HandleShared := HandleShared;
    FConexaoBDE.KeepConnection := KeepConnection;
    FConexaoBDE.LoginPrompt := LoginPrompt;
    FConexaoBDE.Name := FConexaoBDE.DatabaseName;
    FConexaoBDE.Params.Text := Params.Text;
    FConexaoBDE.ReadOnly := ReadOnly;
    FConexaoBDE.SessionName := SessionName;
    FConexaoBDE.TransIsolation := TransIsolation;
    FConexaoBDE.Connected := True;
  end;

Obs2: I’ve tried creating Tdatabase with Owner Application and now I’ve put nil.

Obs3: Safeassigned and Safefree methods are methods I use to avoid error.

Answer 1

I solved this question by not having my thread finished at each cycle, instead I placed the queue in English and made the thread have the intelligence to search for the next record in the queue and, if not, wait 3 seconds to search again.