Getting [SQLITE_BUSY] database file is locked with

问题:

If I run multiple threads against my web app I get:

java.sql.SQLException: [SQLITE_BUSY]  The database file is locked (database is locked)
    at org.sqlite.DB.newSQLException(DB.java:383)
    at org.sqlite.DB.newSQLException(DB.java:387)
    at org.sqlite.DB.execute(DB.java:339)
    at org.sqlite.PrepStmt.executeQuery(PrepStmt.java:75)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)

I do know that only one thread can write to a sqlite database but I'm only reading from the database. So why do I get this error message ?

BTW: My connection pool looks like this:

<bean class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close" id="dataSource">
    <property name="driverClassName" value="${database.driverClassName}" />
    <property name="url" value="${database.url}" />
    <property name="username" value="${database.username}" />
    <property name="password" value="${database.password}" />
    <property name="initialSize" value="1" />
    <property name="maxActive" value="2" />
    <property name="maxIdle" value="1" />
    <property name="poolPreparedStatements" value="true" />
</bean>

The setup is: Java 1.6, Tomcat 7.0.34, Spring 3.2, Hibernate 3.6.9 and sqlite3 3.7.2

Regards Roger

回答1:

After some googling I found that it is a bad practice to use multiple connections when connecting to SQLite. See

http://touchlabblog.tumblr.com/post/24474398246/android-sqlite-locking

Set your poolsize maxactive to 1 and try out.

回答2:

There should be only ONE connection with your application. you can use this to ensure.

public class SqliteHelper {
private static Connection c = null;
public static Connection getConn() throws Exception {
    if(c == null){
    Class.forName("org.sqlite.JDBC");
    c = DriverManager.getConnection("jdbc:sqlite:D:/test.db");
    }
    return c;
    }
}

回答3:

Note also that this may happen if you accidentally forget to close your connection:

Connection connection;
try {
  Statement statement = connection.createStatement();
  ResultSet resultSet = statement.executeQuery(QUERY);
  if (resultSet.next()) { /* do something */ }
catch (SQLException e) { /* handle exception */ }
finally {
  if (connection != null) {
    try {
      connection.close(); // <-- This is important
    } catch (SQLException e) {
      /* handle exception */
    }
  }
}

While the first database connection may work well once the server is started, subsequent queries may not, depending on how the connection pool is configured.

回答4:

Everytime you establish a connection make sure to close it after the work is done, It worked for me like if you are using

Connection con = null;
PreparedStatement pst = con.prepareStatement("...query... "); 
/*
 do some stuff 
*/
pst.executeQuery();
pst.close();
con.close();

回答5:

For me the problem was that I was opening too much Sessions So I made the session field in my DAO class static

回答6:

Try @Transactional(readonly=true) for those methods that only do reads. Maybe that works for you.