Click here to Skip to main content
Click here to Skip to main content

Connection Pooling in multithreaded applications

, 1 Mar 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
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)

Share

About the Author

User-Rock
Software Developer
India India
No Biography provided

Comments and Discussions

 
QuestionMultiple Connection Pinmembertesnep9-Aug-12 22:23 
GeneralGreat Idea PinmemberPrashant A. Patil2-Mar-10 4:43 
GeneralMy vote of 1 Pinmemberdigital man1-Mar-10 22:17 
GeneralRe: My vote of 1 PinmemberRakeshGunijan1-Mar-10 22:34 
GeneralFormatting and more... PinmemberPaulo Zemek1-Mar-10 7:18 
The article needs a better formatting.
Also, it will be good to show some helper object. For example, I usually create a class that manages the connection and the transaction as a single one. So, when you call CreateCommand, it creates the command already linked to the transaction. And, if you use a ThreadStatic variable, you don't even need to pass the connection to every method using it... you use the "context connection".
 
The code will look like:
using(var connection = new ContextConnection())
{
  // the new ContextConnection will create and open a connection, 
  // if none exists, or will use the already existing one.

  //... do whatever you need... if you don't pass the connection to 
  // any method, it can get a connection with the same clause used here.
}
 
The ContextConnection class must implement Dispose. If it created the connection, it closes the connection and sets the ThreadStatic variable to null. If it used the already set connection, it does nothing in Dispose.
AnswerRe: Formatting and more... [modified] PinmemberRakeshGunijan2-Mar-10 8:57 
GeneralRe: Formatting and more... PinmemberPaulo Zemek3-Mar-10 2:29 
GeneralRe: Formatting and more... [modified] Pinmemberwshcdr19-Nov-11 9:35 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141220.1 | Last Updated 1 Mar 2010
Article Copyright 2010 by User-Rock
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid