spring + SQLite in multi-threaded application

Question

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

Answer 1

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