Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Multithreaded ODBC Drivers

119 views
Skip to first unread message

Alec Waters

unread,
Jul 24, 1997, 3:00:00 AM7/24/97
to

Hi,

I have written a multi-threaded ODBC client, which is in itself a
"server" which can make many simultaneous datasource requests in
response to connected users. There can be many users conected to my
client simultaneously, and each may be connected to a different
datasource.

My question is this - is it (thread)safe for more than one user to open
a connection to the same datasource (providing that we are within the
driver's active connections limit)?

I only ask this because I know for a fact that Centura's SQLBase ODBC
driver is not thread safe - only one simultaneous connection is allowed.
For this driver, calling SqlGetInfo with SQL_MAX_DRIVER_CONNECTIONS
returns 0 (no limit/unknown limit).

So - Is "no limits" uncommon enough for me to assume it means "unknown
limit", and use it as a "non-threadsafe driver" indicator so I can
protect datasource access with a synchronisation object (critical
section or similar)? Does anybody have any better plans?

Thanks,
Alec Waters.
--

Michael Welcome

unread,
Jul 25, 1997, 3:00:00 AM7/25/97
to

Dear Alec,

I don't know about all brands of ODBC drivers, but the standard ones that
come with SQL Server can be used in multithreaded programs. There should
be no problem with multiple threads accessing the same data source. Just
make sure that only one thread will use a ODBC conection at one time and
you should be OK.

I have written ISAPI programs that maintain a pool of conections to the
same data source. Each new request launches a thread. The thread requests
a conection from the conection pool and uses it until it is ready to exit.
Then the thread releases the conection and exits. This avoids the overhead
of opening a connection for each new request.

For your purposes you won't want to manage conections this way since the
individual conections will be to different data sources, but this should
give you some idea what the limitations are.

--
Regards,

Michael Welcome
in order to aviod spam remove nospam: mich...@nospam.sqltech.com
check out http://www.sqltech.com

Alec Waters <awa...@bitspersecond.co.uk> wrote in article
<33D782...@bitspersecond.co.uk>...

Alec Waters

unread,
Jul 25, 1997, 3:00:00 AM7/25/97
to

Lee Fesperman wrote:
>
> I think it would be unwise to use SQL_MAX_DRIVER_CONNECTIONS as an indicator of Thread
> Safety. A return of zero probably just means that there is no specific limit other than
> system memory constraints. It's not really intended as a clue about Thread Safety.

OK. Should one assume (unless one knows postively otherwise) that the
ODBC driver to which you are connected is *not* thread safe?

In which case, what can be done about it? What kind of ODBC API
functions should have their access serialised (outside of the kind of
multithreaded issues to do with sharing handles you describe in your
rather handy article)? Should I serialise access to _each_ API function?

> BTW, my experience is that the SQLBase ODBC Driver (from Intersolv I believe) is not
> thread safe.

Quite right. The ODBC driver talks to the native Centura API. When using
this API, access to *all* calls have to be serialised with a critical
section. Needless to say, this has a noticeable effect on performance!

Thanks for your assistance,
Alec Waters.
--

Lee Fesperman

unread,
Jul 25, 1997, 3:00:00 AM7/25/97
to

Alec Waters wrote:
>
> Lee Fesperman wrote:
> >
> > I think it would be unwise to use SQL_MAX_DRIVER_CONNECTIONS as an indicator of Thread
> > Safety. A return of zero probably just means that there is no specific limit other than
> > system memory constraints. It's not really intended as a clue about Thread Safety.
>
> OK. Should one assume (unless one knows postively otherwise) that the
> ODBC driver to which you are connected is *not* thread safe?

I think that is an assumption that you have to make (see below).



> In which case, what can be done about it? What kind of ODBC API
> functions should have their access serialised (outside of the kind of
> multithreaded issues to do with sharing handles you describe in your
> rather handy article)? Should I serialise access to _each_ API function?

You will have to do that for multi-threaded use of a driver which is not thread safe.



> > BTW, my experience is that the SQLBase ODBC Driver (from Intersolv I believe) is not
> > thread safe.
>
> Quite right. The ODBC driver talks to the native Centura API. When using
> this API, access to *all* calls have to be serialised with a critical
> section. Needless to say, this has a noticeable effect on performance!

It really doesn't matter whether the native API is thread safe or not. It is the
responsibility of the ODBC driver to make sure that access is thread safe (by
serializing access to the native API).

On the subject of thread safety and the ODBC driver for Centura, I now have additional
information ... further testing has revealed that the Centura ODBC Driver we are using
is *not* thread safe. The driver is from Intersolv ... we contacted Intersolv and they
claimed that their Centura driver was thread safe, but our testing showed that was not
the case. We had horrible lock-ups and crashes. When we serialized access to ODBC, all
the problems went away! Obviously, Intersolv is stonewalling on thread safety.

I think we need to amend our technical article to say - 'When doing multi-threaded
access to a driver that is not thread safe, the application must serialize *all* access
to ODBC'.

Performance is also a big issue. Users must demand not only that their ODBC drivers are
thread safe but also that the serialization is at the lowest level possible (fine
granularity) to provide good performance.

Lee Fesperman, FFE Software, Inc.

Lee Fesperman

unread,
Jul 30, 1997, 3:00:00 AM7/30/97
to

Ramsesh Kalkunte wrote:
>
> Thread safety of drivers is a variable.
> Unless the threads are doing mutually exclusive queries to the database,
> making simultaneous connections are useless for performance.

Thread safety of drivers is extremely variable. However, I don't think you can justify
your statements about performance in the general case.

Ramsesh Kalkunte

unread,
Jul 31, 1997, 3:00:00 AM7/31/97
to

Thread safety of drivers is a variable.
Unless the threads are doing mutually exclusive queries to the database,
making simultaneous connections are useless for performance.

You may want to connect once to the database through a Process-based
Context and Mutex your access for each thread through a WAIT_INFINITE or
equivalent flag.

--
Ramsesh Kalkunte
Technovations International Inc.
http://www.technovations.com
----------------------------------------------------------------------------

Performance Analysis/Load Testing Frameworks
for Lotus Notes, Web and Database Applications
----------------------------------------------------------------------------

Ramsesh Kalkunte

unread,
Jul 31, 1997, 3:00:00 AM7/31/97
to


Lee Fesperman <firs...@ix.netcom.com> wrote in article
<33E001...@ix.netcom.com>...


> Ramsesh Kalkunte wrote:
> >
> > Thread safety of drivers is a variable.
> > Unless the threads are doing mutually exclusive queries to the
database,
> > making simultaneous connections are useless for performance.
>

> Thread safety of drivers is extremely variable. However, I don't think
you can justify
> your statements about performance in the general case.

I am not generalizing; I have an important UNLESS clause in my statement.

--
Ramsesh Kalkunte
Technovations International Inc.
http://www.technovations.com
----------------------------------------------------------------------------

Performance Analysis/Load Testing Frameworks
for Lotus Notes, Web and Database Applications
----------------------------------------------------------------------------


>

0 new messages