MyBatis ExecutorType.Batch + Spring + Oracle + UCP blocks session

[imosapatryk]

I use MyBatis Batch ExecutorType with Spring Boot and @Transactional. For the database Oracle 19c + UCP Connection Pool.

mybatis-3.4.2 mybatis-spring-1.3.1 mybatis-spring-boot-starter-1.2.0 spring-4.3.2 ucp-19.10.0.0

I have 4 instances having 10 threads each that are saving 50 events each with batch executor type. The events can be duplicated across all the instances.

@Transactional(timeout=30)
public void saveBatch(List<Event> events) {
  for(Event e : events) {
    repository.saveUsingBatchExecutor(e);
  }
}

What I encounter is that sometimes the transaction saving batch is blocked for some time and I suppose is because of that another transaction tries to save the same records. So the first thing that came to my mind was setting up the timeouts for transaction, query and socket. So I setup my UCP connection pool:

connection-factory-class-name: oracle.jdbc.pool.OracleDataSource
connection-wait-timeout: 3
max-pool-size: 10
min-pool-size: 5
initial-pool-size: 5
max-connection-reuse-time: 300
connection-validation-timeout: 2
inactive-connection-timeout: 120
query-timeout: 15
fast-connection-failover-enabled: True
validate-connection-on-borrow: true
timeout-check-interval: 3
abandoned-connection-timeout: 45
connection-properties:
  "[oracle.jdbc.ReadTimeout]": "30000"

So @Transactional timeout >= socket timeout > query timeout. (Im setting up socket timeout because for mybatis it looks like the @Transactional timeout doesn't work when doing commit).

Having more and more tests I noticed that my session sometimes is blocked on the database side(db closes the session after 10 minutes), even though I had an error on application side. So I suppose this could be the reason that other transactions get timed-out because on db side there is a session that keeps records blocked for 10 minutes. What I see in the logs is that sometimes I get connection closed exception during execution, but there should be rollback in case, but what if connections is lost but the db doesn't get notified? It seems like the session is kept open? But why there is no detector for such a behavior and why I lose the connection application side?

Here are examples of the exceptions:

Unexpected error during event processing.org.springframework.transaction.TransactionSystemException: Could not roll back JDBC transaction; nested exception is java.sql.SQLRecoverableException: Closed Connection at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:331) at org.springframework.transaction.support.AbstractPlatformTransactionManager.doRollbackOnCommitException(AbstractPlatformTransactionManager.java:900) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:789) at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730) at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:504) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:292) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

---

Application exception overridden by rollback exception ### Cause: org.springframework.jdbc.UncategorizedSQLException: ### Error querying database. Cause: java.sql.SQLException: The statement is closed: The statement is closed###

Maybe my properties are set in a wrong way? Or I need some additional configuration to be set. Any help appreciated.

[kazuki43zoo]

Could you provide a small reproduce project via GitHub?

[kazuki43zoo]

Close because no reply at long time. Of course you can reopen at any time.