Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

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

On Windows, yes.

On some UNIX operating systems, a thread-safe version of the Easysoft ODBC-ODBC Bridge client is not yet available. However, for Linux, Solaris, Tru64, and FreeBSD, a thread-safe ODBC-ODBC Bridge distribution is available. Thread-safe distributions have "-mt" in the filename.

There are a few issues specific to the ODBC-ODBC Bridge and unixODBC regarding threads:

  1. If you build unixODBC from source, make sure you include --enable-threads=yes on the configure line if you want a thread-safe Driver Manager.
  2. 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 the ODBC-ODBC Bridge, use Threading = 0 as the ODBC-ODBC Bridge client is thread-safe.

  3. One of the ways the ODBC-ODBC Bridge client maintains thread-safety is by holding a mutex on the socket created for each ODBC connection. The socket protocol is therefore not asynchronous and multiple threads cannot use the socket created for an ODBC connection at the same time. 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 ODBC-ODBC Bridge doesn't allow multiple threads to use the socket at the same time.

    For example, thread1 calls SQLAllocHandle and an RPC call is made. The kernel switches to thread2, which also calls SQLAllocHandle. This also results in an RPC call. ODBC-ODBC Bridge will block thread2 until thread1's SQLAllocHandle RPC has completed.

    You can work around this by making each thread open a new ODBC connection. This 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 use multiple threads on the same database handle. Because of the way locking is implemented in some database engines and in the ODBC-ODBC Bridge client, you can deadlock if you use multiple threads per connection. Some database engines or ODBC drivers spin-wait on locks.

    For example, suppose thread1 does something that 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, as far as the ODBC-ODBC Bridge client is concerned, it has not returned from the RPC and thread1's socket is blocked. Now when thread1 attempts to do something, the ODBC-ODBC Bridge client blocks thread1, because thread2 has not returned from the RPC.