ODBC Thread Safety (5)

ODBC in Multi-Threaded Environments (Thread Safety)

Effective Use of ODBC in Multi-Threaded Environments

The general advice of this article is that all calls to ODBC functions using shared handles should be protected (synchronized). For these reasons:

It improves interoperability as defensive coding against ODBC drivers that are not thread-safe and against ODBC drivers claiming to be thread-safe but having defects in their protection scheme.
All ODBC functions (except SQLAllocEnv) store error status in the referenced handle. This is retrieved with the SQLError function. The ODBC function call and possible resulting calls to SQLError should be synchronized between threads.

There are some exceptions to the advice about error processing, which are described below. Otherwise, shared handles should be locked until all error status is retrieved. Even in situations where SQLError is not being called, locking should be used. This allows later insertion of SQLError calls for testing and debugging.

In addition, locking or some type of serialization is often needed to protect other state information carried in handles between function calls. The types of handle states are described in the previous section, Handle States. Serialization techniques are discussed in the section, Synchronization.

The remaining sub-sections describe application techniques for sharing the 3 basic types of ODBC handles - environment, connection and statement, with a minimum of serialization.

Sharing the Environment Handle

The ODBC Specification indicates that an external application or process should use a single environment handle that is shared by local threads. The threads share the environment handle by using it as a common resource for allocating individual connection handles.

Synchronization of the threads is required for allocation of the environment handle. In other words, the handle must be allocated before the threads start to use it, and only one thread can call the SQLAllocEnv function. Normally, Parent/Child Synchronization is used. Once allocated, the environment handle can be shared by multiple threads.

The only operation performed on the shared environment handle is SQLAllocConnect. The sharing threads use SQLAllocConnect to obtain private connection handles.

Optionally, SQLAllocConnect can be called without synchronization, if SQLError is not used. When SQLAllocConnect returns SQL_ERROR, it can only mean a memory allocation failure, so there is no need to call SQLError. SQLAllocConnect is always handled directly by the ODBC Driver Manager, even though the ODBC specification incorrectly states that SQLAllocConnect calls return errors from individual ODBC drivers. SQLAllocConnect errors from individual drivers are returned with the actual connect (SQLConnect, SQLDriverConnect, SQLBrowseConnect).

The only synchronization required for an environment handle is with SQLAllocEnv and SQLFreeEnv. SQLFreeEnv should be issued only after all other thread access to the environment handle is complete - all connection handles have been freed. Often, Parent/Child Synchronization is used.

Sharing Connection Handles

Sharing a connection between multiple threads has more restrictions. The general scheme is that a full connect (SQLConnect, SQLDriverConnect or SQLBrowseConnect) be performed before the handle is shared. The connection handle then can be used to allocate private statement handles (SQLAllocStmt) for multiple threads.

The sharing of a connection handle has the following restrictions:

Connection Options (set with SQLSetConnectOption) should be set before sharing begins and should not be changed.
Connection-level Statement Options (set with SQLSetConnectOption) should be set before sharing begins and should not be changed.
Transactions, there are several choices:
- autocommit, each statement is implicitly committed,
- connection-wide, a single transaction during the entire connection,
- otherwise, commits and rollbacks must be synchronized between threads.

Synchronization of threads is required for allocation of the connection handle and connecting to the datasource. This is normally handled by a single thread that then releases the connection handle to other threads. Parent/Child Synchronization can be used here.

Once connected, the only operation performed on the shared connection handle is SQLAllocStmt. The sharing threads use SQLAllocStmt to obtain private statement handles.

As with sharing the environment handle, SQLAllocStmt can be called without synchronization. The SQLError function is never called, so a return of SQL_ERROR from SQLAllocStmt does not get additional status. SQL_ERROR is assumed to mean memory allocation failure. There is some risk here because SQLAllocStmt is handled by the individual ODBC drivers, and they may return other types of error status. By not calling SQLError, these are basically considered a failure to allocate the statement handle.

The termination of the shared connection handle also needs to be synchronized. After all threads have freed any allocated statement handles, SQLDisconnect and SQLFreeConnect can be called on the shared connection handle. These operations are often performed by the parent thread using Parent/Child Synchronization.

Sharing Statement Handles

There are few possibilities for sharing ODBC statement handles. A statement handle has two major states - prepare and cursor (see Handle States.) Sharing within these states is limited.

Some possible ways of sharing a statement handle:

Only the SQLExecDirect function is called with SQL update statements (INSERT, UPDATE, DELETE) and DDL statements. Bound parameters can be used but require synchronization.
A statement handle with a prepared query (after SQLPrepare is called) can be shared by threads. The threads perform:
- Lock the statement handle (for synchronization.)
- Bind any pre-bound parameters using SQLBindParameter.
- Execute the statement using SQLExecute (and possible SQLParamData and SQLPutData calls.)
- Bind any pre-bound columns using SQLBindCol.
- Fetch the result set using SQLFetch, SQLExtendedFetch (and possible SQLGetData calls.)
- Close the cursor using SQLFreeStmt(SQL_CLOSE).
- Unlock the statement handle.
This type of sharing is only practical for small result sets.
A statement handle that is prepared and executed (in cursor-positioned state) can be shared by threads. The cursor must be scrollable. The sharing threads use SQLExtendedFetch for random access to rowsets. The threads:
- Lock the statement handle.
- Bind any pre-bound columns using SQLBindCol.
- Fetch a rowset using absolute position (SQLExtendedFetch with SQL_FETCH_ABSOLUTE plus possible SQLGetData calls.)
- Unlock the statement handle.

Return to Contents Page: ODBC Thread Safety Return to Issues Page