CODEWITHSUNDEEP
sqlitejdbc
M. Folte
M. Folte

Reputation: 131

SQLite via JDBC: SQLITE_BUSY when inserting after selecting

I'm getting the error code SQLITE_BUSY when trying to write to a table after selecting from it. The select statement and result is properly closed prior to my insert.

If I'm removing the select part the insert works fine. And this is what I'm not getting. According to the documentation SQLITE_BUSY should mean that a different process or connection (which is definetly not the case here) is blocking the database.

There's no SQLite manager running. Also jdbcConn is the only connection to the database I have. No parallel running threads aswell.

Here's my code:

    try {
        if(!jdbcConn.isClosed()) {
            ArrayList<String> variablesToAdd = new ArrayList<String>();

            String sql = "SELECT * FROM VARIABLES WHERE Name = ?";

            try (PreparedStatement stmt = jdbcConn.prepareStatement(sql)) {
                for(InVariable variable : this.variables.values()) {
                    stmt.setString(1, variable.getName());
                    try(ResultSet rs = stmt.executeQuery()) {
                        if(!rs.next()) {
                            variablesToAdd.add(variable.getName());                 
                        }
                    }
                }
            }

            if(variablesToAdd.size() > 0) {
                String sqlInsert = "INSERT INTO VARIABLES(Name, Var_Value) VALUES(?, '')";
                try(PreparedStatement stmtInsert = jdbcConn.prepareStatement(sqlInsert)) {
                    for(String name : variablesToAdd) {
                        stmtInsert.setString(1, name);
                        int affectedRows = stmtInsert.executeUpdate();

                        if(affectedRows == 0) {
                            LogManager.getLogger().error("Error while trying to add missing database variable '" + name + "'.");
                        }
                    }
                }

                jdbcConn.commit();
            }
        }
    }
    catch(Exception e) {
        LogManager.getLogger().error("Error creating potentially missing database variables.", e);
    }

This crashes on int affectedRows = stmtInsert.executeUpdate();. Now if I remove the first block (and manually add a value to the variablesToAdd list) the value inserts fine into the database.

Am I missing something? Am I not closing the ResultSet and PreparedStatement properly? Maybe I'm blind to my mistake from looking at it for too long.

Edit: Also executing the select in a separate thread does the trick. But that can't be the solution. Am I trying to insert into the database too fast after closing previous statements?

Edit2: I came across a busy_timeout, which promised to make updates/queries wait for a specified amount of time before returning with SQLITE_BUSY. I tried setting the busy timeout like so:

if(jdbcConn.prepareStatement("PRAGMA busy_timeout = 30000").execute()) {
    jdbcConn.commit();
}

The executeUpdate() function still immedeiately returns with SQLITE_BUSY.

Upvotes: 1

Views: 1052

Answers (1)

M. Folte
M. Folte

Reputation: 131

I'm dumb. I was so thrown off by the fact that removing the select statement worked (still not sure why that worked, probably bad timing) that I missed a different thread using the same file.

Made both threads use the same java.sql.Connection and everything works fine now.

Thank you for pushing me in the right direction @GordThompson. Wasn't aware of the jdbc:sqlite::memory: option which led to me finding the issue.

Upvotes: 1

Related Questions