1.测试工程
测试工程: F:\workspace\demo\sqlite
(TODO:上传demo项目)
sqlite依赖:
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.32.3.2</version>
</dependency>
<dependency>
<groupId>com.github.gwenn</groupId>
<artifactId>sqlite-dialect</artifactId>
<version>0.1.0</version>
</dependency>
测试原理:
. 预先在一个表中插入10条记录
. 启动多个线程查询所有记录,随机修改其中的一条. 在事务中执行.(@Transactional注解方法). (1个findAll,1个save)
目的是:
.重现并发访问sqlite时,出现[SQLITE_BUSY]异常
.找到消除并发访问[SQLITE_BUSY]的方法
2.问题
测试中常见的异常:
- org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)
- org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (cannot rollback - no transaction is active)
问题1重现:
application.properties配置:
spring.datasource.url=jdbc:sqlite:d:/var/log/alchemy_test.db
2个线程其中一个线程第1次执行立即报[SQLITE_BUSY]异常.
3.解决
采用以下配置,则连续运行未出现[SQLITE_BUSY]或其它异常:
spring.datasource.driver-class-name=org.sqlite.JDBC
spring.datasource.url=jdbc:sqlite:d:/var/log/alchemy_test.db
spring.datasource.hikari.maximum-pool-size=1
该配置保证只有一个数据库连接.不需要额外指定连接属性.
开启10个线程,测试没有出现错误.
4.其它测试
未设置spring.datasource.hikari.maximum-pool-size=1的情况下.
不同连接属性的测试结果.
-
spring.datasource.url=jdbc:sqlite:d:/var/log/alchemy_test.db?busy_timeout=300000
立即出错[SQLITE_BUSY],超时不起作用. -
spring.datasource.url=jdbc:sqlite:d:/var/log/alchemy_test.db?busy_timeout=300000&transaction_mode=EXCLUSIVE
2个线程可以运行超过30次,之后出现[SQLITE_BUSY]异常. -
spring.datasource.url=jdbc:sqlite:d:/var/log/alchemy_test.db?busy_timeout=300000&journal_mode=WAL&transaction_mode=EXCLUSIVE
2个线程连续运行,出现[SQLITE_BUSY]异常. -
spring.datasource.url=jdbc:sqlite:d:/var/log/alchemy_test.db?busy_timeout=300000&journal_mode=TRUNCATE&transaction_mode=EXCLUSIVE
不支持journal_mode=TRUNCATE
启动报错:
org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set
可能与使用的sqlite-dialect实现有关.
5.资料
Understanding SQLITE_BUSY
https://activesphere.com/blog/2018/12/24/understanding-sqlite-busy