Connection Pooling in multithreaded applications

• Download source code - 18.1 KB

Introduction

This article talks about developing design approaches for multithreaded applications performing database operations. While developing a multithreaded database application, we always end up scratching our heads answering (or trying to answer) mischievous questions as:

• Who should create the connection object?
• Should a connection object be a property of the business object? Or should it be used as a utility?
• How to handle transaction operations?
• Who should dispose the connection object?

Looks like seven W's of Wisdoms are making enough noise in our head. I found my way of answering these questions, and I am sharing them here with you. I have tried two approaches for it, which are described here.

Single connection object, multiple transactions objects

I have used the Singleton design pattern to make sure that only one connection object is created. The same connection object is shared across multiple threads. It is now every individual thread's responsibility to handle the transaction. Every thread will create its own transaction object and will pass it to all commands it will be executing. So straight away, the thread should maintain its own commit and rollback policy. Thus, a single connection may execute multiple transactions simultaneously, wholly managed by the calling threads.

As parallel transactions are not supported by the database, make sure the code block handling the opening of the connection and transaction creation is executed under a lock mechanism. Thus, in this approach, though we can use the same connection object, we have to make sure the code is thread safe (use of lock).

Advantages:

• Implements a Singleton, shares one connection object across multiple calling threads.
• No need to dispose the connection object (but you must call the close method).

Disadvantages:

• Does not use the connection pool feature, as only one connection object is created and used.
• Increases execution time as the command must be executed using the same connection object.

Here is the block diagram for the singleton approach:

Multiple connection objects

The multiple connection approach is slightly different. This one gives the calling code control of the connection object. It becomes the calling code's responsibility to use and dispose the connection object. In an ideal scenario, the calling code (Business Layer) should not hold the reference of the connection object. It should request for the connection object when required, and use and pass it to its sub-routines, if required. Thus, the connection object is opened in a method and closed in the same one. The pseudo-code will look like:

Method ()
{
 - Create local connection object, open it
 - Do transaction with database.
 - Pass connection object to other methods called from this.
 - Commit or rollback transaction
 - Close connection, dispose it
}

This approach allows us to create multiple connection objects and execute them in parallel. But, it also enforces some conditions as:

• Calling code should take ownership of the connection object.
• Calling code should handle the connection pool by declaring it in the connection string.
• As there could be multiple connections opened simultaneously, it's the calling code which must maintain the relationship between the connection and its transaction.

Advantages:

• Uses connection pool to create multiple connections simultaneously.
• Faster compared to singleton, as multiple connections will execute their own transactions.

Disadvantages:

• Need to make sure that the connection object is disposed properly.
• Connection object needs to pass through methods.

Here is the block diagram for the multiple connection approach:

That's it. I have tried my best to come with the best approach. Hope you will find this interesting. Feedback is most welcomed. Have fun...

Using the code

You can refer to the attached source code file to find out more about the above mentioned approaches.

Points of interest

Debugging tips: Use SQL server Management Studio Activity Monitor to find the number of connections and transactions:

Similarly, you can use the following database query to find out the active connection objects per database:

Find the attached Scripts.txt file to create the required database. Make sure you have replaced the default connection string in the source code with your one.

History

• Version 0: Drafting initial version.