ODBC Thread Safety (3)

ODBC in Multi-Threaded Environments (Thread Safety)

Synchronization

To prevent conflicts between threads sharing ODBC handles, an application needs to synchronize access to the handles. Even when a thread-safe ODBC driver is used, there are operations that need to be synchronized. This section describes synchronization techniques that are portable to most operating environments.

The purpose of synchronization in this context is to serialize access by threads to a shared ODBC handle. Serialization means that only one thread at a time can utilize a handle. While one thread is calling one or more ODBC functions using a handle, other threads are blocked from accessing the handle. Synchronization may also involve controlling which specific thread is allowed access to the handle.

Three synchronization techniques are discussed:

Locking
Parent/Child
Client/Server

Locking Synchronization

Locking for synchronization of threads requires use of a operating system facility for locking resources like semaphores. The semaphore should be set to allow only one thread to enter at a time. A thread desiring to use a shared ODBC handle locks the semaphore before calling an ODBC function. While the semaphore is locked, other threads attempting to lock the semaphore are blocked (suspended). The thread that locked the semaphore makes one or more ODBC function calls on the handle and then unlocks the semaphore. This allows another thread waiting for the semaphore to complete the lock and begin using the handle.

Threads should minimize the period thay have a semaphore locked. They should not wait for any other events during the period. For fine-grained multi-threading, a semaphore should be allocated for each shared ODBC handle.

Note: Caution should be exercised in locking multiple semaphores as this can produce deadlocks.

Parent/Child Synchronization

In this scenario, one thread is the parent thread with other threads as children. The parent thread controls the child threads, usually starting them. It initializes ODBC handles and passes them to the children.

For example, the parent allocates a connection handle and performs a connect. It then, either,

passes the connection handle to each child thread, and the child threads allocate private statement handles off the connection handle,
or, allocates the statement handles for each child and passes them to the child thread.

Often, the parent passes the ODBC handle to the child when it starts the child thread.

In Parent/Child synchronization, the parent thread controls the synchronization. It serializes access to a shared handle. It performs any initialization on the handle before passing it to the child thread. The parent thread waits on the child thread(s) to finish processing before terminating a handle, often waiting for the child thread to terminate.

Client/Server Synchronization

With this technique, one thread is the server thread and the other threads are clients. The server thread performs all access to ODBC as a service to the client threads. A client thread passes a request to execute an ODBC operation (involving multiple ODBC functions). The server executes the requested calls on behalf of the client, returning results to the client when done.

The client threads and server thread can communicate with a variety of methods:

shared memory controlled by a semaphore,
memory resident file,
other inter-thread communication capabilities provided by the operating environment.

In Client/Server synchronization, the server thread controls the synchronization. It is the only thread that accesses an ODBC handle. The server thread serializes access to an ODBC handle by client threads, by servicing one client request at a time.

The above synchronization techniques are used to serialize access to shared ODBC handles. This protects the handle state information carried between ODBC function calls. The next section, Handle States, describes the types of state information carried in the various ODBC handles.

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