Easysoft ODBC-ODBC Bridge

Can I use a multi-threaded application with the OOB client?

Article:
00127
Last Reviewed:
28th October 2008
Revision:
4

In Windows yes.

On most UNIX operating systems we support, a thread-safe version of the OOB client is not yet available. However, for Linux/glibc, Solaris/Sparc/Intel, Tru64 and FreeBSD a MT-safe OOB distribution is available. These distributions have "-mt" in the
distribution/filename.

There are a few issues specific to OOB and unixODBC regarding threads:

[1]
If you are building unixODBC yourself then make sure you put --enable-threads=yes on the configure line if you want a thread-safe driver manager.

[2]
Please read DriverManager/__handles.c in the unixODBC source code as it describes how to protect ODBC drivers running under unixODBC. The 2.2.4 version of this text is reproduced here:

/*
** If compiled with thread support the DM allows four different
** thread strategies
**
** Level 0 - Only the DM internal structures are protected
** the driver is assumed to take care of itself
**
** Level 1 - The driver is protected down to the statement level
** each statement will be protected, and the same for the connect
** level for connect functions, note that descriptors are considered
** equal to statements when it comes to thread protection.
**
** Level 2 - The driver is protected at the connection level. only
** one thread can be in a particular driver at one time
**
** Level 3 - The driver is protected at the env level, only one thing
** at a time.
**
** By default the driver open connections with a lock level of 3,
** this can be changed by adding the line
**
** Threading = N
**
** to the driver entry in odbcinst.ini, where N is the locking level
** (0-3)
**/

For mt distributions of OOB you can set Threading = 0 as the OOB
client is thread-safe.

[3]
One of the ways the OOB client maintains thread-safety is by holding a mutex on the socket created for each ODBC connection. The protocol used on the socket is therefore not asynchronous and multiple threads cannot use the socket created for an ODBC connection at the same time. i.e. an ODBC call from the application results in one or more Remote Procedure Calls (RPCs) over the socket; each one is a send with the request then wait for response. The socket layer is thread-safe because the OOB does not allow multiple threads to use the socket at the same time.

e.g. thread1 calls SQLAllocHandle and a RPC call is made. The kernel switches to thread2 which also calls SQLAllocHandle which also results in an RPC call. OOB will block thread2 until thread1's SQLAllocHandle RPC has completed.

You can get around this by making each thread open a new ODBC connection which results in multiple sockets.

[4]
Be careful to avoid database locks when using multiple threads on the same ODBC connection. In fact, this is one of the biggest reasons why not to usemultiple threads on the same database handle. Because of the way locking is implemented in some database engines and in the OOB client you can deadlock if you use multiple threads per connection. Some database engines or ODBC drivers spin-wait on locks.

e.g. Suppose thread1 does something which locks table S. thread2 now attempts an operation on table S but it is locked. The ODBC driver will spin-wait thread2 so thread2 is stuck in the ODBC driver and hence as far as the OOB client is concerned it has not returned from the RPC and the socket is blocked for thread1. Now when thread1 attempts to do something the OOB client will block thread1 because thread2 has not returned from the RPC.

Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.