Synchronization and Connection pool ---- saw some time delays

Hi MindWalkr

Thanks a lot for your reply.
If i remove the Synchronize key from the getConnection method signature ....
1)  how do we ensure thread safety on the database access methods themselves ?

Because we wouldn't want a single connection to have half of one query and half of another when it executes.

Does the underlying DataSource getConnection handles this ?

Please elt me know

Thank you

>> If i remove the Synchronize key from the getConnection method signature ....
>> 1)  how do we ensure thread safety on the database access methods themselves ?

getConnection () is only getting a connection. After that if you want to synchronize calls to the method which does DB access, you need to synchronize it too.

Exactly, invoking getConnection does just that... getting a connection.

I don't know if the getConnection method itself is thread safe, but if it isn't, you can use a global class variable and use it as a lock. Say:

 private Object getConnLock = new Object();
...
...
synchronized(getConnLock) {
  getConnection();
}

Now to invoke DB queries on the connection, you must do your own pool management. Seems you have an array where you push / pop connection. Just be carefull on getting them from your arrays. You can have a Stack object for that, and synchronize access to it. Once a client gets one connection, it's removed from the stack (no one will use it), and when the client is done, just push it again on the stack.

You're not using Enterprise Java Beans because you have decided not to use them right ? Because the app server already does all this for you with beans (cache pooling). You just have to invoke EJB instances and call methods on them. No worries.

Are you talking about DataSource.getconnection method or the Synchronized getConnection method that i mentioned above ?

Thank you

DataSource.getConnection(). I don't know if it's thread safe, but just to make sure you can use the lock Object I mentioned before.

then your other method getConnection will retrieve connections to a Stack object. Pushing and poping on this stack should be synchronized (you can synchronized on calls to the stack object itself: say:

synchronized(stack) {
  stack.push(connection);
  or
  stack.pop(connection);
}

Hi Thanks for the reply...


You are suggesting me to use the lock on the DataSource.getConnection(), fine ...
But my guess is that even it will give the same problem at peak loads (time delays) . Because all the methods that are waiting for connection (methods that called getConnection( )) will wait at this Synchronized block  until the App server ready with the new pool connections. This wil give the same performance issue.
we don't want the app to hang up at this point.

Ok, this needs more insight.

Is this a standalone Java application ?

If it is, you can do it differently. Your getConnection method must be run on a new thread (decoupling from the AWT thread). You must create some kind of manager where every party interested in a conncetion registers. When the connection is available, you notify the registered parties with an event listening mechanism.

Anyway, you can also implement some kind of threshold (suppose, when you reach connection 15 out of 20) you start getting pools at this point, even if they may not be used. But then with this look-ahead mechanism you guarantee that there will be connections available. But the you'll need to dispose some of the connections when the peak comes down... maybe with some kind of timer.

Well , this is a J2EE application.  
Everything looks fine in the Normal load situations. But getting timedelays in Peak load period.
With the mechanism you described above.. the appserver will never create the New pool of connections (when you reach connection 15 out of 20)  if there are 5 more free connections.  Is there any mechanism to do that ?

> Well , this is a J2EE application.

Why do you have to write your own connection pool then? Most (if not all) J2EE application servers provide sophisticated connection pooling. I think you are better off letting the application server do the pooling job for you, that's why application servers exist at first place :)

Yes, you're right. The application server pools connections for you.

If there's a mechanism to do that kind of threshold thing, it will be implemented by the application server, so it may depend on which one you're using. I don't know if it's possible to do that.

The event mechanism will stop the main threads from blocking on your getConnection method. However the server delay to get a connection for you will still be there. So, it it's a web app, it will take longer for you to get a reply. But if it's a standalone app, your application will not hang graphically.

Yes, Application server is doing the pooling stuff.
Let me explain you clearly ..

We have initial maxpoolCapacity of 20 connections.
In my application we have Synchronized getConnection method (look at this in my first comment) which is calling DataSource.getConnection method to get the connection from Pool.

Now, at the peak load situations all 20 connections are busy and say  thread 10 is calling Synchronized getConnection method to request for the new pooled connections. Obviously it is going to take sometime to create the 20 more  physical  connections and to update  the pool with new connection.  While above thread on process of making the connection pool, i could see lot of other threads stuck at Synchronized getConnection method which calls the DataSource.getConnection method with makes the lock here. Because of this we are seeing timedelays and effecting the performance of the application.

My consern is Can i make the getConnection method non -synchronize ?  if so,

1)  how do we ensure thread safety on the database access methods themselves ?

