Home > SQL > Managing connections with Oracle or any other Database

Managing connections with Oracle or any other Database

In case that we have small number of clients that contributes data from Database (our desktop application is classical example where we have exact number of clients known during deployment stage), dilemma is to close or leave database connections open permanently during our Application life time. It’s very important to make right decision at design stage.

Additional aspect that we need to consider of – its Multithreaded environment can cause

AccessViolationException

from Oracle, we have experienced this problem when the same open connection is used from different threads simultaneously.

Following C# code demonstrate simple example of connection usage.

using (DALFunctions  dal = new DALFunctions())

{

    dal.GetData(id);

}


As you probably understand, DALFunctions class have to implement IDisposable interface.
Examples of Data access Layer object class’s “Dispose” method which is called at the end of using scope.
Simply closes connection. Such design meaning that connection not staying open and closed after each access to the Database for any of needed operations (Insert, update, delete and select).

public void Dispose()

{

     //IDbConnection

     m_connection.Close();

}

Running something like this Init() method from constructor of your DAL class, simply open the connection.

public int Init()

{

     return m_connection.Open();

}

Another possible solution is to synchronize database access, by using Lock(syncObj) clause for example. These mean that all benefit that we can get from multithread probably will be lost by synchronizations. In most cases we will to try avoiding such system behavior.

Additional approach might be to manage connection pool and provide connection to each running thread. Such approach has number of disadvantages as well:
1. We don’t know how many threads we will run, so we need to decide about pool size, this can be tricky and demand number of benchmarks.
2. Most databases have their own built in mechanism of connection pooling (Oracle, SQL2005/8 and all the others). Probably we won’t write something better than they already did and tested etc.

From our experience we chose not to work with open connections. After each Database operation we are closing connection that will return to Oracle connection managed internal pool.
As following this change in design we have performed Benchmark to check if there can be performance issue from opening and closing database connections – and results were very satisfying something like 3 millisecond takes to open connection (we did it on Oracle client).

Additional disadvantage of leaving connection open is that part of Oracle odp are closing connections automatically after long IDLE time without giving any indication (something like 30 minutes).

Categories: SQL Tags: ,
  1. Vlad
    March 28th, 2011 at 17:19 | #1

    The conclusion is – close your connections!

  1. No trackbacks yet.