TZConnection - thread safe?

[ertank]

Hello,

I am using Lazarus 1.8.4, fpc 3.0.4, zeoslib 7.3 (svn revision 4809).

I have the need to use a database connection and queries in a thread on Linux OS, amd64 platform.

Even if I do create the component at run-time, I get access violation (as if it is not created) on thread execute body. Complete thread code is not big and I failed to find a reason for some hours. I will continue my search tomorrow. In the mean time, I just wanted to ask in here if library is safe to use in a thread.

Thanks & regards,
Ertan

[marsupilami]

Hello Ertan,

as far as I know, Zeos should be more or less thread safe. But that can depend on the database driver in Zeos and on the database client library that you use. It might also make sense to create one connection to the database after the other as some client libs don't like to create several new connections at the same time.

Which database do you use?

Best regards,

Jan

[Fr0sT]

As long as you do all the stuff in a single thread, no problems should happen. Using several threads to access a database could be unsupported by client lib itself.

[ertank]

Which database do you use?

Hello Jan,

That is a PostgreSQL 9.6.9 running in localhost. I am working on some kind of a server code.

There will be main application thread making a connection which seemingly just fine. And there is one thread running in background for some cleanup operations. Thread is a headache for me. Will start working on it again in a couple of hours later.

Thanks & regards,
Ertan

[Fr0sT]

From what I found PG client lib must be explicitly built with thread-safety enabled. Besides this, usual rule "a connection and all related objects must be used only within a single thread" is still actual as Zeos doesn't have special sync methods to access internal properties.

[ertank]

It has been a while that I worked with threads. Apparently, I have to create any class that I will be using in thread in the Execute() procedure. Anything created in constructor Create() is not counting. That code in constructor is executed in main application thread as I am informed. It does not help to have private declarations like below:

Code: Select all

type
  TUserClean = class(TThread)
  protected
    FDB: TZConnection;
    FqryUsers: TZReadOnlyQuery;
    FqryDropUser: TZQuery;

    procedure Execute; override;
    procedure Log(const Value: string);
  public
    LogDir: string;
    constructor Create(); reintroduce; overload;
    destructor Destroy(); override;
  end;

FDB, FqryUsers, FqryDropUser create codes have to be in Execute block. They will raise Access Violation if they are to be created in constructor.

Moving my create codes at the beginning of Execute procedure solved my problem. Posting in here as someone else may be in my shoes in the future.

Thanks & regards,
Ertan

[Fr0sT]

This is weird. Thread context of object creation shouldn't matter, it only matters for window handles (messages sent to these windows will be processed in the thread that created them). AFAIK Zeos uses no windows so the AV you got is either a bug in Zeos or some internal magic inside client lib.

[ertank]

This is weird. Thread context of object creation shouldn't matter,

Agreed, however, I do not have enough time and patience to do more tests as I am rushing to deliver the job. I might go back and check it out later.

I suspect that is something related with fpc 3.0.4 and changed/fixed in fpc 3.1.1 (trunk).

[Wild_Pointer]

Dear community,

I was having access violations on TZConnection.Connect:

:0044e0a6 TStringList.InsertItem + $56
:0044dc2d TStringList.AddObject + $59
ZURL.TZURLStringList.SetTextStr('application_name=nFormer'#$D#$A)
ZDbcIntfs.TZDriverManager.ConstructURL('postgresql-9','localhost','npoint','postgres','a',5435,$3205290,'')
ZAbstractConnection.TZAbstractConnection.ConstructURL('postgres','a')
ZAbstractConnection.TZAbstractConnection.Connect
WorkerThread.TWorker.Execute
:004563ed ThreadProc + $45
:00407102 ThreadWrapper + $2A
:75dd6359 KERNEL32.BaseThreadInitThunk + 0x19
:77767a94 ntdll.RtlGetAppContainerNamedObjectPath + 0xe4
:77767a64 ntdll.RtlGetAppContainerNamedObjectPath + 0xb4


in file \src\dbc\ZDbcIntfs.pas I found:

Code: Select all

var
  {** The common driver manager object. }
  DriverManager: IZDriverManager;

...

initialization
  DriverManager := TZDriverManager.Create;

It seems ZeosLib is using single DriverManager to construct all the connection URL despite the ZConnection objects are created and used in different threads... Should we use threadvar for DriverManager and create as many instances as there are threads in use?

Do you know more places in ZeosLib the single object is used for several ZConnection's ?

[marsupilami]

Hello Wild Pointer,

the driver manager should be protexted by a mutex or something already? I am not sure about access to the plain drivers though.

Best regards,

Jan

[Wild_Pointer]

Hello, marsupilami.

The driver manager should be protected, but the only protection I found (v7.2.4) is in ZdbcPooled.pas line 348:

Code: Select all

      // I had a strong feeling that DriverManager is not thread-safe, because i
      // had random access violations on high load operations at this point.
      // For now, i will serialize DriverManager access, until further
      // investigation (maybe the problem is in the pool driver, as
      // DriverManager should be thread-safe in essence.
      FCriticalSectionDriverManager.Enter;
      try
        FConnections[I] := DriverManager.GetConnection(FURL);
      finally
        FCriticalSectionDriverManager.Leave;
      end;

So it seems I'm not the first one to notice the access violation errors..

The question then is how to fix the problem? I think the best approach is to have a copy of driver manager for every thread. The other would be having a critical section/mutex guarding access to the methods of IZDriverManager. I see TZDriverManager already has a criticalSection and guards AddLoggingListener, RemoveLoggingListener and LogEvent methods with it.

What are your thoughts?

[marsupilami]

Hello,

it seems Egonhugeist has done work on this. Could you please check out rev. 5913 from Zeos 7.3? He also plans to move this change to Zeos 7.2.

Best regards,

Jan