CODEWITHSUNDEEP
javamysqlsqlasynchronous
James Andrew
James Andrew

Reputation: 207

MySQLNonTransientConnectionException: No operations allowed after connection closed.

So Im making a bunch of async calls, only issue is, in my prepare method which generates a PreparedStatement, it grabs the connection (or uses a new one if the connection is faulty), and for some reason still throws this error.

I initially thought (and still kind of do) that it's because majority of the use cases that call this method are called asynchronously. So I made the method synchronized (never used that keyword before, but figured it was fitting after a bit of research), and still, the method throws an error...

public synchronized PreparedStatement prepare(String statement) throws SQLException {
    Connection c = getConnection();
    if (c == null || c.isClosed()) return getNewConnection().prepareStatement(statement);
    Logger.debug("not null and not closed " + Thread.currentThread().getId());
    return c.prepareStatement(statement); //throws error here
}

How do I make it so other threads can't alter the connection until the prepare method has finished with it?

Upvotes: 0

Views: 206

Answers (1)

tgdavies
tgdavies

Reputation: 11401

Something like this is happening:

Thread 1: calls prepare(), creates a connection and returns a prepared statement and leaves prepare(), so other threads may now enter prepare()

Thread 1: starts running the query

Thread 2: enters prepare and checks that the connection is OK -- this is the same connection that thread 1 created and is using.

Thread 1: closes the connection

Thread 2: tries to call prepareStatement on the connection, which is now closed

You should investigate using a connection pool, which will give each thread its own connection, which is returned to the pool when it is 'closed'.

Upvotes: 1

Related Questions