Concurrent Database Commands and C++

Executing multiple database commands within different threads

Harold and John are developers for Document Access and can be contacted at [email protected] and [email protected], respectively.

During the process of porting multithreaded server applications from HP-UX System 9 to Windows NT, we encountered problems with the execution of multiple database commands within different threads using the same database connection. The applications worked properly on HP(UX), but after executing the recompiled and relinked HP-UX source code on an NT system, the applications failed when they tried to access the database. On HP-UX System 9, threads share the same process space and, within each thread, it is possible for multiple database commands to use the same database connection, so database commands can execute concurrently. Under Windows NT, each thread has its one process space; therefore, database commands can only be executed in the thread in which the database connection is made. Because we didn't want each thread to create its own database connection, these threads must execute commands via "database threads." In this article, we will explain how we resolved this problem by using design patterns.

Design Patterns

Class structures developed to solve problems in one program can often be used to solve similar problems in other programs. For instance, common object structures can be reused. If you describe a structure and its objects' interaction and interrelation, and give some examples of the structure's use, software developers can map your structure to their problems.

Design Patterns: Elements of Reusable Object Oriented Software, by Erich Gamma, Richard Helm, Ralph Johnson, and John Vlissides (Addison-Wesley, 1994), provides suggestions for creating coding patterns in your software. Gamma et al. distinguishes three families of patterns:

• Creational patterns, which deal with the creation of objects.
• Structural patterns, which describe the composition of objects.
• Behavioral patterns, which focus on the communication and behavior of objects.

We will describe a pattern for each of these purposes: the Singleton, Strategy, and Bridge pattern, respectively. Figure 1 presents the Singleton and Strategy patterns represented in terms of a class association diagram (CAD). The Singleton is represented as the class CArrayDbase. From this class, there could only be one instance throughout the program; hence, it is called a "Singleton." The Strategy pattern separates the algorithm from the class, so that the algorithm can vary independently from the class. In Figure 1, the class is represented by the CDbase class, while the algorithms are represented by CDbCommand and its derivations. The Bridge pattern can be used to achieve portability.

Implementation

To illustrate how you use these patterns, we'll approach the problem from a database perspective. The complete source code for our multithreaded library is available electronically (see "Availability," page 3). The database used is an Oracle database, and the database transactions are implemented using Oracle ProC as an embedded database command language. For setting up a multithreaded environment for the applications, we use the HP-UX DCE pthread library for HP(UX). For Windows NT, we use Digital's DCE pthread library (Version 1.1).

At the outset, two classes from our code library must be discussed. The classes CThread and CMutex are wrapped around the C-API of DCE and are used for obtaining an object-oriented skeleton for building multithreaded applications. The CThread class () consists of basic thread functions such as Create (creates a thread and start the thread-processing loop); Cancel (stops the created thread); Detach (frees the allocated thread space from memory); and Process (a virtual function, which is implemented in a derived class, where the actual thread execution takes place). See Listing One.

The CMutex class (Listing Two) is used for mutually exclusive use of objects in our program. Through this class, instances of objects and threads can be locked and unlocked. The class has three member functions: Lock(), Trylock(), and Unlock(). With CMutex, you can ensure that data or functions are used by only one thread at a time, if necessary.

The solution to our porting problem sounds simple: Construct a mechanism of special database threads for SQL execution that can be used by the normal application threads. If, within one thread, multiple database actions are executed concurrently, make sure the different actions don't disturb each other. Because different classes in the program will use the database for retrieving, adding, updating, and deleting data, you could use a global point of access for database connections. If a database command executes, it must ensure that it is the only one that uses that connection within its thread of execution. Each implemented database action makes use of a locking mechanism to ensure that there is only one connection to the database at a time in the thread; however, in another thread, another database action can be executed at the same time. In this way, the database can be concurrently accessed.

In our program, the actual database connection is maintained in a thread class CDbase, which requires writing an object for controlling the database connections. On the old HP(UX), a global pointer to a CDbase object was defined, so every class or module from the program could access this object via that global pointer for executing database commands. But can we avoid global variables and address this problem with a design pattern instead?

If you examine the patterns in the book by Gamma et al., you will see the Singleton is a logical choice. The intent of this pattern is to define a class that has only one instance and that provides a point for global access to that instance. The class itself is responsible for ensuring that a unique instance exists. The Singleton pattern is used within the CArrayDbase class (Listing Three).

