Click here to Skip to main content
15,861,172 members
Articles / Web Development / ASP.NET

Connection Pooling in multithreaded applications

Rate me:
Please Sign up or sign in to vote.
3.29/5 (8 votes)
1 Mar 2010CPOL3 min read 89.3K   3.3K   24   8
Approaches describing a design to handle connection objects and pools in C#.

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:

Singleton.gif

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:

Multiconnection.gif

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:

ActivityMonitor.gif

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

ConnectionCount.gif

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Australia Australia
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionMultiple Connection Pin
tesnep9-Aug-12 21:23
professionaltesnep9-Aug-12 21:23 
GeneralGreat Idea Pin
Prashant A. Patil2-Mar-10 3:43
Prashant A. Patil2-Mar-10 3:43 
GeneralMy vote of 1 Pin
R. Giskard Reventlov1-Mar-10 21:17
R. Giskard Reventlov1-Mar-10 21:17 
GeneralRe: My vote of 1 Pin
User-Rock1-Mar-10 21:34
User-Rock1-Mar-10 21:34 
GeneralFormatting and more... Pin
Paulo Zemek1-Mar-10 6:18
mvaPaulo Zemek1-Mar-10 6:18 
AnswerRe: Formatting and more... [modified] Pin
User-Rock2-Mar-10 7:57
User-Rock2-Mar-10 7:57 
GeneralRe: Formatting and more... Pin
Paulo Zemek3-Mar-10 1:29
mvaPaulo Zemek3-Mar-10 1:29 
GeneralRe: Formatting and more... [modified] Pin
wshcdr19-Nov-11 8:35
wshcdr19-Nov-11 8:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.