Thread-safety ness of the MS Access ODBC drivers
Manoj Sati
Hi,
Are the latest ODBC drivers for MS Access thread-safe? Can i use the MFC
ODBC classes from multiple threads and call the same MS Access ODBC driver
in a thread-safe way? The MFC docs says that both the MFC ODBC classes and
the MS Access ODBC driver should be thread-safe to achieve true
multi-threading? I read that the MFC ODBC classes are thread-safe. What I'd
like to know is about the MS Access ODBC drivers and their thread-safety
ness.
Manoj Sati
sa...@lucent.com
Aid
Access uses the Jet engine, version 3 of which is not thread safe. Another
thread in this group suggests that if you use a seperate connection for
each thread then "most" drivers become safe. I've found that this certainly
improves matters greatly using the Jet engine (my test app seems to work
fine) however it still hangs every now and then in our real App.
Suffice it to say I'm not convinced about the connection per thread theory.
Aidan.
Manoj Sati <sa...@lucent.com> wrote in article
<01bc4613$4e856820$49691487@mtpcs105-73>...
Dan Konigsbach
Manoj:
I went through this problem early this year, including extensive conversations
with Microsoft. Here are the answers:
The MFC ODBC classes and ODBC itself are thread-safe, but the ODBC drivers may
or may not be thread-safe. If the driver isn't thread-safe, then the whole
thing is, effectively, not thread-safe. (A chain is only as strong...)
There is a 3.5 version of the Microsoft Desktop Drivers (a/k/a Data Access
Pack) available for download from Microsoft. (I'm sorry that I don't have the
actual file name here - I'm at home at it's at work. Contact me - see below -
if you have trouble finding it). It is thread-safe so long as you do not load
the Cursor Library. Previous versions of the MS Desktop Drivers are not
thread-safe. (Note that the MS Access ODBC driver is one of these desktop
drivers.)
The "3.5", by the way, supposedly refers to the version of the Jet engine,
which all of the desktop drivers use, not to the ODBC version. The
documentation that comes with these drivers claims conformance to ODBC 2.5. I
do not know if they support ODBC 3.0.
The Cursor Library that accompanies 3.5 Data Access Pack has a bug (according
to Microsoft) and is not thread-safe. This bug only affects multi-threading,
and won't harm single-threaded uses of the cursor library.
Avoiding the Cursor Library means that bidirectional, read/write cursors must
be dynasets, not snapshots. (With the cursor library loaded, bidirectional,
read/write cursors must be snapshots, not dynasets.) Beware, using dynasets,
that "holes" can develop in your result set. The MFC documentation describes
this adequately. See CRecordset::IsDeleted().
The way to multi-thread MS Access ODBC is to get the 3.5 Data Access Pack and
to not load the Cursor Library. When I did this, my multithreading problems
went away.
Note: I have seen conflicting Microsoft documentation, saying that (1)
different threads should not share a connection, and (2) all connections
should be made in the same thread. I have a single connection, created in the
primary thread, that all threads share. They do not concurrently share
statement handles (or, by implication, recordsets). This has been working
fine.
Note: Only my primary thread is a user-interface thread. My other threads
are worker threads. At one time, I saw a description of a problem a where a
thread making an ODBC call could wait on other user-interface threads in the
application dispatching messages. This was somehow involved with the way MS
ODBC desktop drivers use OLE. I'm afraid I haven't needed to look into the
details. You might want to look into this, since, without planning, there is
some danger of deadlock.
Good luck,
Dan Konigsbach
DR Systems
Dan_Kon...@dominator.com
In article <01bc4613$4e856820$49691487@mtpcs105-73>, "Manoj Sati"