search

xerial / sqlite-jdbc

SQLite JDBC Driver
Apache License 2.0
2.73k stars 598 forks source link

java sqlite jdbc always database is locked #1120

Open scf18857887860 opened 3 weeks ago

scf18857887860 commented 3 weeks ago

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);
        }
gotson commented 3 weeks ago

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

scf18857887860 commented 3 weeks ago

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);