ZeosLib thread safety

btrewern · Post by **btrewern** » 21.02.2006, 19:34

I've just been having some problems running ZeosLib in a threaded application. It is mainly related to using the ZSQLMonitor component.

There is a global variable declared in ZDbcIntfs.pas:

Code: Select all

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

that is then used throughout the backend specific unit like so:

Code: Select all

DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);

to log messages.

Is there some way I can make DriverManager local to a thread? I was just about to put critical sections in but I don't know if that will fix my problem.

Regards,

Ben

matsgefvert · Post by **matsgefvert** » 22.02.2006, 09:33

We're using Zeos in heavily multithreaded servers and it works excellently. The only thing we've noticed in regard to ZSqlMonitor is that it hooks into all possible ZConnection objects, regardless of thread, so you should only have one in your app.

AFAIK, DriverManager is a global collection of database drivers and I don't think there's any way that could be made thread-local.

Specifically, what problem is it that you're trying to fix?

/ Matt

btrewern · Post by **btrewern** » 22.02.2006, 12:53

I was using ZSQLMonitor on a per thread basis. Every so often I would get a AV due to the above. I've removed the ZSQLMonitor from the newest version of our app and haven't had any problem since.

I can see in the source that DriverManager is used in a few other places but can't see they will be a problem. I'm just a bit over cautious at the moment as this app is meant to work 24/7.

When I have the time (and a bit more confidence) I'll look at just using one ZSQLMonitor and see if it runs reliably.

Regards,

Ben

matsgefvert · Post by **matsgefvert** » 22.02.2006, 14:35

Did you use any custom logging code in the OnTrace or OnLogTrace events? Did you try any critical sections in those events?

I'm asking out of interest because I remember having some intermittent problems with ZSqlMonitor in one app, and then we took it out and it worked fine. But I never thought more about it. Maybe I should run some tests or something.

/ Matt

btrewern · Post by **btrewern** » 22.02.2006, 14:45

Nothing like that. It just had a seperate ZSQLMonitor and log file for each thread and then set AutoSave and Active to true.

Looking at the ZeosLib source I'm not sure what is the best way to go forward with this. Currently I can do without the logging but it would be useful in the future.

Ben

matsgefvert · Post by **matsgefvert** » 22.02.2006, 14:58

We don't typically have much use for ZSqlMonitor actually, because the information is relatively sparse. If it could be expanded to provide information about opening/closing datasets, timing etc, that would be very useful.

But it sounds like there's a problem in there somewhere. Maybe one of the dev's are taking notice? *hint, hint*

/ Matt

matsgefvert · Post by **matsgefvert** » 22.02.2006, 18:06

Well, I don't know if it applies in this situation but I ran some threaded stress tests on the 6.1.5 version of ZSqlMonitor (which is identical to the latest 6.5.1 version) and everything checked out fine with no exceptions or AV's.

I know that Zeos is a little bit sensitive (very sensitive, actually) if you access the same ZConnection from several different threads (i.e. you create Queries in different threads that link to the same global Connection object). It couldn't have been something like that?

My guess is that ZSqlMonitor may not be the problem but rather something else around it.

/ Matt

btrewern · Post by **btrewern** » 22.02.2006, 21:03

It puts my mind at rest that you are also using ZeosLib with lots of threads. I thought it was just me!

Currently everything is working without the ZSQLMonitor so I'll just leave it at that till I have more time to look into things.

Regards,

Ben

WTimmers · Post by **WTimmers** » 21.03.2006, 12:31

matsgefvert wrote:Well, I don't know if it applies in this situation but I ran some threaded stress tests on the 6.1.5 version of ZSqlMonitor (which is identical to the latest 6.5.1 version) and everything checked out fine with no exceptions or AV's.

I know that Zeos is a little bit sensitive (very sensitive, actually) if you access the same ZConnection from several different threads (i.e. you create Queries in different threads that link to the same global Connection object). It couldn't have been something like that?

