spring + SQLite in multi-threaded application

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:

'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:

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

How can I solve this (without adding a 'manual' synchronization in my code)?

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.

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>

Just catch and retry. This is normal SQLite behaviour.

[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_timeout http://www.sqlite.org/c3ref/busy_timeout.html

Hopefully, I have the perfect answer for you -- Berkeley DB and 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.

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 Evaluation and Benefits and Differences.

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 exactly the issue that you bring up -- how to allow concurrent read/write operations in SQLite.

With Spring, you can leverage the SingleConnectionDataSource. For my uses (300+ inserts / second), this works just fine.

@Bean
public DataSource jdbcDataSource() {
    SingleConnectionDataSource ds = new SingleConnectionDataSource();
    ds.setDriverClassName("org.sqlite.JDBC");
    ds.setUrl("jdbc:sqlite:stats.db");
    return ds;
}