Because we wouldn't want a single connection to have half of one query and half of another when it executes.

Does the underlying DataSource getConnection handles this ?

With mindMkr suggestion i understood that we can make lock at DataSource.getConnection. But i guess we will experience the same delays  by doing so.

Hi mindWalkr

is there anything that i can do to avoid time delays ?

> 1)  how do we ensure thread safety on the database access methods themselves ?

Well it depends, from what I can see in your code you do not have access to a class variable so it might be safe not to synchronize the method to get the connection from the database, but it heavily depends on the driver implementation. Most of the database drivers are thread safe, datasources also, so you might not need synchronization at all. I suggest you take a look at your application server's document to see if the jdbc drivers it uses and your datasources are thread safe. if they are you do not need to synchronize your method if you do not have any class variables that you use in that method.

The other solution is simply to increase the connection pool so as to have more conenctions available on peak time.

If each thread has its own connection, there should be no problems on the database access methods.

The underlying Datasource getConnection only get you a connection (and I don't know if this is thread safe). After you have the connection, if 2 threads use the SAME connection to prepare a statement and get resultsets, I don't know what could happen here. Best to stick to 1 connection per thread.

The only way I see now to get connections non-blocking is to get them asynchronously. Create a new Thread to get the datasource connection. Use a runnable that takes as constructor the caller instance. You need a new interface to signal the caller that the thread has been fetched.

Thank for the replay ..

Let me check with some of my team mates..
Get back to you shortly
Appriciate your prompt reply

Thank you

Hi

We are using OracleJDBC Drive. Is that thread safe  when accessing the Database ?

I believe it is(but don't take my words for granted). The datasource will probably lock a connection when it is obtained and you wouldn't get the same one. What you can do to ensure 100% thread safety is to use a testing tool like jmeter: http://jakarta.apache.org/jmeter/ and test your application with hundreds of threads and observe what happens.

Hi girionis,

Thanks for your reply. I will try to test this.

Thanks

Is there any other changes that i can do without making the getConnection mehod non-synchronize to eliminate the dime delays in getting the connection at peak loads ?

Thank you

Don't think so. Your code looks fine. You could move the creation of some objects outside the method but then you would need to synchronize on these objects.

So finally your suggestion is to  remove the Synchronize keyword from the getConnection Method signature right ?
Plz Let me know so that i will proceed further...
Thank you

It depends if the datasource is thread safe (which should be). I cannot tell if it is, you should consider the documentation. If it is you can remove the synchronized keyword.

I am trying to change the logic a bit to increase the performance... so,  i want to Cache the Data Source and get the connection from that data source. He is my conserns in doing so...

Can i chache the Data Source  and get the Connection from the Cached DataSource ?

 If yes, How can i request the App Server to increase the Pool size in the Peak load situatons if all the Connections in the Cached DataSource are busy ? (Please provide me sample code )

Please elt me know

Yes you can cache the Datasource. This will either require to write it to a disk (which wouldn't be a good idea since you have the overhead of reading it every time), or cache it in memory which means that you have to make it a member variable. You could make the datasource a member variable if you make sure that no other process/thread changes its value. THis is very important as to not have a different value in the datasource than the one you expect.

> If yes, How can i request the App Server to increase the Pool size in the Peak load situatons if all the Connections in the Cached DataSource are busy ? (Please
>provide me sample code )

You do not have to dfo it programmatically. You will need to do it in application server level. You could probably do it from the console (if there is any) or from the config file. For more info you better take a look at the application server's documentation.

Yes i know that i have to configure in Server console.  Here is the sample code i am using to chche the DataSource. In the code below i have lock on the object.. so no other thread can access at the same time. I have the caching logic in the constructor of the class  And Syschronized the getInstance method. Lets say i have 30 connections in the pool.
For the firest request to the connection i am calling

DataSource . getInstance( ) .getConnection( );

Where my DataSource class is like this  ...

public class DataSource {
 
    private static DataSource instance = null;
    private Map dataSources = new HashMap();
 
    private DataSource(){
         
  Context ctx = null;
       //Get the Properties from the Properties file
       
  Properties env = new Properties();
     String propsFilePath = System.getProperty("propfile");
     String url = System.getProperty("url");
     env.put(Context.PROVIDER_URL, url);
     env.put(Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory");
     try
        {
      ctx = (Context) new InitialContext(env);
           
      //Get the DataSource from JNDI initial context
      DataSource DataSource = (DataSource) ctx.lookup("jdbc/datasource");
      //Cache the Data Source object.

            dataSources.put("DataSource",DataSource);
       
        }
        catch (Exception e)
        {
            Log.write(" Failed Initializing the DataSource ---> ");
        }
 
 }
 
 /* This method ensures that all the classes running on same JVM uses the single instance of DataSource.
  * If the instance not exists, this method creates new instance of DataSource class.
  */
 public static synchronized DataSource getInstance(){
        if (instance == null){
            instance = new DataSource();
        }
        return instance;
    }
 
 
 
 public Connection getConnection() throws SQLException {
  return this.getConnection("DataSource");
    }
 
 /* This method returns a Connection from the cached datasource  to the calling method.
  *
  */
 public Connection getConnection(String dataSourceKey) throws SQLException {
 
  DataSource ds = (DataSource)dataSources.get(dataSourceKey);
  Connection connection = ds.getConnection();
 
  return connection;
 }
 
 
 
}


For the first request to get the connection from the DataSource, the logic in the constructor of the DataSource class will get the data source from the Server and Cache that. So i have no problem until 30 connections in the pool are used up.

At the request for the 31 st  connection what logic  should i implement to request for additional connections from the server ?

Thank you

Sorry but I am not sure what you are trying to do in you class. Are you trying to get 30 instances of the datasource or 30 connections through one instance of the datasource?

Initially i have 30 connections in the Connection pool. So i am trying to get the DataSopurce containing those 30 connections and Caching data source object to a HashMap.

Now my question is what happen if i need more than 30 connections ?

Thank you for your prompt reply

Do you create the connection pool manually? If yes you will need to create more when the pool is empty. Where do you keep the pool? You will need to check the collection object (that keeps the pool) if it is empty and if it is then create more.

If there are more connection-requests than you have in the pool, you will have to make them wait (maybe through wait (), notify ()) - notify it when a connection is free.

we configured the Datasource and  Connection pool in Weblogic server. What do i need to do in this case if i need more connections than initial capacity. How to request for more connections ?  Or does weblogic automatically create more connections when all 30 connection are busy ?

hi  mayankeagle
 I dont want that to happen.. that is root cause of our app hanging up at the peak load.
Do you have any other idea ?

> we configured the Datasource and  Connection pool in Weblogic server. What do i need to do in this case if i need more connections than initial capacity.

Go to the WebLogic console and set a number in the "Capacity Increment" field of your connection pool (click on the connection pool you want and then on the "Connection" tab). The number you will set there is the number of the connections WebLogic will create if the pool is empty and a new request for a connection comes in. This number should not be bigger then the maximum capacity number.

The second thing you can do is to set an initial capacity of more connections.

Yes we did that.
The problem is with the code above (DataSource class) . DataSource Object is created for the first Connection. That object is not null until all the connections are busy ( 30 connection) and even at the time of requesting more  connections (more than 30). So there is no way to get the updated pool from the Weblogic server. Because in the constructor of the class i am looking in the JNDI. As the object is not null there is no way that the constructor of DataSource class is called.   In this case my request for the more connections is not going upto Weblogic server.

What code i need to do to achieve this.

Thank you girionis..
I am on the way to test that is Dev environment. Thank for your patience and help..
Get back to you as soon as i am done with the testing

Thank you all for your help.

Thank you for accepting, glad I was of help :)