Troubleshooting multi-threaded embedded SQL applications

An application that uses multiple threads is more complex than a single-threaded application. This extra complexity can potentially lead to some unexpected problems.
When writing a multi-threaded application, following context issues must be considered:
Database dependencies between two or more contexts.
Each context in an application has its own set of database resources, including locks on database objects. This characteristic makes it possible for two contexts, if they are accessing the same database object, to deadlock. When the database manager detect a deadlock, SQLCODE -911 is returned to the application and its unit of work is rolled back.
Application dependencies between two or more contexts.
Be careful with any programming techniques that establish inter-context dependencies. Latches, semaphores, and critical sections are examples of programming techniques that can establish such dependencies. If an application has two contexts that have both application and database dependencies between the contexts, it is possible for the application to become deadlocked. If some of the dependencies are outside of the database manager, the deadlock is not detected, thus the application gets suspended or hung.
Deadlock prevention for multiple contexts.
Because the database manager cannot detect deadlocks between threads, code your application in a way that avoids deadlocks.
As an example of a deadlock that the database manager cannot detect, consider an application that has two contexts, both of which access a common data structure. To avoid problems where both contexts change the data structure simultaneously, the data structure is protected by a semaphore. The sample contexts are shown in following pseudocode: 
   context 1
   SELECT * FROM TAB1 FOR UPDATE.... 
   UPDATE TAB1 SET.... 
   get semaphore
   access data structure
   release semaphore
   COMMIT
 
   context 2
   get semaphore
   access data structure
   SELECT * FROM TAB1...
   release semaphore
   COMMIT

Suppose the first context successfully executes the SELECT and the UPDATE statements, while the second context gets the semaphore and accesses the data structure. The first context now tries to get the semaphore, but it cannot because the second context is holding the semaphore. The second context now attempts to read a row from table TAB1, but it stops on a database lock held by the first context. The application is now in a state where context 1 cannot finish before context 2 is done and context 2 is waiting for context 1 to finish. The application is deadlocked, but because the database manager does not know that about the semaphore dependency neither context is rolled back. The unresolved dependency leaves the application suspended.

You can avoid the deadlock that can occur for the previous example in several ways.
  • Release all locks held before obtaining the semaphore.

    Change the code for context 1 to perform a commit before it gets the semaphore.

  • Do not code SQL statements inside a section protected by semaphores.

    Change the code for context 2 to release the semaphore before doing the SELECT.

  • Code all SQL statements within semaphores.

    Change the code for context 1 to obtain the semaphore before running the SELECT statement. While this technique will work, it is not highly recommended because the semaphores will serialize access to the database manager, which potentially negates the benefits of using multiple threads.

  • Set the locktimeout database configuration parameter to a value other than -1.

    While a value other than -1 will not prevent the deadlock, it will allow execution to resume. Context 2 is eventually rolled back because it is unable to obtain the requested lock. When handling the rollback error, context 2 should release the semaphore. Once the semaphore has been released, context 1 can continue and context 2 is free to try again its work.

The techniques for avoiding deadlocks are described in terms of the example, but you can apply them to all multi-threaded applications. In general, treat the database manager as you would treat any protected resource and you should not run into problems with multi-threaded applications.