Getting [SQLITE_BUSY] database file is locked with select statements
Solution 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.
Solution 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;
}
}
Solution 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.
Solution 4
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
Solution 5
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();
rogergl
Updated on January 06, 2022Comments
-
rogergl over 2 years
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
-
rogergl over 11 yearsYes that works. But I still do not understand why the file is locked although only select statements are executed.
-
sorencito almost 11 years@rogergl: Try getting a connection with SQLiteOpenHelper#getReadableDatabase(). Link: developer.android.com/reference/android/database/sqlite/…
-
oden over 7 yearsThis 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
-
sumitya almost 3 yearsthis at-least help me. tks for the answer.
-
Nàñà Yáw Sçrìptûrés over 2 yearsMoreover, close the resultsSet and the prepareStatement after query or you can use the “try with resource” which is very helpful
-
Esteis almost 2 yearsThis 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.)
-
Sammy S. almost 2 yearsI ran into the same issue. It's tracked here (youtrack.jetbrains.com/issue/DBE-11014) on the DataGrip issue tracker. Addendum: The bug seems to be in WSL, not supportng file locking: github.com/microsoft/WSL/issues/4689