Thread-safe global sqlca struct for Oracle database access

Question

I have a multi-threaded (posix thread or pthread based) C application that uses Oracle Pro C precompiler. The application uses a global sqlca struct. In one .c file, it includes global sqlca struct definition for Oracle database access as:

   #include <sqlca.h>

And in all other ,c files, it uses as follows:

   #define SQLCA_STORAGE_CLASS extern
   #include <sqlca.h>

My question is if more than one thread tries to access a database table for query, insert or update and uses global sqlca object, how do I guarantee mutual exclusion or make it thread-safe access? Also, when I query vs insert / update, do they all use sqlca struct?

Answer 1

You could have a global function that grabs a mutex object, and a global function that returns a mutex object.

The thread that needs to use the database attempts to call the function that grabs a mutex. If the mutex is already in use, the function returns a 0. If the mutex is available, the grab mutex function marks the mutex as unavailable, and returns a 1 for success.

Any subsequent calls to grab mutex will fail, until the thread that successfully grabbed the mutex calls the return mutex function.

Any thread that attempts and fails to grab the mutex CAN be put into a loop until the mutex is successfully grabbed (ie the thread will wait for the mutex). A time out can also be instituted here.

The mutex object can be as simple as a bool, or you can use other more complex mutexes (windows.h has mutex objects).

If you create your own mutex, it is VITALLY important that it be volatile.

No thread may access the database if it doesn't have possession of the mutex.