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.
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.
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 ...
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.
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.
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.
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 ...
-java.sql class SQLRecoverableException -Resolve memo when java.sql.SQLRecoverableException: Closed Connection occurs in Java JDBC connection -Using SqlSession --MyBatis.org