Database hangs while using static connection

Question

I have one basic question here - I am working with a new Database named XpressMP.

I wrote a multithreaded program to insert lots of records into the database. I noticed one most important thing while making a connection to database:

In my program, If I am using something like below-

class Task implements Runnable {

   private Connection dbConnection = null;
   private PreparedStatement preparedStatement = null;

   //other stuff

 @Override
  public void run() {

   try {

      dbConnection = getDBConnection();

   } finally {
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
                preparedStatement = null;
            } catch (SQLException e) {
            }
        }
        if (dbConnection != null) {
            try {
                dbConnection.close();
                dbConnection = null;
            } catch (SQLException e) {
            }
        }
    }
  }
}

it works fine and I am closing each and every connection in the final block. And I can insert a lot more rows to database.

But as soon as I start using static connection intentionally (which I shouldn’t be doing) with multiple threads-

class Task implements Runnable {

   private static Connection dbConnection = null;
   private static PreparedStatement preparedStatement = null;

   //other stuff

 @Override
  public void run() {

  try {

    dbConnection = getDBConnection();

   } finally {
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
                preparedStatement = null;
            } catch (SQLException e) {

            }
        }
        if (dbConnection != null) {
            try {
                dbConnection.close();
                dbConnection = null;
            } catch (SQLException e) {
            }
        }
    }
  }
}

The whole Database hangs. I cannot work with that database until I restart the db. So that means there is some problem with the JDBC driver that I have. I have told the DBA about this problem and they are talking with the folks who owned that Database.

But my question is why it hangs. For what reason?

You are sharing object references among various threads... Sometimes, one connection object is created and before it is closed, another thread starts, creating another object and never closing the old one. So, that connection is still alive and in some point of the time, too much connections will be opened and your DBMS will crash, spiting some error like "too many connections". The problem is not your drive, but your code. — davidbuzatto
– davidbuzatto, Commented Feb 8, 2013 at 3:14

davidbuzatto · Accepted Answer · 2013-02-11 02:06:09Z

1

You are sharing one object reference variable among various threads... Sometimes, one connection object is created and before it is closed (and its reference be set to null), another thread starts, creating another object and never closing the old one. So, that connection is still alive and in some point of the time, too much connections will be opened and your DBMS may crash, spiting some error like "too many connections". The problem is not your drive, but your code. Note that some thread may close a connection that was created by another thread too!

I think you are not seeing the exception that is being throw because you not have a catch in you outer try. The exception will be throw when ou try to create an connection. Try to put a catch there to show the stack trace. It will work (it might work) if your database is detecting this problem.

edited Feb 11, 2013 at 2:06

answered Feb 8, 2013 at 3:17

davidbuzatto

9,4841 gold badge48 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

arsenal Over a year ago

Problem is, it is not spitting any exception. Database gets hanged.. You cannot do anything on that.. Its just that you need to reboot the whole DB.

davidbuzatto Over a year ago

@TechGeeky: In this case, maybe your DBMS is not able to deal with this problem, but I insist that in your specific case, your code is not ok. Did you try to do the same with some other DBMS? I think you will have the same problem. I think I know why you are not seeing the DBMS error. I'm updating my answer.

arsenal Over a year ago

Yes I tried doing the same thing with Oracle Database.. Oracle db is throwing exception and it is not hanged as well. I can still us the db to do select from sql developer. I know it's not good to use static connection.

davidbuzatto Over a year ago

@TechGeeky: So, you have two problems: Your code and your DBMS.

Mikhail Vladimirov · Accepted Answer · 2013-02-08 04:42:43Z

1

JDBC is not thread-safe so you should not use one Connection object from several threads in parallel, regardless of whether it is stored in static field or not. Once you do this, it is possible that one thread will send its query in the middle of other thread's query, so database will receive complete garbage and, if database is not rock-solid, this garbage may break down the database.

answered Feb 8, 2013 at 4:42

Mikhail Vladimirov

13.9k1 gold badge40 silver badges41 bronze badges

Collectives™ on Stack Overflow

Database hangs while using static connection

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related