java sqlite jdbc always database is locked

Question

java sqlite jdbc always database is locked

scf18857887860 opened this issue 6 months ago · 3 comments

Describe the bug
use HikariDataSource manage sqlite jdbc connection.

HikariDataSource dataSource = new HikariDataSource();
dataSource.setJdbcUrl(url);
dataSource.setMaximumPoolSize(1);
dataSource.setMinimumIdle(1);
dataSource.setConnectionTimeout(50000);

but still happen database is locked.

To Reproduce
product enviroment is always happen. but test enviroment is less.

Expected behavior
A clear and concise description of what you expected to happen.

Logs

com.mybatisflex.core.transaction.TransactionException: null
	at com.mybatisflex.core.transaction.TransactionalManager.execNewTransactional(TransactionalManager.java:131)
	at com.mybatisflex.core.transaction.TransactionalManager.exec(TransactionalManager.java:69)
	at com.mybatisflex.core.row.Db.txWithResult(Db.java:1134)
	at com.mybatisflex.core.row.Db.txWithResult(Db.java:1127)
	at com.mes.dao.config.MecCommonTransactionManager.execTransaction(MecCommonTransactionManager.java:17)
	at com.mes.dao.dao.mysql.MysqlMESShopDataDao.setEx(MysqlMESShopDataDao.java:217)
	at com.mes.dao.dao.sqlite.SqliteMESShopDataDao.setEx(SqliteMESShopDataDao.java:83)
	at com.mes.thrift.ThriftServerCallbackImpl.isRepeatedMsg(ThriftServerCallbackImpl.java:256)
	at com.mes.thrift.ThriftServerCallbackImpl.Push(ThriftServerCallbackImpl.java:90)
	at com.maihui.platform.edge.messagecenter.ServerCallbackService$Processor$Push.getResult(ServerCallbackService.java:153)
	at com.maihui.platform.edge.messagecenter.ServerCallbackService$Processor$Push.getResult(ServerCallbackService.java:131)
	at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:40)
	at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:40)
	at com.mes.thrift.ThriftServerCallbackImpl.process(ThriftServerCallbackImpl.java:113)
	at com.mes.common.config.threadpoolutil.AESThreadPoolExecutor.lambda$execute$0(AESThreadPoolExecutor.java:35)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.reflect.UndeclaredThrowableException: null
	at com.sun.proxy.$Proxy13.insertSelective(Unknown Source)
	at com.mes.dao.config.BaseDao.create(BaseDao.java:48)
	at com.mes.dao.config.SynchronizedBaseDao.create(SynchronizedBaseDao.java:31)
	at com.mes.dao.dao.mysql.MysqlMESShopDataDao.newCache(MysqlMESShopDataDao.java:388)
	at com.mes.dao.dao.sqlite.SqliteMESShopDataDao.newCache(SqliteMESShopDataDao.java:118)
	at com.mes.dao.dao.mysql.MysqlMESShopDataDao.newCache(MysqlMESShopDataDao.java:368)
	at com.mes.dao.dao.sqlite.SqliteMESShopDataDao.newCache(SqliteMESShopDataDao.java:111)
	at com.mes.dao.dao.mysql.MysqlMESShopDataDao.lambda$setEx$3(MysqlMESShopDataDao.java:222)
	at com.mybatisflex.core.transaction.TransactionalManager.execNewTransactional(TransactionalManager.java:127)
	... 17 common frames omitted
Caused by: java.lang.reflect.InvocationTargetException: null
	at sun.reflect.GeneratedMethodAccessor741.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.mybatisflex.core.mybatis.Mappers$MapperHandler.invoke(Mappers.java:110)
	... 26 common frames omitted
Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: org.sqlite.SQLiteException: [SQLITE_BUSY_SNAPSHOT] Another database connection has already written to the database (database is locked)

Environment (please complete the following information):
windos 7
jdk 1.8

<dependency>
     <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.39.3.0</version>
</dependency>

Additional context
When my Java program performs database operations, I always add a global synchronized lock, but only for delete, insert, and update operations.

for example：

  synchronized (SynchronizedBaseDao.class) {
            return super.updateOrderStatus(orderKey, orderStatus, actionTime);
        }

Answer 1 · 2024-06-03T02:04:06.000Z

did you check https://www.sqlite.org/rescode.html#busy_snapshot ?

Answer 2 · 2024-06-03T15:15:52.000Z

did you check https://www.sqlite.org/rescode.html#busy_snapshot ?

thanks you i solve this problem，but happened a new problem

java.lang.reflect.UndeclaredThrowableException: null
	at com.sun.proxy.$Proxy23.close(Unknown Source)
	at com.mybatisflex.core.transaction.TransactionalManager.release(TransactionalManager.java:194)
	at com.mybatisflex.core.transaction.TransactionalManager.rollback(TransactionalManager.java:168)
	at com.mybatisflex.core.transaction.TransactionalManager.execNewTransactional(TransactionalManager.java:130)
	at com.mybatisflex.core.transaction.TransactionalManager.exec(TransactionalManager.java:69)
	at com.mybatisflex.core.row.Db.txWithResult(Db.java:1134)
	at com.mybatisflex.core.row.Db.txWithResult(Db.java:1127)
	at com.mes.dao.config.MecCommonTransactionManager.execTransaction(MecCommonTransactionManager.java:17)
	at com.mes.dao.dao.mysql.MysqlMESShopDataDao.expire(MysqlMESShopDataDao.java:189)
	at com.mes.dao.dao.sqlite.SqliteMESShopDataDao.expire(SqliteMESShopDataDao.java:69)
	at com.mes.thrift.ThriftServerCallbackImpl.isRepeatedMsg(ThriftServerCallbackImpl.java:255)
	at com.mes.thrift.ThriftServerCallbackImpl.Push(ThriftServerCallbackImpl.java:90)
	at com.maihui.platform.edge.messagecenter.ServerCallbackService$Processor$Push.getResult(ServerCallbackService.java:153)
	at com.maihui.platform.edge.messagecenter.ServerCallbackService$Processor$Push.getResult(ServerCallbackService.java:131)
	at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:40)
	at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:40)
	at com.mes.thrift.ThriftServerCallbackImpl.process(ThriftServerCallbackImpl.java:113)
	at com.mes.common.config.threadpoolutil.AESThreadPoolExecutor.lambda$execute$0(AESThreadPoolExecutor.java:35)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.reflect.InvocationTargetException: null
	at sun.reflect.GeneratedMethodAccessor291.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.mybatisflex.core.datasource.FlexDataSource$ConnectionHandler.invoke(FlexDataSource.java:263)
	... 21 common frames omitted
Caused by: java.sql.SQLException: database in auto-commit mode
	at org.sqlite.SQLiteConnection.rollback(SQLiteConnection.java:454)
	at com.zaxxer.hikari.pool.ProxyConnection.close(ProxyConnection.java:257)
	... 25 common frames omitted

i use HikariDataSource and set one connection

       HikariDataSource dataSource = new HikariDataSource();
            dataSource.setJdbcUrl(url);
            dataSource.setMaximumPoolSize(1);
            dataSource.setMinimumIdle(1);
            dataSource.setConnectionTimeout(50000);

Answer 3 · 2024-08-07T09:11:05.000Z

The "new" problem looks like a usage problem or issue within MyBatis' configuration.

By default all connections returned from a HikariDataSource have autoCommit be set to true (Doc). That is causing the shown stacktrace. Try and set the default value for autoCommit to false on the dataSource, or if you are passing connections around manually, you can just set the autoCommit to true on a certain connection.