Hello Everyone,
I have two unrelated tables table_A and table_B in my SQLite DB. Trying to
drop table_B while a resultset is open on table_A throws
"java.sql.SQLException: database table is locked".
Following simple code will illustrate the Bug clearly.
public class Class1 {
public static void main(String[] args) throws Exception {
Class.forName("org.sqlite.JDBC");
Connection c = DriverManager.getConnection("jdbc:sqlite:" +
"D:\\test.db");
Statement stmt1 = c.createStatement();
stmt1.executeUpdate("create table table_A (col1 text)");
stmt1.executeUpdate("insert into table_A values ('FIRST')");
stmt1.executeUpdate("create table table_B (col1 text)");
PreparedStatement ps = c.prepareStatement("select * from table_A");
ResultSet rs = ps.executeQuery();
//While the resultset is open on table_A try to
//drop table_B. It gives error that database table is locked
//Which makes no sense because there is no relation between
//table_A and table_B
stmt1.executeUpdate("drop table if exists table_B");
rs.close();
stmt1.close();
c.close();
}
}
*Some other details*
1. My Java version is 1.8.0_72.
2. I have tried this with sqlite-jdbc-3.8.11.2.jar as well as
sqlite-jdbc-3.7.2.jar. But the result is same.
3. Following are some of the other things that I tried which made no
difference.
1. Setting auto-commit on or off
2. Searched bug-list and googled but all hits are for "database is
locked" but nothing for "database *table* is locked"
3. Using try-catch-finally to properly close all resources in case of
exception.
4. Closing the connection and reopening it before "Preparedstatement
ps ...".
5. Tried to use another connection to drop the table_B. This
expectedly gave "database is locked" error.
4. My actual scenario is that I have meta-data in one table based on
which I need to drop other tables. So I need to drop other tables while
processing resultset from meta-data table.
