Getting [SQLITE_BUSY] database file is locked with select statements

Question

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

Possible duplicate of [SQLITE\_BUSY The database file is locked (database is locked) in wicket](http://stackoverflow.com/questions/8559623/sqlite-busy-the-database-file-is-locked-database-is-locked-in-wicket) — Stephan, Jan 02 '16 at 18:32

score 31 · Accepted Answer · answered Dec 15 '12 at 11:48

31

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.

answered Dec 15 '12 at 11:48

sorencito

2,517
20
21

Yes that works. But I still do not understand why the file is locked although only select statements are executed. – rogergl Dec 15 '12 at 19:35
@rogergl: Try getting a connection with SQLiteOpenHelper#getReadableDatabase(). Link: http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html – sorencito Aug 13 '13 at 08:25

score 20 · Answer 2 · answered Jan 05 '22 at 16:51

20

For anyone who's having issues with it in WSL2:

Happened to me when I was using WSL2 & Datagrip, even tho the database wasn't busy.

It turns out that Datagrip has tried to connect to the database file that existed inside WSL2 via Windows' sqlite3.

Moving the file from WSL2 to a Windows file directory seems to solve this issue

answered Jan 05 '22 at 16:51

Eliya Cohen

10,716
13
59
116

This solution worked for me, too: copying the file from the WSL2 filesystem to the Windows filesystem fixed the connection problem. In my case I was trying to connect with DBeaver, so the problem&solution is not limited to Datagrip. (Perhaps both programs use the same database driver library.) – Esteis May 18 '22 at 16:39
1

I ran into the same issue. It's tracked here (https://youtrack.jetbrains.com/issue/DBE-11014) on the DataGrip issue tracker. Addendum: The bug seems to be in WSL, not supportng file locking: https://github.com/microsoft/WSL/issues/4689 – Sammy S. Jun 08 '22 at 18:50

score 13 · Answer 3 · answered Jun 15 '14 at 18:27

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;
    }
}

score 9 · Answer 4 · edited Oct 21 '14 at 21:02

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.

score 3 · Answer 5 · answered May 01 '18 at 05:15

3

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();

answered May 01 '18 at 05:15

Mahesh Jamdade

17,235
8
110
131

I got this issue when executing two prepared statements one after the other. The solution was to close the prepared statement, just like you suggested. – Endrju Dec 30 '22 at 17:51

score 1 · Answer 6 · answered Nov 10 '20 at 07:52

1

I experienced the same problem, even though all connections, resulsets and statements were closed, I still had the error. The problem for me was using the DB browser plugin in Intellij to visualize and manage tables. Disconnecting the database from this tool solved the problem. So make sure that no external tool is connecting to the database and locking tables.

answered Nov 10 '20 at 07:52

picc

11
2

this at-least help me. tks for the answer. – sumitya May 28 '21 at 17:46

score 1 · Answer 7 · answered Apr 18 '21 at 11:41

1

In my case, there are thread using sqlite connection in the background, which caused this error.

close sqlitebrowser
close electron app ( maybe need restart)
re-run your program.

answered Apr 18 '21 at 11:41

Siwei

19,858
7
75
95

score 0 · Answer 8 · answered Dec 07 '18 at 16:49

0

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

answered Dec 07 '18 at 16:49

Soundous Bahri

96
1
4

score 0 · Answer 9 · answered Nov 09 '19 at 11:21

Thanks from bowman han, I added a piece of code to his solution and it worked for me.

private static Connection c = null;
public static Connection connect() throws Exception {

    if (c == null) {
        c = (Connection) DriverManager.getConnection(url);
    } else {
        c.close();
        c = (Connection) DriverManager.getConnection(url);
    }
    return c;
}

score 0 · Answer 10 · answered Aug 26 '21 at 23:23

0

You have opened another application containing the database, Try to close that application and run your program again. This worked for me

answered Aug 26 '21 at 23:23

Nàñà Yáw Sçrìptûrés

1
1

Moreover, close the resultsSet and the prepareStatement after query or you can use the “try with resource” which is very helpful – Nàñà Yáw Sçrìptûrés Oct 08 '21 at 08:35

score -2 · Answer 11 · edited May 03 '16 at 04:07

-2

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

edited May 03 '16 at 04:07

Shree Krishna

8,474
6
40
68

answered Dec 15 '12 at 09:30

sorencito

2,517
20
21

This is also recommended within SQLite documentation as one of several steps to reduce the occurrence of this situation. The first step would be to ensure everything is closed. Also should check that the database supports threading (default is on) FULLMUTEX – oden Sep 30 '16 at 10:17

Getting [SQLITE_BUSY] database file is locked with select statements

11 Answers11

Linked