0

Using a Spring Boot application with Hibernate and Sqlite, after a while I receive these errors in the console over and over again. The server requires a restart in order to recover because it cannot fetch a new connection to the DB.

From my logs, I suspect that this occurs while having 2 threads, one writing into a table and the other one reading from that table. The situation is hard to reproduce and it occurs after around 5 hours.

I thought about using a dirty read since consistency is not that important.

I have set the maximum pool size to 1 while browsing similar questions because otherwise, I get Getting [SQLITE_BUSY] database file is locked with select statements

Error

2020-07-30 20:47:57.250  WARN 6 --- [pool-2-thread-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: null
2020-07-30 20:47:57.251 ERROR 6 --- [pool-2-thread-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : HikariPool-1 - Connection is not available, request timed out after 1500ms.
2020-07-30 20:47:57.366 ERROR 6 --- [pool-2-thread-2] o.s.s.s.TaskUtils$LoggingErrorHandler    : Unexpected error occurred in scheduled task

org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JaTransactionManager.java:448)
    ...
Caused by: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
    at org.hibernate.dialect.SQLiteSQLExceptionConversionDelegate.convert(SQLiteSQLExceptionConversionDelegate.java:48) ~[sqlite-dialect-1.0.jar!/:5.4.17.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.4.17.Final.jar!/:5.4.17.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.4.17.Final.jar!/:5.4.17.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) ~[hibernate-core-5.4.17.Final.jar!/:5.4.17.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:107) ~[hibernate-core-5.4.17.Final.jar!/:5.4.17.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:134) ~[hibernate-core-5.4.17.Final.jar!/:5.4.17.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getConnectionForTransactionManagement(LogicalConnectionManagedImpl.java:259) ~[hibernate-core-5.4.17.Final.jar!/:5.4.17.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:267) ~[hibernate-core-5.4.17.Final.jar!/:5.4.17.Final]
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:246) ~[hibernate-core-5.4.17.Final.jar!/:5.4.17.Final]
    at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:83) ~[hibernate-core-5.4.17.Final.jar!/:5.4.17.Final]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:184) ~[spring-orm-5.2.7.RELEASE.jar!/:5.2.7.RELEASE]
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:402) ~[spring-orm-5.2.7.RELEASE.jar!/:5.2.7.RELEASE]
    ... 22 common frames omitted
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 1500ms.

application.properties

spring.datasource.driverClassName=org.sqlite.JDBC
spring.datasource.max-active=1
spring.datasource.initialSize=1
spring.datasource.tomcat.initial-size=1
spring.datasource.maxIdle=1
spring.datasource.hikari.minimumIdle=1
spring.datasource.hikari.maximumPoolSize=1
spring.datasource.hikari.connectionTimeout=2500
spring.datasource.hikari.idleTimeout=1500
spring.datasource.tomcat.testOnBorrow=true
spring.datasource.tomcat.validationQuery=SELECT 1
Sorin Penteleiciuc
  • 653
  • 1
  • 10
  • 26
  • I would suggest to set minimum pool size to 5, maximum pool size to 25 and remove max-active property. This setting should resolve the issue. – Santosh Bhosle Aug 01 '20 at 14:19
  • The problem is that I will get this error Getting [SQLITE_BUSY] database file is locked with select statements – Sorin Penteleiciuc Aug 01 '20 at 20:12
  • 2
    I am using SingleConnectionDataSource with setSuppressClose(true) to solve SQLITE_BUSY. – mger1979 Nov 30 '20 at 02:40
  • 1
    SQLite currently only supports a single writer connection that locks out all readers; this isn't a big deal if writes are infrequent and small, and _nobody_ holds a transaction open for a long time. – Donal Fellows Jun 10 '21 at 08:39

0 Answers0