Where to close connection/file/logs with multiple threads?

Question

Assume the following pseudo code for a simple two thread scenario:

I have two threads, I would like to insert data to different tables to database. On thread1, I would like to insert to some table, at same time, I want to insert other data to thread 2. My question is how/where to place connection.close(), if I place it on thread 1 and it executes while thread2 is still processing, or vice versa, if thread2 has finished and closed the connection, yet thread1 hasn't finished.

Note, the database is just an example, it can be anything like a file,logger..etc.

class Thread1{
    DataBaseConnection connection;
    main(){
        threadPool = Executors.newFixedThreadPool(1);
        connection.open();
        if(ThisMightTakeSomeTime)
        threadPool.submit(new MyRunnable(connection));
        InsertDataToDataBase(Table A, Table B));
        connection.Close(); //What if thread2 isn't done yet?
    }
}

public class MyRunnable implements Runnable {
    MyRunnable(connection){}
    @override
    void Run() { ...}
    void TaskThatMayTakeWhile(){
        ...get data ...
        ...Connection.InsertToTables(table X, table Y)
    }
}

Answer 1

My question is how/where to place connection.close(),

To start, as far as I know, you should not be sharing a single connection with 2 different threads. Each thread should have it's own database connection, possibly utilizing a database connection pool such as Apache's DBCP.

Once you have multiple connections, I would have each thread manage and release its own connection back to the pool. You should make sure this is done in a finally block to make sure that if there is a database exception, the connection is still released.

If you are forced to have multiple threads share the same connection then they will have to use synchronized to make sure they have an exclusive lock to it:

 synchronized (connection) {
    // use the connection
 }

As to when to close it if it is shared, you could have a shared usage counter (maybe an AtomicInteger) and close it when the counter goes to 0. Or as others have recommended you could use a thread-pool and then the thread pool is done free the connection.

Note, the database is just an example, it can be anything like a file,logger..etc.

In terms of a more generic answer I always try to mirror where the thing is created. If a method opens the stream then it should have the finally that closes the stream.

 public void someMethod() {
    InputStream stream = ...
    try {
         // process the stream here probably by calling other methods
    } finally {
         // stream should be closed in the same method for parity
         stream.close();
    }
 }

The exception to this pattern is a thread handler. Then the Thread should close the stream or release connection in a finally block at the end of the run() or call() method.

 public void serverLoopMethod() {
     while (weAcceptConnections) {
        Connection connection = accept(...);
        threadPool.submit(new ConnectionHandler(connection);
     }
 }
 ...
 private static class ConnectionHandler implements Runnable {
     private Connection connection;
     public ConnectionHandler(Connection connection) {
         this.connection = connection;
     }
     // run (or call) method executed in another thread
     public void run() {
         try {
            // work with the connection probably by calling other methods
         } finally {
              // connection is closed at the end of the thread run method
              connection.close();
         }
     }
 }

Answer 2

If you run your code it's likely that database connection will be closed before insert statement execution and of course insert will be unsuccessful.

Proper solutions If you have multiple insert tasks:

1. Use ExecutorService instead of Execuutor
2. Submit all tasks
3. Invoke executorService.shutdown() it will wait until all submitted tasks are done.
4. Close connection

If you have only one task to submit:
You should close the connection after Connection.InsertToTables(table X, table Y) in your task.

Good for both scenarios and recommended:
Each tasks has it own connection.

Example:

class Thread1 {
        private static DataSource dataSource; // initialize it

        public static void main(String[] args){
            ExecutorService threadPool = Executors.newFixedThreadPool(1);
            threadPool.submit(new MyRunnable(dataSource));                
        }
}

class MyRunnable implements Runnable {
        private final DataSource dataSource;

    MyRunnable(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void run() {
        Connection connection = dataSource.getConnection();
        // do something with connection
        connection.close();
    }
}