SQL Recoverable Exception when dealing with large amounts of data

In rare cases, you will encounter SQLRecoverableException when you refer to and update tens of thousands of data from the database. I felt that this guy was really ... and the way to reproduce it was refreshing, but I was able to reproduce it the other day, so I will share the survey contents.

Execution environment

item version
Java amazon Corretto-11.0.3.7.1
Spring Batch 4.2.2.RELEASE
Database Oracle Database (driver is ojdbc8)
mybatis 3.3.0
mybatis-spring 1.2.3

The app is a batch process that periodically references and updates the database. It runs in multiple threads on the server, and thousands of processes are running on each thread.

Status

From Java, I run the following exception regardless of the ORM.

 Caused by: java.sql.SQLRecoverableException:It is a closed connection.
            at oracle.jdbc.driver.PhysicalConnection.getAutoCommit(PhysicalConnection.java:1943)
            at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:2068)
            at org.apache.commons.dbcp2.DelegatingConnection.rollback(DelegatingConnection.java:492)
            at org.apache.commons.dbcp2.DelegatingConnection.rollback(DelegatingConnection.java:492)
            at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:328)
            at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:835)
            at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:809)
            at org.springframework.transaction.support.TransactionTemplate.rollbackOnException(TransactionTemplate.java:168)
            at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:144)
            at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:273)
            at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:82)
            at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:375)
            at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
            at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:145)
            at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:258)
            at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:208)
            at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148)
            at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:410)
            at org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:136)
            at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:319)
            at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:147)
            at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50)
            at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:140)
            at org.springframework.batch.core.launch.support.CommandLineJobRunner.start(CommandLineJobRunner.java:376)
            at org.springframework.batch.core.launch.support.CommandLineJobRunner.main(CommandLineJobRunner.java:609)

Suddenly the connection was lost, which hindered data operations. This app does not manage JDBC by myself, but operates it from Mybatis.

According to the documentation, what is SQLRecoverableException?

A subclass of SQLException that is thrown when the application performs a recovery procedure and retries the entire transaction (transaction branch for distributed transactions) and the previously failed operation may succeed.

And that. fuzzy... I'm not sure how to reproduce it, so I caught SQLRecoverableException and tried again ...? I was thinking ...

I was able to reproduce it suddenly one day

It's ambiguous even if I read the document, and it doesn't appear at all in the unit test, so I was in great trouble ... When I made a program that had nothing to do with it, I was able to reproduce it, although I couldn't solve it.

Reproduction source

We will prepare three types of classes. The following is a super private sample, so the rigor of the program is not considered.

--Class that operates DB resources --Repository --Main class to call

First, DB resource related classes that provide connections and statements.

DatabaseResourceManager.java


public final class DatabaseResourceManager {

    private DatabaseResourceManager() { }

    private static final String uri = "jdbc:oracle:thin:@localhost:1521/XEPDB1";
    private static final String user = "user";
    private static final String password = "password";

    //Create a connection
    public static Connection openConnection() throws SQLException {
        DriverManager.registerDriver(new OracleDriver()); // <==The driver specification seems to be wrong...
        return DriverManager.getConnection(uri, user, password);
    }

    //Generate a statement
    public static Statement createStatement(Connection connection) throws SQLException {
        if (connection.isClosed()) {
            throw new RuntimeException("It is a closed connection");
        }

        return connection.createStatement();
    }

    //Close ResultSet
    public static void closeResultSetConnection(ResultSet resultSet) throws SQLException {
        if (resultSet.isClosed()) {
            throw new SQLException("It is a closed result set");
        }

        resultSet.close();
    }

Next, prepare a simple repository class that receives connections and statements and issues SQL.

Repository.java


public final class Repository {

    private Repository() { }

    //I want to issue a simple SELECT statement to
    public static ResultSet selectFromTable() throws SQLException {
        try (
            final Connection connection = DatabaseResourceManager.openConnection();
            final Statement statement = DatabaseResourceManager.createStatement(connection)
        ) {
            return statement.executeQuery("select * from table");
        }
    }

    //Just display the contents of the ResultSet
    public static void printTableRow(ResultSet resultSet) throws SQLException {
        try {
            while (resultSet.next()) {
                System.out.println(new ObjectMapper().writeValueAsString(resultSet.getCursorName()));
            }
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
    }
}

Finally, prepare a main class that acquires resources, issues SQL, and controls operations.

MainHandler.java


public class MainHandler {
    public static void main(String[] args) {
        try {
            final ResultSet set = Repository.selectFromExclusionMail();

            Repository.printTableRow(set);

            DatabaseResourceManager.closeResultSetConnection(set);
        } catch (SQLException throwables) {
            DatabaseResourceManager.handleSQLException(throwables);
        }
    }
}

I will execute it with. Then ...

java.sql.SQLRecoverableException:It is a closed connection.: next
	at oracle.jdbc.driver.InsensitiveScrollableResultSet.ensureOpen(InsensitiveScrollableResultSet.java:108)
	at oracle.jdbc.driver.InsensitiveScrollableResultSet.next(InsensitiveScrollableResultSet.java:402)
	at Repository.printTableRow(Repository.java:40)
	at MainHandler.main(MainHandler.java:36)
8:53:19: Task execution finished 'MainHandler.main()'.

Oh oh ... I don't know why it came out occasionally in the actual production. Certainly, the connection is closed at the part where the cursor is swirled with while.

Cause

I just knew how to reproduce it, but I don't know the cause ... However, as can be said from the result, it seems that the resource should not be closed before getting the ResultSet and manipulating it.

Solution?

The sample, raw JDBC, solved it easily. In the part that issues the SQL of the repository class, stop try-with-resource and set the closing timing to manual. In other words

Repository.java


public static ResultSet selectFromTable() throws SQLException {
    try (
        final Connection connection = DatabaseResourceManager.openConnection();
            final Statement statement = DatabaseResourceManager.createStatement(connection)
    ) {
        return statement.executeQuery("select * from table");
    }
}

,

Repository.java


public static ResultSet selectFromTable() throws SQLException {
    //Generate statement
    final Statement statement = DatabaseResourceManager.createStatement(DatabaseResourceManager.openConnection())

    //Issue SQL
    return statement.executeQuery("select * from table");
}

To. With this, the sample was completed normally for the time being.

In the batch of the main subject, I modified it so that an instance of the class that manages the session called SqlSession provided by Mybatis is acquired before SQL execution and closed after execution. Let's take a look at this for a while. It is painful that there are few samples of the part that opens a session from the Mybatis document, SqlSessionFactory and issues SQL ...

reference

-java.sql class SQLRecoverableException -Resolve memo when java.sql.SQLRecoverableException: Closed Connection occurs in Java JDBC connection -Using SqlSession --MyBatis.org

Recommended Posts

SQL Recoverable Exception when dealing with large amounts of data
[MyBatis] Use Cursor when mapping a large amount of data
How to delete large amounts of data in Rails and concerns