Connection pooling in multithreaded
applications
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:
1.
Need to make sure that
the connection object is disposed properly.
2.
Connection object
needs to pass through methods.
Here is the block
diagram for the multiple connection approach:
Posts