The constructor of this class checks whether there is already an instance by using the static pointer g_pDbArray. If not, it will set this pointer to its own address, then create the database connection objects. References to these objects are stored in the static array m_pDbases. The destructor of the class checks to see if the static pointer points to itself. If so, it will reset that pointer. The static functions in the class work on the attributes of the object, referred to by g_pDbArray. If that pointer does not point to a valid object, the static functions will throw exceptions.

CArrayDbase has a link association with the mutex class (CMutex) for locking and unlocking the usage of the Singleton instance, to make sure that only one instance of this object is used at a time. The parameter m_nNrDbases holds the number of database connections. The ReserveDbase function returns a pointer to the first available database connection. If, later on, this pointer is passed as a parameter of ReleaseDbase, the connection is freed. The object is created only once; therefore, the Singleton pattern is a typical example of a creational pattern.

Another important object is CDbase (Listing Four), which is derived from CThread and maintains the real connection to the database. Through this class, the actual database commands are executed. Within the constructor, the Create method of the CThread base class is called. The thread that is created will immediately jump into the Process method of the CDbase instance. In this method, the first thing to do is make the database connection, after which the thread is ready to execute the database commands. When the database connection is to be closed, the thread will jump out of the loop in which the database commands are executed and close the database connection. This also ends the process method and finishes the thread.

Again, the intent of the Strategy pattern is to define a family of algorithms where each class encapsulates its own algorithm implementation. There is an abstract base class that prescribes the template of the algorithm by its pure virtual functions. Deriving from this base class enables the implementation of a new algorithm. Introducing a new algorithm changes the behavior of the implementation; therefore, the Strategy pattern is called a "behavioral" pattern.

The Strategy implementation in the program consists of an abstract base class CDbCommand (see Figure 1 and Listing Five), which is the template for our database algorithms. The implementation of the various algorithms can be achieved by deriving from this abstract class. The base class has two virtual methods, Do() and Execute(). The Do() method is the interface of the clients (that is, applications that use the database classes). Within this Do() method, the CDbase object is instructed to execute this database command. The Execute method executes the actual database actions. This method is called by the process thread of the CDbase object, in which the actual database connection is made. The transfer of the database command from the client thread to the process thread is achieved by two mutexes, startsql and endsql.

Putting the connected classes together, you obtain the object model in Figure 1. In this object model, only the methods that you need for resolving the database problem are mentioned. For the complete interface of each class, study Listings One through Five. CDbase is an aggregation of CArrayDbase because the database object is part of the array of database connections. The association is also qualified through the usage of CurCon, which is an integer containing the database connection number that is the position in the array of database connections. CDbase has a parent abstract base class CThread. The mutex class contains several associations to CArrayDbase and CDbase for the protection of attributes, class instances, or functionality. Between CDbase and CDbCommand you see an association called "executes." This means that a database command is executed in the database class. The abstract class CDbCommand has two abstract members, Do() and Execute(). These class members are implemented in their derived classes CDbXXX where the actual database algorithm is implemented.

While Figure 1 only shows the commit command, other commands (like rollback, connect, disconnect, and so on) or queries (such as "find all persons younger than 30 years old who live in Holland") are defined here as CDbCommand-derived classes. The event flow of the execution of a database command is shown in the event-trace diagram (ETD); see Figure 2.

The call for starting this event trace could look like the following:

CDbCommit lCommit(*this); lCommit.Do();

As Figure 2 illustrates, the ETD starts from the source code where two create arrows are drawn. Create denotes that a constructor is called. At the top, the CDbase constructor is called and a thread is created for a connection to the database. The thread locks startsql, which means the thread is waiting until a database command has to be executed. When the client constructs a CDbCommand, it calls the ReserveDbase method, which looks for the first free database connection and locks the database object for usage (Trylock). Next, the client calls the command Do() method, which calls the database Do() method. Within this method, the following actions are performed:

1. First, the docmd mutex is locked, and the CDbase object makes sure that only one client uses the Do() method at a time.

2. The startsql mutex is then unlocked, informing the processing thread that a database command needs to be executed.

3. The endsql mutex is locked until execution of the database command is completed.

4. The processing thread, which is waiting until it can lock the startsql mutex (unlocked in step 2), instructs the database command object to execute the database actions.

5. When execution is finished, the processing thread unlocks the endsql mutex, causing the client thread to continue.

6. The client thread unlocks the docmd mutex, returning the results of the database action.

7. By releasing the database connection, other threads use this object for performing their database actions.

At this point, the command is executed safely in one thread. The locking mechanism prevents the second command from disturbing execution of the first. This is true whether the second (interrupting) command comes from the same or a different thread. Database commands can execute concurrently through multiple threads.

Extensions

The main intent of the Bridge pattern is to decouple the class structure from its implementation, thus achieving portability. This means that you could use several implementations based on an abstract class structure with an implementation class that is part of an abstraction class. Figure 3 is the result of translating this to the database-command implementation.

The client creates the implementation instance, for example, COdbcCommit, and uses this instance for the creation of the CDbCommit object. The CDbase class does not know which implementation object is used because it communicates only with the CDbCommit instance. The entire database structure is now independent of the chosen database. The disadvantage is that the client is responsible for the creation of the implementation and bridge objects. This means that porting an application to another database still is difficult, because the creation of the implementation objects is spread all over the client's source code. By using the Abstract Factory (see Gamma et al.) pattern, the creation of the implementation objects can be centralized. This can even allow a for change to a different database at run time.

Conclusion

By mapping design problems to programming problems, you'll end up with software solutions that are easy to extend and reuse. And by documenting these solutions with techniques such as OMT notation, you can easily discuss them with other software developers and understand your own source code long after applications have shipped.

Listing One

class CThread;void ThreadFunc(CThread *);


</p>
class CThread 
{
public:
        CThread();
        virtual ~CThread();


</p>
        void    Create();
        void    Cancel();
        void    Detach();
        void    Join();


</p>
        friend  void    ThreadFunc(CThread *);
        virtual void    Process(void) = 0;
private:
        pthread_t       m_Thread;
};

Back to Article

Listing Two

class CMutex {
public:
        CMutex();
        ~CMutex();
        void    Lock();
        void    Unlock();
        int     TryLock();
private:
        pthread_mutex_t         m_Mutex;
};

Back to Article

Listing Three

#define MAX_NR_DBASES   15

</p>
class CArrayDbase 
{
private:
    // Pointer to global database connection
    static CArrayDbase      *g_pDbArray;                    
    // Mutex to make sure only one reserve/release at a time
    static CMutex           g_mtxArray;                 
    // Array of database connections
    CDbase*             m_pDbases[MAX_NR_DBASES];   
    // Number of database connections
    int             m_nNrDbases;
public:
    CArrayDbase(int nNrConnections, const char *szUsr, 
                                   const char *szPasswd, const char *szDB);
    virtual ~CArrayDbase();
    // Get pointer to free database connection and reserve database connnection
    static  CDbase*     ReserveDbase();
    // Release database connection
    static  void        ReleaseDbase(CDbase &dbase);
private:
    static void         Lock();
    static void         Unlock();
};

Back to Article

Listing Four

{private:
    friend class    CArrayDbase;


</p>
    // Locks usage of object
    CMutex      m_mtxInuse; 
    // Instruct dbase-thread to do command
    CMutex      m_mtxStartSql;  
    // Informs instruct-thread command is done
    CMutex      m_mtxEndSql;    
    // Make sure no parallel usage of do 
    CMutex      m_mtxDoCmd;     
    // boolean flag indicates if connected
    boolean         m_bConnected;   
    // Continue flag
    boolean         m_bContinue;    
    // Current command
    CDbCommand*     m_pCommand;     
    // Return code of command execution
    long            m_lSql;         
public:
    CDbase();
    virtual ~CDbase();
    // Command functions
    long            Do(CDbCommand &command);
private:
    // Thread loop overwrite
    virtual void    Process();
    // Inuse Funtions
    boolean         TryLock();
    void            Lock();
    void            Unlock();
};

Back to Article

Listing Five

class CDbCommand{
private:
    friend class CDbase;
    CDbase *    m_pDbase;
protected:
    long        m_lSql;
public:
    CDbCommand();
    CDbCommand(CDbase &dbase);
    virtual ~CDbCommand();


</p>
    virtual void    Do() = 0;


</p>
    // Get and set functions
   CDbase *        Dbase();
    void            Dbase(CDbase &dbase);
private:
    // Is called by database object to instruct command to go
    virtual long    Execute() = 0;
};

Back to Article

DDJ