java 多线程应用程序中的 spring + SQLite
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5117248/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
spring + SQLite in multi-threaded application
提问by jfu
I'm developing an application that uses SQLite database and spring. I have problems when multiple threads try to modify the database - I get an error:
我正在开发一个使用 SQLite 数据库和 spring 的应用程序。当多个线程尝试修改数据库时出现问题 - 我收到错误消息：
'The database file is locked'
'数据库文件被锁定'
I have a single datasource configured:
我配置了一个数据源：
<bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close" lazy-init="true">
<property name="driverClassName" value="org.sqlite.JDBC" />
<property name="url" value="jdbc:sqlite:sample.db" />
<property name="initialSize" value="2" />
<property name="maxActive" value="20" />
<property name="maxIdle" value="5" />
<property name="poolPreparedStatements" value="true" />
</bean>
and in each thread I have a separate instance of the JdbcDaoSupport that performs an insert to the database:
并且在每个线程中，我都有一个单独的 JdbcDaoSupport 实例，它执行对数据库的插入：
getJdbcTemplate().update(
"insert into counts values(15)"
);
The function that performs the database update is transactional (I've tried all isolation levels, in each case I get the same error).
执行数据库更新的函数是事务性的（我已经尝试了所有隔离级别，在每种情况下我都遇到了相同的错误）。
The same code works fine, when using other database (MySql).
使用其他数据库 (MySql) 时，相同的代码工作正常。
How can I solve this (without adding a 'manual' synchronization in my code)?
我该如何解决这个问题（不在我的代码中添加“手动”同步）？
回答by Matt Sheppard
I've not tried it, but I'd suggest that, given that SQLite supports only one connection at a time, you should configure your data source to only ever create one connection.
我没有尝试过，但我建议，鉴于 SQLite 一次仅支持一个连接，您应该将数据源配置为只创建一个连接。
I think that would be something like the following...
我认为这将类似于以下内容...
<bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" lazy-init="true">
<property name="driverClassName" value="org.sqlite.JDBC" />
<property name="url" value="jdbc:sqlite:sample.db" /> <
<property name="initialSize" value="1" />
<property name="maxActive" value="1" />
<property name="maxIdle" value="1" />
<property name="poolPreparedStatements" value="true" />
</bean>
回答by andrew cooke
Just catch and retry. This is normal SQLite behaviour.
只需捕获并重试。这是正常的 SQLite 行为。
[edit:] SQLite will retry itself; this error is thrown if the retries don't work within a certain period. You can increase the period in various ways: http://www.sqlite.org/pragma.html#pragma_busy_timeouthttp://www.sqlite.org/c3ref/busy_timeout.html
[编辑：] SQLite 将重试；如果在一定时间内重试不起作用，则会引发此错误。您可以通过多种方式增加周期：http://www.sqlite.org/pragma.html#pragma_busy_timeout http://www.sqlite.org/c3ref/busy_timeout.html
回答by wmarbut
With Spring, you can leverage the SingleConnectionDataSource. For my uses (300+ inserts / second), this works just fine.
使用 Spring，您可以利用SingleConnectionDataSource. 对于我的使用（300+ 次插入/秒），这很好用。
@Bean
public DataSource jdbcDataSource() {
SingleConnectionDataSource ds = new SingleConnectionDataSource();
ds.setDriverClassName("org.sqlite.JDBC");
ds.setUrl("jdbc:sqlite:stats.db");
return ds;
}
回答by dsegleau
Hopefully, I have the perfect answer for you -- Berkeley DBand the SQL API. Last year Berkeley DB combined it's storage engine with the SQL layer of SQLite, providing a combined product that offers the best of both worlds. The ubiquity and ease of use of SQLite, with the concurrency, performance, scalability and reliability of Berkeley DB.
希望我为您提供完美的答案——Berkeley DB和SQL API。去年，Berkeley DB 将其存储引擎与 SQLite 的 SQL 层相结合，提供了一个兼具两全其美的组合产品。SQLite 的普遍性和易用性，以及 Berkeley DB 的并发性、性能、可扩展性和可靠性。
Why will this address your problem? Because Berkeley DB is completely SQLite compatible, but implements a different, more concurrent lock manager. This means that in Berkeley DB you can have multiple update threads accessing the database at the same time. There are a couple of interesting white papers on the subject, written by Mike Owens (the author of "The Definitive Guide to SQLite"): Technical & Performance Evaluationand Benefits and Differences.
为什么这会解决您的问题？因为 Berkeley DB 完全兼容 SQLite，但实现了不同的、并发性更高的锁管理器。这意味着在 Berkeley DB 中，您可以有多个更新线程同时访问数据库。关于这个主题，有几篇有趣的白皮书，由 Mike Owens（“SQLite 权威指南”的作者）撰写：技术和性能评估以及优势和差异。
Disclaimer: I'm the Product Manager for Berkeley DB, so I'm slightly biased. However, you will find that the Berkeley DB SQL API addresses exactlythe issue that you bring up -- how to allow concurrent read/write operations in SQLite.
免责声明：我是 Berkeley DB 的产品经理，所以我有点偏见。但是，您会发现 Berkeley DB SQL API正好解决了您提出的问题 —— 如何在 SQLite 中允许并发读/写操作。
