JDBC auto commit for connection shared across threads

Question

I have a servlet where I get a Connection object which is then handed to two worker threads for various activities. I now need to add a transaction on one thread.

If I start a transaction like this: connection.setAutoCommit(false);

would that impact both threads? I think it would.

Do I have to get a separate connection of each thread?

Thanks

Answer 1

I think what you are doing is very bad practice. You can't share a JDBC connection among threads.

If you are running under an application server (like TOMCAT/JBoss/WebSphere/WebLogic) use a proper DataSource to get your connections as you need them.

Look at your Application Server documentation to get information on how to do that.

You will have something like this in your servlet:

public void doGet(HttpServletRequest req, HttpServletResponse resp)
{
    Connection c = null;
    try {
        c = ...; /* preferred way of getting a connection in your AppServer
        // do what you need with your JDBC connection
    } catch (Exception e) {
        // handle errors
    } finally {
        c.close(); /* you will need another TRY/CATCH here */
    }
}

Similarly, your worker threads will have something like:

public void run()
{
    Connection c = null;
    try {
        c = ...; /* preferred way of getting a connection in your AppServer
        // do what you need with your JDBC connection
    } catch (Exception e) {
        // handle errors
    } finally {
        c.close(); /* you will need another TRY/CATCH here */
    }
}

Eventually, you could set auto commit to whatever you need on separate connection objects.