vendredi 25 septembre 2015

Java - SQLite Database is locked when dropping table

I was trying to delete a column in a SQLite database table using JDBC. As there is no such function in SQLite, to achieve this, I used this approach:

  1. Create a new table (t1_backup) with all columns but not the one to be deleted,
  2. Copy all data (except that of the column to be deleted) to the new table,
  3. Drop the old table (t1),
  4. Rename the new table (t1_backup --> t1).

The actual code is as follows:

Connection cn = DriverManager.getConnection("jdbc:sqlite:"+path);
Statement stmt = new Statement();
stmt = cn.createConnection();
stmt.execute("CREATE TABLE t1_backup(id);");
stmt.executeUpdate("INSERT INTO t1_backup SELECT id FROM t1;");
stmt.execute("DROP TABLE t1;");
stmt.execute("ALTER TABLE t1_backup RENAME TO t1;");

The first two statements were executed without a problem. But when the DROP TABLE statement was executed, it gave me java.sql.SQLException: [SQLITE_LOCKED] A table in the database is locked (database table is locked).

I have found a solution but I was not sure why it worked. The solution is to create three more Statement objects, connect them to the same database and execute each of the four SQL statements with a different Statement object:

stmt.execute("CREATE ...");
stmt2.executeUpdate("INSERT ...");
stmt3.execute("DROP ...");
stmt4.execute("ALTER ...");

Can this be explained?

The JDBC I am using is the one by Xerial.

Thank you.

Aucun commentaire:

Enregistrer un commentaire