My guess is that ZSqlMonitor may not be the problem but rather something else around it.

/ Matt

May I ask what you mean by sensitive?

We're having errors when we use a global ZConnection (for MySQL 4.1), which we pass between several DLL's.
We want to use a single ZConnection for our application suite, or at least not 1 ZConnection per datamodule.

Our application consists of a large quantity of datamodules ( > 30 ), each of the datamodules can be used by multiple DLL's.
At the moment we're using 1 ZConnection per datamodule.

We're having trouble with the '10 new connections per second'-rule in Windows XP SP2.

Is there a way to make one single connection available for different applications?
What things must we avoid when sharing the ZConnection?

matsgefvert · Post by **matsgefvert** » 21.03.2006, 14:56

The problem goes down to the libmysql API. A single connection is instantiated using memory buffers inside libmysql, and each query uses this connection buffer or handle to process queries back and forth. There is no problem whatsoever with allowing hundreds of queries to use the same connection, as long as they are properly serialized - that is, as long as all access happens from one thread, you're fine.

If you're using multiple threads, then things will go wrong rather quickly, and libmysql can start producing nasty errors like "connection to server lost", "invalid handle", "table nn does not exist" and so on.

For your situation, I see no problem with lots of ZQuery objects in different datamodules using the same ZConnection object, even in DLL's, as long as it can be guaranteed that you're not running multiple threads.

As soon as you have two threads running, you either need to serialize access to queries/connections through critical sections, or instantiate one ZConnection per thread and tie the respective ZQuery objects to the thread-specific ZConnection.

/ Matt

WTimmers · Post by **WTimmers** » 21.03.2006, 15:13

matsgefvert wrote:The problem goes down to the libmysql API. A single connection is instantiated using memory buffers inside libmysql, and each query uses this connection buffer or handle to process queries back and forth. There is no problem whatsoever with allowing hundreds of queries to use the same connection, as long as they are properly serialized - that is, as long as all access happens from one thread, you're fine.

If you're using multiple threads, then things will go wrong rather quickly, and libmysql can start producing nasty errors like "connection to server lost", "invalid handle", "table nn does not exist" and so on.

For your situation, I see no problem with lots of ZQuery objects in different datamodules using the same ZConnection object, even in DLL's, as long as it can be guaranteed that you're not running multiple threads.

As soon as you have two threads running, you either need to serialize access to queries/connections through critical sections, or instantiate one ZConnection per thread and tie the respective ZQuery objects to the thread-specific ZConnection.

/ Matt

Thanks, that's what I wanted (or rather not wanted, but so be it) to hear.

We have problems with running multiple apps using the same ZConnection.
This is a simplistic schematic of our app:
Executable --> DLL1 --> DLL2 --> DLL3

A record/struct is passed from the executable up to the last DLL. If you close the APP in the last DLL (DLL3), then DLL2 will become active again.

We wanted to include the ZConnection-component in our record/struct, but it seems this isn't gonna work with Zeos & MySQL (mostly because of MySQL).

We knew of the problem, but I had a little hope that someone else solved the problem. Too bad.

matsgefvert · Post by **matsgefvert** » 21.03.2006, 15:18

But I don't quite understand. Are your DLL's using separate threads? If they're not, then it should work fine? Especially since all the DLL's are using the same memory space.

/ Matt

WTimmers · Post by **WTimmers** » 18.07.2006, 07:36

A bit later than I hoped, but we found a solution.

It has to do with sharing memory between DLL's.
Apparently the TZConnection isn't suitable for exchange between two DLL's when you're using the default memory manager.
Some elements of TZConnection (such as a variable length TStringField? ) don't like it when they're shared between DLL's.

We used FastShareMem ( http://www.codexterity.com/fastsharemem.htm ) as a memory manager, and this seems to work OK.
No more errors when sharing one TZConnection between multiple DLL's.

Terence · Post by **Terence** » 18.07.2006, 23:43

I think this "bug" is fixed since delphi 2005.