Reputation: 3781

Getting [SQLITE_BUSY] database file is locked with select statements

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

Upvotes: 45

Answers (14)

Synthesis

Reputation: 554

I had this issue with SqlDelight, I was using nested transactions.

It turned out some DI code was creating multiple instances of the AndroidSqliteDriver, which were likely not correctly closed. I fixed it by making sure AndroidSqliteDriver was only instantiated once.

Upvotes: 0

shdr

Reputation: 966

The most elegant and worked solution that I found here is:

move the database file to a path accessible from both WSL2 and Windows.

Steps:

Move the Database File to a Windows Path: For example, place foo.db on your Windows Desktop.
Create a Symbolic Link in WSL2: Use the ln -s command to create a symlink in WSL2 pointing to the Windows file. Note the use of the -s flag for creating a symbolic link.
Accessing Windows Files in WSL2: Windows files are accessible in WSL2 through the /mnt/c/... path.

Command:

ln -s /mnt/c/Users/Tanut/Desktop/foo.db ./foo.db

Upvotes: 1

Silviano Da Silva

Reputation: 1

Always work with me

private static Connection con = null; 

public static Connection sqlite(){

        try {
            if (con != null) {
                con.close();
            }
            return   con =(Connection) DriverManager.getConnection(url);
        } catch (SQLException ex) {
            throw new RuntimeException( ex);
        }

    }

Upvotes: 0

Eliya Cohen

Reputation: 11508

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

Upvotes: 32

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

Reputation: 1

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

Upvotes: 0

Siwei

Reputation: 21557

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.

Upvotes: 1

picc

Reputation: 11

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.

Upvotes: 1

ParisaN

Reputation: 2092

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

Upvotes: 0

Soundous Bahri

Reputation: 106

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

Upvotes: 0

Mahesh Jamdade

Reputation: 20359

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

Upvotes: 3

sorencito

Reputation: 2625

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

Upvotes: -1

johanwannheden

Reputation: 962

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.

Upvotes: 9

bowman han

Reputation: 1135

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

Upvotes: 13

sorencito

Reputation: 2625

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.

Upvotes: 34

Getting [SQLITE_BUSY] database file is locked with select statements

Answers (14)

Related Questions