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: 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:

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:

Return to Contents Page: ODBC Thread Safety     Return to Issues Page
Copyright © 1998 FFE Software, Inc. All Rights Reserved WorldWide