Application model for multithreaded CLI applications

A multithreaded CLI application allows parallel execution of tasks under a single process. The typical application model for multithreaded CLI application consists of following attributes:
  • Designate a master thread which allocates:
    • m "child" threads
    • n connection handles
  • Each task that requires a connection is executed by one of the child threads, and is given one of the n connections by the master thread.
  • Each connection is marked as in use by the master thread until the child thread returns it to the master thread.
  • Any SQLCancel() request is handled by the master thread.
This model allows the master thread to have more threads than connections if the threads are also used to perform non-SQL related tasks, or more connections than threads if the application wants to maintain a pool of active connections to various databases, but limit the number of active tasks.
Note: A multithreaded CLI stored procedure can only connect to the database where the stored procedure is currently executing.

Most importantly, this ensures that two threads are not trying to use the same connection handle at any one time. Although CLI controls access to its resources, the application resources such as bound columns and parameter buffers are not controlled by CLI, and the application must guarantee that a pointer to a buffer is not being used by two threads at any one time. Any deferred arguments must remain valid until the column or parameter has been unbound.

If it is necessary for two threads to share a data buffer, the application must implement some form of synchronization mechanism. For example, in the database-to-database copy scenario where one thread connects to database A and reads data from one connection into a shared application buffer while the other thread connects to database B and concurrently reads from the shared buffer and inserts data into database B, the use of the shared buffer must be synchronized by the application.

Application deadlocks

The application must be aware of the possibility of creating deadlock situations with shared resources in the database and the application.

Db2® can detect deadlocks at the server and rollback one or more transactions to resolve them. An application may still deadlock if:
  • two threads are connected to the same database, and
  • one thread is holding an application resource 'A' and is waiting for a database resource 'B', and
  • the other thread has a lock on the database resource 'B' while waiting for the application resource 'A'.
In this case the Db2 server is only going to see a lock, not a deadlock, and unless the database LockTimeout configuration keyword is set, the application will wait forever.

The application model discussed earlier avoids this problem by not sharing application resources between threads once a thread starts executing on a connection.